List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:January 7 2011 8:39am
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3320 to 3321) Bug#58463
View as plain text  
 3321 Olav Sandstaa	2011-01-07
      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).
     @ 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
 3320 Tor Didriksen	2011-01-03 [merge]
      automerge trunk => opt-backporting

    renamed:
      mysql-test/suite/funcs_1/r/myisam_views.result => mysql-test/suite/funcs_1/r/myisam_views-big.result
      mysql-test/suite/funcs_1/t/myisam_views.test => mysql-test/suite/funcs_1/t/myisam_views-big.test
    modified:
      .bzr-mysql/default.conf
      VERSION
      mysql-test/collections/default.experimental
      mysql-test/collections/default.weekly
      mysql-test/suite/funcs_1/t/myisam_views-big.test
=== 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	2011-01-07 08:38:31 +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	2011-01-07 08:38:31 +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	2011-01-07 08:38:31 +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	2011-01-07 08:38:31 +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	2011-01-07 08:38:31 +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	2011-01-07 08:38:31 +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	2011-01-07 08:38:31 +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-06 13:12:51 +0000
+++ b/mysql-test/r/innodb_mrr_none.result	2011-01-07 08:38:31 +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-17 09:41:21 +0000
+++ b/mysql-test/r/myisam_mrr.result	2011-01-07 08:38:31 +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-20 14:21:44 +0000
+++ b/mysql-test/r/myisam_mrr_all.result	2011-01-07 08:38:31 +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-17 09:41:21 +0000
+++ b/mysql-test/r/myisam_mrr_cost.result	2011-01-07 08:38:31 +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-20 14:21:44 +0000
+++ b/mysql-test/r/myisam_mrr_cost_all.result	2011-01-07 08:38:31 +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-17 09:41:21 +0000
+++ b/mysql-test/r/myisam_mrr_cost_icp.result	2011-01-07 08:38:31 +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-17 09:41:21 +0000
+++ b/mysql-test/r/myisam_mrr_icp.result	2011-01-07 08:38:31 +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-12-17 09:41:21 +0000
+++ b/mysql-test/r/myisam_mrr_none.result	2011-01-07 08:38:31 +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-12-16 19:36:57 +0000
+++ b/sql/handler.cc	2011-01-07 08:38:31 +0000
@@ -4701,6 +4701,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 "change our mind" and use 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);

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (olav.sandstaa:3320 to 3321) Bug#58463Olav Sandstaa7 Jan