List:Commits« Previous MessageNext Message »
From:Oystein.Grovlen Date:May 6 2010 7:43pm
Subject:bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3138 to
3139) Bug#46733
View as plain text  
 3139 oystein.grovlen@stripped	2010-05-06
      Bug#46733 - NULL value not returned for aggregate on empty result 
                  set w/ semijoin on
      
      Issue was fixed by BUG#46328. This patch adds a regression test
      for the query that was reported in BUG#46733.
     @ mysql-test/r/join_cache.result
        Add regression test for BUG#46733
     @ mysql-test/t/join_cache.test
        Add regression test for BUG#46733

    modified:
      mysql-test/r/join_cache.result
      mysql-test/t/join_cache.test
 3138 Roy Lyseng	2010-05-06
      Bug#38075: Wrong result: rows matching a subquery with outer join not returned
        
      The problem is that duplicate weedout handling in evaluate_join_record() is
      incompatible with outer-join condition handling.
      Duplicate weedout checked only the initial predicate, but not the conditional predicates.
      Hence, all the row combinations involving the first row of the innermost
      table that qualified according to the initial predicate would be candidates
      for duplicate elimination. This prevented all row combinations where the
      first row to qualify from the innermost table was not the first row where the
      initial predicate evaluated to true.
      Here, "initial predicate" refers to join_tab->select_cond and "conditional
      predicates" refers to select_cond of "unmatched" join_tabs.
        
      Solved by considering the "found" boolean when performing duplicate elimination.
      Notice also that duplicate elimination now sets "found" instead of exiting
      from routine. This ensures correct book-keeping of e.g examined_rows.
        
      The fix also eliminates one boolean local variable and adds a function header.
        
      mysql-test/r/subselect_sj2.result
        Added test result for bug#38075.
      mysql-test/r/subselect_sj2_jcl6.result
        Added test result for bug#38075.
      mysql-test/t/subselect_sj2.test
        Added test for bug#38075.
      sql/sql_select.cc
        Considers outer-join condition before executing duplicate elimination code.
        Eliminated a boolean variable (select_cond_result).
        Added Doxygen style function header for evaluate_join_record().
      
      original revid: -c3719.1.70

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/r/subselect_sj_jcl6.result
      mysql-test/t/subselect_sj.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-05-06 14:11:28 +0000
+++ b/mysql-test/r/join_cache.result	2010-05-06 19:42:45 +0000
@@ -4018,3 +4018,38 @@ c1	c2	c1	c2	LENGTH(t2.c1)	LENGTH(t2.c2)
 2	2	tt	uu	2	2
 set join_cache_level=default;
 DROP TABLE t1,t2;
+#
+# Regression test for
+# Bug#46733 - NULL value not returned for aggregate on empty result 
+#             set w/ semijoin on
+CREATE TABLE t1 (
+i int(11) NOT NULL,
+v varchar(1) DEFAULT NULL,
+PRIMARY KEY (i)
+);
+INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d');
+CREATE TABLE t2 (
+i int(11) NOT NULL,
+v varchar(1) DEFAULT NULL,
+PRIMARY KEY (i)
+);
+INSERT INTO t2 VALUES (1,'x'),(2,'y');
+
+SELECT MAX(t1.i) 
+FROM t1 JOIN t2 ON t2.v
+ORDER BY t2.v;
+MAX(t1.i)
+NULL
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'x'
+Warning	1292	Truncated incorrect INTEGER value: 'y'
+
+EXPLAIN
+SELECT MAX(t1.i) 
+FROM t1 JOIN t2 ON t2.v
+ORDER BY t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index; Using join buffer
+
+DROP TABLE t1,t2;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-05-06 14:11:28 +0000
+++ b/mysql-test/t/join_cache.test	2010-05-06 19:42:45 +0000
@@ -1686,3 +1686,42 @@ set join_cache_level=default;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Regression test for
+--echo # Bug#46733 - NULL value not returned for aggregate on empty result 
+--echo #             set w/ semijoin on
+CREATE TABLE t1 (
+  i int(11) NOT NULL,
+  v varchar(1) DEFAULT NULL,
+  PRIMARY KEY (i)
+);
+
+INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d');
+
+CREATE TABLE t2 (
+  i int(11) NOT NULL,
+  v varchar(1) DEFAULT NULL,
+  PRIMARY KEY (i)
+);
+
+INSERT INTO t2 VALUES (1,'x'),(2,'y');
+
+--echo
+
+SELECT MAX(t1.i) 
+FROM t1 JOIN t2 ON t2.v
+ORDER BY t2.v;
+
+--echo
+
+EXPLAIN
+SELECT MAX(t1.i) 
+FROM t1 JOIN t2 ON t2.v
+ORDER BY t2.v;
+
+--echo
+
+DROP TABLE t1,t2;
+
+
+


Attachment: [text/bzr-bundle]
Thread
bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3138 to3139) Bug#46733Oystein.Grovlen6 May