[Commits] Rev 3680: MDEV-4612 SQ pushdown: Server crashes in make_join_statistics with materialization+semijoin, IN subqueries, constant table, impossible condition in file:///home/tsk/mprog/src/10.0-md83/

timour at askmonty.org timour at askmonty.org
Fri Aug 9 15:54:21 EEST 2013


At file:///home/tsk/mprog/src/10.0-md83/

------------------------------------------------------------
revno: 3680
revision-id: timour at askmonty.org-20130611081135-tym2k9fo1f2t9w30
parent: timour at askmonty.org-20130528122232-cuzl0l22skk1yvor
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4612
committer: timour at askmonty.org
branch nick: 10.0-md83
timestamp: Tue 2013-06-11 11:11:35 +0300
message:
  MDEV-4612 SQ pushdown: Server crashes in make_join_statistics with materialization+semijoin, IN subqueries, constant table, impossible condition
  
  Analysis:
  The subquery materialization execution method subselect_hash_sj_engine::exec()
  didn't restore thd->lex->current_select through all return paths. This was
  not a problem when the method is called during execution. When a subquery
  was executed during the optimization phase of the outer query, current_select
  remained to point to the wrong subquery. This resulted in update_ref_and_keys()
  getting the wrong number of conditions via thd->lex->current_select->cond_count,
  thus allocating a smaller buffer for sargable conditions. This later resulted
  in memory overwrite.
  
  Solution:
  Make sure that subselect_hash_sj_engine::exec() always restores current_select.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2013-05-23 20:16:38 +0000
+++ b/mysql-test/r/subselect4.result	2013-06-11 08:11:35 +0000
@@ -2416,6 +2416,53 @@ SELECT * FROM t1 AS outer_t1 WHERE b IN
 a       b
 2       2
 drop table t1;
+#
+# MDEV-4612 SQ pushdown: Server crashes in make_join_statistics with
+# materialization+semijoin, IN subqueries, constant table, impossible condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0),(8);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0),(8);
+CREATE TABLE t3 (c INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (7);
+EXPLAIN EXTENDED
+SELECT * FROM t1 AS outer_t1, t2 
+WHERE outer_t1.a = b AND outer_t1.a IN ( 
+SELECT c FROM t1 AS inner_t1, t3 
+WHERE (inner_t1.a = 7 AND inner_t1.a != outer_t1.a) OR
+(2) IN (SELECT a FROM t1 WHERE 0));
+id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
+1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+3       MATERIALIZED    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+Warnings:
+Note    1276    Field or reference 'test.outer_t1.a' of SELECT #2 was resolved in SELECT #1
+Note    1003    select `test`.`outer_t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `outer_t1` semi join (`test`.`t1` `inner_t1`) join `test`.`t2` where ((`test`.`outer_t1`.`a` = 7) and (`test`.`t2`.`b` = 7) and (`test`.`inner_t1`.`a` = 7) and 0)
+SELECT * FROM t1 AS outer_t1, t2 
+WHERE outer_t1.a = b AND outer_t1.a IN ( 
+SELECT c FROM t1 AS inner_t1, t3 
+WHERE (inner_t1.a = 7 AND inner_t1.a != outer_t1.a) OR
+(2) IN (SELECT a FROM t1 WHERE 0));
+a       b
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE a = b AND 
+a IN (SELECT c FROM t1 AS inner_t1, t3
+WHERE inner_t1.a = 7 OR 2 IN (SELECT a FROM t1 WHERE 0));
+id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
+1       PRIMARY t3      system  NULL    NULL    NULL    NULL    1       100.00  
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
+1       PRIMARY inner_t1        ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (incremental, BNL join)
+3       MATERIALIZED    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+Warnings:
+Note    1003    select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t1` `inner_t1`) join `test`.`t2` where ((`test`.`t1`.`a` = 7) and (`test`.`t2`.`b` = 7) and (`test`.`inner_t1`.`a` = 7))
+SELECT * FROM t1, t2
+WHERE a = b AND 
+a IN (SELECT c FROM t1 AS inner_t1, t3
+WHERE inner_t1.a = 7 OR 2 IN (SELECT a FROM t1 WHERE 0));
+a       b
+drop table t1,t2,t3;
 SET optimizer_switch=@@global.optimizer_switch;
 SET optimizer_use_condition_selectivity=default;
 SET use_stat_tables=default;

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2013-05-23 20:16:38 +0000
+++ b/mysql-test/t/subselect4.test	2013-06-11 08:11:35 +0000
@@ -1936,6 +1936,43 @@ SELECT * FROM t1 AS outer_t1 WHERE b IN
 
 drop table t1;
 
+--echo #
+--echo # MDEV-4612 SQ pushdown: Server crashes in make_join_statistics with
+--echo # materialization+semijoin, IN subqueries, constant table, impossible condition
+--echo #
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0),(8);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0),(8);
+CREATE TABLE t3 (c INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (7);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 AS outer_t1, t2 
+WHERE outer_t1.a = b AND outer_t1.a IN ( 
+  SELECT c FROM t1 AS inner_t1, t3 
+  WHERE (inner_t1.a = 7 AND inner_t1.a != outer_t1.a) OR
+        (2) IN (SELECT a FROM t1 WHERE 0));
+
+SELECT * FROM t1 AS outer_t1, t2 
+WHERE outer_t1.a = b AND outer_t1.a IN ( 
+  SELECT c FROM t1 AS inner_t1, t3 
+  WHERE (inner_t1.a = 7 AND inner_t1.a != outer_t1.a) OR
+        (2) IN (SELECT a FROM t1 WHERE 0));
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE a = b AND 
+      a IN (SELECT c FROM t1 AS inner_t1, t3
+            WHERE inner_t1.a = 7 OR 2 IN (SELECT a FROM t1 WHERE 0));
+SELECT * FROM t1, t2
+WHERE a = b AND 
+      a IN (SELECT c FROM t1 AS inner_t1, t3
+            WHERE inner_t1.a = 7 OR 2 IN (SELECT a FROM t1 WHERE 0));
+
+drop table t1,t2,t3;
+
 SET optimizer_switch=@@global.optimizer_switch;
 SET optimizer_use_condition_selectivity=default;
 SET use_stat_tables=default;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-05-28 12:22:32 +0000
+++ b/sql/item_subselect.cc	2013-06-11 08:11:35 +0000
@@ -5338,7 +5338,7 @@ int subselect_hash_sj_engine::exec()
   materialize_join->exec();
   if ((res= test(materialize_join->error || thd->is_fatal_error ||
                  thd->is_error())))
-    goto err;
+    goto end;
 
   /*
     TODO:
@@ -5363,7 +5363,8 @@ int subselect_hash_sj_engine::exec()
     item_in->reset();
     item_in->make_const();
     item_in->set_first_execution();
-    DBUG_RETURN(FALSE);
+    res= 0;
+    goto end;
   }
 
   /*
@@ -5406,7 +5407,8 @@ int subselect_hash_sj_engine::exec()
       item_in->null_value= 1;
       item_in->make_const();
       item_in->set_first_execution();
-      DBUG_RETURN(FALSE);
+      res= 0;
+      goto end;
     }
 
     if (has_covering_null_row)
@@ -5464,7 +5466,7 @@ int subselect_hash_sj_engine::exec()
       {
         /* This is an irrecoverable error. */
         res= 1;
-        goto err;
+        goto end;
       }
     }
   }
@@ -5473,7 +5475,7 @@ int subselect_hash_sj_engine::exec()
     lookup_engine= pm_engine;
   item_in->change_engine(lookup_engine);
 
-err:
+end:
   thd->lex->current_select= save_select;
   DBUG_RETURN(res);
 }



More information about the commits mailing list