From: Ole John Aske
Date: January 25 2012 2:52pm
Subject: bzr push into mysql-trunk branch (ole.john.aske:3784 to 3785) Bug#13531865
List-Archive: http://lists.mysql.com/commits/142549
X-Bug: 13531865
Message-Id: <20120125145242.B5689235@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 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).