And you thought OR was commutative…
I came across this at dbdebunk (ON THE NOTHING THAT’S WRONG WITH NULLS with Hugh Darwen, Fabian Pascal). Couldn’t let that go so I tested it under 9i (9.1) and 10g (10.2) and got identical results. The last two statements are logically equivalent, but gives different results!
SQL> create type point as object (x real, y real);/
Type created.
SQL> create table t (p point);
Table created.
SQL> insert into t values (point(null, null));
1 row created.
SQL> insert into t values (point(1, null));
1 row created.
SQL> insert into t values (point(1, 2));
1 row created.
SQL> select * from t;
P(X, Y)
——————————————————————————–
POINT(NULL, NULL)
POINT(1, NULL)
POINT(1, 2)
SQL> select * from t where p=p or not p=p;
P(X, Y)
——————————————————————————–
POINT(1, 2)
SQL> select * from t where not p=p or p=p;
P(X, Y)
——————————————————————————–
POINT(1, NULL)
POINT(1, 2)
According to CM, it looks like a bug. The filter predicates seem to be applied incorrectly by the optimiser:
where ( p=p ) or ( not p=p );
filter(“T”.”SYS_NC00003$”=”T”.”SYS_NC00003$” AND
“T”.”SYS_NC00002$”=”T”.”SYS_NC00002$” OR T.”P”T.”P”)
where ( not p=p ) or ( p=p );
filter(T.”P”T.”P” OR “T”.”SYS_NC00002$”=”T”.”SYS_NC00002$”)