List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:April 14 2011 7:30am
Subject:bzr commit into mysql-5.5 branch (jorgen.loland:3444) Bug#11765699
View as plain text  
#At file:///export/home/jl208045/mysql/mysql-5.5-11765699/ based on revid:serge.kozlov@stripped

 3444 Jorgen Loland	2011-04-14
      Bug#11765699 - 58690: !TABLE || 
      (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
      
      Due to condition optimizations, a condition of a query block may
      be replaced with a different expression tree. This change is
      performed on JOIN's conds and having variables, but also leaves
      SELECT_LEX::where/having variables unusable because the item tree
      these point to have been modified.
      
      This bug was about a query with three levels of subselects:
      
      SELECT 1 FROM t1 WHERE a = 
      (SELECT 1 FROM t2 WHERE b = 
      (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
      GROUP BY b
      );
      
      In the inner-most subselect, "OR t1.a = 1 AND 1 = 2" is 
      optimized away. 
      
      Prior to optimization, select#3 has 
      
      join->conds = select_lex->where = "c = 1 OR t1.a = 1 AND 1 = 2"
      
      After condition optimization, select#3 has:
      
      join->conds = "c = 1"
      select_lex->where = ""   // <- !!
      
      The bug is that filesort() calls walk(register_field_in_read_map)
      to get an updated read_set before reading in rows that will be
      sorted. Prior to this fix, Item_subselect::walk() would update
      read_set with fields in lex->where (none in this case), causing
      an ASSERT to trigger when the field "c" was later read.
      
      Fixed by making Item_subselect::walk() walk over join->conds
      instead of lex->where. An alternative fix would be to assign
      the optimized condition to select_lex->where after optimization:
      
      -  conds= optimize_cond(...);
      +  select_lex->where= conds= optimize_cond(...);
     @ mysql-test/r/subselect_innodb.result
        Add test for BUG#11765699
     @ mysql-test/t/subselect_innodb.test
        Add test for BUG#11765699
     @ sql/item_subselect.cc
        Making Item_subselect::walk() walk over join->conds/having
        instead of lex->where/having because join contains the 
        optimized versions of the WHERE and HAVING conditions.

    modified:
      mysql-test/r/subselect_innodb.result
      mysql-test/t/subselect_innodb.test
      sql/item_subselect.cc
=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2011-02-17 12:41:25 +0000
+++ b/mysql-test/r/subselect_innodb.result	2011-04-14 07:30:18 +0000
@@ -254,3 +254,21 @@ SELECT * FROM t1 WHERE b < (SELECT CAST(
 a	b
 2011-05-13	0
 DROP TABLE t1;
+#
+# Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+#                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+#
+CREATE TABLE t1( a INT );
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2( b TEXT, c INT, PRIMARY KEY (b( 1 )) ) ENGINE = INNODB;
+INSERT INTO t2 VALUES ('a', ''), ('b', '');
+Warnings:
+Warning	1366	Incorrect integer value: '' for column 'c' at row 1
+Warning	1366	Incorrect integer value: '' for column 'c' at row 2
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+GROUP BY b
+);
+1
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/subselect_innodb.test'
--- a/mysql-test/t/subselect_innodb.test	2011-02-17 12:41:25 +0000
+++ b/mysql-test/t/subselect_innodb.test	2011-04-14 07:30:18 +0000
@@ -247,3 +247,22 @@ CREATE TABLE t1(a date, b int, unique(b)
 INSERT INTO t1 VALUES ('2011-05-13', 0);
 SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); 
 DROP TABLE t1;
+
+--echo #
+--echo # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+--echo #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+--echo #
+
+CREATE TABLE t1( a INT );
+INSERT INTO t1 VALUES (0), (1); 
+
+CREATE TABLE t2( b TEXT, c INT, PRIMARY KEY (b( 1 )) ) ENGINE = INNODB;
+INSERT INTO t2 VALUES ('a', ''), ('b', '');
+
+SELECT 1 FROM t1 WHERE a = 
+ (SELECT 1 FROM t2 WHERE b = 
+  (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+   GROUP BY b
+ );
+
+DROP TABLE t1, t2;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-03-16 14:11:20 +0000
+++ b/sql/item_subselect.cc	2011-04-14 07:30:18 +0000
@@ -227,10 +227,11 @@ bool Item_subselect::walk(Item_processor
       Item *item;
       ORDER *order;
 
-      if (lex->where && (lex->where)->walk(processor, walk_subquery, argument))
+      if (lex->join->conds && 
+          (lex->join->conds)->walk(processor, walk_subquery, argument))
         return 1;
-      if (lex->having && (lex->having)->walk(processor, walk_subquery,
-                                             argument))
+      if (lex->join->having && 
+          (lex->join->having)->walk(processor, walk_subquery, argument))
         return 1;
 
       while ((item=li++))


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110414073018-b0dp00gbwwawxwge.bundle
Thread
bzr commit into mysql-5.5 branch (jorgen.loland:3444) Bug#11765699Jorgen Loland14 Apr