3818 Ole John Aske 2012-02-01
Fix for bug#13528826 TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT
An incorrect 'adjusted select_limit' was returned from test_if_cheaper_ordering()
if no excplicit limit-clause was specified in the query.
This fix detect when a limit-clause was not specified, and returns the
full 'table_records' when no limit-clause was used.
Furthermore, this fix caused the testcase for bug #12838420
to change beyond what was required as a testcase for that bug.
Fixed that by forcing correct query plan for testcase by
introducing 'USE INDEX' and 'STRAIGHT_JOIN'.
@ mysql-test/include/icp_tests.inc
Slightly changed syntax of testcase in order to force required query plan
for this testcase.
@ mysql-test/include/order_by.inc
New testcase for this bug
@ mysql-test/r/innodb_icp.result
Result should report the full number of records in table wo/ any limit clause.
Also slightly changed syntax of testcase in order to force required query plan
for this testcase.
@ mysql-test/r/innodb_icp_all.result
Result should report the full number of records in table wo/ any limit clause.
Also slightly changed syntax of testcase in order to force required query plan
for this testcase.
@ mysql-test/r/innodb_icp_none.result
Result should report the full number of records in table wo/ any limit clause.
Also slightly changed syntax of testcase in order to force required query plan
for this testcase.
@ mysql-test/r/join_cache_nojb.result
Result should report the full number of records in table wo/ any limit clause.
@ mysql-test/r/myisam_icp.result
Slightly changed syntax of testcase in order to force required query plan
for this testcase.
@ mysql-test/r/myisam_icp_all.result
Slightly changed syntax of testcase in order to force required query plan
for this testcase.
@ mysql-test/r/myisam_icp_none.result
Slightly changed syntax of testcase in order to force required query plan
for this testcase.
@ mysql-test/r/order_by_all.result
Accept result for new testcase
@ mysql-test/r/order_by_icp_mrr.result
Accept result for new testcase
@ mysql-test/r/order_by_none.result
Accept result for new testcase
modified:
mysql-test/include/icp_tests.inc
mysql-test/include/order_by.inc
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_all.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/join_cache_nojb.result
mysql-test/r/myisam_icp.result
mysql-test/r/myisam_icp_all.result
mysql-test/r/myisam_icp_none.result
mysql-test/r/order_by_all.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
sql/sql_select.cc
3817 Tor Didriksen 2012-02-01
All the subquery_sj_xxx tests can get thread stack overrun on solaris
modified:
mysql-test/collections/default.experimental
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc 2011-10-05 13:16:38 +0000
+++ b/mysql-test/include/icp_tests.inc 2012-02-01 09:59:13 +0000
@@ -1043,7 +1043,7 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
let $query=
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
=== modified file 'mysql-test/include/order_by.inc'
--- a/mysql-test/include/order_by.inc 2012-01-25 14:52:00 +0000
+++ b/mysql-test/include/order_by.inc 2012-02-01 09:59:13 +0000
@@ -1781,3 +1781,33 @@ SELECT c FROM t1 WHERE a=2 AND (c=10 OR
SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
DROP TABLE t1;
+
+--echo #
+--echo # Bug #13528826
+--echo # TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+--echo #
+--echo #
+
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+ (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+ (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+ (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+
+--echo # number of rows in t1 was incorrectly used as an
+--echo # implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+
+--echo # Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+
+-- echo # A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+
+DROP TABLE t1, t2;
+
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2012-01-25 12:45:56 +0000
+++ b/mysql-test/r/innodb_icp.result 2012-02-01 09:59:13 +0000
@@ -988,14 +988,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where; Using index
+1 SIMPLE t2 range col_int_key col_int_key 5 NULL 5 Using where; Using index
1 SIMPLE t1 index NULL col_int_key 5 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result 2012-01-30 13:08:34 +0000
+++ b/mysql-test/r/innodb_icp_all.result 2012-02-01 09:59:13 +0000
@@ -988,14 +988,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where; Using index
+1 SIMPLE t2 range col_int_key col_int_key 5 NULL 5 Using where; Using index
1 SIMPLE t1 index NULL col_int_key 5 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result 2012-01-25 12:45:56 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2012-02-01 09:59:13 +0000
@@ -987,14 +987,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where; Using index
+1 SIMPLE t2 range col_int_key col_int_key 5 NULL 5 Using where; Using index
1 SIMPLE t1 index NULL col_int_key 5 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result 2012-01-30 13:13:15 +0000
+++ b/mysql-test/r/join_cache_nojb.result 2012-02-01 09:59:13 +0000
@@ -1445,7 +1445,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/myisam_icp.result 2012-02-01 09:59:13 +0000
@@ -983,14 +983,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index; Using temporary; Using filesort
-1 SIMPLE t2 ALL PRIMARY,col_int_key NULL NULL NULL 6 Using where
+1 SIMPLE t2 ALL col_int_key NULL NULL NULL 6 Using where; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result 2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/myisam_icp_all.result 2012-02-01 09:59:13 +0000
@@ -983,14 +983,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index; Using temporary; Using filesort
-1 SIMPLE t2 ALL PRIMARY,col_int_key NULL NULL NULL 6 Using where
+1 SIMPLE t2 ALL col_int_key NULL NULL NULL 6 Using where; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result 2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2012-02-01 09:59:13 +0000
@@ -982,14 +982,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index; Using temporary; Using filesort
-1 SIMPLE t2 ALL PRIMARY,col_int_key NULL NULL NULL 6 Using where
+1 SIMPLE t2 ALL col_int_key NULL NULL NULL 6 Using where; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result 2012-01-30 13:08:34 +0000
+++ b/mysql-test/r/order_by_all.result 2012-02-01 09:59:13 +0000
@@ -2639,4 +2639,36 @@ c
10
NULL
DROP TABLE t1;
+#
+# Bug #13528826
+# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+#
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+# number of rows in t1 was incorrectly used as an
+# implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2012-01-25 14:52:00 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2012-02-01 09:59:13 +0000
@@ -2639,4 +2639,36 @@ c
10
NULL
DROP TABLE t1;
+#
+# Bug #13528826
+# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+#
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+# number of rows in t1 was incorrectly used as an
+# implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2012-01-25 14:52:00 +0000
+++ b/mysql-test/r/order_by_none.result 2012-02-01 09:59:13 +0000
@@ -2638,4 +2638,36 @@ c
10
NULL
DROP TABLE t1;
+#
+# Bug #13528826
+# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+#
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+# number of rows in t1 was incorrectly used as an
+# implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2012-01-31 07:45:59 +0000
+++ b/sql/sql_select.cc 2012-02-01 09:59:13 +0000
@@ -4684,7 +4684,7 @@ bool JOIN::change_result(select_result *
@param ref_key
* 0 <= key < MAX_KEY - key number (hint) to start the search
* -1 - no key number provided
- @param select_limit LIMIT value
+ @param select_limit LIMIT value, or HA_POS_ERROR if no limit
@param [out] new_key Key number if success, otherwise undefined
@param [out] new_key_direction Return -1 (reverse) or +1 if success,
otherwise undefined
@@ -4732,6 +4732,7 @@ test_if_cheaper_ordering(const JOIN_TAB
ha_rows table_records= table->file->stats.records;
bool group= join && join->group && order == join->group_list;
ha_rows ref_key_quick_rows= HA_POS_ERROR;
+ const bool has_limit= (select_limit != HA_POS_ERROR);
/*
If not used with LIMIT, only use keys if the whole query can be
@@ -4824,7 +4825,7 @@ test_if_cheaper_ordering(const JOIN_TAB
be included into the result set.
*/
if (select_limit > table_records/rec_per_key)
- select_limit= table_records;
+ select_limit= table_records;
else
select_limit= (ha_rows) (select_limit*rec_per_key);
}
@@ -4905,7 +4906,7 @@ test_if_cheaper_ordering(const JOIN_TAB
*new_key= best_key;
*new_key_direction= best_key_direction;
- *new_select_limit= best_select_limit;
+ *new_select_limit= has_limit ? best_select_limit : table_records;
if (new_used_key_parts != NULL)
*new_used_key_parts= best_key_parts;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (ole.john.aske:3817 to 3818) Bug#13528826 | Ole John Aske | 1 Feb |
