Вопрос по mysql – Почему ключевое слово MYSQL IN не учитывает значения NULL

9

Я использую следующий запрос:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and Error not in ('Timeout','Connection Error');

Удивительно, но этот оператор не включает в себя строки, имеющие значение Error в качестве NULL. Мое намерение состоит в том, чтобы фильтровать только те строки, в которых значение Error равно «a timeout». (или) «Ошибка соединения». Мне нужно дать дополнительное условие (ИЛИ ошибка равна NULL), чтобы получить правильный результат.

Почему MYSQL отфильтровывает результаты со значениями NULL? Я думал, что ключевое слово IN вернет логический результат (1/0), и теперь я понимаю, что некоторые ключевые слова MYSQL не возвращают логические значения, он также может возвращать NULL .... но почему он обрабатывает NULL как особый?

Ваш Ответ

6   ответов
0

но я хочу проиллюстрировать другой пример:

Я согласен с @Wagner Bianchi в [2] на этом форуме, когда он говорит: & Л; & л; Это хитрость при работе с данными и подзапросами & gt; & gt;

Более того, это НЕ должно быть таким поведением, я думаю, что дизайнеры Mysql ошибаются, когда они принимают это решение, документированное в [1]. Дизайн должен быть другим. Позвольте мне объяснить: вы знаете, что при сравнении

select (2) not in (1, 4, 3);
    you will get:
        +----------------------+
        | (2) not in (1, 4, 3) |
        +----------------------+
        |                    1 |
        +----------------------+
        1 row in set (0.00 sec)

НО, если в списке есть хотя бы один NULL, тогда:

select (2) not in (1, NULL, 3);
    throws:
        +-------------------------+
        | (2) not in (1, NULL, 3) |
        +-------------------------+
        |                    NULL |
        +-------------------------+
        1 row in set (0.00 sec)
    This is pretty absurd.

Мы не первые, кого это смущает. Смотри [2]

Рекомендации:

[1] http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

[2] http://blog.9minutesnooze.com/sql-not-in-subquery-null/comment-page-1/#comment-86954

1

то ноль не равен нулю. Вы всегда должны явно обрабатывать ноль.

1

IN возвращает трехвалентныйBOOLEAN (который принимаетNULL как значение).NOT IN возвращает трехвалентное отрицаниеINи отрицаниеNULL этоNULL.

Представьте, что у нас есть таблица со всеми числами из1 в1,000,000 вid и этот запрос:

SELECT  *
FROM    mytable
WHERE   id IN (1, 2, NULL)

или его эквивалент:

SELECT  *
FROM    mytable
WHERE   id = ANY
             (
             SELECT  1
             UNION ALL
             SELECT  2
             UNION ALL
             SELECT  NULL
             )

Предикат возвращаетсяTRUE за1 а также2 а такжеNULL для всех других значений, так1 а также2 возвращаются.

В противоположность:

SELECT  *
FROM    mytable
WHERE   id NOT IN (1, 2, NULL)

, или же

SELECT  *
FROM    mytable
WHERE   id <> ALL
             (
             SELECT  1
             UNION ALL
             SELECT  2
             UNION ALL
             SELECT  NULL
             )

предикат возвращаетсяFALSE за1 а также2 а такжеNULL для всех других значений, поэтому ничего не возвращается.

Обратите внимание, что логическое отрицание не только меняет оператора (= в<>), но квантификатор тоже (ANY вALL).

1

IN возвращаетсяNULL если выражение на левой сторонеNULL, Для того, чтобы получитьNULL значения, вы должны сделать:

'2012-05-28 15:34:02.403504' and (Error not in ('Timeout','Connection Error') or Error is null);
Да, я упоминал, что это сам вопрос. Я просто хотел знать, почему это странное поведение? Never Back Down
23

Error not in ('Timeout','Connection Error');

семантически эквивалентно:

Error <> 'TimeOut' AND Error <> 'Connection Error'

Правила о нулевом сравнении также применимы к IN. Поэтому, если значение Error равно NULL, база данных не может сделать выражение истинным.

Чтобы исправить, вы можете сделать это:

COALESCE(Error,'') not in ('Timeout','Connection Error');

Или еще лучше:

Error IS NULL OR Error not in ('Timeout','Connection Error');

Или еще лучше:

 CASE WHEN Error IS NULL THEN 1
 ELSE Error not in ('Timeout','Connection Error') THEN 1
 END = 1

OR не закорачивает, CASE может как-то закорачивать ваш запрос

Возможно, конкретный пример мог бы показать, почемуNULL NOT IN expression ничего не возвращает:

Учитывая эти данные:http://www.sqlfiddle.com/#!2/0d5da/11

create table tbl
(
  msg varchar(100) null,
  description varchar(100) not null
  );


insert into tbl values
('hi', 'greet'),
(null, 'nothing');

И вы делаете это выражение:

select 'hulk' as x, msg, description 
from tbl where msg not in ('bruce','banner');

Это выведет "привет" только.

NOT IN переводится как:

select 'hulk' as x, msg, description 
from tbl where msg <> 'bruce' and msg <> 'banner';

NULL <> 'bruce' не может быть определено, даже не верно, даже не ложно

NULL <> 'banner' не может быть определено, даже не верно, даже не ложно

Таким образом, выражение с нулевым значением эффективно разрешается в

can't be determined AND can't bedetermined

Фактически, если ваша СУБД поддерживает логическое значение в SELECT (например, MySQL, Postgresql), вы можете понять, почему:http://www.sqlfiddle.com/#!2/d41d8/828

select null <> 'Bruce' 

Это возвращает ноль.

Это также возвращает ноль:

select null <> 'Bruce' and null <> 'Banner'

Учитывая, что вы используетеNOT IN, который в основном является выражением AND.

NULL AND NULL

Результаты к NULL. Так что вы делаете:http://www.sqlfiddle.com/#!2/0d5da/12

select * from tbl where null

Ничего не будет возвращено

Да, я знаю, что есть обходной путь. Мне просто интересно узнать, почему это странное поведение? Never Back Down
А такжеIN / NOT IN, семантически эквивалентноV = 'Alpha' OR V = 'Beta', V <> 'Alpha' AND V <> 'Beta' соответственно, следовательно, правила о NULL все еще применяются
Это не странно, это даже в ANSI SQL. Думайте о NULL как о подстановочном знаке, вы не можете утверждать, что NULL & lt; & gt; & APOS; Привет & APOS ;; иногда NULL должен быть заменен на реальное значение. Вот как я на это смотрю. NULL имеет особое значение в базе данных
Возможно, неправильный перевод лежит на стороне питона. Я проверю язык, который поддерживает переменную Nullable
Спасибо, Майкл Буэн ... Я использую python с mysql.python возвращает 0/1 для операторов AND, OR, IN, но Mysql возвращает NULL Never Back Down
0

Ответ был правильным для моего случая, но позвольте мне упростить причину.

@ Майкл говорит в своем посте:

Error not in ('Timeout','Connection Error');

is semantically equivalent to:

Error <> 'TimeOut' AND Error <> 'Connection Error'

Rules about null comparison applies to IN too. So if the value of Error is NULL, the database can't make the expression true.

И в [1] я нашел это предложение, которое подтверждает его самое важное утверждение для понимания, почему IN терпит неудачу с NULL. В спецификациях («спецификации») в [1] вы будете: «Если один или оба аргумента равны NULL, результатом сравнения будет NULL, за исключением NULL-safe & lt; = & gt; оператор сравнения равенства. & quot;

Так что да, дело в том, что Mysql теряется в таком случае. Я думаю, что разработчики Mysql не должны были этого делать, потому что когда я сравниваю 2 с NULL, Mysql ДОЛЖЕН видеть, что они РАЗНЫЕ, а не просто выдают ошибочные результаты. Например, я сделал:

select id from TABLE where id not in (COLUMN WITH NULLS);

тогда он бросает пустые результаты. НО. Если я сделаю

select id from TABLE where id not in (COLUMN WITH OUT NULLS);

это показывает правильный результат. Поэтому при использовании оператора IN вы должны отфильтровывать NULL. Это нежелательное поведение для меня как пользователя, но это задокументировано в спецификациях в [1]. Я думаю, что языки и технологии должны быть более простыми, в том смысле, что вы должны иметь возможность УСТАВИТЬ без необходимости читать спецификации. И действительно, 2 отличается от NULL, я должен отвечать за контроль и устранение ошибок более высокого уровня абстракции, но MySQL ДОЛЖЕН выдавать ЛОЖНЫЙ результат при сравнении NULL с конкретным значением.

Ссылки на спецификации: [1]http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

Похожие вопросы