Глюк с not in () или <>

[chaos]

Наткнулся случайно на такой глюк:
имеются две таблицы:

CREATE TABLE TEST1 (
TEST1_ID INTEGER NOT NULL,
TEST1_NAME VARCHAR(10)
);
ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST1 PRIMARY KEY (TEST1_ID);


CREATE TABLE TEST2 (
TEST2_ID INTEGER NOT NULL,
TEST1_ID INTEGER,
TEST2_NAME VARCHAR(10)
);
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (TEST2_ID);
ALTER TABLE TEST2 ADD CONSTRAINT FK_TEST2_1 FOREIGN KEY (TEST1_ID) REFERENCES TEST1 (TEST1_ID);

INSERT INTO TEST1 VALUES (1,'11111');
INSERT INTO TEST2 VALUES (1,1,'11111');
INSERT INTO TEST2 VALUES (2,null,'22222');

Запрос
SELECT * FROM Test2 WHERE test2.test1_id not in (1) /*или test2.test1_id <> 1*/ не возвращает ничего

Запрос
SELECT * FROM test2 WHERE test2.test1_id not in (SELECT test1.test1_id FROM test1) возвращает 1 строку (как вроде и должен)

Стоит FireBird 1.5 Classsic

Наверняка с такой фишкой уже сталкивались и эта тема обсуждалась. К сожалению, ничего не нашел (видимо искал плохо).

[kdv]

SELECT * FROM Test2 WHERE test2.test1_id not in (1)

NULL <> остальным значениям.
поэтому not in (1) не учитывает null.
по идее здесь надо добавить проверку на null.
насколько это соответствует стандарту, не знаю.

[chaos]

NULL <> остальным значениям.
поэтому not in (1) не учитывает null.

Я заметил, но SELECT test1.test1_id FROM test1 возвращает тот же набор значений, но в этом случае с null никаких проблем не возникает.

по идее здесь надо добавить проверку на null.
насколько это соответствует стандарту, не знаю.

Ну вот такой вариант вроде прокатывает -
SELECT * FROM Test2 WHERE (test2.test1_id <> 1 OR test2.test1_id is Null)

Но как это это искусственно, на мой взгляд. Только чтобы проблему обойти.

А может влияние оказывать то, что планы по разному строятся?
для SELECT * FROM Test2 WHERE test2.test1_id not in (1)
PLAN (TEST2 NATURAL)

для SELECT * FROM test2 WHERE test2.test1_id not in (SELECT test1.test1_id FROM test1)
PLAN (TEST1 INDEX (PK_TEST1)) PLAN (TEST2 NATURAL)
???

[dimitr]

Я заметил, но SELECT test1.test1_id FROM test1 возвращает тот же набор значений, но в этом случае с null никаких проблем не возникает.

NOT IN (подзапрос) работает неправильно. Исправлено в 2.0.

[hvlad]

Запрос
SELECT * FROM Test2 WHERE test2.test1_id not in (1) /*или test2.test1_id <> 1*/ не возвращает ничего

И это правильно

Запрос
SELECT * FROM test2 WHERE test2.test1_id not in (SELECT test1.test1_id FROM test1) возвращает 1 строку (как вроде и должен)

А это - старая бага, iirc

Стоит FireBird 1.5 Classsic

На 2-ке проверь

[Andrew Sagulin]

SELECT * FROM Test2 WHERE test2.test1_id not in (1)

NULL <> остальным значениям.
насколько это соответствует стандарту, не знаю.

Соответствует полностью:
a NOT IN(x1) ::= NOT(a = x1), поэтому NULL NOT IN(1) ::= NOT(NULL = 1) ::= NULL.

[chaos]

Стоит FireBird 1.5 Classsic

На 2-ке проверь

Лежит у нас сейчас сервер тестовый, на котором 2.0 гоняем. Поэтому не мог проверить перед тем как писать сюда. Впрочем, как написали выше - в 2.0 исправили вроде все.