Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: хотя бы один из переменных = null

подскажите плиз есть ли одной функцией проверка на равенство одного из множеств NULL'ю
нужен аналог:
5 in (1,4,5,6) равен t

только надо что бы было так:
???(1,4,null,6) равнялся бы t
Ответ:
vsl
Legushka,

create or replace function is_not_distinct_from(a integer,b integer) returns boolean as $$
    select a is not distinct from b as result;
$$ language sql cost 1;
create operator === (leftarg=integer,rightarg=integer,procedure=is_not_distinct_from);


select null === any(array[5,6,7,null,8])

!
откуда мораль -- "is not distinct from" и "is distinct from" -- не операторы, а бред сивой кобылы.
если бы "IS" был оператором со значением "is not distinct from" а "IS NOT" -- оператором со значением "is distinct from", то всё было бы весьма стройно. а не наследовало бы ублюдочному стандарту, что "IS NULL" -- однопозиционный оператор. а не частный случай использования оператора "a IS b". появилась бы утраченная симметрия.
а если б их еще и по индексам... -- цены б не было.
а главное -- старый код бы не пострадал. почти :]
а то "is not distinct from" -- казалось бы именно оператор проверки совпадения с узлом индекса -- ан хрен, изволь руками расписывать вариации. иначе не подхватит.


то же с симметрией any() -- в 9.4. any и all допускались только у левого операнда, как его модификаторы. (это по поводу контры).
Вопрос: SQL запрос для поиска значений где Null как значение по умолчанию

Есть таблица с тремя с 4-мя стобцами с помощью которой определяеться результат (столбец RESULT) по 3-ем столбщам (А.В.С)
пример значений (может меняться):

ABCRESULT
a1b1c result1
a3b3null result2
anullc2 result3
anullnullresult4


Соответственно на входе имеем 3 произвольные значения Ai, Bi, Ci каждое из которых может быть как произвольной строкой так и NULL. Логика поиска следующая - если Ai != null то берем подмножество записей где A==Ai если не нашлось ни одной такой записи(где A==Ai) то тогда берем подмножество где A == null, а если Ai == null то сразу берем подмножество записей где A == null.
Таким же образом сужаем наше наше резульитруещее подмножество для Bi затем для Ci
Например для Ai = a, Bi = null, Ci=c3 результат result4
или
для Ai = a, Bi = null, Ci=c2 результат result3

Вопрос как подобную логику можно представить в виде SQL запроса (можно с диалектом oracle) , но использовать PL/SQL блоки функции и процедуры нельзя

заранее спасибо за советы
Ответ:
with sample_data as (
                     select 'a1' a,'b1' b,'c' c,'result1' result from dual union all
                     select 'a3','b3',null,'result2' from dual union all
                     select 'a',null,'c2','result3' from dual union all
                     select 'a',null,null,'result4' from dual
                    ),
         choices as (
                     select 1 id,'a' a,null b,null c from dual union all
                     select 2 id,'a' a,null b,'c2' c from dual
                    ),
              t1 as (
                     select  s.*,
                             c.id
                       from  sample_data s,
                             choices c
                       where c.a = s.a
                          or (
                                  s.a is null
                              and
                                  not exists (
                                              select 1 from sample_data ss where c.a = ss.a
                                             )
                             )
                    ),
              t2 as (
                     select  t1.*
                       from  t1,
                             choices c
                       where c.id = t1.id
                         and (
                                  c.b = t1.b
                              or (
                                      t1.b is null
                                  and
                                      not exists (
                                                  select 1 from sample_data ss where c.b = ss.b
                                                 )
                                 )
                             )
                    )
select  t2.*
  from  t2,
        choices c
  where c.id = t2.id
    and (
            c.c = t2.c
         or (
                 t2.c is null
             and
                 not exists (
                             select 1 from sample_data ss where c.c = ss.c
                            )
            )
        )
/

A  B  C  RESULT          ID
-- -- -- ------- ----------
a        result4          1
a     c2 result3          2

SQL>


SY.
Вопрос: NULL Шрёдингера

Всем привет.

Мне кажется, что я видел подобную тему, но ни автора, ни ключевых слов подобрать не могу.
Ерунда какая-то случилась:

CREATE TABLE x (
...
GENERATE_TOKEN VARCHAR2(1 BYTE) DEFAULT ON NULL 'N' NOT NULL,
...);


SQL> ALTER TABLE x MODIFY generate_token NULL;
ALTER TABLE x MODIFY generate_token NULL
*
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL


SQL> ALTER TABLE x MODIFY generate_token NOT NULL;
ALTER TABLE x MODIFY generate_token NOT NULL
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL


SQL> SELECT COUNT(*) FROM x WHERE generate_token IS NULL;

COUNT(*)
----------
0

SQL> SELECT DISTINCT DUMP(generate_token) FROM x;

DUMP(GENERATE_TOKEN)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=1: 89
NULL
Typ=1 Len=1: 78

SQL> spool off

Когда-то на таблице был check constraint novalidate вида (generate_token IN ('Y', 'N')). Сейчас его нет. Но NOT NULL-то никуда не делся. Как это?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Ответ:
SQL> select *
2 from dba_constraints
3 where table_name='X'
4 and search_condition_vc like '%GENERATE_TOKEN%';

OWNER CONSTRAINT_NAME CONSTRAINT TABLE_NAME SEARCH_CONDITION SEARCH_CONDITION_VC R_OWNER R_CONSTRAI DELETE_RUL STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNE INDEX_NAME INVALID VIEW_RELATED ORIGIN_CON_ID
---------- --------------- ---------- ---------- -------------------------------------------------- -------------------------------------------------- ---------- ---------- ---------- ---------- --------------- --------------- ---------- --------------- --- ---- -------------------- ---------- ---------- ---------- --------------- -------------
PSP SYS_C00175411 C X "GENERATE_TOKEN" IS NOT NULL "GENERATE_TOKEN" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 27-JUN-17 0

SQL> spool off

История такова:
1. сначала ALTER TABLE x ADD COLUMN generate_token VARCHAR2(1 BYTE) DEFAULT ON NULL 'N'
2. потом создавалась другая таблица, куда переливались данные из существующей X, и в этой другой таблице колонка generate_token уже была объявлена как DEFAULT ON NULL 'N' NOT NULL
3. INSERT as SELECT, DROP, RENAME. Здесь, наверное, и был direct path load.

Проблема с этого момента тянется.
Вопрос: LEFT JOIN и IS NULL

Работаю с таким запросом (как обычно кусок, то есть смысла в нем не много):

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS)
  (SELECT t0.key0 AS jkey0,
          t0.ZReport_dateReceiptDetail_ReceiptDetail AS jprop0,
          t0.key0 AS jprop1,
          CASE
              WHEN t1.System__CLASS_ZReport_receiptReturnDetail=40 THEN t1.System__CLASS_ZReport_receiptReturnDetail
              ELSE NULL
          END AS jprop2
   FROM ZReport_receiptDetail t0
   LEFT JOIN ZReport_receiptReturnDetail t1 ON t1.key0=t0.key0
   LEFT JOIN t_52 t6 ON t6.k0=t0.key0
   WHERE (t0.ZReport_departmentStoreReceiptDetail_ReceiptDetail=287879
          AND (CAST('2015-07-04 +03:00:00' AS date)>=t0.ZReport_dateReceiptDetail_ReceiptDetail)
          AND (t0.ZReport_dateReceiptDetail_ReceiptDetail>=CAST('2015-07-01 +03:00:00' AS date))
          AND (t6.p0 IS NULL)
          )
   )


Этот запрос выполняется аж 35 секунд. План следующий:

"Nested Loop Left Join  (cost=0.74..138648.25 rows=1 width=12) (actual time=204.376..34842.256 rows=95011 loops=1)"
"  Output: t0.key0, t0.zreport_datereceiptdetail_receiptdetail, t0.key0, CASE WHEN (t1.system__class_zreport_receiptreturndetail = 40) THEN t1.system__class_zreport_receiptreturndetail ELSE NULL::integer END"
"  Join Filter: (t1.key0 = t0.key0)"
"  Rows Removed by Join Filter: 408167233"
"  Buffers: shared hit=6321534 read=51736"
"  ->  Hash Left Join  (cost=0.74..138202.31 rows=1 (!) width=8) (actual time=203.486..882.099 rows=95011 loops=1)"
"        Output: t0.key0, t0.zreport_datereceiptdetail_receiptdetail"
"        Hash Cond: (t0.key0 = t6.k0)"
"        Filter: (t6.p0 IS NULL)"
"        Rows Removed by Filter: 2"
"        Buffers: shared hit=1000974 read=51680"
"        ->  Index Scan using zreport_datereceiptdetail_receiptdetail_key0_idx_zreport_receip on public.zreport_receiptdetail t0  (cost=0.44..138111.38 rows=24153 width=8) (actual time=203.432..858.052 rows=95013 loops=1)"
"              Output: t0.key0, t0.zreport_idreceiptdetail_receiptdetail, t0.zreport_receiptreceiptdetail_receiptdetail, t0.zreport_signedsumreceiptdetail_receiptdetail, t0.zreport_discountsumreceiptdetail_receiptdetail, t0.zreport_vatreceiptdetail_receiptd (...)"
"              Index Cond: (('2015-07-04'::date >= t0.zreport_datereceiptdetail_receiptdetail) AND (t0.zreport_datereceiptdetail_receiptdetail >= '2015-07-01'::date))"
"              Filter: (t0.zreport_departmentstorereceiptdetail_receiptdetail = 287879)"
"              Rows Removed by Filter: 1271341"
"              Buffers: shared hit=1000974 read=51679"
"        ->  Hash  (cost=0.20..0.20 rows=2 width=8) (actual time=0.022..0.022 rows=2 loops=1)"
"              Output: t6.k0, t6.p0"
"              Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"              Buffers: shared read=1"
"              ->  Seq Scan on public.t_52 t6  (cost=0.00..0.20 rows=2 width=8) (actual time=0.017..0.018 rows=2 loops=1)"
"                    Output: t6.k0, t6.p0"
"                    Buffers: shared read=1"
"  ->  Seq Scan on public.zreport_receiptreturndetail t1  (cost=0.00..220.40 rows=4296 width=8) (actual time=0.001..0.158 rows=4296 loops=95011)"
"        Output: t1.key0, t1.zreport_receiptreceiptreturndetail_receiptreturndetail, t1.zreport_quantityreceiptreturndetail_receiptreturndetail, t1.zreport_batchreceiptreturndetail_receiptreturndetail, t1.zreport_skureceiptreturndetail_receiptreturndetail,  (...)"
"        Buffers: shared hit=5320560 read=56"
"Planning time: 0.329 ms"
"Execution time: 34846.358 ms"


Как видно из плана проблема вот в чем, когда Postgres видит A LEFT JOIN B WHERE B.field IS NULL, он при определении selectivity предиката B.field IS NULL, читает его из просто B.field, и не учитывает тот факт, что в A может быть очень много строк, в B очень мало строк, поэтому в результате LEFT JOIN'а почти все записи будут NULL и selectivity на самом деле будет гораздо выше. Соответственно единственный способ с этим бороться, который я пока вижу, это смотреть что если у нас не INNER JOIN + статистика JOIN'а поля с проверкой на NULL значительно меньше общей статистики (без этой проверки можно получить обратный эффект если B большое - в этом случае postgres правильно считает что selectivity низкое), добавлять к предикату B.field IS NULL что-то типа current_timestamp<>current_timestamp.

Тогда план будет нормальным:

"Hash Left Join  (cost=446.68..138888.83 rows=24032 width=12) (actual time=206.607..718.234 rows=95011 loops=1)"
"  Output: t0.key0, t0.zreport_datereceiptdetail_receiptdetail, t0.key0, CASE WHEN (t1.system__class_zreport_receiptreturndetail = 40) THEN t1.system__class_zreport_receiptreturndetail ELSE NULL::integer END"
"  Hash Cond: (t0.key0 = t1.key0)"
"  Buffers: shared hit=1001057 read=51653 written=1"
"  ->  Hash Left Join  (cost=0.74..138202.32 rows=24032 width=8) (actual time=205.697..698.073 rows=95011 loops=1)"
"        Output: t0.key0, t0.zreport_datereceiptdetail_receiptdetail"
"        Hash Cond: (t0.key0 = t6.k0)"
"        Filter: ((t6.p0 IS NULL) OR (now() <> now()))"
"        Rows Removed by Filter: 2"
"        Buffers: shared hit=1001001 read=51653 written=1"
"        ->  Index Scan using zreport_datereceiptdetail_receiptdetail_key0_idx_zreport_receip on public.zreport_receiptdetail t0  (cost=0.44..138111.38 rows=24153 width=8) (actual time=205.673..688.592 rows=95013 loops=1)"
"              Output: t0.key0, t0.zreport_idreceiptdetail_receiptdetail, t0.zreport_receiptreceiptdetail_receiptdetail, t0.zreport_signedsumreceiptdetail_receiptdetail, t0.zreport_discountsumreceiptdetail_receiptdetail, t0.zreport_vatreceiptdetail_receiptd (...)"
"              Index Cond: (('2015-07-04'::date >= t0.zreport_datereceiptdetail_receiptdetail) AND (t0.zreport_datereceiptdetail_receiptdetail >= '2015-07-01'::date))"
"              Filter: (t0.zreport_departmentstorereceiptdetail_receiptdetail = 287879)"
"              Rows Removed by Filter: 1271341"
"              Buffers: shared hit=1001001 read=51652 written=1"
"        ->  Hash  (cost=0.20..0.20 rows=2 width=8) (actual time=0.010..0.010 rows=2 loops=1)"
"              Output: t6.k0, t6.p0"
"              Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"              Buffers: shared read=1"
"              ->  Seq Scan on public.t_52 t6  (cost=0.00..0.20 rows=2 width=8) (actual time=0.008..0.009 rows=2 loops=1)"
"                    Output: t6.k0, t6.p0"
"                    Buffers: shared read=1"
"  ->  Hash  (cost=220.40..220.40 rows=4296 width=8) (actual time=0.904..0.904 rows=4296 loops=1)"
"        Output: t1.system__class_zreport_receiptreturndetail, t1.key0"
"        Buckets: 1024  Batches: 1  Memory Usage: 168kB"
"        Buffers: shared hit=56"
"        ->  Seq Scan on public.zreport_receiptreturndetail t1  (cost=0.00..220.40 rows=4296 width=8) (actual time=0.004..0.534 rows=4296 loops=1)"
"              Output: t1.system__class_zreport_receiptreturndetail, t1.key0"
"              Buffers: shared hit=56"
"Planning time: 0.358 ms"
"Execution time: 720.657 ms"


Но тут соответственно 2 вопроса :
a) Может есть более простой способ забороть проблему
б) Какой еще проверкой можно значительно повысить selectivity кроме как OR current_timestamp<>current_timestamp? OR 1<>1 и аналогичные уберет оптимизатор. А current_timestamp как мне кажется может overhead в крайних случаях давать.
Ответ:
Ivan Durak
автор
Seq Scan on public.t_52 t6 (cost=0.00..0.20 rows=2 width=8)

статистика по количеству записей - того. Тухлая. Там же не 2 ровса?????


Судя по

-> Seq Scan on public.t_52 t6 (cost=0.00..0.20 rows=2 width=4) (actual time=0.007..0.008 rows=2 loops=1)
Output: t6.k0
Filter: (t6.p0 IS NOT NULL)
Buffers: shared read=1
- именно два. Так что с статистикой все нормально.

PS: При пустой статистике по умолчанию считается 1000 в таблице.

--
Maxim Boguk
Вопрос: Запрос на изменения NULL

Добрый день.
Необходимо соединить строку из ячеек если в какой, то ячейки есть NULL необходимо его заменить на запятую.
Пробую использовать replace

К примеру

drop table #t1

create table #t1
(a varchar(10) null,
b varchar(10) null,
c varchar(10) null)

insert into #t1 (a,b,c) values ('1','2','3')

select a+','+replace(b,NULL,',')+','+c
 from #t1


Выводится результат NULL

а должно быть a,,c
Ответ: BKV88,

Если в описании функции или оператора не оговорено иного, то все выражения с участием NULL, дают NULL.
SELECT 1 + NULL
SELECT 'A' + NULL
SELECT CONCAT('A', NULL)
Вопрос: NOT NULL - значение переменной при вставке строки в таблицу передается как NULL

Доброго времени суток !

Версия БД 11.2.0.3

Имеем некую процедуру , в которой переменная l_sess_id принимает некое значение.
В редких случаях в момент инсерта этой l_sess_id в Not NUll - столбец получаю ошибку ORA-01400: невозможно вставить NULL в ....

На этот инсерт повесили exception , который логирует значение переменной l_sess_id при возникновении ORA=01400 .
Логирование показывает , что данная переменная непосредственно в момент инсерта имеет значение и никак не может быть NULL...

Никаких триггеров на таблице нет. Данная проблема проявляется временами. Никак не можем отловить причину такого поведения.
Известен ли какойто баг при инсерте когда переменная имеющая значение , при вставке передается как NULL?

Как отловить ? Кто что посоветует ?
Ответ: Может, проходит update по записи с уже пустым sess_id? Такое может быть если not null включали с novalidate.
Вопрос: Добавление ограничение NOT NULL к существующему типу поля

Всем привет!

Как изменить свойство поля с
start_km integer на start_km integer not null?

Данная штука увы (естественно) не прокатывает:

alter table xx_apr_speed_tr alter start_km type integer not null;


Вот это выдал FlameRobin при установке галочки <хочу not null>:

(*)
UPDATE XX_APR_SPEED_TR 
SET START_KM = ' ' 
WHERE START_KM IS NULL;
UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME = 'START_KM' AND RDB$RELATION_NAME = 'XX_APR_SPEED_TR';


Правильно ли я понимаю, что SET START_KM = ' ' заменяет уже существующие значения NULL на значения, которое мы укажем в ' '? А затем идет условие невозможности значения NULL в столбце START_KM?

Есть ли смысл - заморачиваться с (*), может просто удалить поле и заново его добавить?
Ответ:
guly2808

Данная штука увы (естественно) не прокатывает:

alter table xx_apr_speed_tr alter start_km type integer not null;



правильный способ появился только в Firebird 3.0

ALTER TABLE tablename
ALTER [COLUMN] colname [SET | DROP] NOT NULL
Вопрос: RETURN NULL в функции, возвращающей множество

Добрый день.
Приходится переводить древний проект с PostgreSQL версии 7.4 на версию 9.2
Большую часть явных ошибок уже исправлено.
Столкнулась с функциями, возвращающими множество (SETOF integer).
Раньше (в 7.4) в начале функции стояла проверка с возвращением NULL (компилятор пропускал ее, а сама функция и функция ее вызывающая корректно работали):

...
IF $2 IS NULL THEN
  RETURN NULL;
END IF;
...


Сейчас компилятор СУБД не воспринимает это для подобных функций. Рекомендует заменить на RETURN NEXT или RETURN QUERY.
Пробовала разные варианты, например RETURN NEXT NULL; RETURN;
Но логика работы с возвращаемым значением функции не отрабатывает. Подскажите как изменить условие, чтобы отработал ANY?
Результат возвращается и должен подставляться для проверки:
...
IF $3=ANY(SELECT * FROM func($1,$2)) THEN 
  RETURN true;
END IF;
Ответ: Спасибо за помощь, нашла способ отлаживать выполнение скриптов и данных к ним.

В данной ситуации, как и советует компилятор

для функций, возвращающих множество (например, SETOF integer)
в версии 7.4 использовалось RETURN NULL;
в версии 9.2 можно заменить на RETURN; или RETURN NULL; RETURN;


Я дальше буду переводить проект. Такая ситуация встречается еще в нескольких местах. И конечно нужно рассматривать данные, которые передаются таким функциям. Ибо как показала практика, если в массиве есть NULL и искомое значение, то не сможеет найти искомое значение. (см. и )
Вопрос: not in и null значение

where ROW_ID not in (178, 192, 183, 184, 186)

Эта команда исключает NULL значения, как их сохранить? В купе с or is null запрос выполняется непозволительно долго.

Спасибо!
Ответ:
qwwq
Hawkmoon
coalesce в роли nvl вам в помощь

9.17.2. COALESCE

COALESCE(value [, ...])
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:

where COALESCE(ROW_ID,0) not in (178, 192, 183, 184, 186)
вот не надо учить плохому.

вернее -- безмерно плохому.

вы не 2 величины сравниваете. а множество с величиной. поэтому лезущий преобразовывать множество неизвестной мощности, вместо расписывания величины -- ССЗБ.


2ТС не стесняйтесь, распишите проблему полностью. иначе местныя гурии вам так и будут рецепты по прострелянию ноги в 7 местах одной пулей выдавать


Вот +1. Опишите проблему целиком включая запрос целиком и explain analyze варианта с not in и варианта с not in + null.
А то вам тут такого насоветуют что рады не будете. ;)

PS: т.е. ответ то конечно формально был верным.. но как всегда есть большое НО.

--
Maxim Boguk
Вопрос: Проектирование: есть ли смысл разрешать null-значения для "бизнес-данных"?

База, в в ней таблички. Каждая табличка имеет суррогатный ключ типа Int64.
Таблички связаны между собой с помощью FK - ограничений. Каждое поле, ссылающееся по FK на другую табличку, может быть как "nullable", так и "not null", в зависимости от особенностей FK - связи. Тут удобство null-ов не вызывает сомнений.
С полями, реализующими FK-связи, вопросов нет.
...
А еще есть поля, несущие уже "кондовую" смысловую нагрузку: Фамилия, Стоимость, Адрес...
Какой смысл разрешать для таких полей значение null?

Ну, можно придумать. Например, поле "Дата начала строительство". Пока оно null, строительство и не началось. Но ведь можно для этого задействовать какую-либо "волшебную константу", какой-нибудь
cast(0 as date)
...

Для строк использовать значение "пустая строка", для чисел - "0".

Null, конечно, сам по себе является такой "волшебной константой". Но ведь значение 0 (или "пустая строка") можно использовать в запросах наравне с остальными значениями, а с null-ами приходится кочевряжиться!

Вот и вопрос: есть ли смысл для "бизнес" - значений использовать типы с ограничениями
not null & (devault value = 0|'')
?

Не создаст ли сие каких-либо неудобств в дальнейшем?
Ответ: Юзер 01,

skyANA все точно описал.
При проектировании базы Вы на 100% не можете заложить все нужные константы на все случаи жизни для всех разных атрибутов всех разных сущностей.
Может конечно и сможете, но это потребует умопомрачительной работы, анализа и знания данных предметной области, причем с учетом перспективы будущего.
Т.е. есть большой шанс нарваться на то, что Ваша константа в один прекрасный момент вдруг станет не пустым значением для предметной области и бизнес-логики, со всеми вытекающими последствиями для системы.
Null умные люди специально придумали именно для того, чтобы указывать что значение неизвестно. Это значение ни с чем не пересечется, оно не равно никакому другому значению в предметной области.

Это как 0 - специальное число, которое до поры до времени числом не считали. Пока есть что считать, ноль нас мало интересует. Но чтобы обозначит как-то, что считать нечего, придумали 0. А в математ теориях это уже полноправное число и без него уже никак.