From: Jorgen Loland
Date: December 16 2010 11:25am
Subject: bzr commit into mysql-5.5-bugteam branch (jorgen.loland:3203) Bug#58456
List-Archive: http://lists.mysql.com/commits/127056
X-Bug: 58456
Message-Id: <20101216112505.155A572A@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0306511734721537452=="

--===============0306511734721537452==
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-bugteam/ based on revid:jon.hauglid@stripped

 3203 Jorgen Loland	2010-12-16
      BUG#58456 - Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output 
                  in opt_range.h
      
      In this bug, there are two alternative access plans: 
       * Index merge range access
       * Const ref access
      
      best_access_path() decided that the ref access was preferrable, 
      but make_join_select() still decided to point 
      SQL_SELECT::quick to the index merge because the table had 
      type==JT_CONST which was not handled. 
      
      At the same time the table's ref.key still referred to the 
      index the ref access would use indicating that ref access 
      should be used. In this state, different parts of the 
      optimizer code have different perceptions of which access path
      is in use (ref or range).
      
      test_if_skip_sort_order() was called to check if the ref access
      needed ordering, but test_if_skip_sort_order() got confused and
      requested the index merge to return records in sorted order. 
      Index merge cannot do this, and fired an ASSERT.
      
      The fix is to take join_tab->type==JT_CONST into concideration
      when make_join_select() decides whether or not to use the 
      range access method.
     @ mysql-test/r/join_outer_innodb.result
        Add test for BUG#58456
     @ mysql-test/t/join_outer_innodb.test
        Add test for BUG#58456

    modified:
      mysql-test/r/join_outer_innodb.result
      mysql-test/t/join_outer_innodb.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/join_outer_innodb.result'
--- a/mysql-test/r/join_outer_innodb.result	2008-05-07 05:58:21 +0000
+++ b/mysql-test/r/join_outer_innodb.result	2010-12-16 11:25:02 +0000
@@ -17,3 +17,38 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
+#
+# BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#            in opt_range.h
+#
+CREATE TABLE t1 (
+col_int INT,
+col_int_key INT,
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4);
+INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5);
+CREATE TABLE t2 (
+pk INT PRIMARY KEY
+) ENGINE=InnoDB;
+
+EXPLAIN SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY,col_int_key	PRIMARY	4	const	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.col_int	1	Using index
+
+SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+pk
+
+DROP TABLE t1,t2;
+# End BUG#58456

=== modified file 'mysql-test/t/join_outer_innodb.test'
--- a/mysql-test/t/join_outer_innodb.test	2006-05-22 11:57:32 +0000
+++ b/mysql-test/t/join_outer_innodb.test	2010-12-16 11:25:02 +0000
@@ -24,3 +24,40 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON 
   WHERE t1.name LIKE 'A%' OR FALSE;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+--echo #            in opt_range.h
+--echo #
+
+CREATE TABLE t1 (
+  col_int INT,
+  col_int_key INT,
+  pk INT NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4);
+INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5);
+
+CREATE TABLE t2 (
+  pk INT PRIMARY KEY
+) ENGINE=InnoDB;
+
+let $query=
+SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+      AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+
+--echo
+--eval EXPLAIN $query
+--echo
+--eval $query
+--echo
+
+DROP TABLE t1,t2;
+
+--echo # End BUG#58456

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-14 10:46:00 +0000
+++ b/sql/sql_select.cc	2010-12-16 11:25:02 +0000
@@ -6499,10 +6499,12 @@ make_join_select(JOIN *join,SQL_SELECT *
 	{
 	  /* Use quick key read if it's a constant and it's not used
 	     with key reading */
-	  if (tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF
-	      && tab->type != JT_FT && (tab->type != JT_REF ||
-               (uint) tab->ref.key == tab->quick->index))
-	  {
+          if (tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF &&
+              tab->type != JT_FT &&
+              ((tab->type != JT_CONST && tab->type != JT_REF) ||
+               (uint)tab->ref.key == tab->quick->index))
+          {
+            DBUG_ASSERT(tab->quick->index != MAX_KEY);
 	    sel->quick=tab->quick;		// Use value from get_quick_...
 	    sel->quick_keys.clear_all();
 	    sel->needed_reg.clear_all();
@@ -13857,7 +13859,7 @@ check_reverse_order:                  
   SYNOPSIS
    create_sort_index()
      thd		Thread handler
-     tab		Table to sort (in join structure)
+     join		Join with table to sort
      order		How table should be sorted
      filesort_limit	Max number of rows that needs to be sorted
      select_limit	Max number of rows in final output
@@ -13867,8 +13869,8 @@ check_reverse_order:                  
 
 
   IMPLEMENTATION
-   - If there is an index that can be used, 'tab' is modified to use
-     this index.
+   - If there is an index that can be used, the first non-const join_tab in
+     'join' is modified to use this index.
    - If no index, create with filesort() an index file that can be used to
      retrieve rows in order (should be done with 'read_record').
      The sorted data is stored in tab->table and will be freed when calling


--===============0306511734721537452==
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\
#   w7bind6txs0ii8pv
# target_branch: file:///export/home/jl208045/mysql/mysql-5.5-bugteam/
# testament_sha1: 785dacbf28d617e0f39070e73a1315dea47e9582
# timestamp: 2010-12-16 12:25:04 +0100
# base_revision_id: jon.hauglid@stripped\
#   uguxqyzv0aai9wkb
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWbqjQHkABNzfgHF4fff//3//
/+C////8YAtnd9u2c5y++dPp9AZ17s5rZW29yqc6+5ujfR3UZ7t4YqaJjSU/RNlT9U9ipto1Jp6n
5KbZTUaMIaGgaGmhoNGCSgTTI0Jk1PJPSTaNFAZNAABoNDQABoBkjTJRT/RKeNMVPKfko0aAAPUA
aAABoAAJERCGmgmKYaUfkkyP1DTU2pkaDQepk9RoAaABtSmlN6U9NTynlDagGQ0GjQAAAAAABoCS
IgmmJlMZAKm8U2TUyTag0aep6J6gBmoDQ02o90JA6OoewdUXWznezGrQ45A/E2Dn2B2FW6Vi0ihn
46XB0dena3pFviZ6Bgbvfz+j7Xvthnpll1f9lo7JLhrQU2CTL3qMj1caVY4XO66OU/TdrDIV42ZN
8Yng1uUEN28VqfxUo8E4YhZKANKaat0LWkNb18SLWOLpNSqO/SAdOOgIwpn7axCxovVUuFkCoSvG
B2FIHj0I/v8MPf3anzZmbvJlaBAw3Fd5h4s5s0+ZfstyYNNcwexGql49DUOkRBZkLzOYvCpeq+Fj
7ubxGaAx1j0unAP8Kx0nlzgVujCTNCm2hBYeGCK6g2hryLmjIb0xevYnJpVq+Y/UYmDx87D6XBw5
vC+FckxSj45s58NQ5rt2vFdRrGyDl5rKUdPLZ0LA/iwzzHcfZ48k2RIWHhUxqZVRrvIsVlj1GtTr
eR8rxRAupsgCwaWmy8azwgQGJQ2HlnnmI4S5buCvFM1o06Ab9ay4z4xVZujBltijwcILYYzgrQYR
F1U6I3uwswUkeXMrePRjF4vvg4ypRQQxEnD5EgkkvoSj3wEBk5crAMG7Pj8KTwJFyAn8/NRYOCkv
dwBEcPg3BsFx6DAbD0FxT1t2F2Q5vlyju5PAFx5Q3PYTFpaWFZWSBaMQHHGJixjEXCH5ysVnuSVe
wxK44oMWjhAFoQnYsOoGucbm2WaHXhiFJsyAh+rPNC6s778mMzhJOBFNAUFidQAnkYyKwo2Fikzq
kcY60NXeI2YI0s0yfCBcA3KA4SigIvSKsB6dKtJhCtKHxlRUP7YuUhwoqi3mXElCVNBFfI8mWSLp
uHLmiQS8wcBzKwB1zgHaxkzay7zZhHswpRknErxE24QayepKOKTXGyLHasw890kuONaFsd/oeKxl
l3bhEtLcFemZaOA0YYceGVr2uMiLVsHGxIcrxIqrSzCXLp0AXHIQrsKql1wiSlyq0cEythcaoM1B
UKG3nm4m5YN7cQsUzEZcVN9vE3zyqoepq1+Q+aiPaSGFjFIm7FSZ16ej3qvpKS3hvpwYjWtr+xuD
N6W4XMFneGVRQ1TGwDHejIUlt3DL/LTnApOWvRsLb5uCDDntu1mIaSnJqU8U0id26+KmSXgrlluS
folpImR8iXSdNpMYdZVHw2dqZRO7CpZ49DU7gdJBX30zSkUQr6n9RXuLSVFqw74KorInRk1gUM6K
/A2XSoREbG63MuUry2DHTuThicdznY2m7LF53FtUbxpXa+uEEO/0RoczreTMzVZFmZ0jBhR9CBYP
JvJjjR6TamEWEGcBj4FpW7dMnr1CJzt4N6J01YSKxxOp6LCo1utRvHlRCw2sIF1ph42PvMDIxXU8
unh4wlRmugHlLcHcDcqAZf3lbKKkTdSaBlKPGLFjfgnq4huIUSeIOPx+tELz+3yGDYzyLalFjPlC
08netnJI+Oj5MIlsF5z3BWvXkPUB4RtCG0gpMbJkzb+kJX6iZVhLVvsWJXBKVOwXlD8/SpVhnNAM
lCMoUoEpXBcHKA7QqlVKSU+SgqIuJWR8zzzpeZPPptKla1SSuW4G4JicBQ0mmfcpK0LAgANt90GD
GsvJCKWf0taS/ENQtKVqzpLAP8nMtoEZFArUworBQlIVICiYKEfMHoBmoPerlIrE46tWYoPA1Fyl
Ur54LEEwmVAWjrKB3rWQFkAYMipoacMQUlRGciqJLAdQKxh9yZBwMcExRB4WrcKeoagaKpJXEgzU
VIoZUpgYGMCZ9MqNavCmEqq8vr7dxyinTERHqAJJENLrgqI8CtkPXM/ZUuVgvSdRapqPJ5y4CBL6
mnyr/qxHlYEqjig7m0ekp2YtThWrFeVWe8e6eWl99tuSk+DKMzYxIvcJ7qSqwEfWr5Jk3g2U7GrF
6qRvy5W0HEI7OWAn6/vNpxeos7TWekb4dojIjbP4I/PFrBGfFivO3nhQeUHEJKm771uiI5mzejzd
WAjQWH5lRe2LEBpTEWpaQKlBJb5rQa/tcgmh2kADmodZtv7FwIFfhmhqToiFvGKdkz2pmLnpo3xb
UxiztCxJMNbYSQ4aQ1qENSHJV9czZXCLjkLdwaabRWJ3mlYSh1RgmYkc4Bg/KzCSu8WyTFjc0wC/
gdMjjyqUa1yWXjNEUTkzQml4j1RfySJk4BBMmkY0bCHKSToLepUrmLo0unnRaNgFbLDXVvAyJtDA
pZhkYJKEr2VMKxyJ+uuByyBwv5jvsIae9pTigjVpHLmuMEddE7a/8fKK7DnZInpEyZGenjDZNmB8
aRDBTgMQOlmLPIOpkZKdtQImZg3oBB4UGeko3BBYREqWZCxsS5CphbX9L7AIlht0w3Fe0oNhITlx
sGFAsUSUWdvBeLoTHOKg8Q4QopTxRqjfUnKGcGHfUMTEqKF0Jq1d6dN/PXuNWLbrbbZgRvAuKKqi
A+BgaLPJLY0wgB23UMutBVcWhaCMVlPl2DsV0WZ4LxoOaaDtcP80iUS5CN5EFEXRajtTIAONHfEO
eDWeIIw14nqzCWNmpiLr69uJoDQVcYxtCGd9OJLPKDVY4GwRF2nZqP5/WU3fVwu95aZVAiDJ7uFi
rXSgN6YZMsi51GnrYvZDMO3NMa4CuNUifpXQYTOs6LQlwqwcGaThIvfuALkwV1TMbO9lTB3pJrU0
TOQV92nf4FoxDl37Ehg3AlHXk3FJVM6jvVrwcGSc1mTLVLKXhjKQTfycv+gya3iB3TCgiFc+saqi
iQxQJY1Xrtqz5B2ADSGrhijpreu54pwtIYWiwxBIieoSw6bFMmzPit7ppkMaUDYNIabRs3krp6eR
pe7tA2sR12hqUziN0bcZ7fsZJc8oB270h0BaFDSpkqGhhtQOY55FIWHk6cq4F8jOTldzygnkoTxD
Y2FaeN2etCe3Gr6UKqBbvZMhidyUPpAjTTl5IIuM63jqs/lOY/LtpjVCkNxeuwRfCGlVXCM9f/Ka
nkWqpEWbi7nUZ3+JlIwjFrmQj4cuhsCmtTbFMh1Rso+CyWz0ccW5shMmgusIb3NDKoVwJhWxq4bO
+HOzLnqacSUBmiBp7t2wLCzhBLnJhQny1JxOM3MIeusrhVLs5uWeMrAMkZeLUrTBrB61f+BcJ6PW
vDN6C3NYNrzLod8RERBl4wmCDwrShfOtt6kfQ4EXsZjFA+PEq4FuwlN27YHjLBuroOkvJXprzFul
wp0s5M6RvaShgfQfH6FhbjTGI0Ee0twoy4cCn2u+1o4nCYpsVHTVJjdPsvERwE9GshVISQE9O1lB
JaGE30Q0V1kuwulJE7+dTS5mAnE6tWbLCgZEcUcMYIkmtIlsNgaMUh3XVwb4Gt4qksOamca6B4zU
41vDROZUw4KhJC5RGJ6D01fDYzsqpJc1hNVi6DlaIxLOwdw3csj2hgLWApwZVSpxEiXZUnNowDMM
zJskcrpJOtomMQplFWJoR8RbKaysrj/af8XckU4UJC6o0B5A


--===============0306511734721537452==--