Not repeatable in maria-5.2,mysql-5.5.

The following WHERE condition: t2.c >= ALL ( SELECT c FROM t1 ) seems to evaluate to TRUE even though t1 is empty.

explain:

test case:

CREATE TABLE t1 ( c int) ;

CREATE TABLE t2 ( d int, c int) ;

CREATE TABLE t3 ( a int NOT NULL , c int) ;
INSERT INTO t3 VALUES (1,'x');

CREATE TABLE t4 ( a int NOT NULL , b int, c int) ;

SELECT t4.c, t2.c
FROM ( t3 LEFT JOIN t2 ON ( t2.d = t3.a ) )
LEFT JOIN t4 ON ( t2.c = t3.c )
WHERE t2.c >= ALL ( SELECT c FROM t1 )
OR t4.c = 'y';

returns (NULL,NULL) even though it should return an empty set since neither side of the OR predicate in the WHERE is TRUE.

optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:

bzr version-info
revision-id: <email address hidden>
date: 2011-07-22 23:47:28 -0700
build-date: 2011-07-28 10:59:33 +0300
revno: 3134
branch-nick: maria-5.3