summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/compress.result4
-rw-r--r--mysql-test/r/join.result164
-rw-r--r--mysql-test/r/select.result4
-rw-r--r--mysql-test/r/ssl.result4
-rw-r--r--mysql-test/r/ssl_compress.result4
-rw-r--r--mysql-test/t/join.test29
-rw-r--r--sql/sql_select.cc12
7 files changed, 207 insertions, 14 deletions
diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result
index 68206c0dc0e..a71d840d229 100644
--- a/mysql-test/r/compress.result
+++ b/mysql-test/r/compress.result
@@ -611,11 +611,11 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ref period period 4 test.t1.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t3 index period period 4 NULL 1
1 SIMPLE t1 ref period period 4 test.t3.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t1 index period period 4 NULL 1
1 SIMPLE t3 ref period period 4 test.t1.period 4181
select period from t1;
period
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index fcb141a3510..9b799fccf06 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -897,4 +897,168 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than
Z
^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error
drop table t1, t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
+INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
+INSERT INTO t1 SELECT a + 4, b FROM t1;
+INSERT INTO t1 SELECT a + 8, b FROM t1;
+INSERT INTO t1 SELECT a + 16, b FROM t1;
+INSERT INTO t1 SELECT a + 32, b FROM t1;
+INSERT INTO t1 SELECT a + 64, b FROM t1;
+INSERT INTO t2 SELECT a, b FROM t1;
+EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
+a b c d
+SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
+a b c d
+1 NULL 1 NULL
+2 NULL 2 NULL
+EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 128 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
+a b c d
+SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
+a b c d
+1 NULL 1 NULL
+2 NULL 2 NULL
+3 NULL 3 NULL
+4 NULL 4 NULL
+5 NULL 5 NULL
+6 NULL 6 NULL
+7 NULL 7 NULL
+8 NULL 8 NULL
+9 NULL 9 NULL
+10 NULL 10 NULL
+11 NULL 11 NULL
+12 NULL 12 NULL
+13 NULL 13 NULL
+14 NULL 14 NULL
+15 NULL 15 NULL
+16 NULL 16 NULL
+17 NULL 17 NULL
+18 NULL 18 NULL
+19 NULL 19 NULL
+20 NULL 20 NULL
+21 NULL 21 NULL
+22 NULL 22 NULL
+23 NULL 23 NULL
+24 NULL 24 NULL
+25 NULL 25 NULL
+26 NULL 26 NULL
+27 NULL 27 NULL
+28 NULL 28 NULL
+29 NULL 29 NULL
+30 NULL 30 NULL
+31 NULL 31 NULL
+32 NULL 32 NULL
+33 NULL 33 NULL
+34 NULL 34 NULL
+35 NULL 35 NULL
+36 NULL 36 NULL
+37 NULL 37 NULL
+38 NULL 38 NULL
+39 NULL 39 NULL
+40 NULL 40 NULL
+41 NULL 41 NULL
+42 NULL 42 NULL
+43 NULL 43 NULL
+44 NULL 44 NULL
+45 NULL 45 NULL
+46 NULL 46 NULL
+47 NULL 47 NULL
+48 NULL 48 NULL
+49 NULL 49 NULL
+50 NULL 50 NULL
+51 NULL 51 NULL
+52 NULL 52 NULL
+53 NULL 53 NULL
+54 NULL 54 NULL
+55 NULL 55 NULL
+56 NULL 56 NULL
+57 NULL 57 NULL
+58 NULL 58 NULL
+59 NULL 59 NULL
+60 NULL 60 NULL
+61 NULL 61 NULL
+62 NULL 62 NULL
+63 NULL 63 NULL
+64 NULL 64 NULL
+65 NULL 65 NULL
+66 NULL 66 NULL
+67 NULL 67 NULL
+68 NULL 68 NULL
+69 NULL 69 NULL
+70 NULL 70 NULL
+71 NULL 71 NULL
+72 NULL 72 NULL
+73 NULL 73 NULL
+74 NULL 74 NULL
+75 NULL 75 NULL
+76 NULL 76 NULL
+77 NULL 77 NULL
+78 NULL 78 NULL
+79 NULL 79 NULL
+80 NULL 80 NULL
+81 NULL 81 NULL
+82 NULL 82 NULL
+83 NULL 83 NULL
+84 NULL 84 NULL
+85 NULL 85 NULL
+86 NULL 86 NULL
+87 NULL 87 NULL
+88 NULL 88 NULL
+89 NULL 89 NULL
+90 NULL 90 NULL
+91 NULL 91 NULL
+92 NULL 92 NULL
+93 NULL 93 NULL
+94 NULL 94 NULL
+95 NULL 95 NULL
+96 NULL 96 NULL
+97 NULL 97 NULL
+98 NULL 98 NULL
+99 NULL 99 NULL
+100 NULL 100 NULL
+101 NULL 101 NULL
+102 NULL 102 NULL
+103 NULL 103 NULL
+104 NULL 104 NULL
+105 NULL 105 NULL
+106 NULL 106 NULL
+107 NULL 107 NULL
+108 NULL 108 NULL
+109 NULL 109 NULL
+110 NULL 110 NULL
+111 NULL 111 NULL
+112 NULL 112 NULL
+113 NULL 113 NULL
+114 NULL 114 NULL
+115 NULL 115 NULL
+116 NULL 116 NULL
+117 NULL 117 NULL
+118 NULL 118 NULL
+119 NULL 119 NULL
+120 NULL 120 NULL
+121 NULL 121 NULL
+122 NULL 122 NULL
+123 NULL 123 NULL
+124 NULL 124 NULL
+125 NULL 125 NULL
+126 NULL 126 NULL
+127 NULL 127 NULL
+128 NULL 128 NULL
+DROP TABLE IF EXISTS t1,t2;
End of 5.0 tests.
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index ed61cfffa6b..d1d85aef0ec 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -607,11 +607,11 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ref period period 4 test.t1.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t3 index period period 4 NULL 1
1 SIMPLE t1 ref period period 4 test.t3.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t1 index period period 4 NULL 1
1 SIMPLE t3 ref period period 4 test.t1.period 4181
select period from t1;
period
diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result
index 1f1a6ec9e22..3622fb51c99 100644
--- a/mysql-test/r/ssl.result
+++ b/mysql-test/r/ssl.result
@@ -608,11 +608,11 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ref period period 4 test.t1.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t3 index period period 4 NULL 1
1 SIMPLE t1 ref period period 4 test.t3.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t1 index period period 4 NULL 1
1 SIMPLE t3 ref period period 4 test.t1.period 4181
select period from t1;
period
diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result
index e77fcefeafd..3018fce5cad 100644
--- a/mysql-test/r/ssl_compress.result
+++ b/mysql-test/r/ssl_compress.result
@@ -611,11 +611,11 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ref period period 4 test.t1.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t3 index period period 4 NULL 1
1 SIMPLE t1 ref period period 4 test.t3.period 4181
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort
+1 SIMPLE t1 index period period 4 NULL 1
1 SIMPLE t3 ref period period 4 test.t1.period 4181
select period from t1;
period
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 14c98431970..ed1b84bb5ec 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -698,4 +698,33 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than
drop table t1, t2;
+#
+# Bug #31094: Forcing index-based sort doesn't work anymore if joins are
+# done
+#
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
+
+INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
+INSERT INTO t1 SELECT a + 4, b FROM t1;
+INSERT INTO t1 SELECT a + 8, b FROM t1;
+INSERT INTO t1 SELECT a + 16, b FROM t1;
+INSERT INTO t1 SELECT a + 32, b FROM t1;
+INSERT INTO t1 SELECT a + 64, b FROM t1;
+INSERT INTO t2 SELECT a, b FROM t1;
+
+#expect indexed ORDER BY
+EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
+SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
+SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
+
+#expect filesort
+EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
+SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
+SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
+
+DROP TABLE IF EXISTS t1,t2;
--echo End of 5.0 tests.
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 008f2f9c70a..5f516f5e21c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12775,12 +12775,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
LINT_INIT(best_key_direction);
LINT_INIT(best_records);
- /*
- filesort() and join cache are usually faster than reading in
- index order and not using join cache
- */
- if (tab->type == JT_ALL && tab->join->tables > tab->join->const_tables + 1)
- DBUG_RETURN(0);
/*
If not used with LIMIT, only use keys if the whole query can be
resolved with a key; This is because filesort() is usually faster than
@@ -12788,6 +12782,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
*/
if (select_limit >= table_records)
{
+ /*
+ filesort() and join cache are usually faster than reading in
+ index order and not using join cache
+ */
+ if (tab->type == JT_ALL && tab->join->tables > tab->join->const_tables + 1)
+ DBUG_RETURN(0);
keys= *table->file->keys_to_use_for_scanning();
keys.merge(table->covering_keys);
View Lorry Controller Status