MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:September 5 2008 2:36pm
Subject:bzr commit into mysql-6.0-opt branch (sergefp:2686) Bug#37977
View as plain text  
#At file:///home/spetrunia/dev/mysql-6.0-opt-look/

 2686 Sergey Petrunia	2008-09-05
      BUG#37977: Wrong result returned on GROUP BY + OR + Innodb
      - Make test_if_skip_sort_order() restore the where condition if it 
        decides not to use the index that make_join_readinfo() has previously 
        decided to use. We need to do this because part of the condition may 
        have been pushed down to storage engine with push_index_cond() 
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
    Testcase
  mysql-test/t/innodb_mrr.test
    Testcase
  sql/sql_select.cc
    BUG#37977: Wrong result returned on GROUP BY + OR + Innodb
    - Make test_if_skip_sort_order() restore the where condition if it 
      decides not to use the index that make_join_readinfo() has previously 
      decided to use. We need to do this because part of the condition may 
      have been pushed down to storage engine with push_index_cond()
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result	2008-01-24 00:46:18 +0000
+++ b/mysql-test/r/innodb_mrr.result	2008-09-05 14:36:37 +0000
@@ -314,3 +314,40 @@ f1	f2	f3	f4
 9	9	9	A
 10	10	10	A
 drop table t1;
+
+BUG#37977: Wrong result returned on GROUP BY + OR + Innodb
+
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`int_key` int(11) NOT NULL,
+`date_key` date NOT NULL,
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`time_nokey` time NOT NULL,
+`datetime_key` datetime NOT NULL,
+`datetime_nokey` datetime NOT NULL,
+`varchar_key` varchar(5) DEFAULT NULL,
+`varchar_nokey` varchar(5) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `date_key` (`date_key`),
+KEY `time_key` (`time_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES 
+(1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
+(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
+(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
+(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
+(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
+select pk from t1 WHERE `varchar_key` > 'kr' group by pk;
+pk
+1
+5
+select pk from t1 WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
+pk
+1
+5
+drop table t1;

=== modified file 'mysql-test/t/innodb_mrr.test'
--- a/mysql-test/t/innodb_mrr.test	2008-01-24 00:46:18 +0000
+++ b/mysql-test/t/innodb_mrr.test	2008-09-05 14:36:37 +0000
@@ -66,3 +66,36 @@ select * from t1 where (f3>=5 and f3<=10
 
 drop table t1;
 
+--echo
+--echo BUG#37977: Wrong result returned on GROUP BY + OR + Innodb
+--echo
+CREATE TABLE t1 (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `int_nokey` int(11) NOT NULL,
+  `int_key` int(11) NOT NULL,
+  `date_key` date NOT NULL,
+  `date_nokey` date NOT NULL,
+  `time_key` time NOT NULL,
+  `time_nokey` time NOT NULL,
+  `datetime_key` datetime NOT NULL,
+  `datetime_nokey` datetime NOT NULL,
+  `varchar_key` varchar(5) DEFAULT NULL,
+  `varchar_nokey` varchar(5) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `int_key` (`int_key`),
+  KEY `date_key` (`date_key`),
+  KEY `time_key` (`time_key`),
+  KEY `datetime_key` (`datetime_key`),
+  KEY `varchar_key` (`varchar_key`)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES 
+(1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
+(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
+(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
+(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
+(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
+select pk from t1 WHERE `varchar_key` > 'kr' group by pk;
+select pk from t1 WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
+drop table t1;
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-08-16 15:22:47 +0000
+++ b/sql/sql_select.cc	2008-09-05 14:36:37 +0000
@@ -16169,6 +16169,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
             table->key_read=1;
             table->file->extra(HA_EXTRA_KEYREAD);
           }
+          if (tab->pre_idx_push_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)
           {

Thread
bzr commit into mysql-6.0-opt branch (sergefp:2686) Bug#37977Sergey Petrunia7 Sep