Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 where not in (...) и NULL. Почему так?  [new]
Dima T
Member

Откуда:
Сообщений: 15017
Такой код:
create table #t1 (ni int null)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)

create table #z1 (ni int)
insert into #z1 values (1)
insert into #z1 values (3)
insert into #z1 values (5)

select * from #z1 where ni not in (select ni from #t1)
insert into #t1 values (NULL)
select * from #z1 where ni not in (select ni from #t1)

drop table #z1
drop table #t1

select в первом случае возвращает одну запись (5), а после добавления NULL - ни одной.
Почему так? Это где-то описано в стандарте SQL?

select @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
4 дек 14, 15:14    [16950166]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Dima T,

not in (null) что должен вернуть?
4 дек 14, 15:19    [16950193]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Dima T, открываете официальную справку по IN и видите выделенное предупреждение:

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN.
4 дек 14, 15:20    [16950210]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Dima T
Member

Откуда:
Сообщений: 15017
Владислав Колосов
Dima T,

not in (null) что должен вернуть?

Всё. Я так думал. Вот и спрашиваю что не так я понимаю.
4 дек 14, 15:21    [16950214]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Dima T
Member

Откуда:
Сообщений: 15017
Shakill, спасибо.
4 дек 14, 15:28    [16950263]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Почему всё? Он вернет "неизвестно", а такое значение не попадает в результат. В результат попадают только достоверные значения.
4 дек 14, 15:58    [16950535]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Владислав Колосов
Почему всё? Он вернет "неизвестно", а такое значение не попадает в результат. В результат попадают только достоверные значения.
Этот пример неинтересен.
А вот если кроме одного NULL в списке тысяча значений, которым удовлетворяет NOT IN(),
то всё равно результат будет UNKNOWN. Ибо логически неизвестно, может NULL, представляющий
понятие "UNKNOWN", "включает в себя" запрещённое значение, а может нет.
4 дек 14, 16:11    [16950662]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
JeStone
Member

Откуда:
Сообщений: 248
Dima T,
Все дело в особенностях троичной логики.
Почему так происходит даже на хабре писали. Вот ссылочка
Раздел "Операторы IN и NOT IN" с разложением на бинарное дерево
4 дек 14, 16:11    [16950664]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
iap
Владислав Колосов
Почему всё? Он вернет "неизвестно", а такое значение не попадает в результат. В результат попадают только достоверные значения.
Этот пример неинтересен.
А вот если кроме одного NULL в списке тысяча значений, которым удовлетворяет NOT IN(),
то всё равно результат будет UNKNOWN. Ибо логически неизвестно, может NULL, представляющий
понятие "UNKNOWN", "включает в себя" запрещённое значение, а может нет.


ну это примерно как конкатенировать обычную строку и NULL
4 дек 14, 16:14    [16950692]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
JeStone
Dima T,
Все дело в особенностях троичной логики.
Почему так происходит даже на хабре писали. Вот ссылочка
Раздел "Операторы IN и NOT IN" с разложением на бинарное дерево
Можно и Ицика Бен-Гана почитать.
Он то же самое очень доходчиво рассказал словами
4 дек 14, 16:16    [16950707]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
iap, да-с проблема бочки мёда и ложки дёгтя :)
4 дек 14, 16:29    [16950816]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
все регулируется сетами
set ansi_nulls off/on
4 дек 14, 18:15    [16951649]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Змей
все регулируется сетами
set ansi_nulls off/on

И вы можете это показать для приведенного в начале скрипта ?
4 дек 14, 18:16    [16951655]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
На всякий случай.
автор
В будущей версии параметр SQL Server ANSI_NULLS всегда будет иметь значение ON, а приложения, явно присваивающие ему значение OFF, будут вызывать ошибку. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

SET ANSI_NULLS
Не знаю, когда поменяют, но лучше вообще избегать этой конструкции. Она и на планы может влиять и вообще, вносить неразбериху.
4 дек 14, 18:21    [16951682]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Glory,

а чего показывать то?

---
set ansi_nulls on
select * from #z1 where ni not in (select ni from #t1)
set ansi_nulls off
select * from #z1 where ni not in (select ni from #t1)
4 дек 14, 19:07    [16951949]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Змей,

Не смотря на то, что по факту вы правы, вы оказываете ТС медвежью услугу, думаю, Glory намекает именно на это.
4 дек 14, 20:36    [16952315]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Dima T
Member

Откуда:
Сообщений: 15017
Проблема легко решается без всяких SEТ`ов
select * from #z1 where ni not in (select ni from #t1 where ni is not NULL)


Просто надо быть в курсе что есть такая багофича и учитывать. Теперь я в курсе.
5 дек 14, 11:32    [16954253]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Dima T
Просто надо быть в курсе что есть такая багофича и учитывать.

Это багофича называется троичная логика.
5 дек 14, 11:36    [16954289]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Сия тема дает важный урок - прежде, чем строить предположения и делать заключения, необходимо собрать по вопросу как можно больше информации.

not exists(), кстати, гарантирует результат без знания о NULLABLE сравниваемого значения.
5 дек 14, 11:45    [16954363]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Dima T
Проблема легко решается без всяких SEТ`ов
select * from #z1 where ni not in (select ni from #t1 where ni is not NULL)



Просто надо быть в курсе что есть такая багофича и учитывать. Теперь я в курсе.
Владислав прав: надо всегда использовать NOT EXISTS вместо NOT IN.
5 дек 14, 11:59    [16954469]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Dima T
Member

Откуда:
Сообщений: 15017
JeStone
Dima T,
Все дело в особенностях троичной логики.
Почему так происходит даже на хабре писали. Вот ссылочка
Раздел "Операторы IN и NOT IN" с разложением на бинарное дерево

Почитал, ясности не добавило. Там просто констатация факта: "IN выберет совпавшие кроме NULL, NOT IN ничего не выберет"
Про троичную в курсе, но нестыковка получается:
для выборки с IN, например 1 in (1, NULL), два результата сравнения: есть и неизвестно - включаем 1 в результат.
для выборки с NOT IN, например 2 not in (1, NULL), два результата сравнения: нет и неизвестно - не включаем 2 в результат.
Чем "есть" отличается "нет"?

ИМХУ данное поведение просто кем-то надумано и заявлено как аксиома.
5 дек 14, 12:10    [16954527]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Dima T
Чем "есть" отличается "нет"?

bol - IN
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results
5 дек 14, 12:16    [16954585]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Dima T
Про троичную в курсе, но нестыковка получается:
для выборки с IN, например 1 in (1, NULL), два результата сравнения: есть и неизвестно - включаем 1 в результат.
для выборки с NOT IN, например 2 not in (1, NULL), два результата сравнения: нет и неизвестно - не включаем 2 в результат.
Чем "есть" отличается "нет"?


в случае с IN мы точно знаем, что одно из сравнений истинно. этого уже достаточно, чтобы сказать, что условие истинно. true OR unknown = true.
в случае с NOT IN мы точно знаем, что один из результатов - нет, а вот при сравнении с NULL результат получается "неизвестно". поэтому, никаких оснований считать результат истинным нет. false AND unknown = unknown.
5 дек 14, 12:16    [16954586]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Dima T
ИМХУ данное поведение просто кем-то надумано и заявлено как аксиома.

Потому что вы опять из троичной логики пытатесь сделать двоичную
2 not in NULL - это не "истина" и не "ложь". Это "неизвестно"
5 дек 14, 12:19    [16954618]     Ответить | Цитировать Сообщить модератору
 Re: where not in (...) и NULL. Почему так?  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Dima T,

на данном форуме, кстати, есть множество тем по этому поводу.
Вот, например, вспоминаю те, в которых сам участвовал:
Не работает ... where in (select a ....
Неожиданное поведение NOT IN с пустыми значениеми (NULL)
5 дек 14, 12:25    [16954664]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить