[DERBY-4385] Comparision with ALL (subquery) may give wrong result if constant is selected

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Duplicate
• Component/s: SQL
• Issue & fix info: Repro attached
• Bug behavior facts: Wrong query result
• Assignee: Unassigned
• Reporter: Bernt Johnsen
• Created: 22/Sep/09 14:54
• Updated: 21/Jan/11 17:52
• Resolved: 22/Sep/09 21:22

Description

Consider the following:

CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
CREATE TABLE t2 (i INT);
INSERT INTO t1 VALUES (1,1),(2,2);
INSERT INTO t2 VALUES (81);

The two following queries should give the same result:

SELECT * FROM t1 WHERE i < ALL (SELECT i FROM t2);
SELECT * FROM t1 WHERE i < ALL (SELECT 81 FROM t2);

But, they don't:

ij> SELECT * FROM t1 WHERE i < ALL (SELECT i FROM t2);
I |J
-----------------------
1 |1
2 |2

2 rows selected
ij> SELECT * FROM t1 WHERE i < ALL (SELECT 81 FROM t2);
I |J
-----------------------

0 rows selected
ij>

Issue Links

• duplicates: DERBY-4001 Sequence comparison with "ALL" does not yield correct results