Merge lp:~stewart/drizzle/bug588408 into lp:~drizzle-trunk/drizzle/development

[Stewart Smith]

http://hudson.drizzle.org/view/Drizzle-param/job/drizzle-param/247/

seems to be good.

[Stewart Smith (stewart)]

and http://hudson.drizzle.org/view/Drizzle-param/job/drizzle-param-freebsd80/4/ for the FreeBSD one due to some kind of spurious failure on the first run.

=== modified file 'drizzled/join.cc'
--- drizzled/join.cc	2010-06-02 23:40:24 +0000
+++ drizzled/join.cc	2010-06-04 05:51:24 +0000
@@ -495,8 +495,7 @@
     {           /* Impossible cond */
       zero_result_cause=  having_value == Item::COND_FALSE ?
                            "Impossible HAVING" : "Impossible WHERE";
-      error= 0;
-      return(0);
+      goto setup_subq_exit;
     }
   }
 
@@ -515,8 +514,7 @@
       if (res == HA_ERR_KEY_NOT_FOUND)
       {
         zero_result_cause= "No matching min/max row";
-        error=0;
-        return(0);
+        goto setup_subq_exit;
       }
       if (res > 1)
       {
@@ -526,8 +524,7 @@
       if (res < 0)
       {
         zero_result_cause= "No matching min/max row";
-        error=0;
-        return(0);
+        goto setup_subq_exit;
       }
       zero_result_cause= "Select tables optimized away";
       tables_list= 0;       // All tables resolved
@@ -546,6 +543,7 @@
         COND *table_independent_conds= make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0, 0);
         conds= table_independent_conds;
       }
+      goto setup_subq_exit;
     }
   }
   if (!tables_list)
@@ -578,8 +576,7 @@
        select_lex->master_unit() == &session->lex->unit)) // upper level SELECT
   {
     zero_result_cause= "no matching row in const table";
-    error= 0;
-    return(0);
+    goto setup_subq_exit;
   }
   if (!(session->options & OPTION_BIG_SELECTS) &&
       best_read > (double) session->variables.max_join_size &&
@@ -645,7 +642,7 @@
   {
     zero_result_cause=
       "Impossible WHERE noticed after reading const tables";
-    return(0);        // error == 0
+    goto setup_subq_exit;
   }
 
   error= -1;          /* if goto err */
@@ -1110,6 +1107,15 @@
 
   error= 0;
   return(0);
+
+setup_subq_exit:
+  /* Even with zero matching rows, subqueries in the HAVING clause
+     may need to be evaluated if there are aggregate functions in the query.
+  */
+  if (setup_subquery_materialization())
+    return 1;
+  error= 0;
+  return 0;
 }
 
 /**

=== added file 'tests/r/bug588408.result'
--- tests/r/bug588408.result	1970-01-01 00:00:00 +0000
+++ tests/r/bug588408.result	2010-06-04 05:51:24 +0000
@@ -0,0 +1,152 @@
+#
+# BUG#46680 - Assertion failed in file item_subselect.cc, 
+#             line 305 crashing on HAVING subquery
+#
+# Create tables
+#
+CREATE TABLE t1 (
+pk INT,
+v VARCHAR(1) DEFAULT NULL,
+PRIMARY KEY(pk)
+);
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+CREATE TABLE empty1 (a int);
+INSERT INTO t1 VALUES (1,'c'),(2,NULL);
+INSERT INTO t2 VALUES (3,'m'),(4,NULL);
+INSERT INTO t3 VALUES (1,'n');
+
+#
+# 1) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "Impossible WHERE"
+#
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(t2.pk)
+NULL
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'j'
+
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'j'
+
+#
+# 2) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "No matching min/max row"
+#
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(t2.pk)
+NULL
+
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+
+#
+# 3) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "Select tables optimized away"
+#
+# NOTE: The result of this query is actually wrong; it should be NULL
+# See BUG#47762. Even so, the test case is still needed to test
+# that the HAVING subquery does not crash the server
+# 
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(pk)
+2
+
+EXPLAIN
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+
+#
+# 4) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "No matching row in const table"
+#
+
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+MIN(a)
+NULL
+
+EXPLAIN 
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	empty1	ALL	NULL	NULL	NULL	NULL	1	
+
+#
+# 5) Test that subquery materialization is setup for query with
+#    premature optimize() exit due to "Impossible WHERE noticed 
+#    after reading const tables"
+#
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+min(t1.pk)
+NULL
+
+EXPLAIN
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	2	Using where; Using index
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t3	index	PRIMARY	PRIMARY	4	NULL	1	Using where; Using index
+#
+# Cleanup for BUG#46680
+#
+DROP TABLE IF EXISTS t1,t2,t3,empty1;
+End of 6.0 tests

=== added file 'tests/t/bug588408.test'
--- tests/t/bug588408.test	1970-01-01 00:00:00 +0000
+++ tests/t/bug588408.test	2010-06-04 05:51:24 +0000
@@ -0,0 +1,137 @@
+--echo #
+--echo # BUG#46680 - Assertion failed in file item_subselect.cc, 
+--echo #             line 305 crashing on HAVING subquery
+--echo #
+
+--echo # Create tables
+--echo #
+
+CREATE TABLE t1 (
+  pk INT,
+  v VARCHAR(1) DEFAULT NULL,
+  PRIMARY KEY(pk)
+);
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+CREATE TABLE empty1 (a int);
+
+INSERT INTO t1 VALUES (1,'c'),(2,NULL);
+INSERT INTO t2 VALUES (3,'m'),(4,NULL);
+INSERT INTO t3 VALUES (1,'n');
+
+--echo
+--echo #
+--echo # 1) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "Impossible WHERE"
+--echo #
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 JOIN t1 ON t1.pk=t2.pk
+WHERE 'j'
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo 
+--echo #
+--echo # 2) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "No matching min/max row"
+--echo #
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT MIN(t2.pk)
+FROM t2 
+WHERE t2.pk>10
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+--echo #
+--echo # 3) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "Select tables optimized away"
+--echo #
+--echo # NOTE: The result of this query is actually wrong; it should be NULL
+--echo # See BUG#47762. Even so, the test case is still needed to test
+--echo # that the HAVING subquery does not crash the server
+--echo # 
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT MIN(pk)
+FROM t1
+WHERE pk=NULL
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo 
+--echo #
+--echo # 4) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "No matching row in const table"
+--echo #
+--echo
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN 
+SELECT MIN(a)
+FROM (SELECT a FROM empty1) tt
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo 
+--echo #
+--echo # 5) Test that subquery materialization is setup for query with
+--echo #    premature optimize() exit due to "Impossible WHERE noticed 
+--echo #    after reading const tables"
+--echo #
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo
+EXPLAIN
+SELECT min(t1.pk)
+FROM t1
+WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
+HAVING ('m') IN ( 
+SELECT v
+FROM t2);
+
+--echo #
+--echo # Cleanup for BUG#46680
+--echo #
+DROP TABLE IF EXISTS t1,t2,t3,empty1;
+
+###
+--echo End of 6.0 tests