diff options
| -rw-r--r-- | mysql-test/r/having.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 16 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 2 | ||||
| -rw-r--r-- | sql/sql_lex.h | 1 | ||||
| -rw-r--r-- | sql/sql_prepare.cc | 10 | ||||
| -rw-r--r-- | sql/sql_select.cc | 22 |
6 files changed, 57 insertions, 11 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 9730f9f81bf..ccd1f0e61e7 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -141,3 +141,20 @@ SUM(a) 6 4 DROP TABLE t1; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2), (1), (3), (2), (1); +SELECT a FROM t1 GROUP BY a HAVING a > 1; +a +2 +3 +SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1; +a +SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; +x a +EXPLAIN SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +DROP table t1; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 3dd9ace6a1b..8b39e3bd454 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -135,4 +135,20 @@ SELECT SUM(a) FROM t1 GROUP BY a HAVING SUM(a); DROP TABLE t1; +# +# Bug #14927: HAVING clause containing constant false conjunct +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2), (1), (3), (2), (1); + +SELECT a FROM t1 GROUP BY a HAVING a > 1; +SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1; +SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; + +EXPLAIN SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1; +EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; + +DROP table t1; + # End of 4.1 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 16641ad6dd5..7348816ea27 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1074,6 +1074,7 @@ void st_select_lex::init_query() item_list.empty(); join= 0; where= 0; + having= 0; olap= UNSPECIFIED_OLAP_TYPE; having_fix_field= 0; resolve_mode= NOMATTER_MODE; @@ -1081,6 +1082,7 @@ void st_select_lex::init_query() ref_pointer_array= 0; select_n_having_items= 0; prep_where= 0; + prep_having= 0; subquery_in_having= explicit_limit= 0; parsing_place= NO_MATTER; is_item_list_lookup= 0; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 47908ba8685..f5eb94ac93e 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -421,6 +421,7 @@ public: char *db, *db1, *table1, *db2, *table2; /* For outer join using .. */ Item *where, *having; /* WHERE & HAVING clauses */ Item *prep_where; /* saved WHERE clause for prepared statement processing */ + Item *prep_having;/* saved HAVING clause for prepared statement processing */ enum olap_type olap; SQL_LIST table_list, group_list; /* FROM & GROUP BY clauses */ List<Item> item_list; /* list of fields & expressions */ diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 8a50d0bd50e..ec5fa92f689 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1665,10 +1665,11 @@ int mysql_stmt_prepare(THD *thd, char *packet, uint packet_length, for (; sl; sl= sl->next_select_in_list()) { /* - Save WHERE clause pointers, because they may be changed + Save WHERE, HAVING clause pointers, because they may be changed during query optimisation. */ sl->prep_where= sl->where; + sl->prep_having= sl->having; /* Switch off a temporary flag that prevents evaluation of subqueries in statement prepare. @@ -1694,13 +1695,18 @@ static void reset_stmt_for_execute(Prepared_statement *stmt) /* remove option which was put by mysql_explain_union() */ sl->options&= ~SELECT_DESCRIBE; /* - Copy WHERE clause pointers to avoid damaging they by optimisation + Copy WHERE, HAVING clause pointers to avoid damaging they by optimisation */ if (sl->prep_where) { sl->where= sl->prep_where->copy_andor_structure(thd); sl->where->cleanup(); } + if (sl->prep_having) + { + sl->having= sl->prep_having->copy_andor_structure(thd); + sl->having->cleanup(); + } DBUG_ASSERT(sl->join == 0); ORDER *order; /* Fix GROUP list */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ec62e80ba13..27c8ac126fa 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -501,12 +501,18 @@ JOIN::optimize() DBUG_RETURN(1); } - if (cond_value == Item::COND_FALSE || - (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) - { /* Impossible cond */ - zero_result_cause= "Impossible WHERE"; - error= 0; - DBUG_RETURN(0); + { + Item::cond_result having_value; + having= optimize_cond(thd, having, &having_value); + + if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || + (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) + { /* Impossible cond */ + zero_result_cause= having_value == Item::COND_FALSE ? + "Impossible HAVING" : "Impossible WHERE"; + error= 0; + DBUG_RETURN(0); + } } /* Optimize count(*), min() and max() */ @@ -4611,10 +4617,8 @@ optimize_cond(THD *thd, COND *conds, Item::cond_result *cond_value) DBUG_EXECUTE("info", print_where(conds, "after remove");); } else - { *cond_value= Item::COND_TRUE; - select->prep_where= 0; - } + DBUG_RETURN(conds); } |