Fixed LP bug #904345.

The MIN/MAX optimizer code from the function opt_sum_query erroneously
did not take into account conjunctive conditions that did not depend on
any table, yet were not identified as constant items. These could be
items containing rand() or PS/SP parameters. These items are supposed
to be evaluated at the execution phase. That's why if such conditions
can be extracted from the WHERE condition the MIN/MAX optimization is
not applied as currently it is always done at the optimization phase.

(In 5.3 expensive subqueries are also evaluated only at the execution
phase. So, if a constant condition with such subquery can be extracted
from the WHERE clause the MIN/MAX optimization should not be applied 
in 5.3.)

IF an IN/ALL/SOME predicate with a constant left part is transformed
into an EXISTS subquery the resulting subquery should not be considered
uncacheable if the right part of the predicate is not uncacheable.

Backported the function dbug_print_item() from 5.3. The function is used
only for debugging.  
 

12 files changed, 123 insertions, 35 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 2ff1cd2ec4a..de5672941c7 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1767,4 +1767,42 @@ MAX(a)
 10
 drop table t1;
 #
-End of 5.1 tests
+# Bug #904345: MIN/MAX optimization  with constant FALSE condition
+#
+CREATE TABLE t1 (a int NOT NULL, KEY(a));
+INSERT INTO t1 VALUES (10), (8), (11), (7), (15), (12), (9);
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+(8,2), (6,9), (8,4), (5,3), (9,1);
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+Warnings:
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where 0
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
+MAX(a)
+NULL
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where 0
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10;
+MAX(a)
+NULL
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	4	100.00	Using where; Using index
+Warnings:
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (((rand() * 0) <> 0) and (`test`.`t1`.`a` < 10))
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+MAX(a)
+NULL
+DROP TABLE t1,t2;
+#
+End of 5.2 tests
diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result
index 5c11163ab9e..3b4d525aeb4 100644
--- a/mysql-test/r/ps_11bugs.result
+++ b/mysql-test/r/ps_11bugs.result
@@ -120,9 +120,9 @@ create table t1 (a int primary key);
 insert into t1 values (1);
 explain select * from t1 where 3 in (select (1+1) union select 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
-3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 select * from t1 where 3 in (select (1+1) union select 1);
 a
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 5453345d50c..e2c2dcaced4 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2778,10 +2778,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	range	key1	key1	5	NULL	3	Using where; Using index
 select max(key1) from t1 where key1 <= 0.6158;
 max(key1)
 0.615800023078918
@@ -2800,10 +2800,10 @@ max(key1)	min(key2)
 0.615800023078918	1.37619996070862
 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 max(key1)
-0.615800023078918
+0.384499996900558
 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 min(key1)
-0.376199990510941
+0.384499996900558
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 5453345d50c..e2c2dcaced4 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -2778,10 +2778,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	range	key1	key1	5	NULL	3	Using where; Using index
 select max(key1) from t1 where key1 <= 0.6158;
 max(key1)
 0.615800023078918
@@ -2800,10 +2800,10 @@ max(key1)	min(key2)
 0.615800023078918	1.37619996070862
 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 max(key1)
-0.615800023078918
+0.384499996900558
 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 min(key1)
-0.376199990510941
+0.384499996900558
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 1f3432ecde1..65e76d04c1f 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1302,7 +1302,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
 EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
 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');
@@ -1312,7 +1312,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
 EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
 Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 drop table t1;
@@ -4476,15 +4476,15 @@ INSERT INTO t1 VALUES (1),(2);
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 0
 DROP TABLE t1;
 #
 # Bug#45061: Incorrectly market field caused wrong result.
diff --git a/mysql-test/suite/pbxt/r/ps_11bugs.result b/mysql-test/suite/pbxt/r/ps_11bugs.result
index fad35b97b24..0be781bca27 100644
--- a/mysql-test/suite/pbxt/r/ps_11bugs.result
+++ b/mysql-test/suite/pbxt/r/ps_11bugs.result
@@ -120,9 +120,9 @@ create table t1 (a int primary key);
 insert into t1 values (1);
 explain select * from t1 where 3 in (select (1+1) union select 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
-3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 select * from t1 where 3 in (select (1+1) union select 1);
 a
diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result
index c06dd06ea3e..b98f980f821 100644
--- a/mysql-test/suite/pbxt/r/select.result
+++ b/mysql-test/suite/pbxt/r/select.result
@@ -2786,10 +2786,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 select max(key1) from t1 where key1 <= 0.6158;
 max(key1)
 0.615800023078918
@@ -2808,10 +2808,10 @@ max(key1)	min(key2)
 0.615800023078918	1.37619996070862
 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 max(key1)
-0.615800023078918
+0.384499996900558
 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 min(key1)
-0.376199990510941
+0.384499996900558
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result
index 0cdec48e192..63b289d259e 100644
--- a/mysql-test/suite/pbxt/r/subselect.result
+++ b/mysql-test/suite/pbxt/r/subselect.result
@@ -1177,7 +1177,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
 EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
 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');
@@ -1187,7 +1187,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
 EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
 Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 drop table t1;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 8c2be79ee7d..84afe328aeb 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -1150,4 +1150,29 @@ SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;
 drop table t1;
 
 --echo #
---echo End of 5.1 tests
+--echo # Bug #904345: MIN/MAX optimization  with constant FALSE condition
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL, KEY(a));
+INSERT INTO t1 VALUES (10), (8), (11), (7), (15), (12), (9);
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+  (8,2), (6,9), (8,4), (5,3), (9,1);
+
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
+
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; 
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; 
+
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo End of 5.2 tests
diff --git a/sql/item.cc b/sql/item.cc
index c08333e4b80..72adb9d0b52 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -7915,6 +7915,27 @@ void view_error_processor(THD *thd, void *data)
   ((TABLE_LIST *)data)->hide_view_error(thd);
 }
 
+#ifndef DBUG_OFF
+
+/* Debugger help function */
+static char dbug_item_print_buf[256];
+
+const char *dbug_print_item(Item *item)
+{
+  char *buf= dbug_item_print_buf;
+  String str(buf, sizeof(dbug_item_print_buf), &my_charset_bin);
+  str.length(0);
+  if (!item)
+    return "(Item*)NULL";
+  item->print(&str ,QT_ORDINARY);
+  if (str.c_ptr() == buf)
+    return buf;
+  else
+    return "Couldn't fit into buffer";
+}
+
+#endif /*DBUG_OFF*/
+
 /*****************************************************************************
 ** Instantiate templates
 *****************************************************************************/
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index fa9eff0d95b..82d2313aa2b 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1117,8 +1117,8 @@ Item_in_subselect::single_value_transformer(JOIN *join,
                               (Item**)optimizer->get_cache(),
 			      (char *)"<no matter>",
 			      (char *)in_left_expr_name);
-
-    master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
+    if (!left_expr->const_item())
+      master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
   }
   if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
   {
@@ -1127,7 +1127,8 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     pushed_cond_guards[0]= TRUE;
   }
 
-  select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
+  if (!left_expr->const_item())
+    select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
   if (join->having || select_lex->with_sum_func ||
       select_lex->group_list.elements)
   {
@@ -1338,7 +1339,8 @@ Item_in_subselect::row_value_transformer(JOIN *join)
     optimizer->keep_top_level_cache();
 
     thd->lex->current_select= current;
-    master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
+    if (!left_expr->const_item())
+      master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
 
     if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
     {
@@ -1350,7 +1352,8 @@ Item_in_subselect::row_value_transformer(JOIN *join)
     }
   }
 
-  select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
+  if (!left_expr->const_item())
+    select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
   if (is_having_used)
   {
     /*
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index f8a81eac380..27a848cb0ec 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -608,7 +608,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
   if (!cond)
     DBUG_RETURN(TRUE);
   Field *field= field_part->field;
-  if (!(cond->used_tables() & field->table->map))
+  if (!(cond->used_tables() & field->table->map) &&
+      test(cond->used_tables() & ~PSEUDO_TABLE_BITS))
   {
     /* Condition doesn't restrict the used table */
     DBUG_RETURN(TRUE);