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 | 19 | ||||
| -rw-r--r-- | sql/sql_select.cc | 28 |
6 files changed, 68 insertions, 15 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 379c2f83c78..e54f6d7f2a4 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -141,6 +141,23 @@ 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; create table t1 (col1 int, col2 varchar(5), col_t1 int); create table t2 (col1 int, col2 varchar(5), col_t2 int); create table t3 (col1 int, col2 varchar(5), col_t3 int); diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 1cc894697f9..78628bef198 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -135,6 +135,22 @@ 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 946b6a28430..1ed8887a878 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1122,7 +1122,7 @@ void st_select_lex::init_query() embedding= leaf_tables= 0; item_list.empty(); join= 0; - having= where= prep_where= 0; + having= prep_having= where= prep_where= 0; olap= UNSPECIFIED_OLAP_TYPE; having_fix_field= 0; context.select_lex= this; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 5ba47d768fb..7b2ea359fb2 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -474,6 +474,7 @@ public: char *db; 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 */ /* point on lex in which it was created, used in view subquery detection */ st_lex *parent_lex; enum olap_type olap; diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 68f033f78fe..efe73dbe275 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2066,14 +2066,19 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) sl->exclude_from_table_unique_test= FALSE; /* - Copy WHERE clause pointers to avoid damaging they by optimisation + Copy WHERE, HAVING clause pointers to avoid damaging them by optimisation */ - if (sl->prep_where) - { - sl->where= sl->prep_where->copy_andor_structure(thd); - sl->where->cleanup(); - } - DBUG_ASSERT(sl->join == 0); + 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 */ for (order= (ORDER *)sl->group_list.first; order; order= order->next) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4ac2bb91af5..9160c023576 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -612,6 +612,7 @@ JOIN::optimize() build_bitmap_for_nested_joins(join_list, 0); sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0; + sel->prep_having= having ? having->copy_andor_structure(thd) : 0; if (arena) thd->restore_active_arena(arena, &backup); @@ -625,13 +626,26 @@ JOIN::optimize() DBUG_RETURN(1); } - if (cond_value == Item::COND_FALSE || - (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) - { /* Impossible cond */ - DBUG_PRINT("info", ("Impossible WHERE")); - zero_result_cause= "Impossible WHERE"; - error= 0; - DBUG_RETURN(0); + { + Item::cond_result having_value; + having= optimize_cond(this, having, join_list, &having_value); + if (thd->net.report_error) + { + error= 1; + DBUG_PRINT("error",("Error from optimize_cond")); + DBUG_RETURN(1); + } + + if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || + (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) + { /* Impossible cond */ + DBUG_PRINT("info", (having_value == Item::COND_FALSE ? + "Impossible HAVING" : "Impossible WHERE")); + zero_result_cause= having_value == Item::COND_FALSE ? + "Impossible HAVING" : "Impossible WHERE"; + error= 0; + DBUG_RETURN(0); + } } /* Optimize count(*), min() and max() */ |