bzr push into mysql-trunk branch (norvald.ryeng:3891 to 3892) Bug#13801019

[Norvald H. Ryeng]

 3892 Norvald H. Ryeng	2012-04-18
      Bug#13801019 ASSERTION `0' FAILED IN CREATE_MYISAM_TMP_TABLE
      
      Problem: JOIN::exec() tries to create temporary tables for derived
      tables that the optimizer has decided to treat as constant tables.
      
      Consider the query from the test case:
      
      SELECT *
      FROM (SELECT * FROM t1) AS a1
        RIGHT JOIN t3
        LEFT JOIN t2 ON d=c ON a=c;
      
      The optimizer first decides to materialize a1. In
      make_join_statistics() it calls update_ref_and_keys() to identify all
      possible keys for the table. Later on, make_join_statistics()
      discovers that the join conditions are impossible and tags a1 as a
      constant table returning only NULL values. Unused keys are supposed to
      be removed later on by JOIN::drop_unused_derived_keys(), but since a1
      has been marked as a constant table, it is ignored and all the
      possible keys are left.
      
      At the start of query execution, JOIN::prepare_result() calls
      st_select_lex::handle_derived() to create the necessary temporary
      tables. When it tries to create a temporary table for a1, the attempt
      triggers an assertion in create_myisam_tmp_table() since it tries to
      create a temporary table with more than one key.
      
      Fix: Check that a derived table is not marked as constant before
      creating a temporary table for it.
     @ mysql-test/r/derived.result
        Add test case for bug #13801019.
     @ mysql-test/t/derived.test
        Add test case for bug #13801019.
     @ sql/sql_derived.cc
        Check that a derived table is not marked as constant before
        creating a temporary table for it.
     @ sql/sql_optimizer.cc
        Drop unused derived keys also from const tables.
     @ sql/sql_optimizer.h
        Initialize const_table_map when JOIN object is constructed.

    modified:
      mysql-test/r/derived.result
      mysql-test/t/derived.test
      sql/sql_derived.cc
      sql/sql_optimizer.cc
      sql/sql_optimizer.h
 3891 Marko Mäkelä	2012-04-18
      recv_recovery_from_checkpoint_start_func(): Fix a compiler warning about
      uninitialized variable group_scanned_lsn.

    modified:
      storage/innobase/log/log0recv.cc
=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/derived.result	2012-04-18 07:07:07 +0000
@@ -1962,3 +1962,14 @@ id	select_type	table	type	possible_keys
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	NULL
 DROP TABLE t1,t2;
+#
+# Bug #13801019 ASSERTION `0' FAILED IN CREATE_MYISAM_TMP_TABLE
+#
+CREATE TABLE t1 (a INT, b BLOB) ENGINE=InnoDB;
+CREATE TABLE t2 (c INT);
+CREATE TABLE t3 (d INT);
+INSERT INTO t3 VALUES (0);
+SELECT * FROM (SELECT * FROM t1) AS a1 RIGHT JOIN t3 LEFT JOIN t2 ON d=c ON a=c;
+a	b	d	c
+NULL	NULL	0	NULL
+DROP TABLE t1, t2, t3;

=== modified file 'mysql-test/t/derived.test'
--- a/mysql-test/t/derived.test	2012-02-13 10:42:27 +0000
+++ b/mysql-test/t/derived.test	2012-04-18 07:07:07 +0000
@@ -1317,4 +1317,15 @@ SELECT pk FROM ( SELECT col_blob, pk FRO
 EXPLAIN SELECT pk FROM ( SELECT col_blob, pk FROM t2 ) AS A NATURAL JOIN t1;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug #13801019 ASSERTION `0' FAILED IN CREATE_MYISAM_TMP_TABLE
+--echo #
 
+CREATE TABLE t1 (a INT, b BLOB) ENGINE=InnoDB;
+CREATE TABLE t2 (c INT);
+CREATE TABLE t3 (d INT);
+INSERT INTO t3 VALUES (0);
+
+SELECT * FROM (SELECT * FROM t1) AS a1 RIGHT JOIN t3 LEFT JOIN t2 ON d=c ON a=c;
+
+DROP TABLE t1, t2, t3;

=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc	2012-03-06 14:29:42 +0000
+++ b/sql/sql_derived.cc	2012-04-18 07:07:07 +0000
@@ -338,9 +338,21 @@ bool mysql_derived_create(THD *thd, LEX
    *) Some commands, like show table status, doesn't prepare views/derived
       tables => no need to create result table also.
    *) Table is already created.
+   *) Table is a constant one with all NULL values.
   */
-  if (!derived->uses_materialization() || !table || table->created)
+  if (!derived->uses_materialization() || !table || table->created ||
+      (derived->select_lex->join != NULL &&
+       (derived->select_lex->join->const_table_map & table->map)))
+  {
+    /*
+      At this point, JT_CONST derived tables should be null rows. Otherwise they
+      would have been materialized already.
+    */
+    DBUG_ASSERT(table == NULL || table->reginfo.join_tab == NULL ||
+                table->reginfo.join_tab->type != JT_CONST ||
+                table->null_row == 1);
     DBUG_RETURN(FALSE);
+  }
   /* create tmp table */
   select_union *result= (select_union*)unit->get_result();
 

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-04-17 14:37:50 +0000
+++ b/sql/sql_optimizer.cc	2012-04-18 07:07:07 +0000
@@ -6801,7 +6801,7 @@ bool JOIN::generate_derived_keys()
 
 void JOIN::drop_unused_derived_keys()
 {
-  for (uint i= const_tables ; i < tables ; i++)
+  for (uint i= 0 ; i < tables ; i++)
   {
     JOIN_TAB *tab= join_tab + i;
     TABLE *table= tab->table;

=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h	2012-03-15 14:41:06 +0000
+++ b/sql/sql_optimizer.h	2012-04-18 07:07:07 +0000
@@ -409,6 +409,7 @@ public:
     all_tables= 0;
     tables= 0;
     const_tables= 0;
+    const_table_map= 0;
     join_list= 0;
     implicit_grouping= FALSE;
     sort_and_group= 0;

No bundle (reason: useless for push emails).