Description:
In three-valued-logic, a predicate can only be one of (TRUE/FALSE/NULL) at a time. Therefore, the given "TRUE QUERY" union "NULL QUERY" should only return a subset of the "BASE QUERY". However, in SQL Server version 8.0.41, the former returned one more row than the latter.
At a closer look, the row `A` of t1.c2 is returned in the "TRUE QUERY", which should not be the case. `A` is not in `t2.c3`, therefore `c2 IN ( SELECT c3 FROM t2)` should never be evaluated to true for that specific query.
How to repeat:
CREATE DATABASE dt;
USE dt;
CREATE TABLE t1(c1 INT ,c2 TEXT ) ;
CREATE TABLE t2(c3 DOUBLE ,c4 INT) ;
INSERT t1(c1) VALUES (1);
INSERT t1(c1) VALUES (2);
INSERT t1(c1) VALUES (3);
INSERT t1(c2) VALUES ("A");
INSERT t2(c4,c3) VALUES (4, 0/-1e1);
INSERT t2(c3) VALUES (5);
INSERT t2(c4) VALUES (6);
INSERT t2(c4) VALUES (7);
SELECT c2 FROM t1; -- BASE QUERY: 4 rows
SELECT c2 FROM t1 WHERE ( c2 IN ( SELECT c3 FROM t2) ) -- TRUE QUERY: 1 row
UNION ALL
SELECT c2 FROM t1 WHERE ( c2 IN ( SELECT c3 FROM t2) ) IS NULL; -- NULL QUERY: 4 rows
Description: In three-valued-logic, a predicate can only be one of (TRUE/FALSE/NULL) at a time. Therefore, the given "TRUE QUERY" union "NULL QUERY" should only return a subset of the "BASE QUERY". However, in SQL Server version 8.0.41, the former returned one more row than the latter. At a closer look, the row `A` of t1.c2 is returned in the "TRUE QUERY", which should not be the case. `A` is not in `t2.c3`, therefore `c2 IN ( SELECT c3 FROM t2)` should never be evaluated to true for that specific query. How to repeat: CREATE DATABASE dt; USE dt; CREATE TABLE t1(c1 INT ,c2 TEXT ) ; CREATE TABLE t2(c3 DOUBLE ,c4 INT) ; INSERT t1(c1) VALUES (1); INSERT t1(c1) VALUES (2); INSERT t1(c1) VALUES (3); INSERT t1(c2) VALUES ("A"); INSERT t2(c4,c3) VALUES (4, 0/-1e1); INSERT t2(c3) VALUES (5); INSERT t2(c4) VALUES (6); INSERT t2(c4) VALUES (7); SELECT c2 FROM t1; -- BASE QUERY: 4 rows SELECT c2 FROM t1 WHERE ( c2 IN ( SELECT c3 FROM t2) ) -- TRUE QUERY: 1 row UNION ALL SELECT c2 FROM t1 WHERE ( c2 IN ( SELECT c3 FROM t2) ) IS NULL; -- NULL QUERY: 4 rows