From cf31ccc33c2f5ac5e2ae92c672bd54cc7ce63107 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 10 Jan 2012 23:26:00 +0200 Subject: Fix for LP BUG#908269 Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria table. Problem: When building the condition for JOIN::outer_ref_cond the optimizer forgot to take into account that this condition could depend on constant tables as well. --- mysql-test/r/subselect_no_semijoin.result | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'mysql-test/r/subselect_no_semijoin.result') diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index b92230cff3b..9b2370efd61 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -5905,6 +5905,30 @@ a 2009-02-02 set @@optimizer_switch=@old_optimizer_switch; drop table t1; +# +# LP BUG#908269 incorrect condition in case of subqueries depending +# on constant tables +# +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1),(5); +CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (4),(5); +SET optimizer_switch='subquery_cache=off'; +SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; +( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) +1 +NULL +SELECT ( SELECT b FROM t2 WHERE b = a OR b * 0) FROM t1; +( SELECT b FROM t2 WHERE b = a OR b * 0) +1 +NULL +SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; +( SELECT b FROM t2 WHERE b = a OR rand() * 0) +1 +NULL +drop table t1,t2,t3; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null; -- cgit v1.2.1