bk commit into 5.0 tree (evgen:1.2411) BUG#22331

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-03-05 22:32:49+03:00, evgen@stripped +10 -0
  Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
  away.
  
  During optimization stage the WHERE conditions can be changed or even
  be removed at all if they know for sure to be true of false. Thus they aren't
  showed in the EXPLAIN EXTENDED which prints conditions after optimization.
  
  Now if all elements of an Item_cond were removed this Item_cond is substituted
  for an Item_int with the int value of the Item_cond.
  If a condition found to be always true or false now it is substituted for
  an Item_int with the value of the condition.

  mysql-test/r/explain.result@stripped, 2007-03-05 21:53:57+03:00, evgen@stripped +9 -0
    Added a test case for the bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all
expressions were optimized away.

  mysql-test/r/func_test.result@stripped, 2007-03-05 21:53:54+03:00, evgen@stripped +1
-1
    Corrected test case result after fix for bug#22331.

  mysql-test/r/group_min_max.result@stripped, 2007-03-05 21:53:48+03:00, evgen@stripped
+1 -1
    Corrected test case result after fix for bug#22331.

  mysql-test/r/subselect.result@stripped, 2007-03-05 21:53:43+03:00, evgen@stripped +8
-8
    Corrected test case result after fix for bug#22331.

  mysql-test/t/explain.test@stripped, 2007-03-05 21:53:38+03:00, evgen@stripped +10 -0
    Added a test case for the bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all
expressions were optimized away.

  sql/opt_range.cc@stripped, 2007-03-05 21:59:40+03:00, evgen@stripped +4 -2
    Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
    away.
    Allow constant conditions to be used with GROUP BY MIN/MAX optimization.

  sql/sql_delete.cc@stripped, 2007-03-05 22:31:02+03:00, evgen@stripped +1 -1
    Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
    away.
    Updated call to the remove_eq_conds() function.

  sql/sql_select.cc@stripped, 2007-03-05 21:58:50+03:00, evgen@stripped +9 -5
    Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
    away.
    Now if all elements of an Item_cond were removed this Item_cond is substituted
    for an Item_int with the int value of the Item_cond.

  sql/sql_select.h@stripped, 2007-03-05 22:32:32+03:00, evgen@stripped +2 -1
    Updated the prototype of the remove_eq_conds() function.

  sql/sql_update.cc@stripped, 2007-03-05 22:32:25+03:00, evgen@stripped +1 -1
    Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
    away.
    Updated call to the remove_eq_conds() function.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/22331-bug-5.0-opt-mysql

--- 1.241/sql/opt_range.cc	2007-02-12 18:28:48 +03:00
+++ 1.242/sql/opt_range.cc	2007-03-05 21:59:40 +03:00
@@ -1960,7 +1960,7 @@
     TRP_GROUP_MIN_MAX *group_trp;
     double best_read_time= read_time;
 
-    if (cond)
+    if (cond && !cond->const_item())
     {
       if ((tree= get_mm_tree(&param,cond)))
       {
@@ -7935,7 +7935,9 @@
   */
   if (cond_type == Item::SUBSELECT_ITEM)
     DBUG_RETURN(FALSE);
-  
+
+  if (cond->const_item())
+    DBUG_RETURN(TRUE);
   /* We presume that at this point there are no other Items than functions. */
   DBUG_ASSERT(cond_type == Item::FUNC_ITEM);
 

--- 1.191/sql/sql_delete.cc	2007-02-01 11:54:43 +03:00
+++ 1.192/sql/sql_delete.cc	2007-03-05 22:31:02 +03:00
@@ -102,7 +102,7 @@
   if (conds)
   {
     Item::cond_result result;
-    conds= remove_eq_conds(thd, conds, &result);
+    conds= remove_eq_conds(thd, conds, &result, 1);
     if (result == Item::COND_FALSE)             // Impossible where
       limit= 0;
   }

--- 1.490/sql/sql_select.cc	2007-02-13 10:10:29 +03:00
+++ 1.491/sql/sql_select.cc	2007-03-05 21:58:50 +03:00
@@ -7535,6 +7535,8 @@
           break;
       }
     }
+    if (!((Item_cond*)cond)->argument_list()->elements)
+      cond= new Item_int(cond->val_int());
   }
   else if (cond->type() == Item::FUNC_ITEM && 
            ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
@@ -8312,7 +8314,7 @@
       Remove all and-levels where CONST item != CONST item
     */
     DBUG_EXECUTE("where",print_where(conds,"after const change"););
-    conds= remove_eq_conds(thd, conds, cond_value) ;
+    conds= remove_eq_conds(thd, conds, cond_value, 0);
     DBUG_EXECUTE("info",print_where(conds,"after remove"););
   }
   DBUG_RETURN(conds);
@@ -8328,7 +8330,8 @@
 */
 
 COND *
-remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
+remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value,
+                bool remove_conds)
 {
   if (cond->type() == Item::COND_ITEM)
   {
@@ -8342,7 +8345,7 @@
     Item *item;
     while ((item=li++))
     {
-      Item *new_item=remove_eq_conds(thd, item, &tmp_cond_value);
+      Item *new_item=remove_eq_conds(thd, item, &tmp_cond_value, 1);
       if (!new_item)
 	li.remove();
       else if (item != new_item)
@@ -8463,8 +8466,9 @@
   }
   else if (cond->const_item())
   {
-    *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
-    return (COND*) 0;
+    bool res= eval_const_cond(cond);
+    *cond_value= res ? Item::COND_TRUE : Item::COND_FALSE;
+    return (COND*) remove_conds ? 0 : new Item_int((int32)res);
   }
   else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)
   {						// boolan compare function

--- 1.115/sql/sql_select.h	2007-01-12 23:22:37 +03:00
+++ 1.116/sql/sql_select.h	2007-03-05 22:32:32 +03:00
@@ -596,4 +596,5 @@
 bool error_if_full_join(JOIN *join);
 int report_error(TABLE *table, int error);
 int safe_index_read(JOIN_TAB *tab);
-COND *remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value);
+COND *remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value,
+                      bool remove_conds);

--- 1.212/sql/sql_update.cc	2007-02-08 01:41:54 +03:00
+++ 1.213/sql/sql_update.cc	2007-03-05 22:32:25 +03:00
@@ -229,7 +229,7 @@
   if (conds)
   {
     Item::cond_result cond_value;
-    conds= remove_eq_conds(thd, conds, &cond_value);
+    conds= remove_eq_conds(thd, conds, &cond_value, 1);
     if (cond_value == Item::COND_FALSE)
       limit= 0;                                   // Impossible WHERE
   }

--- 1.172/mysql-test/r/subselect.result	2007-01-26 08:01:25 +03:00
+++ 1.173/mysql-test/r/subselect.result	2007-03-05 21:53:43 +03:00
@@ -421,7 +421,7 @@
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1`
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -1180,7 +1180,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` 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)
@@ -1190,7 +1190,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 drop table t1;
 CREATE TABLE `t1` (
 `i` int(11) NOT NULL default '0',
@@ -1532,7 +1532,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
@@ -1540,7 +1540,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1548,7 +1548,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
@@ -1556,7 +1556,7 @@
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1618,7 +1618,7 @@
 3	UNION	t1	system	NULL	NULL	NULL	NULL	1	
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS `s1` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

--- 1.17/mysql-test/r/explain.result	2006-06-01 04:39:05 +04:00
+++ 1.18/mysql-test/r/explain.result	2007-03-05 21:53:57 +03:00
@@ -57,3 +57,12 @@
 explain select 3 into @v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1);
+create view v1 as select * from t1 where f1=1;
+explain extended select * from v1 where f2=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where
1
+drop table t1;

--- 1.33/mysql-test/r/func_test.result	2006-11-07 07:02:38 +03:00
+++ 1.34/mysql-test/r/func_test.result	2007-03-05 21:53:54 +03:00
@@ -79,7 +79,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1`
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
 select - a from t1;
 - a
 -1

--- 1.10/mysql-test/t/explain.test	2006-06-01 04:39:06 +04:00
+++ 1.11/mysql-test/t/explain.test	2007-03-05 21:53:38 +03:00
@@ -51,4 +51,14 @@
 select 3 into @v1;
 explain select 3 into @v1;
 
+#
+# Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were
+#            optimized away.
+#
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1);
+create view v1 as select * from t1 where f1=1;
+explain extended select * from v1 where f2=1;
+drop table t1;
+
 # End of 5.0 tests.

--- 1.28/mysql-test/r/group_min_max.result	2006-11-28 19:06:40 +03:00
+++ 1.29/mysql-test/r/group_min_max.result	2007-03-05 21:53:48 +03:00
@@ -1364,7 +1364,7 @@
 where exists ( select * from t2 where t2.c > 'b1' )
 group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
+1	PRIMARY	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
 2	SUBQUERY	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
 explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b
> 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra