bzr commit into mysql-5.1 branch (kgeorge:2673) Bug#37548

[Georgi Kodinov]

#At file:///home/kgeorge/mysql/bzr/B37548-5.1-bugteam/

 2673 Georgi Kodinov	2008-08-27
      Bug#37548: result value erronously reported being NULL in certain subqueries
            
      When switching to indexed ORDER BY we must be sure to reset the index read
      flag if we are switching from a covering index to non-covering.
modified:
  mysql-test/r/subselect.result
  mysql-test/t/subselect.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/subselect.result
    Bug#37548: test case
  mysql-test/t/subselect.test
    Bug#37548: test case
  sql/sql_select.cc
    Bug#37548: update the index read flag if the index for indexed ORDER BY is not
        covering.
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2008-05-16 16:28:24 +0000
+++ b/mysql-test/r/subselect.result	2008-08-27 15:19:22 +0000
@@ -4391,3 +4391,42 @@ SELECT * FROM t1 WHERE _utf8'a' = ANY (S
 s1
 a
 DROP TABLE t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY b (b));
+INSERT INTO t1 VALUES (1,NULL), (9,NULL);
+CREATE TABLE t2 (
+a int,
+b int,
+c int,
+d int,
+PRIMARY KEY (a),
+UNIQUE KEY b (b,c,d),
+KEY b_2 (b),
+KEY c (c),
+KEY d (d)
+);
+INSERT INTO t2 VALUES 
+(43, 2, 11 ,30),
+(44, 2, 12 ,30),
+(45, 1, 1  ,10000),
+(46, 1, 2  ,10000),
+(556,1, 32 ,10000);
+CREATE TABLE t3 (
+a int,
+b int,
+c int,
+PRIMARY KEY (a),
+UNIQUE KEY b (b,c),
+KEY c (c),
+KEY b_2 (b)
+);
+INSERT INTO t3 VALUES (1,1,1), (2,32,1);
+explain 
+SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	index	b,b_2	b	10	NULL	2	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.b	1	Using index
+2	DEPENDENT SUBQUERY	t2	index	b,b_2,c	d	5	NULL	1	Using where
+SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
+a	incorrect
+1	1
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2008-05-16 16:03:50 +0000
+++ b/mysql-test/t/subselect.test	2008-08-27 15:19:22 +0000
@@ -3273,3 +3273,47 @@ INSERT INTO t1 VALUES ('a');
 SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
 DROP TABLE t1;
 
+#
+# Bug #37548: result value erronously reported being NULL in certain subqueries
+#
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY b (b));
+
+INSERT INTO t1 VALUES (1,NULL), (9,NULL);
+
+CREATE TABLE t2 (
+  a int,
+  b int,
+  c int,
+  d int,
+  PRIMARY KEY (a),
+  UNIQUE KEY b (b,c,d),
+  KEY b_2 (b),
+  KEY c (c),
+  KEY d (d)
+);
+
+INSERT INTO t2 VALUES 
+  (43, 2, 11 ,30),
+  (44, 2, 12 ,30),
+  (45, 1, 1  ,10000),
+  (46, 1, 2  ,10000),
+  (556,1, 32 ,10000);
+
+CREATE TABLE t3 (
+  a int,
+  b int,
+  c int,
+  PRIMARY KEY (a),
+  UNIQUE KEY b (b,c),
+  KEY c (c),
+  KEY b_2 (b)
+);
+
+INSERT INTO t3 VALUES (1,1,1), (2,32,1);
+
+explain 
+SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
+SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
+
+DROP TABLE t1,t2,t3;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-05-16 16:03:50 +0000
+++ b/sql/sql_select.cc	2008-08-27 15:19:22 +0000
@@ -13155,6 +13155,16 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
             table->key_read=1;
             table->file->extra(HA_EXTRA_KEYREAD);
           }
+          else if (table->key_read)
+          {
+            /*
+              Clear the covering key read flags that might have been
+              previously set for some key other than the current best_key.
+            */
+            table->key_read= 0;
+            table->file->extra(HA_EXTRA_NO_KEYREAD);
+          }
+
           table->file->ha_index_or_rnd_end();
           if (join->select_options & SELECT_DESCRIBE)
           {