MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:May 12 2010 3:35pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (epotemkin:3167)
Bug#45267
View as plain text  
#At file:///work/bzrroot/backporting-next-mr-bugfixing/ based on revid:epotemkin@stripped

 3167 Evgeny Potemkin	2010-05-12
      Bug#45267: Incomplete check caused wrong result.
      
      If a join buffer is used to join a table the ordering by an index for the
      first non-constant table cannot be employed unless ORDER BY/GROUP BY clauses
      are optimized away. The JOIN::get_sort_by_join_tab function is employed to
      choose whether to sort result with filesort or to use index scan.
      It wasn't checking whether the GROUP BY clause was optimized away and thus
      returned wrong result in this case. All this caused the optimizer to choose
      wrong execution plan and return wrong query result.
      
      The JOIN::get_sort_by_join_tab function now checks whether the GROUP BY
      clause was optimized away.
      
      Original revid:epotemkin@stripped
      Only the test case is added as the code is already present.
     @ mysql-test/r/join_cache.result
        A test case added for the bug#45267.
     @ mysql-test/t/join_cache.test
        A test case added for the bug#45267.

    modified:
      mysql-test/r/join_cache.result
      mysql-test/t/join_cache.test
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-05-11 16:23:10 +0000
+++ b/mysql-test/r/join_cache.result	2010-05-12 15:35:38 +0000
@@ -4185,3 +4185,30 @@ id	select_type	table	type	possible_keys	
 
 SET optimizer_join_cache_level=default;
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug#45267: Incomplete check caused wrong result.
+#
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+);
+CREATE TABLE t3 (
+`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+);
+INSERT INTO t3 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
+(16),(17),(18),(19),(20);
+CREATE TABLE t2 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`time_key` time NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `time_key` (`time_key`)
+);
+INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46');
+SELECT DISTINCT t1.`pk`
+FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey`  ON t2.`time_key`
+GROUP BY 1;
+pk
+NULL
+DROP TABLE IF EXISTS t1, t2, t3;
+#

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-05-10 20:26:08 +0000
+++ b/mysql-test/t/join_cache.test	2010-05-12 15:35:38 +0000
@@ -1845,4 +1845,31 @@ SET optimizer_join_cache_level=default;
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # Bug#45267: Incomplete check caused wrong result.
+--echo #
+CREATE TABLE t1 (
+  `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+);
+CREATE TABLE t3 (
+  `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+);
+INSERT INTO t3 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
+(16),(17),(18),(19),(20);
+CREATE TABLE t2 (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `int_nokey` int(11) NOT NULL,
+  `time_key` time NOT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `time_key` (`time_key`)
+);
+INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46');
 
+SELECT DISTINCT t1.`pk`
+FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey`  ON t2.`time_key`
+GROUP BY 1;
+
+DROP TABLE IF EXISTS t1, t2, t3;
+
+--echo #


Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20100512153538-jlai4lso3cpmcqer.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (epotemkin:3167)Bug#45267Evgeny Potemkin12 May