diff options
| -rw-r--r-- | mysql-test/r/having.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 25 | ||||
| -rw-r--r-- | sql/item.h | 1 | ||||
| -rw-r--r-- | sql/item_sum.h | 1 | ||||
| -rw-r--r-- | sql/sql_select.cc | 4 |
5 files changed, 54 insertions, 1 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index fc2df2b7fc2..e631c7dbe5a 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -570,3 +570,27 @@ ORDER BY t1.f1; f1 DROP TABLE t1,t2; End of 5.1 tests +# +# LP bug #791761: MAX over an empty join + HAVING +# +CREATE TABLE t1 (a int, b int , KEY (b)) ; +INSERT INTO t1 VALUES (3,1); +CREATE TABLE t2 (a int NOT NULL ) ; +INSERT INTO t2 VALUES (29); +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) <> 6; +MAX(t1.b) +1 +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) IS NULL; +MAX(t1.b) +EXPLAIN +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6; +MAX(t1.b) +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (NULL); +SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0 +WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ; +f +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 2ed8b40b858..01342fdf5fa 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -591,3 +591,28 @@ DROP TABLE t1,t2; --echo End of 5.1 tests + +--echo # +--echo # LP bug #791761: MAX over an empty join + HAVING +--echo # + +CREATE TABLE t1 (a int, b int , KEY (b)) ; +INSERT INTO t1 VALUES (3,1); + +CREATE TABLE t2 (a int NOT NULL ) ; +INSERT INTO t2 VALUES (29); + +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) <> 6; +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) IS NULL; + +EXPLAIN +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6; +SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6; + +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (NULL); + +SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0 + WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ; + +DROP TABLE t1,t2,t3; diff --git a/sql/item.h b/sql/item.h index f2e7a2587da..8a453f31c7e 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1020,6 +1020,7 @@ public: virtual bool set_fake_select_as_master_processor(uchar *arg) { return 0; } virtual bool view_used_tables_processor(uchar *arg) { return 0; } virtual bool eval_not_null_tables(uchar *opt_arg) { return 0; } + virtual bool clear_sum_processor(uchar *opt_arg) { return 0; } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/item_sum.h b/sql/item_sum.h index 7b6bdbabf7f..0d557a030d8 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -388,6 +388,7 @@ public: { return trace_unsupported_by_check_vcol_func_processor(func_name()); } + bool clear_sum_processor(uchar *arg) { clear(); return 0; } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c4f03cf2c4f..eacebcb498a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10146,7 +10146,9 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, TABLE_LIST *table; while ((table= ti++)) mark_as_null_row(table->table); // All fields are NULL - if (having && having->val_int() == 0) + if (having && + !having->walk(&Item::clear_sum_processor, FALSE, NULL) && + having->val_int() == 0) send_row=0; } if (!(result->send_fields(fields, |