[Commits] Rev 3684: Fixed bug mdev-4944. in file:///home/igor/maria/maria-5.3-mdev4944/

Igor Babaev igor at askmonty.org
Mon Aug 26 22:55:59 EEST 2013


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

------------------------------------------------------------
revno: 3684
revision-id: igor at askmonty.org-20130826195558-bdfh22gblb7e5ahr
parent: igor at askmonty.org-20130824051702-jp6c9dwdstk3i83u
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mdev4944
timestamp: Mon 2013-08-26 12:55:58 -0700
message:
  Fixed bug mdev-4944.
  The patch to fix mdev-4418 turned out to be incorrect.
  At the substitution of single row tables in make_join_statistics()
  the used multiple equalities may change and references to the new multiple
  equalities must be updated. The function remove_eq_conds() takes care of it and
  it should be called right after the substitution of single row tables.
  Calling it after the call of make_join_statistics was a mistake.
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2013-08-24 05:17:02 +0000
+++ b/mysql-test/r/join_outer.result	2013-08-26 19:55:58 +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`))))
+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
 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-08-24 05:17:02 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2013-08-26 19:55:58 +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`))))
+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
 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-17 05:01:47 +0000
+++ b/mysql-test/r/select.result	2013-08-26 19:55:58 +0000
@@ -5235,4 +5235,23 @@
 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;
+#
+# Bug mdev-4944: range conditition in OR formula with fields
+#                belonging to multiple equalities 
+#
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where 0
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+i1	j1	i2
+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-17 05:01:47 +0000
+++ b/mysql-test/r/select_jcl6.result	2013-08-26 19:55:58 +0000
@@ -5246,6 +5246,25 @@
 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;
+#
+# Bug mdev-4944: range conditition in OR formula with fields
+#                belonging to multiple equalities 
+#
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where 0
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+i1	j1	i2
+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-17 05:01:47 +0000
+++ b/mysql-test/r/select_pkeycache.result	2013-08-26 19:55:58 +0000
@@ -5235,4 +5235,23 @@
 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;
+#
+# Bug mdev-4944: range conditition in OR formula with fields
+#                belonging to multiple equalities 
+#
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where 0
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+i1	j1	i2
+DROP TABLE t1,t2;
 End of 5.3 tests

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2013-08-17 05:01:47 +0000
+++ b/mysql-test/r/subselect.result	2013-08-26 19:55:58 +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)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) 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-08-17 05:01:47 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2013-08-26 19:55:58 +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)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) 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	2013-08-17 05:01:47 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2013-08-26 19:55:58 +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)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) 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-08-17 05:01:47 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2013-08-26 19:55:58 +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)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) 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	2013-08-17 05:01:47 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2013-08-26 19:55:58 +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)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) 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	2013-08-17 05:01:47 +0000
+++ b/mysql-test/r/table_elim.result	2013-08-26 19:55:58 +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`
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
 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`))
+Note	1003	select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t0`.`a`)) where 1
 # 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/t/select.test'
--- a/mysql-test/t/select.test	2013-08-17 05:01:47 +0000
+++ b/mysql-test/t/select.test	2013-08-26 19:55:58 +0000
@@ -4389,4 +4389,23 @@
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug mdev-4944: range conditition in OR formula with fields
+--echo #                belonging to multiple equalities 
+--echo #
+
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 
+  WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+SELECT * FROM t1, t2 
+  WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+
+DROP TABLE t1,t2;
+
 --echo End of 5.3 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-08-24 05:17:02 +0000
+++ b/sql/sql_select.cc	2013-08-26 19:55:58 +0000
@@ -1211,17 +1211,12 @@
     conds=new Item_int((longlong) 1,1);	// Always true
   }
 
-  if (const_tables && conds)
+  if (impossible_where)
   {
-    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);
-      conds=new Item_int((longlong) 0,1);
-      goto setup_subq_exit;
-    }
+    zero_result_cause=
+      "Impossible WHERE noticed after reading const tables";
+    select_lex->mark_const_derived(zero_result_cause);
+    goto setup_subq_exit;
   }
 
   select= make_select(*table, const_table_map,
@@ -3528,6 +3523,18 @@
     }
   }
 
+  join->impossible_where= false;
+  if (conds && const_count)
+  { 
+    conds= remove_eq_conds(join->thd, conds, &join->cond_value);
+    if (join->cond_value == Item::COND_FALSE)
+    {
+      join->impossible_where= true;
+      conds=new Item_int((longlong) 0,1);
+    }
+    join->conds= conds;      
+  }
+
   /* Calc how many (possible) matched records in each table */
 
   for (s=stat ; s < stat_end ; s++)

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2013-02-08 05:46:02 +0000
+++ b/sql/sql_select.h	2013-08-26 19:55:58 +0000
@@ -1139,6 +1139,11 @@
   bool need_tmp, hidden_group_fields;
   DYNAMIC_ARRAY keyuse;
   Item::cond_result cond_value, having_value;
+  /**
+    Impossible where after reading const tables 
+    (set in make_join_statistics())
+  */
+  bool impossible_where; 
   List<Item> all_fields; ///< to store all fields that used in query
   ///Above list changed to use temporary table
   List<Item> tmp_all_fields1, tmp_all_fields2, tmp_all_fields3;



More information about the commits mailing list