Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria table
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Oleksandr "Sanja" Byelkin |
Bug Description
The following query
SELECT (
SELECT b FROM t2
WHERE b = a
OR EXISTS (
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=
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>
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>
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 (
FROM t1;
Changed in maria: | |
status: | New → In Progress |
importance: | Undecided → Medium |
summary: |
Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria - table + table (exists2in) |
summary: |
Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria - table (exists2in) + table |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Item_in_ optimizer: :val_int( ) executed only once (could be right because the subquery depends on a constant).