Fix LP BUG#611622

Fix MySQL BUG#52344 - Subquery materialization: Assertion if subquery in on-clause of outer join

Original fix and comments from Oysten, adjusted for the different
subquery optimization in MariaDB.
"
Problem: If tables of an outer join are constant tables,
the associated on-clause will be evaluated in the optimization
phase. If the on-clause contains a query that is to be
executed with subquery materialization, this will not work
since the infrastructure for such execution is not yet set up.
      
Solution: Do not evaluate on-clause in optimization phase if
is_expensive() returns true for this clause.  This is how the
problem is currently avoided for where-clauses.  This works
because, Item_in_subselect::is_expensive_processor returns true
if query is to be executed with subquery materialization.
"
In addition, after MWL#89, in MariaDB if the IN-EXISTS strategy
is chosen, the in-to-exists predicates are insterted after
join_read_const_table() is called, resulting in evaluation of
the subquery without the in-to-exists predicates.

1 files changed, 45 insertions, 0 deletions

diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test
index 0209bf66a57..81a33c95424 100644
--- a/mysql-test/t/subselect_mat.test
+++ b/mysql-test/t/subselect_mat.test
@@ -943,3 +943,48 @@ SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;
 SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;
 
 drop table t1, t2;
+
+--echo #
+--echo # BUG#52344 - Subquery materialization: 
+--echo #  	     Assertion if subquery in on-clause of outer join
+--echo #
+
+set @@optimizer_switch='semijoin=off';
+
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (10);
+
+CREATE TABLE t2 (j INTEGER);
+INSERT INTO t2 VALUES (5);
+
+CREATE TABLE t3 (k INTEGER);
+
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+
+DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # LPBUG#609121: RQG: wrong result on aggregate + NOT IN + HAVING and
+--echo # partial_match_table_scan=on
+--echo #
+
+CREATE TABLE t1 (c1 int);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (c2 int);
+INSERT INTO t2 VALUES (10);
+
+PREPARE st1 FROM "
+SELECT *
+FROM t2 LEFT JOIN (SELECT * FROM t2) t3 ON (8, 4) IN (SELECT c1, c1 FROM t1)";
+
+EXECUTE st1;
+EXECUTE st1;
+
+DROP TABLE t1, t2;