bzr push into mysql-next-mr-bugfixing branch (olav:3069 to 3070) Bug#49604

[Olav Sandstaa]

 3070 Olav Sandstaa	2010-04-13
      Test case for Bug#49604 6.0 processing compound WHERE clause incorrectly with Innodb - extra rows
      
      (Backporting of revid:olav.sandstaa@stripped )
        
      The fix for this bug is found in the patch for Bug#48665. This patch adds a test for this bug.
     @ mysql-test/r/innodb_mysql.result
        Result for test for Bug#49604.
     @ mysql-test/t/innodb_mysql.test
        Test case for Bug#49604.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
 3069 oystein.grovlen@stripped	2010-04-07
      Bug#49630 "Segfault in select_describe() with double 
                 nested subquery and materialization"
      
      (Backporting of revid:jorgen.loland@stripped)
      
      If a JOIN contains const tables, make_join_select() will 
      evaluate the conditions in the WHERE clause. If the WHERE 
      clause contains a subquery, the process of evaluating 
      the condition involves optimize() and exec(). 
      
      Calling optimize() and exec() on a subselect may require the
      use of temp tables, in which case the original JOIN query
      execution plan will be replaced by a simple scan of the 
      temp table. 
      
      To be able to describe the query plan, the original query 
      layout needs to be saved. This was not done for materialized
      subqueries. The fix is to make materialized subqueries save 
      the original join query layout if it is needed by EXPLAIN.
     @ mysql-test/r/subselect4.result
        Added test for BUG#49630
     @ mysql-test/t/subselect4.test
        Added test for BUG#49630
     @ sql/item_subselect.cc
        Make subselect_hash_sj_engine::exec save JOIN layout if needed by explain. Added function subselect_single_select_engine::save_join_if_explain() with code common to single_select_engine and hash_sj_engine
     @ sql/item_subselect.h
        Add function subselect_single_select_engine::save_join_if_explain() that saves the JOIN layout if needed by explain

    modified:
      mysql-test/r/subselect4.result
      mysql-test/t/subselect4.test
      sql/item_subselect.cc
      sql/item_subselect.h
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2010-03-01 09:39:44 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-04-13 08:49:52 +0000
@@ -2320,6 +2320,38 @@ t2	CREATE TABLE `t2` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 drop table t2, t1;
 #
+# Bug#49604 "6.0 processing compound WHERE clause incorrectly 
+#            with Innodb - extra rows"
+#
+CREATE TABLE t1 (
+c1 INT NOT NULL,
+c2 INT,
+PRIMARY KEY (c1),
+KEY k1 (c2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (12,1);
+INSERT INTO t1 VALUES (15,1);
+INSERT INTO t1 VALUES (16,1);
+INSERT INTO t1 VALUES (22,1);
+INSERT INTO t1 VALUES (20,2);
+CREATE TABLE t2 (
+c1 INT NOT NULL,
+c2 INT,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (2,9);
+SELECT STRAIGHT_JOIN t2.c2, t1.c2, t2.c1
+FROM t1 JOIN t2 ON t1.c2 = t2.c1  
+WHERE t2.c1 IN (2, 1, 6) OR t2.c1 NOT IN (1);
+c2	c2	c1
+2	1	1
+2	1	1
+2	1	1
+2	1	1
+9	2	2
+DROP TABLE t1, t2;
+#
 # Bug#44613 SELECT statement inside FUNCTION takes a shared lock
 #
 DROP TABLE IF EXISTS t1;

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-03-01 09:39:44 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-04-13 08:49:52 +0000
@@ -580,6 +580,40 @@ drop table t2, t1;
 
 
 --echo #
+--echo # Bug#49604 "6.0 processing compound WHERE clause incorrectly 
+--echo #            with Innodb - extra rows"
+--echo #
+
+CREATE TABLE t1 (
+  c1 INT NOT NULL,
+  c2 INT,
+  PRIMARY KEY (c1),
+  KEY k1 (c2)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (12,1);
+INSERT INTO t1 VALUES (15,1);
+INSERT INTO t1 VALUES (16,1);
+INSERT INTO t1 VALUES (22,1);
+INSERT INTO t1 VALUES (20,2);
+
+CREATE TABLE t2 (
+  c1 INT NOT NULL,
+  c2 INT,
+  PRIMARY KEY (c1)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (2,9);
+
+SELECT STRAIGHT_JOIN t2.c2, t1.c2, t2.c1
+FROM t1 JOIN t2 ON t1.c2 = t2.c1  
+WHERE t2.c1 IN (2, 1, 6) OR t2.c1 NOT IN (1);
+
+DROP TABLE t1, t2;
+
+
+--echo #
 --echo # Bug#44613 SELECT statement inside FUNCTION takes a shared lock
 --echo #
 


Attachment: [text/bzr-bundle] bzr/[e-mail]