bzr commit into mysql-6.0 branch (sergefp:2714) Bug#39447

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-6.0-look/ based on
revid:alik@stripped

 2714 Sergey Petrunia	2008-12-29
      BUG#39447: Error with NOT NULL condition and LIMIT 1
      - in test_if_skip_sort_order(): if we first decided to use one index and use index 
        condition pushdown but then re-considered and picked another index, undo the index

        condition pushdown properly. 
modified:
  mysql-test/r/innodb_mrr.result
  mysql-test/t/innodb_mrr.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/innodb_mrr.result
    BUG#39447: Error with NOT NULL condition and LIMIT 1
    - Testcase
  mysql-test/t/innodb_mrr.test
    BUG#39447: Error with NOT NULL condition and LIMIT 1
    - Testcase
  sql/sql_select.cc
    BUG#39447: Error with NOT NULL condition and LIMIT 1
    - in test_if_skip_sort_order(): if we first decided to use one index and use index 
      condition pushdown but then re-considered and picked another index, undo the index 
      condition pushdown properly.
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result	2008-09-05 14:36:37 +0000
+++ b/mysql-test/r/innodb_mrr.result	2008-12-29 03:42:30 +0000
@@ -351,3 +351,32 @@ pk
 1
 5
 drop table t1;
+#
+# BUG#39447: Error with NOT NULL condition and LIMIT 1
+#
+CREATE TABLE t1 (                 
+id int(11) NOT NULL,                
+parent_id int(11) DEFAULT NULL,     
+name varchar(10) DEFAULT NULL,      
+PRIMARY KEY (id),                   
+KEY ind_parent_id (parent_id)     
+) ENGINE=InnoDB;
+insert into t1 (id, parent_id, name) values
+(10,NULL,'A'),
+(20,10,'B'),
+(30,10,'C'),
+(40,NULL,'D'),
+(50,40,'E'),
+(60,40,'F'),
+(70,NULL,'J');
+SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+id
+60
+This must show type=index, extra=Using where
+explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	ind_parent_id	PRIMARY	4	NULL	2	Using where
+SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+id	parent_id	name
+60	40	F
+drop table t1;

=== modified file 'mysql-test/t/innodb_mrr.test'
--- a/mysql-test/t/innodb_mrr.test	2008-09-05 14:36:37 +0000
+++ b/mysql-test/t/innodb_mrr.test	2008-12-29 03:42:30 +0000
@@ -99,3 +99,28 @@ select pk from t1 WHERE `varchar_key` > 
 select pk from t1 WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
 drop table t1;
 
+--echo #
+--echo # BUG#39447: Error with NOT NULL condition and LIMIT 1
+--echo #
+CREATE TABLE t1 (                 
+   id int(11) NOT NULL,                
+   parent_id int(11) DEFAULT NULL,     
+   name varchar(10) DEFAULT NULL,      
+   PRIMARY KEY (id),                   
+   KEY ind_parent_id (parent_id)     
+) ENGINE=InnoDB;
+
+insert into t1 (id, parent_id, name) values
+(10,NULL,'A'),
+(20,10,'B'),
+(30,10,'C'),
+(40,NULL,'D'),
+(50,40,'E'),
+(60,40,'F'),
+(70,NULL,'J');
+
+SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+--echo This must show type=index, extra=Using where
+explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+drop table t1;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-12-16 11:29:22 +0000
+++ b/sql/sql_select.cc	2008-12-29 03:42:30 +0000
@@ -16495,9 +16495,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           }
           if (tab->pre_idx_push_select_cond)
           {
+            tab->select_cond= tab->pre_idx_push_select_cond;
             if (tab->select)
               tab->select->cond= tab->select_cond;
-            tab->select_cond= tab->pre_idx_push_select_cond;
           }
           table->file->ha_index_or_rnd_end();
           if (join->select_options & SELECT_DESCRIBE)