On Informix Dynamic Server Version 14.10.FC5WE I observe a strange behavior where a query returns a result, but shouldn't. I reduced the problem to the following example as best as I could; the example is brittle...
CREATE TEMP TABLE xxx_main (foo INT PRIMARY KEY NOT NULL);
CREATE TEMP TABLE xxx_sub (foo INT NOT NULL); -- Notice: No PRIMARY KEY
INSERT INTO xxx_main (foo) VALUES (1);
-- Notice that `sub` remains empty
SELECT sub1.bar -- The literal from the subquery; but since `sub1` has no rows, it should be `NULL`
FROM xxx_main
LEFT JOIN (SELECT xxx_sub.foo,
"foobar" AS bar
FROM xxx_sub) AS sub1 ON sub1.foo = xxx_main.foo
LEFT JOIN xxx_sub AS sub2 ON sub2.foo = xxx_main.foo
WHERE xxx_main.foo = 1
AND sub1.foo IS NULL
My expectation for the query is to return a single row with NULL as the only column.
The actual result is one row with a value of "foobar", which is the literal value from the sub1 subquery. But sub1 can't have any values, because the filter sub1.foo IS NULL prevents any rows from being joined, and sub being empty anyway. So where is "foobar" coming from? Am I confusing myself?
Even more puzzling: Modifying the query or the table layout in ways that should not have any effect causes the query to yield the expected result. Among other things, I observed this when removing the sub2-join in the query, defining sub.foo as a primary key in the table definition (with sub remaining empty), adding a (useless) GROUP BY-subquery, modifying the name foo AS oof in the subquery, and any arithmetic in the subquery (xxx_sub.foo + 1). All this leads me to believe that this is a SQL-compiler/-optimizer bug in Informix...