List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:December 9 2010 2:06pm
Subject:bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3312) Bug#58463
View as plain text  
#At file:///export/home/tmp/mysql2/opt-bug58463-fix1/ based on revid:tor.didriksen@stripped

 3312 Olav Sandstaa	2010-12-09
      Patch for Bug#58463 Error Can't find record on SELECT with JOIN and ORDER BY
      
      This problem was caused by the server pushing down an index condition on
      the primary key as part of the optimization phase. Later, during the 
      execution phase the server (due to a bug) changed to use a secondary index
      instead of the primary index for accessing the table. This made MRR fail
      in phase 3 due to not finding the expected records when retrieving them
      using the primary key due to the pushed index condition filtering them away.
      
      This problem with the server accidentally switching to using a secondary index will
      be fixed by the fix for Bug#58456. 
      
      This patch contains a test case based on the bug report and an added assert to
      the DS-MRR implementation that will trigger if a similar situation should occur
      again (DS-MRR used on a secondary index while an index condition has been pushed
      on the primary index for the same handler object).
      
      Note to reviewers: In order for running the new test without failure the patch
      for Bug#58456 needs to be applied first.
     @ mysql-test/include/mrr_tests.inc
        Test case for Bug#58463 Error Can't find record on SELECT with JOIN and ORDER BY.
     @ mysql-test/r/innodb_mrr.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/innodb_mrr_all.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/innodb_mrr_cost.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/innodb_mrr_cost_all.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/innodb_mrr_cost_icp.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/innodb_mrr_icp.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/innodb_mrr_none.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/myisam_mrr.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/myisam_mrr_all.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/myisam_mrr_cost.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/myisam_mrr_cost_all.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/myisam_mrr_cost_icp.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/myisam_mrr_icp.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ mysql-test/r/myisam_mrr_none.result
        Result file for test case for Bug#58463 Error Can't find record on SELECT 
        with JOIN and ORDER BY.
     @ sql/handler.cc
        Added assert that will detect if DS-MRR is attempted run using
        a secondary index when there exists a pushed index condition for
        the primary key on the same handler.

    modified:
      mysql-test/include/mrr_tests.inc
      mysql-test/r/innodb_mrr.result
      mysql-test/r/innodb_mrr_all.result
      mysql-test/r/innodb_mrr_cost.result
      mysql-test/r/innodb_mrr_cost_all.result
      mysql-test/r/innodb_mrr_cost_icp.result
      mysql-test/r/innodb_mrr_icp.result
      mysql-test/r/innodb_mrr_none.result
      mysql-test/r/myisam_mrr.result
      mysql-test/r/myisam_mrr_all.result
      mysql-test/r/myisam_mrr_cost.result
      mysql-test/r/myisam_mrr_cost_all.result
      mysql-test/r/myisam_mrr_cost_icp.result
      mysql-test/r/myisam_mrr_icp.result
      mysql-test/r/myisam_mrr_none.result
      sql/handler.cc
=== modified file 'mysql-test/include/mrr_tests.inc'

=== modified file 'mysql-test/include/mrr_tests.inc'
--- a/mysql-test/include/mrr_tests.inc	2010-07-16 11:51:02 +0000
+++ b/mysql-test/include/mrr_tests.inc	2010-12-09 14:06:48 +0000
@@ -342,3 +342,38 @@
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+--echo #
+
+# To produce the same query plan as in the bug report the first table
+# must be stored in MyISAM.
+CREATE TABLE t1 (
+  pk INT NOT NULL,
+  PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (2);
+
+CREATE TABLE t2 (
+  pk INT NOT NULL,
+  i1 INT NOT NULL,
+  i2 INT NOT NULL,
+  c1 VARCHAR(1024) CHARACTER SET utf8,
+  PRIMARY KEY (pk),
+  KEY k1 (i1)
+);
+
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+
+let query=
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/innodb_mrr.result	2010-12-09 14:06:48 +0000
@@ -511,6 +511,38 @@
 c2
 DROP TABLE t1, t2;
 #
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_all.result'
--- a/mysql-test/r/innodb_mrr_all.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/innodb_mrr_all.result	2010-12-09 14:06:48 +0000
@@ -511,6 +511,38 @@
 c2
 DROP TABLE t1, t2;
 #
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using index condition; Using where
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_cost.result'
--- a/mysql-test/r/innodb_mrr_cost.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/innodb_mrr_cost.result	2010-12-09 14:06:48 +0000
@@ -511,6 +511,38 @@
 c2
 DROP TABLE t1, t2;
 #
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_cost_all.result'
--- a/mysql-test/r/innodb_mrr_cost_all.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/innodb_mrr_cost_all.result	2010-12-09 14:06:48 +0000
@@ -511,6 +511,38 @@
 c2
 DROP TABLE t1, t2;
 #
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using index condition; Using where
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_cost_icp.result'
--- a/mysql-test/r/innodb_mrr_cost_icp.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/innodb_mrr_cost_icp.result	2010-12-09 14:06:48 +0000
@@ -511,6 +511,38 @@
 c2
 DROP TABLE t1, t2;
 #
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using index condition; Using where
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_icp.result'
--- a/mysql-test/r/innodb_mrr_icp.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/innodb_mrr_icp.result	2010-12-09 14:06:48 +0000
@@ -511,6 +511,38 @@
 c2
 DROP TABLE t1, t2;
 #
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using index condition; Using where
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_none.result'
--- a/mysql-test/r/innodb_mrr_none.result	2010-12-02 16:17:50 +0000
+++ b/mysql-test/r/innodb_mrr_none.result	2010-12-09 14:06:48 +0000
@@ -510,6 +510,38 @@
 c2
 DROP TABLE t1, t2;
 #
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2010-12-02 15:51:35 +0000
+++ b/mysql-test/r/myisam_mrr.result	2010-12-09 14:06:48 +0000
@@ -513,5 +513,36 @@
 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1;
 c2
 DROP TABLE t1, t2;
+#
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_all.result'
--- a/mysql-test/r/myisam_mrr_all.result	2010-12-02 15:51:35 +0000
+++ b/mysql-test/r/myisam_mrr_all.result	2010-12-09 14:06:48 +0000
@@ -513,5 +513,36 @@
 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1;
 c2
 DROP TABLE t1, t2;
+#
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_cost.result'
--- a/mysql-test/r/myisam_mrr_cost.result	2010-12-02 15:51:35 +0000
+++ b/mysql-test/r/myisam_mrr_cost.result	2010-12-09 14:06:48 +0000
@@ -513,5 +513,36 @@
 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1;
 c2
 DROP TABLE t1, t2;
+#
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_cost_all.result'
--- a/mysql-test/r/myisam_mrr_cost_all.result	2010-12-02 15:51:35 +0000
+++ b/mysql-test/r/myisam_mrr_cost_all.result	2010-12-09 14:06:48 +0000
@@ -513,5 +513,36 @@
 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1;
 c2
 DROP TABLE t1, t2;
+#
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_cost_icp.result'
--- a/mysql-test/r/myisam_mrr_cost_icp.result	2010-12-02 15:51:35 +0000
+++ b/mysql-test/r/myisam_mrr_cost_icp.result	2010-12-09 14:06:48 +0000
@@ -513,5 +513,36 @@
 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1;
 c2
 DROP TABLE t1, t2;
+#
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_icp.result'
--- a/mysql-test/r/myisam_mrr_icp.result	2010-12-02 15:51:35 +0000
+++ b/mysql-test/r/myisam_mrr_icp.result	2010-12-09 14:06:48 +0000
@@ -513,5 +513,36 @@
 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1;
 c2
 DROP TABLE t1, t2;
+#
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_none.result'
--- a/mysql-test/r/myisam_mrr_none.result	2010-11-26 15:20:05 +0000
+++ b/mysql-test/r/myisam_mrr_none.result	2010-12-09 14:06:48 +0000
@@ -512,5 +512,36 @@
 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1;
 c2
 DROP TABLE t1, t2;
+#
+# Bug#58463: Error Can't find record on SELECT with JOIN and ORDER BY
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT NOT NULL,
+i2 INT NOT NULL,
+c1 VARCHAR(1024) CHARACTER SET utf8,
+PRIMARY KEY (pk),
+KEY k1 (i1)
+);
+INSERT INTO t2 VALUES (3, 9, 1, NULL);
+EXPLAIN SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i1
+FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
+WHERE t2.i1 > 5
+AND t2.pk IS  NULL
+ORDER BY i1;
+i1
+DROP TABLE t1, t2;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2010-11-18 16:34:56 +0000
+++ b/sql/handler.cc	2010-12-09 14:06:48 +0000
@@ -4683,6 +4683,16 @@
                                               n_ranges, mode, buf);
     DBUG_RETURN(retval);
   }
+
+  /* 
+    This assert will hit if we have pushed an index condition to the
+    primary key index and then "changes our mind" and uses a different
+    index for retrieving data with MRR.
+  */
+  DBUG_ASSERT(!h->pushed_idx_cond ||
+              h->pushed_idx_cond_keyno == h->active_index ||
+              h->pushed_idx_cond_keyno != table->s->primary_key);
+
   rowids_buf= buf->buffer;
 
   is_mrr_assoc= !test(mode & HA_MRR_NO_ASSOCIATION);


Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20101209140648-wcbhs0j2vso3nma0.bundle
Thread
bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3312) Bug#58463Olav Sandstaa9 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3312)Bug#58463Jorgen Loland17 Dec
    • Re: bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3312)Bug#58463Olav Sandstaa29 Dec
      • Re: bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3312)Bug#58463Jorgen Loland3 Jan