3785 Ole John Aske 2012-01-25
Fix for bug#13531865 TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF 'TYPE' IS REF_OR_NULL
Introduce the function is_ref_or_null_optimized() which is used by test_if_subkey()
in order to avoid changing to an index requiring 'ref_or_null' access as we can
never skip sort order for such an index.
@ mysql-test/include/order_by.inc
Added testcase: REF_OR_NULL should never be selected as access 'type' when filesort is skipped by an ordered index.
@ mysql-test/r/order_by_icp_mrr.result
corrected testcase: As 'ref_or_null' can't skip filesort, use 'ref' access as choosen by condition optimizer.
@ mysql-test/r/order_by_none.result
corrected testcase: As 'ref_or_null' can't skip filesort, use 'ref' access as choosen by condition optimizer.
modified:
mysql-test/include/order_by.inc
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
sql/sql_select.cc
3784 Ole John Aske 2012-01-25
Fix for bug#13529048 TEST_IF_SKIP_SORT_ORDER() DON'T HAVE TO FILESORT A SINGLE ROW.
test_if_skip_sort_order() should catch the jointypes
JT_EQ_REF, JT_CONST and JT_SYSTEM and skip sort order for these.
Didn't add a specific testcase for this bug as it should be
covered by existing testcases which now will skip 'Using filesort'
modified:
mysql-test/r/innodb_mrr.result
mysql-test/r/innodb_mrr_cost.result
mysql-test/r/innodb_mrr_cost_icp.result
mysql-test/r/innodb_mrr_icp.result
mysql-test/r/innodb_mrr_none.result
sql/sql_select.cc
=== modified file 'mysql-test/include/order_by.inc'
--- a/mysql-test/include/order_by.inc 2011-12-19 09:22:28 +0000
+++ b/mysql-test/include/order_by.inc 2012-01-25 14:52:00 +0000
@@ -1750,3 +1750,34 @@ INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1,2), (2,3);
SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1 ORDER BY a DESC) FROM t2;
DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug #13531865
+--echo # TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+--echo # 'TYPE' IS REF_OR_NULL
+--echo #
+--echo #
+
+CREATE TABLE t1 (
+ a INT,
+ c INT,
+ UNIQUE KEY a_c (a,c),
+ KEY (a)) engine=myisam;
+
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+
+--echo # Using 'KEY a_c' for order-by opt, would have required
+--echo # REF_OR_NULL access which never can be order_by skipped.
+--echo # -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+
+DROP TABLE t1;
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2011-12-19 09:22:28 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2012-01-25 14:52:00 +0000
@@ -1461,7 +1461,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL)
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 1 Using where
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
col
@@ -1470,7 +1470,7 @@ col
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
id select_type table type possible_keys key key_len ref rows Extra
-x x x ref_or_null a_c,a x x x x x
+x x x ref a_c,a x x x x x
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
col
@@ -2600,4 +2600,43 @@ SELECT (SELECT 1 FROM t1 WHERE a=b AND c
NULL
NULL
DROP TABLE t1, t2;
+#
+# Bug #13531865
+# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+# 'TYPE' IS REF_OR_NULL
+#
+#
+CREATE TABLE t1 (
+a INT,
+c INT,
+UNIQUE KEY a_c (a,c),
+KEY (a)) engine=myisam;
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+# Using 'KEY a_c' for order-by opt, would have required
+# REF_OR_NULL access which never can be order_by skipped.
+# -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+c
+NULL
+10
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+c
+10
+NULL
+DROP TABLE t1;
set optimizer_switch=default;
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2011-12-19 09:22:28 +0000
+++ b/mysql-test/r/order_by_none.result 2012-01-25 14:52:00 +0000
@@ -1460,7 +1460,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL)
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 1 Using where
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
col
@@ -1469,7 +1469,7 @@ col
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
id select_type table type possible_keys key key_len ref rows Extra
-x x x ref_or_null a_c,a x x x x x
+x x x ref a_c,a x x x x x
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
col
@@ -2599,4 +2599,43 @@ SELECT (SELECT 1 FROM t1 WHERE a=b AND c
NULL
NULL
DROP TABLE t1, t2;
+#
+# Bug #13531865
+# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+# 'TYPE' IS REF_OR_NULL
+#
+#
+CREATE TABLE t1 (
+a INT,
+c INT,
+UNIQUE KEY a_c (a,c),
+KEY (a)) engine=myisam;
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+# Using 'KEY a_c' for order-by opt, would have required
+# REF_OR_NULL access which never can be order_by skipped.
+# -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+c
+NULL
+10
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+c
+10
+NULL
+DROP TABLE t1;
set optimizer_switch=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2012-01-25 14:37:43 +0000
+++ b/sql/sql_select.cc 2012-01-25 14:52:00 +0000
@@ -3565,6 +3565,38 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
}
/**
+ Test if REF_OR_NULL optimization will be used if the specified
+ ref_key is used for REF-access to 'tab'
+
+ @retval
+ true JT_REF_OR_NULL will be used
+ @retval
+ false no JT_REF_OR_NULL access
+*/
+bool
+is_ref_or_null_optimized(const JOIN_TAB *tab, uint ref_key)
+{
+ if (tab->keyuse)
+ {
+ const Key_use *keyuse= tab->keyuse;
+ while (keyuse->key != ref_key && keyuse->table == tab->table)
+ keyuse++;
+
+ const table_map const_tables= tab->join->const_table_map;
+ do
+ {
+ if (!(keyuse->used_tables & ~const_tables))
+ {
+ if (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)
+ return true;
+ }
+ keyuse++;
+ } while (keyuse->key == ref_key && keyuse->table == tab->table);
+ }
+ return false;
+}
+
+/**
Test if we can use one of the 'usable_keys' instead of 'ref' key
for sorting.
@@ -3577,12 +3609,13 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
*/
static uint
-test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts,
+test_if_subkey(ORDER *order, JOIN_TAB *tab, uint ref, uint ref_key_parts,
const key_map *usable_keys)
{
uint nr;
uint min_length= (uint) ~0;
uint best= MAX_KEY;
+ TABLE *table= tab->table;
KEY_PART_INFO *ref_key_part= table->key_info[ref].key_part;
KEY_PART_INFO *ref_key_part_end= ref_key_part + ref_key_parts;
@@ -3593,6 +3626,7 @@ test_if_subkey(ORDER *order, TABLE *tabl
table->key_info[nr].key_parts >= ref_key_parts &&
is_subkey(table->key_info[nr].key_part, ref_key_part,
ref_key_part_end) &&
+ !is_ref_or_null_optimized(tab, nr) &&
test_if_order_by_key(order, table, nr))
{
min_length= table->key_info[nr].key_length;
@@ -3821,7 +3855,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
if (table->covering_keys.is_set(ref_key))
usable_keys.intersect(table->covering_keys);
- if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
+ if ((new_ref_key= test_if_subkey(order, tab, ref_key, ref_key_parts,
&usable_keys)) < MAX_KEY)
{
/* Found key that can be used to retrieve data in sorted order */
@@ -3843,6 +3877,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
tab->join->const_table_map))
goto use_filesort;
+ DBUG_ASSERT(tab->type != JT_REF_OR_NULL && tab->type != JT_FT);
pick_table_access_method(tab);
}
else
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (ole.john.aske:3784 to 3785) Bug#13531865 | Ole John Aske | 25 Jan |
