[Commits] Rev 3508: Fix bug lp:985667, MDEV-229 in file:///home/tsk/mprog/src/5.3-lpb985667/

timour at askmonty.org timour at askmonty.org
Fri Apr 27 12:59:37 EEST 2012


At file:///home/tsk/mprog/src/5.3-lpb985667/

------------------------------------------------------------
revno: 3508
revision-id: timour at askmonty.org-20120427095917-4m3mojtzu6yq4t9n
parent: wlad at montyprogram.com-20120423183744-vdwrhix3y2ivfuhg
fixes bug(s): https://launchpad.net/bugs/985667
committer: timour at askmonty.org
branch nick: 5.3-lpb985667
timestamp: Fri 2012-04-27 12:59:17 +0300
message:
  Fix bug lp:985667, MDEV-229
  
  Analysis:
  
  The reason for the wrong result is the interaction between constant
  optimization (in this case 1-row table) and subquery optimization.
  
  - First the outer query is optimized, and 'make_join_statistics' finds that
  table t2 has one row, reads that row, and marks the whole table as constant.
  This also means that all fields of t2 are constant.
  
  - Next, we optimize the subquery in the end of the outer 'make_join_statistics'.
  The field 'f2' is considered constant, with value '3'. The subquery predicate
  is rewritten as the constant TRUE.
  
  - The outer query execution detects early that the whole query result is empty
  and calls 'return_zero_rows'. Since the query is with implicit grouping, we
  have to produce one row with special values for the aggregates (depending on
  each aggregate function), and NULL values for all non-aggregate fields.  This
  function calls 'no_rows_in_result' to set each aggregate function to the
  default value when it aggregates over an empty result, and then calls
  'send_data', which in turn evaluates each Item in the SELECT list.
  
  - When evaluation reaches the subquery predicate, it executes the subquery
  with field 'f2' having a constant value '3', and the subquery produces the
  incorrect result '7'.
  
  Solution:
  
  Implement Item::no_rows_in_result for all subquery predicates. In order to
  make this work, it is also needed to make all val_* methods of all subquery
  predicates respect the Item_subselect::forced_const flag. Otherwise subqueries
  are executed anyways, and override the default value set by no_rows_in_result
  with whatever result is produced from the subquery evaluation.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-04-02 17:41:54 +0000
+++ b/mysql-test/r/subselect.result	2012-04-27 09:59:17 +0000
@@ -4532,7 +4532,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1
 FROM t1 
 WHERE a = 230;
 MAX(b)  (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
-NULL    0
+NULL    NULL
 DROP TABLE t1, st1, st2;
 #
 # Bug #48709: Assertion failed in sql_select.cc:11782: 
@@ -6004,5 +6004,46 @@ INSERT INTO t1 VALUES (1);
 SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
 a
 drop table t1;
+#
+# LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
+# main query and implicit grouping
+#
+CREATE TABLE t1 (f1 int) engine=MyISAM;
+INSERT INTO t1 VALUES (7),(8);
+CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
+INSERT INTO t2 VALUES (3,'f');
+EXPLAIN
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       NULL
+EXPLAIN
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+EXPLAIN
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       1
+EXPLAIN
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+drop table t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-04-02 17:41:54 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-04-27 09:59:17 +0000
@@ -4534,7 +4534,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1
 FROM t1 
 WHERE a = 230;
 MAX(b)  (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
-NULL    0
+NULL    NULL
 DROP TABLE t1, st1, st2;
 #
 # Bug #48709: Assertion failed in sql_select.cc:11782: 
@@ -6003,6 +6003,47 @@ INSERT INTO t1 VALUES (1);
 SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
 a
 drop table t1;
+#
+# LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
+# main query and implicit grouping
+#
+CREATE TABLE t1 (f1 int) engine=MyISAM;
+INSERT INTO t1 VALUES (7),(8);
+CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
+INSERT INTO t2 VALUES (3,'f');
+EXPLAIN
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       NULL
+EXPLAIN
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+EXPLAIN
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       1
+EXPLAIN
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+drop table t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-03-05 13:48:12 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-04-27 09:59:17 +0000
@@ -4530,7 +4530,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1
 FROM t1 
 WHERE a = 230;
 MAX(b)  (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
-NULL    0
+NULL    NULL
 DROP TABLE t1, st1, st2;
 #
 # Bug #48709: Assertion failed in sql_select.cc:11782: 
@@ -5999,6 +5999,47 @@ INSERT INTO t1 VALUES (1);
 SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
 a
 drop table t1;
+#
+# LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
+# main query and implicit grouping
+#
+CREATE TABLE t1 (f1 int) engine=MyISAM;
+INSERT INTO t1 VALUES (7),(8);
+CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
+INSERT INTO t2 VALUES (3,'f');
+EXPLAIN
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       NULL
+EXPLAIN
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+EXPLAIN
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       1
+EXPLAIN
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+drop table t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-04-02 17:41:54 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-04-27 09:59:17 +0000
@@ -4538,7 +4538,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1
 FROM t1 
 WHERE a = 230;
 MAX(b)  (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
-NULL    0
+NULL    NULL
 DROP TABLE t1, st1, st2;
 #
 # Bug #48709: Assertion failed in sql_select.cc:11782: 
@@ -6010,6 +6010,47 @@ INSERT INTO t1 VALUES (1);
 SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
 a
 drop table t1;
+#
+# LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
+# main query and implicit grouping
+#
+CREATE TABLE t1 (f1 int) engine=MyISAM;
+INSERT INTO t1 VALUES (7),(8);
+CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
+INSERT INTO t2 VALUES (3,'f');
+EXPLAIN
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       NULL
+EXPLAIN
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+EXPLAIN
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       1
+EXPLAIN
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+drop table t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-03-05 13:48:12 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-04-27 09:59:17 +0000
@@ -4530,7 +4530,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1
 FROM t1 
 WHERE a = 230;
 MAX(b)  (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
-NULL    0
+NULL    NULL
 DROP TABLE t1, st1, st2;
 #
 # Bug #48709: Assertion failed in sql_select.cc:11782: 
@@ -5999,6 +5999,47 @@ INSERT INTO t1 VALUES (1);
 SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
 a
 drop table t1;
+#
+# LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
+# main query and implicit grouping
+#
+CREATE TABLE t1 (f1 int) engine=MyISAM;
+INSERT INTO t1 VALUES (7),(8);
+CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
+INSERT INTO t2 VALUES (3,'f');
+EXPLAIN
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       NULL
+EXPLAIN
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+EXPLAIN
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       1
+EXPLAIN
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+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      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+COUNT(f1)       f4
+0       0
+drop table t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2012-03-05 13:48:12 +0000
+++ b/mysql-test/t/subselect.test	2012-04-27 09:59:17 +0000
@@ -5090,6 +5090,35 @@ SELECT a FROM t1 WHERE ( SELECT MIN(a) =
 
 drop table t1;
 
+--echo #
+--echo # LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
+--echo # main query and implicit grouping
+--echo #
+
+CREATE TABLE t1 (f1 int) engine=MyISAM;
+INSERT INTO t1 VALUES (7),(8);
+
+CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
+INSERT INTO t2 VALUES (3,'f');
+
+EXPLAIN
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+
+EXPLAIN
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
+
+EXPLAIN
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+
+EXPLAIN
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
+
+drop table t1,t2;
+
 
 --echo # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-04-19 00:50:32 +0000
+++ b/sql/item_subselect.cc	2012-04-27 09:59:17 +0000
@@ -889,6 +889,15 @@ void Item_maxmin_subselect::print(String
 }
 
 
+void Item_maxmin_subselect::no_rows_in_result()
+{
+  value= 0;
+  null_value= 0;
+  was_values= 0;
+  make_const();
+}
+
+
 void Item_singlerow_subselect::reset()
 {
   Item_subselect::reset();
@@ -1084,6 +1093,8 @@ void Item_singlerow_subselect::bring_val
 double Item_singlerow_subselect::val_real()
 {
   DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    return value->val_real();
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1099,6 +1110,8 @@ double Item_singlerow_subselect::val_rea
 longlong Item_singlerow_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    return value->val_int();
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1113,6 +1126,9 @@ longlong Item_singlerow_subselect::val_i
 
 String *Item_singlerow_subselect::val_str(String *str)
 {
+  DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    return value->val_str(str);
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1128,6 +1144,9 @@ String *Item_singlerow_subselect::val_st
 
 my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
 {
+  DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    return value->val_decimal(decimal_value);
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1143,6 +1162,9 @@ my_decimal *Item_singlerow_subselect::va
 
 bool Item_singlerow_subselect::val_bool()
 {
+  DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    return value->val_bool();
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1312,10 +1334,17 @@ Item* Item_exists_subselect::expr_cache_
 }
 
 
+void Item_exists_subselect::no_rows_in_result()
+{
+  value= 0;
+  null_value= 0;
+  make_const();
+}
+
 double Item_exists_subselect::val_real()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec())
+  if (!forced_const && exec())
   {
     reset();
     return 0;
@@ -1326,7 +1355,7 @@ double Item_exists_subselect::val_real()
 longlong Item_exists_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec())
+  if (!forced_const && exec())
   {
     reset();
     return 0;
@@ -1351,7 +1380,7 @@ longlong Item_exists_subselect::val_int(
 String *Item_exists_subselect::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec())
+  if (!forced_const && exec())
     reset();
   str->set((ulonglong)value,&my_charset_bin);
   return str;
@@ -1374,7 +1403,7 @@ String *Item_exists_subselect::val_str(S
 my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec())
+  if (!forced_const && exec())
     reset();
   int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value);
   return decimal_value;
@@ -1384,7 +1413,7 @@ my_decimal *Item_exists_subselect::val_d
 bool Item_exists_subselect::val_bool()
 {
   DBUG_ASSERT(fixed == 1);
-  if (exec())
+  if (!forced_const && exec())
   {
     reset();
     return 0;
@@ -2649,6 +2678,15 @@ void Item_allany_subselect::print(String
 }
 
 
+void Item_allany_subselect::no_rows_in_result()
+{
+  value= 0;
+  null_value= 0;
+  was_null= 0;
+  make_const();
+}
+
+
 void subselect_engine::set_thd(THD *thd_arg)
 {
   thd= thd_arg;

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2012-04-05 21:07:18 +0000
+++ b/sql/item_subselect.h	2012-04-27 09:59:17 +0000
@@ -146,6 +146,11 @@ class Item_subselect :public Item_result
     eliminated= FALSE;
     null_value= 1;
   }
+  /**
+    Set the subquery result to the default value for the predicate when the
+    subquery is known to produce an empty result.
+  */
+  void no_rows_in_result()= 0;
   virtual bool select_transformer(JOIN *join);
   bool assigned() { return value_assigned; }
   void assigned(bool a) { value_assigned= a; }
@@ -262,6 +267,7 @@ class Item_singlerow_subselect :public I
   subs_type substype() { return SINGLEROW_SUBS; }
 
   void reset();
+  void no_rows_in_result() { reset(); make_const(); }
   bool select_transformer(JOIN *join);
   void store(uint i, Item* item);
   double val_real();
@@ -314,6 +320,7 @@ class Item_maxmin_subselect :public Item
   bool any_value() { return was_values; }
   void register_value() { was_values= TRUE; }
   void reset_value_registration() { was_values= FALSE; }
+  void no_rows_in_result();
 };
 
 /* exists subselect */
@@ -335,6 +342,7 @@ class Item_exists_subselect :public Item
     eliminated= FALSE;
     value= 0;
   }
+  void no_rows_in_result();
 
   enum Item_result result_type() const { return INT_RESULT;}
   longlong val_int();
@@ -664,6 +672,7 @@ class Item_allany_subselect :public Item
   virtual void print(String *str, enum_query_type query_type);
   bool is_maxmin_applicable(JOIN *join);
   bool transform_into_max_min(JOIN *join);
+  void no_rows_in_result();
 };
 
 

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-04-02 17:41:54 +0000
+++ b/sql/opt_subselect.cc	2012-04-27 09:59:17 +0000
@@ -5450,8 +5450,8 @@ bool JOIN::choose_tableless_subquery_pla
     /*
       If the optimizer determined that his query has an empty result,
       in most cases the subquery predicate is a known constant value -
-      either FALSE or NULL. The implementation of Item_subselect::reset()
-      determines which one.
+      either of TRUE, FALSE or NULL. The implementation of
+      Item_subselect::no_rows_in_result() determines which one.
     */
     if (zero_result_cause)
     {
@@ -5459,14 +5459,13 @@ bool JOIN::choose_tableless_subquery_pla
       {
         /*
           Both group by queries and non-group by queries without aggregate
-          functions produce empty subquery result.
+          functions produce empty subquery result. There is no need to further
+          rewrite the subquery because it will not be executed at all.
         */
-        subs_predicate->reset();
-        subs_predicate->make_const();
         return FALSE;
       }
 
-      /* TODO:
+      /* @todo
          A further optimization is possible when a non-group query with
          MIN/MAX/COUNT is optimized by opt_sum_query. Then, if there are
          only MIN/MAX functions over an empty result set, the subquery

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-02-14 14:52:56 +0000
+++ b/sql/sql_lex.cc	2012-04-27 09:59:17 +0000
@@ -3115,6 +3115,11 @@ bool st_select_lex::optimize_unflattened
           continue;
       }
 
+      bool empty_union_result= true;
+      /*
+        If the subquery is a UNION, optimize all the subqueries in the UNION. If
+        there is no UNION, then the loop will execute once for the subquery.
+      */
       for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select())
       {
         JOIN *inner_join= sl->join;
@@ -3137,9 +3142,19 @@ bool st_select_lex::optimize_unflattened
         res= inner_join->optimize();
         inner_join->select_options= save_options;
         un->thd->lex->current_select= save_select;
+        if (empty_union_result)
+        {
+          /*
+            If at least one subquery in a union is non-empty, the UNION result
+            is non-empty. If there is no UNION, the only subquery is non-empy.
+          */
+          empty_union_result= inner_join->empty_result();
+        }
         if (res)
           return TRUE;
       }
+      if (empty_union_result)
+        subquery_predicate->no_rows_in_result();
     }
   }
   return FALSE;

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-04-23 07:45:27 +0000
+++ b/sql/sql_select.h	2012-04-27 09:59:17 +0000
@@ -1322,6 +1322,7 @@ class JOIN :public Sql_alloc
     return (do_send_rows && implicit_grouping && !group_optimized_away &&
             having_value != Item::COND_FALSE);
   }
+  bool empty_result() { return (zero_result_cause && !implicit_grouping); }
   bool change_result(select_result *result);
   bool is_top_level_join() const
   {



More information about the commits mailing list