Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria table

[Elena Stepanova]

The following query

SELECT (
        SELECT b FROM t2
        WHERE b = a
            OR EXISTS (
                SELECT c FROM t3
                WHERE c = b ) )
FROM t1;

returns different results in 5.3 comparing to 5.2, MySQL-5.5.19, PostreSQL-8.4 and 5.3-exists2in with exists_to_in=ON.
Table t2 contains 1 row and must be Aria or MyISAM.

Result in 5.3:
1
1

Result in 5.2.10, MySQL-5.5.19, PostreSQL, 5.3-exists2in with exists_to_in=ON:
1
NULL

I assume that the latter is the correct result, although it needs to be confirmed.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-20 12:13:47 +0400
build-date: 2011-12-23 23:14:14 +0400
revno: 3367
branch-nick: maria-5.3

Minimal optimizer_switch: in_to_exists=on or materialization=on (otherwise the query doesn't run)
Full 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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN from 5.3 (presumably wrong result):

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 100.00
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2
Note 1003 select <expr_cache><`test`.`t1`.`a`>((select 1 from `test`.`t2` where ((1 = `test`.`t1`.`a`) or <expr_cache><1>(exists(select `test`.`t3`.`c` from `test`.`t3` where (`test`.`t3`.`c` = 1)))))) AS `(
SELECT b FROM t2
WHERE b = a
OR EXISTS (
SELECT c
FROM t3
WHERE c = b ) )` from `test`.`t1`

EXPLAIN from 5.2 (presumably correct result):

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 100.00
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2
Note 1003 select (select '1' from `test`.`t2` where (('1' = `test`.`t1`.`a`) or exists(select `test`.`t3`.`c` from `test`.`t3` where (`test`.`t3`.`c` = '1')))) AS `(
SELECT b FROM t2
WHERE b = a
OR EXISTS (
SELECT c
FROM t3
WHERE c = b ) )` from `test`.`t1`

EXPLAIN from 5.3-exists2in with exists_to_in=ON (presumably correct result):

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1100.00
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2
Note 1003 select <expr_cache><`test`.`t1`.`a`>((select 1 from `test`.`t2` where ((1 = `test`.`t1`.`a`) or <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`t3`.`c` from `test`.`t3` where (<cache>(1) = `test`.`t3`.`c`))))))) AS `(
SELECT b FROM t2
WHERE b = a
OR EXISTS (
SELECT c
FROM t3
WHERE c = b ) )` from `test`.`t1`

Test case:

CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1),(5);

# t2 must be MyISAM or Aria and contain 1 row
CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1);

CREATE TABLE t3 ( c INT );
INSERT INTO t3 VALUES (4),(5);

SELECT (
        SELECT b FROM t2
        WHERE b = a
            OR EXISTS (
                SELECT c
                FROM t3
                WHERE c = b ) )
FROM t1;

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

Item_in_optimizer::val_int() executed only once (could be right because the subquery depends on a constant).

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

The error is in main 5.3.

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

The problem was that when we JOIN::outer_ref_cond we did not take static tables as known.
  For usual expression it is OK because Item_field::used_tables() return 0 in case of static tables
  but subquery return mask of the statuc tables used in it. So expression with subqueries goes nowhere
  (it was not in JOIN::exec_const_cond and was not in JOIN::outer_ref_cond => was just skipped).

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

SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
( SELECT b FROM t2 WHERE b = a OR rand() * 0)
1
1

Also wrong.