#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
