From: Jorgen Loland
Date: April 14 2011 7:30am
Subject: bzr commit into mysql-5.5 branch (jorgen.loland:3444) Bug#11765699
List-Archive: http://lists.mysql.com/commits/135385
X-Bug: 11765699
Message-Id: <20110414073021.4F1C48FC@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============6285367802861214373=="

--===============6285367802861214373==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#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++))


--===============6285367802861214373==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jorgen.loland@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
#   b0dp00gbwwawxwge
# target_branch: file:///export/home/jl208045/mysql/mysql-5.5-\
#   11765699/
# testament_sha1: e3fe57409c9912418aaa7c915cf21e0824c24a22
# timestamp: 2011-04-14 09:30:21 +0200
# base_revision_id: serge.kozlov@stripped\
#   nb6gvmrt4lc1nwu9
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWRmIaHsABOVfgFE0ee///3//
3+C////0YAsPntbmu7oUAdBr7DN7LtSvRqnkarRioMJRTRMhPI9SPUzU2kekMhoNADQ0PKNNGgAA
yRAap7FTZGkxqaNNAGgA0DQAGg0AHMJoDQGjRhGgxGmJkxNBhGgZAMmAkRICaaZVPDKbQZTaoeUb
U0PUAA00aZNAHqaCSSmp6QzU9TxT0agbSMRp6Q0yADQAAAaAkkE0yCYmQ0U9Am1NqNBAJo9TQNHq
A0GjJOTwBQSk00V0rvudUjjFfsOyyreJBTMVz1879WFuqOFRj76sX5MiBlyi0IwwCBRpui8i0WIj
wcFP8uLao7//RST+vU3Suq4uHusp1qWBDdVydubKoWzjX4HmfmMDm2wSwDWYQSdvY9iv7WencGHJ
WVhxGo/AkYzCHIWrxn2Rg9QW0gjTxS4iiqKKVswflT64B9mJDSQhQNGspV47wvrRQKdcDg6E5l9/
T2+tjNh1QjjRej4kHI06d3uPkvUdp4KtB2YbjYbDiHEr2eA1AeZc4eEFcLwCIiwoqMJuiT10b1qV
TUkgjGphFNYxGYZIKJhlbappopSoW4FJULSPj6oaqqJHNsIVJGoVxkrNvhHCOi4uH2TBmVWTjsNG
E3J6C0ds3PIvtKWqcibZKA0SUCVk8LvMeQNkDGRUYBAYAwODQZzK4PR1lYdWhW78Mfp1WUiyMtj0
Q7FSqvra/acLVUXoshqJdtQ3MOB6NsbTyPfOvUOW6rDtRFzhkyE3zMwzNr4lp9RArOg4TDgdpsCh
+0niNZQ0sNHurMlFBs51Afc26l1FfxY/F5EvCwcYHA39N8dlXudlAhB8JwCwMV4sU6DOIdpM7in3
3Jwfr9CoL1gHvPUvWK0DALxkuKF9YZxbf0V5rt7S5e2C4QkLAk8hNzcQ9BXnbhqjYistYStGWCR3
iPq4hZElVg9TZTcXbZiOrIvvyN/yqr2pEeMR9kAFlwjQoAiBQJSJ+eFcIiuDnK1XJ6UlLrvEUhW4
RY14d7UYIsgWKJJk5jEZL3/GIkacD0e+SoK4Ayu+X27BjvpZdxW41FYW3Ya0dPKS2TPvIwEao4VK
vlhdtULCgoEO/ARG09vZVCsa8KWrM58YwQR6qa5IYYZU1QasAWrJYoNRjdr4zVKDIcZDC4P2PHDh
8CfVXXGkxYG8iZH43qHZNl2E9ZyEPkLwheJzVEVMjiVu0zqyGCI/t2KE5aSL0xO2/BYrey4e6uZb
WWq4RiyjucnmaExqPcLinKsvqXIwMSZ2xVKjO4fYbDtsNQxNw1g9xX/16/Jc1SlRw7aLQqDifkxm
s578BboQliITmkxCQ1zsOFy8V0HQN4x/xYFJcszVQYXTlYeoMLA4E3zOVREtmrFMeJ27c9gqcc8l
f2ajFTJk2VdCTi4y9AjyeQLC+ZMWXZeYZFKKd3n4QRKs7C0yMOmJoVkTNcU9bVuoC3JnbVWa7xiw
qL6LYVqQ1MZGmNkCoxuzr3UsJYuWMBNMMGwZsZhI4tZCoyJSehmqbJzWmJI71gdBvasVcV5PXNfy
wHl7GOMjWWbubkICXNdEQorsUSD4ymgVtzuNYUPgBoMu33AyQsch2T7WGG2rik41+C9ILoySi+XU
5IwpLALGECOZDUg+JSN6FwzMDHaUvQ9klCLWWVhIj60eYak/1WCiTEPOIqSTEYQI/teoRgahEI8A
aDJV3HgWAsMLRkyP/hHgsTL2L9DXYWgwKLrU+YoGtmtVJJiK9YUvQZrlLXUhisGZDBVBB7NSRdva
hcS0El61RKLBH4i/MP9GiDU3KpFBgovkJsA5aIKs1AtQYomgefSYi/AVqmDyaqCdpDmGpmnhWwjI
VUCAfUSkUzlGmU5iZXKE5XHCHCYHNSvdLw6JlNsBxqToKJmOGADJRiOsh5TZwc4UhSfG/QVp/lqA
R1XxZF115OoJC3b/tu/4I0gVn6jmq+kKZOGSNa/2rXRBUWH2qV418ZvcM7uLJp+oYfvmq+DCpYIQ
LfYBMYSIQxiE2WMcmJ1Go601jJYke7oCvKqGitR6FHoODBGaqJfpENq1B7kps2+3OwldSZ0qKvMz
SZmN7AiN5HXhtzu0NfKy0KowcAQLEBu+olH2g2NXFyQrH5FyUJhkOgzcUg1VGSroa7qu3mGMpodw
GJ2NREbCKUXpDSXqMxH1D/BWm4qDbKqaNeS7xFxBFzA6WHceej+vmmafm8cnUfW/FZEWWw91a3Ol
9dsAFFJChc1fdJzTzyeBlX64pzrxoajqYF/Re5SxscnQMRWiiSDNoSzPWECAhV40bMxa7ay2Q031
xPqpR+6jAy5psGBG6qnm6CDnMID4eDkiJbmMEkyPQuLpwGJLZ13wLAOXS5WNGkIA7kWqKwnDYEdS
pJ7PNlhepQEyR0qSleWHpR5bkHJ3eb+PHYeo6HOJWMqi4LGVyGImvJQy9bc1/hefWmqC1cE7iQ4m
Vc2S2rmXv7K8lejeMGxj4bRHpXzEGC5Mpg5hOcC2HfhY/xcuPlSs28161mujKtRygPSUH6uxkRJ8
zDZxIIlt2BnHStq6AJWSyWkltB+faI6Dswn+RAT1KBo47ICWUUjTyZMBZ2ixuvLdhEa9lCdYjnMp
A+j06xvC5hhmQxGUy8DQCsuwxqxnKRF6pLWOx4IUyNqwF+iEtSjOYhIgZVq898tEADMZl2cU49gw
rQVBbK+uqcwoQeE/m9fRr0BazUaG61587g0AGQSgIUB7/UDZphs0jyjzfZXokb1As2uqG0HuMTw7
/GuAB5qCc8DGJj1T5p1poGRzZBZZE9S9lHUmFMV1UoL85E9WJjo05eWhEqI80SVWwSA6khzptJCg
6sllgrqDKoqE5IcAcdFASg2qlWrIJEhHLCNIsMaWxRBCbRuWUrvXk2WdwZhwYTTVGCOjIRSeRH4I
PH90kjxBEcBJwBPwen/QqFQujN9a2BIxIiyAMJ7wiSHBVHyJ9RyW6CeVREamVnSEZcALmpeLJgOw
iHDZskSxlL7O8pCaDnyWj17V6UyyXfxW0DRenGSyNTRbbg0C5nDJ7h3Vx8HqDPEiTgcwV1Nwbibc
m4StQVeawY1pAKkWgpA62nI/YVBzV2L6OhYl18aFcWutOH0m1TLTHp4YOFDhfS4mzGm+LKVzKTjG
znknDf17gzyWipu/M2pBywo3GwOU2rfIStN75RdFFv4lxs12GeXet70xEREY+UIZWGbI5sLkNjUL
A3MBNUscnOhIq3KzMgWP2HQcOmKZ0qOSsjGIRTB4ISrXdUr7FAlXwE5dOopJMnWVGQvcvQS4Sl4q
8bcPHPE65UVaglqMvY/XZ3Gp3IJhUDNVwUyjIkbBbVpBOda5FNdikJHP7sj6fn9gfE7hmuNpysPI
oYLJQ3PZSnKxeoyUVMJbd9FEjG34eSeKQrVOwJkzgvsHI4CJ+Ah8BRZwZpiWB1WK2iKCLjYkZd7/
FQ3pksRErybA5uMyeWwLbE/UCdMgmUMigixilF/i7kinChIDMQ0PYA==


--===============6285367802861214373==--