MySQL Bugs: #7351: incomplete result, outer query with NULL <> ALL (empty subquery)

Bug #7351	incomplete result, outer query with NULL <> ALL (empty subquery)
Submitted:	16 Dec 2004 13:15	Modified:	20 Jan 2008 21:41
Reporter:	Matthias Leich	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
test case derived from NIST test script dml079
#      TEST:0453 NULL with empty subquery of ALL, SOME, ANY

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( f1 BIGINT );
INSERT INTO t1 SET f1= NULL;
INSERT INTO t1 SET f1= 1;
CREATE TABLE t2 ( f1 BIGINT );
SELECT f1 FROM t1
WHERE  f1 <> ALL ( SELECT f1 FROM t2);
f1
1
NIST expects, that the record containing NULL is also selected.
MySQL shows this incomplete result in version 4.1 and 5.0 .

The behaviour of the MySQl server is not covered by the manual:
13.1.8.4 Subqueries with ALL
<cut>
Finally, if table t2 is empty, the result is TRUE. You might think the
result should be UNKNOWN, but sorry, it's TRUE. So, rather oddly, 
the following statement is TRUE when table t2 is empty:

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
...

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 4.1 ChangeSet@1.2092.1.86, 2004-12-16
        Version 5.0 ChangeSet@1.1746, 2004-12-16

How to repeat:
Please execute the statements above

ChangeSet
  1.2128 04/12/25 19:17:57 igor@rurik.mysql.com +4 -0
  subselect.result, subselect.test:
    Added test cases for bug #7351.
  item_cmpfunc.cc:
    Fixed bug #7351: incorrect result for a query with a
    subquery returning empty set.
    If in the predicate v IN (SELECT a FROM t WHERE cond)
    v is null, then the result of the predicate is either
    INKNOWN or FALSE. It is FALSE if the subquery returns
    an empty set.
  item_subselect.cc:
    Fixed bug #7351: incorrect result for a query with a
    subquery returning empty set.
    The problem was due to not a quite legal transformation
    for 'IN' subqueries. A subquery containing a predicate
    of the form
    v IN (SELECT a FROM t WHERE cond)
    was transformed into
    EXISTS(SELECT a FROM t WHERE cond AND (a=v OR a IS NULL)).
    Yet, this transformation is valid only if v is not null.
    If v is null, then, in the case when
    (SELECT a FROM t WHERE cond) returns an empty set the value
    of the predicate is FALSE, otherwise the result of the
    predicate is INKNOWN.
    The fix resolves this problem by changing the result
    of the transformation to
    EXISTS(SELECT a FROM t WHERE cond AND (v IS NULL OR (a=v OR a IS NULL)))
    in the case when v is nullable.
    The new transformation prevents applying the lookup
    optimization for IN subqueries. To make it still
    applicable we have to introduce guarded access methods.

This bug will not be fixed in 4.1

Fixed by fixes for BUG324127, BUG#8804. Changing status to duplicate.