[Commits] Rev 3677: Fixed bug mdev-4418. in file:///home/igor/maria/maria-5.3-mdev4418/

Igor Babaev igor at askmonty.org
Sat Aug 17 08:01:48 EEST 2013


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

------------------------------------------------------------
revno: 3677
revision-id: igor at askmonty.org-20130817050147-k5dvrilf0u0vz8cr
parent: igor at askmonty.org-20130815235920-io2h7tlypwlbunsp
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mdev4418
timestamp: Fri 2013-08-16 22:01:47 -0700
message:
  Fixed bug mdev-4418.
  After single row substitutions there might appear new equalities.
  They should be properly propagated to all AND/OR levels the WHERE
  condition. It's done now with an additional call of remove_eq_conds(). 
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2013-05-04 05:46:45 +0000
+++ b/mysql-test/r/join_outer.result	2013-08-17 05:01:47 +0000
@@ -1649,7 +1649,7 @@
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`)))) where 1
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`))))
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2013-05-04 05:46:45 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2013-08-17 05:01:47 +0000
@@ -1660,7 +1660,7 @@
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`)))) where 1
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`))))
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON

=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2013-08-15 21:16:16 +0000
+++ b/mysql-test/r/select.result	2013-08-17 05:01:47 +0000
@@ -5217,4 +5217,22 @@
 SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
 a	b
 DROP TABLE t1;
+#
+# Bug mdev-4418: impossible multiple equality in OR formula 
+#                after row substitution
+#
+CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,'j'), (8,'v');
+CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k','k');
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136))
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+a	b	c	d
+DROP TABLE t1,t2;
 End of 5.3 tests

=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result	2013-08-15 21:16:16 +0000
+++ b/mysql-test/r/select_jcl6.result	2013-08-17 05:01:47 +0000
@@ -5228,6 +5228,24 @@
 SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
 a	b
 DROP TABLE t1;
+#
+# Bug mdev-4418: impossible multiple equality in OR formula 
+#                after row substitution
+#
+CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,'j'), (8,'v');
+CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k','k');
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136))
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+a	b	c	d
+DROP TABLE t1,t2;
 End of 5.3 tests
 set join_cache_level=default;
 show variables like 'join_cache_level';

=== modified file 'mysql-test/r/select_pkeycache.result'
--- a/mysql-test/r/select_pkeycache.result	2013-08-15 21:16:16 +0000
+++ b/mysql-test/r/select_pkeycache.result	2013-08-17 05:01:47 +0000
@@ -5217,4 +5217,22 @@
 SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
 a	b
 DROP TABLE t1;
+#
+# Bug mdev-4418: impossible multiple equality in OR formula 
+#                after row substitution
+#
+CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,'j'), (8,'v');
+CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k','k');
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136))
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+a	b	c	d
+DROP TABLE t1,t2;
 End of 5.3 tests

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2013-02-21 02:01:36 +0000
+++ b/mysql-test/r/subselect.result	2013-08-17 05:01:47 +0000
@@ -1322,7 +1322,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2013-02-21 02:01:36 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2013-08-17 05:01:47 +0000
@@ -1329,7 +1329,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-11-01 19:36:31 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2013-08-17 05:01:47 +0000
@@ -1325,7 +1325,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2013-02-21 02:01:36 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2013-08-17 05:01:47 +0000
@@ -1328,7 +1328,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-11-01 19:36:31 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2013-08-17 05:01:47 +0000
@@ -1325,7 +1325,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)

=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result	2012-05-12 08:27:26 +0000
+++ b/mysql-test/r/table_elim.result	2013-08-17 05:01:47 +0000
@@ -17,7 +17,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1`
 select t1.a from t1 left join t2 on t2.a=t1.a;
 a
 0
@@ -62,7 +62,7 @@
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t0`.`a`)) where 1
+Note	1003	select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t0`.`a`))
 # Elimination with aggregate functions
 explain select count(*) from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2013-06-06 20:33:40 +0000
+++ b/mysql-test/r/view.result	2013-08-17 05:01:47 +0000
@@ -4300,7 +4300,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select 'r' AS `f4` from `test`.`t1` where ((20 <> 0) or 0)
+Note	1003	select 'r' AS `f4` from `test`.`t1` where (20 <> 0)
 DROP VIEW v1;
 DROP TABLE t1;
 #

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2013-08-15 21:16:16 +0000
+++ b/mysql-test/t/select.test	2013-08-17 05:01:47 +0000
@@ -4372,4 +4372,21 @@
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug mdev-4418: impossible multiple equality in OR formula 
+--echo #                after row substitution
+--echo #
+
+CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,'j'), (8,'v');
+
+CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k','k');
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+
+DROP TABLE t1,t2;
+
 --echo End of 5.3 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-08-15 23:59:20 +0000
+++ b/sql/sql_select.cc	2013-08-17 05:01:47 +0000
@@ -1210,6 +1210,23 @@
     /* Handle the case where we have an OUTER JOIN without a WHERE */
     conds=new Item_int((longlong) 1,1);	// Always true
   }
+
+  if (const_tables && conds)
+  {
+    conds= remove_eq_conds(thd, conds, &cond_value);
+    if (cond_value == Item::COND_FALSE)
+    {
+      zero_result_cause=
+        "Impossible WHERE noticed after reading const tables";
+      select_lex->mark_const_derived(zero_result_cause);
+      if (select_options & SELECT_DESCRIBE)
+      {
+         conds=new Item_int((longlong) 0,1);
+      }
+      goto setup_subq_exit;
+    }
+  }
+
   select= make_select(*table, const_table_map,
                       const_table_map, conds, 1, &error);
   if (error)



More information about the commits mailing list