Непонятная "двуличность" операции "=NULL"

← →
Bless © (2005-07-20 10:00) [0]

Есть таблица t1 (kod float, name char(10))
В ней буквально пара строк, в одной из которых kod= NULL
Написал процедуру
CREATE PROCEDURE [dbo].[vx] AS SELECT * FROM t1 where kod=NULL и в Query Analizer-е выполнил : exec vx select * from t1 where kod=NULL В результате выполнения команды EXEC получил одну строку, в результате работы команды SELECT - ни одной. На всякий случай посмотрел в профайлере : нигде при выполнении этого батча SET ANSI_NULLS не вызывается. Почему тогда такое разное поведение для одинаковых по сути операций?

← →
Bless © (2005-07-20 10:01) [1]

Забыл закрывающий тэг. Исправляюсь.

Есть таблица t1 (kod float, name char(10))
В ней буквально пара строк, в одной из которых kod= NULL
Написал процедуру
CREATE PROCEDURE [dbo].[vx] AS SELECT * FROM t1 where kod=NULL

и в Query Analizer-е выполнил :

exec vx select * from t1 where kod=NULL

В результате выполнения команды EXEC получил одну строку,
в результате работы команды SELECT - ни одной.
На всякий случай посмотрел в профайлере : нигде при выполнении этого батча
SET ANSI_NULLS не вызывается.
Почему тогда такое разное поведение для одинаковых по сути операций?

← →
evvcom © (2005-07-20 10:04) [2]

> kod=NULL

kod is NULL!

← →
Bless © (2005-07-20 10:32) [3]

evvcom[2]>

Да я знаю, что правильно писать IS NULL. Но ведь проблему это не отменяет, не так ли?
Там где я в примере в процедуре поставил =NULL на самом деле стоит
=@param, который оказался равным NULL. Результат работы процедуры лменя слегка удивил и я скопировал содержимое в Query Analizer, чтоб проверить и удивился еще больше.

Может вместо того, чтоб искать нелепости в моем примере, можешь сказать что-нибудь по сути?

← →
Ярослав (2005-07-20 10:36) [4]

Тебе уже ответили, с Null ничего сравнивать нельзя, почитай учебник по SQL, для этого есть is Null

← →
Nikolay M. © (2005-07-20 10:38) [5]

Читай бол по CREATE PROCEDURE. Что за манера все валить на "этот глупый компьютер"?

SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

← →
Nikolay M. © (2005-07-20 10:43) [6]

> Ярослав (20.07.05 10:36) [4]
> Тебе уже ответили, с Null ничего сравнивать нельзя

Не понимаю, зачем лепить такие ответы, если не понимаешь, в чем проблема.

USE pubs SET ANSI_NULLS ON SELECT * FROM discounts WHERE stor_id = NULL SELECT * FROM discounts WHERE stor_id IS NULL SET ANSI_NULLS OFF SELECT * FROM discounts WHERE stor_id = NULL SELECT * FROM discounts WHERE stor_id IS NULL

← →
Johnmen © (2005-07-20 10:44) [7]

>Bless © (20.07.05 10:01) [1]

Я подозреваю, что дело в разной интерпретации =NULL.
В случае процедуры этим занимется чисто сервер на основании настроек.
В случае QA это делает этот самый QA.

Но в любом случае правило "тринарной" логики говорит, что
любые сравнения чего бы то ни было с NULL дают False в терминах бинарной логики.

← →
Nikolay M. © (2005-07-20 10:47) [8]

> Johnmen © (20.07.05 10:44) [7]

Не угадал :)

← →
Johnmen © (2005-07-20 10:51) [9]

>Nikolay M. © (20.07.05 10:47) [8]
>Не угадал :)

Ну почему же, по-моему вполне угадал :-)

← →
Nikolay M. © (2005-07-20 10:54) [10]

> Johnmen © (20.07.05 10:51) [9]

А это что? :)
Все - неверно...

> В случае QA это делает этот самый QA.
>
> Но в любом случае правило "тринарной" логики говорит, что
> любые сравнения чего бы то ни было с NULL дают False в терминах
> бинарной логики.

← →
evvcom © (2005-07-20 10:56) [11]

> на самом деле стоит
> =@param, который оказался равным NULL

where (kod=@param) or ((@param is null) and (kod is null))

← →
Johnmen © (2005-07-20 10:57) [12]

>Nikolay M. © (20.07.05 10:54) [10]
>Все - неверно...

Ну разжуй же наконец, в чём неверность. А то и дальше буду заблуждаться назло друзьям...:))

← →
ANB © (2005-07-20 11:00) [13]

А еще есть функция IsNull, ее тоже удобно юзать в таких случаях. В принципе, в мсскуле = null ИНОГДА отрабатывает. Имхо, я бы на это отрабатывание не завязывался.

← →
Nikolay M. © (2005-07-20 11:18) [14]

> Johnmen © (20.07.05 10:57) [12]

QA не занимается никакими "интерпретациями" =NULL. Это всегда делает сервер, основываясь на значении ANSI_NULLS {ON | OFF}.
И сравнение "Х = NULL" не ВСЕГДА дает False, а зависит от значения того же ANSI_NULLS и Х. Собственно, в БОЛ все описано:

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.

Если есть под рукой МС СКЛ, запусти пример из [6] для наглядности.

> ANB © (20.07.05 11:00) [13]

Ты ветку читал? Или просто захотелось абы что запостить?

← →
Johnmen © (2005-07-20 11:33) [15]

>Nikolay M. © (20.07.05 11:18) [14]

Это-то всё известно, вот ты объясни это
exec vx select * from t1 where kod=NULL В результате выполнения команды EXEC получил одну строку, в результате работы команды SELECT - ни одной.

← →
Bless © (2005-07-20 11:37) [16]

Nikolay M.[5]
Гм... Все-равно непонятно. Может я не правильно перевожу.

Я так понял, что SQL Server сохраняет те настройки SET QUOTED_IDENTIFIER и SET ANSI_NULLS для хранимой процедуры, которые были на момент ее создания. Поэтому всякие последующие манипуляции с этими SET-ами в клиенских сессиях на выполнение хранимых процедур никакого влияния не оказывают. Но между созданием тестовой ХП и выполнением запроса я никаких SET ANSI_NULLS не запускал. Так что вроде бы эта настройка должна быть одинакова и для ХП и для запроса. Или нет?

P.S. А где я все валил на глупый компьютер?

← →
evvcom © (2005-07-20 11:46) [17]

> Bless © (20.07.05 11:37) [16]

Используй явные условия. Либо SET ANSI_NULLS прямо в процедуре (не знаю пройдет ли, поможет ли?), либо условия как в [11]. Иначе будешь ловить глюки и не знать, где искать.

← →
Bless © (2005-07-20 11:48) [18]

to Johnmen, Nikolay M.>

Johnmen прав. Дело в настройках.
В query Analizer-е в tools->options->connecting properties
стоит галочка напротив ansi nulls. Ее снятие привело к одинаковому поведению в обоих случаях.

Вот так вот :)

Да, чуть не забыл. Спасибо :)

evvcom[17]>
Если внимательно прочитать последнее предложение в [5], то становится понятно, что
SET ANSI_NULLS внутри процеруды не поможет. Кроме того мне не нужно решение, как это обойти, я и "сам с усам". :) Просто хотелось разобраться с тем, что показалось непонятным. Что, собственно, уже произошло :)

to Nikolay M.

Bless © (20.07.05 11:48) [18]
to Johnmen, Nikolay M.>
Johnmen прав. Дело в настройках.

Ты тоже, конечно прав. :) Потому что из [5] понятно, что настройки Query Analizer-а никак на хранимую процедуру не влияют.
Кстати, если б ты эту цитату BOL не привел, то я б ее неверное никогда не перевел. Это ж надо было такую тонкость засунуть в середину многостраничного топика, посвященного CREATE PROCEDURE.
Хоть бы красным выделили.

Привычное решение
where isnull(field,0)=0 или where isnull(field,"")=""
В зависимости от типа данных

> SET ANSI_NULLS внутри процеруды не поможет

а может и поможет, так как компилироваться она дальше, возможно, будет уже с новыми опциями.

Сорри опять влез не по теме..

> Johnmen © (20.07.05 11:33) [15]

Потому что разные значения ANSI_NULLS для ХП и селекта.

> Bless © (20.07.05 11:48) [18]
> Johnmen прав. Дело в настройках.

Johnmen ничего не говорил про настройки, тем более что дело не в них. То, что ты нашел в настройках, есть, по сути, выставление ANSI_NULLS для данной сессии и это делается аналогично тому, что я написал еще в 6-м постинге.

> Bless © (20.07.05 12:00) [20]
> to Nikolay M.
> Ты тоже, конечно прав. :)

Это стало понятно только к 20-му посту? :)

>Это стало понятно только к 20-му посту? :)

К 18-му. :)

А если б я догадался запустить Profiler ДО запуска Query Analizer-а, то прозрел бы сразу после 5-го, поскольку увидел бы SET ANSI_NULLS, а так возник еще вопрос в [17].

> Bless © (20.07.05 16:53) [25]
> >Это стало понятно только к 20-му посту? :)
>
> К 18-му. :)

Вот что значит талант все правильно объяснить (это я про себя) :)

Непонятная "двуличность" операции "=NULL" Найти похожие ветки