diff options
| -rw-r--r-- | mysql-test/r/having.result | 35 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 30 | ||||
| -rw-r--r-- | sql/sql_select.cc | 25 |
3 files changed, 90 insertions, 0 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 9568ef88786..95893510987 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -450,4 +450,39 @@ HAVING amount > 0 ORDER BY t1.id1; id1 amount DROP TABLE t1; +# +# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause +# +CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT); +INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9); +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); +f1 f2 +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +Warnings: +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having (('7' = 8) and (`test`.`table1`.`f1` >= 6)) +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +Warnings: +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having ('7' = 8) +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 185ca4bdddb..b68ba69b975 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -467,5 +467,35 @@ ORDER BY t1.id1; DROP TABLE t1; +--echo # +--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause +--echo # +CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT); +INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9); + +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); + +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); + +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8); + +DROP TABLE t1; --echo End of 5.0 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b3640f9dd22..618332ae89b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1112,6 +1112,31 @@ JOIN::optimize() { conds=new Item_int((longlong) 0,1); // Always false } + + /* + It's necessary to check const part of HAVING cond as + there is a chance that some cond parts may become + const items after make_join_statisctics(for example + when Item is a reference to cost table field from + outer join). + This check is performed only for those conditions + which do not use aggregate functions. In such case + temporary table may not be used and const condition + elements may be lost during further having + condition transformation in JOIN::exec. + */ + if (having && !having->with_sum_func) + { + COND *const_cond= make_cond_for_table(having, const_table_map, 0); + DBUG_EXECUTE("where", print_where(const_cond, "const_having_cond", + QT_ORDINARY);); + if (const_cond && !const_cond->val_int()) + { + zero_result_cause= "Impossible HAVING noticed after reading const tables"; + DBUG_RETURN(0); + } + } + if (make_join_select(this, select, conds)) { zero_result_cause= |