Wrong result with NULL >= ALL (<empty result>) in maria-5.3

[Philip Stoev]

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:

| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
| 1 | PRIMARY | t4 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
| 2 | SUBQUERY | t1 | system | NULL | NULL | NULL | NULL | 0 | const row not found |

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The result is correct because it is LEFT JOIN and it put NULL if there is no pair on other table. If you remove 'LEFT' everything works as it should.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

in 5.1 there was bug when NULL <op> ALL (<empty set>) returned NULL instead of TRUE, it is fixed on previous week, so 5.1, 5.2 Maria DB could return wrong results.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The bug is LP BUG#780386, it is fixed in 5.3 and it should be decided where to fix it 5.1 or 5.2.

[Philip Stoev (pstoev-askmonty)]

Sorry about this bug you are right. I must have spaced out.