[Commits] Rev 3778: Fixed bugs mdev-5927 and mdev-6116. in file:///home/igor/maria/maria-5.3-mdev5927/

Igor Babaev igor at askmonty.org
Thu Apr 17 08:34:53 EEST 2014


At file:///home/igor/maria/maria-5.3-mdev5927/

------------------------------------------------------------
revno: 3778
revision-id: igor at askmonty.org-20140417053452-16dln5q5jqj5pcm0
parent: knielsen at knielsen-hq.org-20140411073855-l9czi7ebpqbl7bc6
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mdev5927
timestamp: Wed 2014-04-16 22:34:52 -0700
message:
  Fixed bugs mdev-5927 and mdev-6116.
  Both bugs are caused by the same problem: the function optimize_cond() should
  update the value of *cond_equal rather than the value of join->cond_equal,
  because it is called not only for the WHERE condition, but for the HAVING
  condition as well.
-------------- next part --------------
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2013-02-21 02:01:36 +0000
+++ b/mysql-test/r/having.result	2014-04-17 05:34:52 +0000
@@ -635,3 +635,32 @@
 set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 End of 5.2 tests
+#
+# Bug mdev-6116: an equality in the conjunction of HAVING
+#                and IN subquery in WHERE
+# (The bug is caused by the same problem as bug mdev-5927)
+#
+CREATE TABLE t1 (f_key varchar(1), f_nokey varchar(1), INDEX(f_key));
+INSERT INTO t1 VALUES ('v','v'),('s','s');
+CREATE TABLE t2 (f_int int, f_key varchar(1), INDEX(f_key));
+INSERT INTO t2 VALUES 
+(4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),
+(9,'t'),(3,'d'),(8,'s'),(1,'r'),(8,'m'),(8,'b'),(5,'x');
+SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
+WHERE t1.f_nokey IN ( 
+SELECT t1.f_key FROM t1, t2 WHERE t1.f_key = t2.f_key 
+) HAVING t2.f_int >= 0 AND t2.f_int != 0;
+f_int
+6
+8
+DROP TABLE t1,t2;
+#
+# Bug mdev-5927: an equality in the conjunction of HAVING
+#                and an equality in WHERE
+#
+CREATE TABLE t1 (pk int PRIMARY KEY, f int NOT NULL, INDEX(f));
+INSERT INTO t1 VALUES (1,0), (2,8);
+SELECT * FROM t1 WHERE f = 2 HAVING ( pk IN ( SELECT 9 ) AND f != 0 );
+pk	f
+DROP TABLE t1;
+End of 5.3 tests

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2012-02-26 10:42:45 +0000
+++ b/mysql-test/t/having.test	2014-04-17 05:34:52 +0000
@@ -657,3 +657,39 @@
 drop table t1,t2,t3;
 
 --echo End of 5.2 tests
+
+--echo #
+--echo # Bug mdev-6116: an equality in the conjunction of HAVING
+--echo #                and IN subquery in WHERE
+--echo # (The bug is caused by the same problem as bug mdev-5927)
+--echo #
+
+CREATE TABLE t1 (f_key varchar(1), f_nokey varchar(1), INDEX(f_key));
+INSERT INTO t1 VALUES ('v','v'),('s','s');
+
+CREATE TABLE t2 (f_int int, f_key varchar(1), INDEX(f_key));
+INSERT INTO t2 VALUES 
+(4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),
+(9,'t'),(3,'d'),(8,'s'),(1,'r'),(8,'m'),(8,'b'),(5,'x');
+
+SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
+WHERE t1.f_nokey IN ( 
+  SELECT t1.f_key FROM t1, t2 WHERE t1.f_key = t2.f_key 
+) HAVING t2.f_int >= 0 AND t2.f_int != 0;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug mdev-5927: an equality in the conjunction of HAVING
+--echo #                and an equality in WHERE
+--echo #
+
+CREATE TABLE t1 (pk int PRIMARY KEY, f int NOT NULL, INDEX(f));
+INSERT INTO t1 VALUES (1,0), (2,8);
+
+SELECT * FROM t1 WHERE f = 2 HAVING ( pk IN ( SELECT 9 ) AND f != 0 );
+
+DROP TABLE t1;
+
+--echo End of 5.3 tests
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2014-03-18 08:06:32 +0000
+++ b/sql/sql_select.cc	2014-04-17 05:34:52 +0000
@@ -13234,7 +13234,7 @@
     conds= remove_eq_conds(thd, conds, cond_value);
     if (conds && conds->type() == Item::COND_ITEM &&
         ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC)
-      join->cond_equal= &((Item_cond_and*) conds)->cond_equal;
+      *cond_equal= &((Item_cond_and*) conds)->cond_equal;
     DBUG_EXECUTE("info",print_where(conds,"after remove", QT_ORDINARY););
   }
   DBUG_RETURN(conds);



More information about the commits mailing list