Bug #46680 | Assertion failed in file item_subselect.cc, line 305 crashing on HAVING subquery | ||
---|---|---|---|
Submitted: | 12 Aug 2009 20:21 | Modified: | 22 Nov 2010 1:22 |
Reporter: | Patrick Crews | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.4/6.0 | OS: | Any |
Assigned to: | Jørgen Løland | CPU Architecture: | Any |
Tags: | crashing bug, having, materialization, optimizer_switch, subquery |
[12 Aug 2009 20:21]
Patrick Crews
[12 Aug 2009 20:22]
Patrick Crews
backtrace information for the bug (produced by rqg)
Attachment: bug46680_backtrace.txt (text/plain), 15.53 KiB.
[12 Aug 2009 20:22]
Patrick Crews
full MTR test case with original and simplified queries
Attachment: bug46680_test.txt (text/plain), 7.75 KiB.
[29 Sep 2009 9:19]
Jørgen Løland
The following is a simplified test that also reproduces the problem: CREATE TABLE t1 ( pk int(11) NOT NULL AUTO_INCREMENT, varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk) ) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES (1,'c'), (2, NULL); CREATE TABLE t2 ( pk int(11) NOT NULL AUTO_INCREMENT, varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES (3,'m'),(4,NULL); SELECT MIN(t2.pk) FROM t2 JOIN t1 ON t1.pk=t2.pk WHERE 'j' HAVING ('m') IN ( SELECT varchar_nokey FROM t2);
[29 Sep 2009 9:27]
Jørgen Løland
The following properties hold for queries that fail like this bug: - Has one or more aggregate functions in the SELECT list - Has an impossible WHERE condition - Does not have GROUP BY clause - Has a subquery in the HAVING clause
[29 Sep 2009 9:32]
Jørgen Løland
The result when running the simplified query above on MySQL 5.1: mysql> SELECT MIN(t2.pk) > FROM t2 JOIN t1 ON t1.pk=t2.pk > WHERE 'j' HAVING ('m') IN ( > SELECT varchar_nokey > FROM t2 > ); MIN(t2.pk) NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: 'j'
[29 Sep 2009 11:27]
Jørgen Løland
The problem occurs when the optimizer finds an impossible WHERE clause (sets zero_result_cause @ sql_select.cc:1508) which in turn shortcuts JOIN::optimize halfway through execution by calling return_zero_rows() around line 2400. The call to return_zero_rows() happens before the subqueries have been setup properly (for non-shortcuted queries, this happens in the call to setup_subquery_materialization() @ sql_select.cc:1930 (still inside JOIN::optimize function)). In return_zero_rows(), this line: 10908 if (having && having->val_int() == 0) triggers the execution of the subselect. Since the subselect has not been initialized, the assertion fails. Suggested fix: call setup_subquery_materialization() before calling having->val_int() on line 10908. I consider these locations to be good candidates for setting up the subquery: 1) Before calling return_zero_rows() @@ -2392,6 +2392,7 @@ JOIN::exec() if (zero_result_cause) { + setup_subquery_materialization(); (void) return_zero_rows(this, result, select_lex->leaf_tables, *columns_list, send_row_on_empty_set(), 2) Right before calling having->val_int() @@ -10905,8 +10906,12 @@ return_zero_rows(JOIN *join, select_resu { for (TABLE_LIST *table= tables; table; table= table->next_leaf) mark_as_null_row(table->table); // All fields are NULL - if (having && having->val_int() == 0) - send_row=0; + if (having) + { + setup_subquery_materialization(); + if (having->val_int() == 0) + send_row=0; + }
[29 Sep 2009 11:31]
Jørgen Løland
There is a typo in alternative 2) above; it should be: join->setup_subquery_materialization();
[30 Sep 2009 8:11]
Jørgen Løland
The above comment should be "The problem occurs when the optimizer finds an impossible WHERE clause (sets zero_result_cause @ sql_select.cc:1508) which in turn shortcuts JOIN::optimize halfway through execution." return_zero_rows() is later called from JOIN::exec(), not JOIN::optimize() The same argument applies, though: JOIN::optimize didn't setup the subselects properly because setup_subquery_materialization() was not called. This should be fixed in JOIN::optimize(), not in exec(). Hence, this seems to be the preferred location to setup the subqueries and thereby fix the bug: 3) +++ sql/sql_select.cc 2009-09-30 07:45:49 +0000 @@ -1509,6 +1509,7 @@ JOIN::optimize() "Impossible HAVING" : "Impossible WHERE"; tables= 0; error= 0; + setup_subquery_materialization(); DBUG_RETURN(0); } } I.e., right before JOIN::optimize shourtcuts optimization because it found an impossible WHERE.
[30 Sep 2009 11:06]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/85188 3631 Jorgen Loland 2009-09-30 Bug#46680 - Assertion failed in file item_subselect.cc, line 305 crashing on HAVING subquery For queries with impossible WHERE clauses, the optimizer shortcuts when it becomes clear that there are zero rows that match the impossible WHERE. In this case, subselects for which the optimizer has decided to use materialization strategy are not setup properly. However, if the query also has one or more aggregate functions, no GROUP BY clause and a subselect in the HAVING clause, the HAVING subselect has to be evaluated to decide if the query should return zero rows (HAVING evaluates to false) or one row with aggregates computed from NULL values. When the subquery is evaluated, an ASSERT fails because exec() is called before the query has been properly initialized. This patch calls setup_subquery_materialization() in JOIN:optimize() if the optimizer finds that WHERE contains an impossible condition. @ mysql-test/r/func_group.result Added regression test for BUG#46680 @ mysql-test/t/func_group.test Added regression test for BUG#46680 @ sql/sql_select.cc Call setup_subquery_materialization() in JOIN:optimize() if the optimizer finds that WHERE contains an impossible condition.
[8 Oct 2009 11:47]
Jørgen Løland
Guilhem points out that there are other premature exits from JOIN::optimize() that happen before setup_subquery_materialization() is called around line 1931. These exits should be analyzed to see if subquery setup is also required here. The locations are: [1] @1503: zero_result_cause= having_value == Item::COND_FALSE ? "Impossible HAVING" : "Impossible WHERE"; tables= 0; error= 0; DBUG_RETURN(0); [2] @1550: DBUG_PRINT("info",("No matching min/max row")); zero_result_cause= "No matching min/max row"; tables= 0; error=0; DBUG_RETURN(0); [3] @1564: DBUG_PRINT("info",("No matching min/max row")); zero_result_cause= "No matching min/max row"; tables= 0; error=0; DBUG_RETURN(0); [4] @1600 if (!tables_list) { DBUG_PRINT("info",("No tables")); error= 0; DBUG_RETURN(0); } [5] @1630 zero_result_cause= "no matching row in const table"; DBUG_PRINT("error",("Error: %s", zero_result_cause)); error= 0; DBUG_RETURN(0); [6] zero_result_cause= "Impossible WHERE noticed after reading const tables"; DBUG_RETURN(0); // error == 0 In addition, this premature exit sets up subquery correctly: [7] @1571 zero_result_cause= "Select tables optimized away"; tables_list= 0; // All tables resolved if (conds && !(thd->lex->describe & DESCRIBE_EXTENDED)) { COND *table_independent_conds= make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0, 0); DBUG_EXECUTE("where", print_where(table_independent_conds, "where after opt_sum_query()", QT_ORDINARY);); conds= table_independent_conds; } /* Create all structures needed for materialized subquery execution. */ if (setup_subquery_materialization()) DBUG_RETURN(1); // (...) // then into this if due to tables_list=0 above if (!tables_list) { DBUG_PRINT("info",("No tables")); error= 0; DBUG_RETURN(0); }
[8 Oct 2009 12:33]
Jørgen Løland
I have found test cases that confirm that the ASSERT fires for cases [1], [2], [5], [6], [7] (if if setup_subquery_materialization() is commented out) above. I cannot figure a way to enter [3]; I cannot see how opt_sum_query() can possibly return a negative value. However, since the return value is in some cases set to the return value from function calls (which return values from sub-function calls etc), I cannot say 100% sure that it cannot happen. As far as I can tell, [4] does not need to setup subqueries since it is only entered if there are no tables left to handle. Code inspection indicates that this is only true if a) there were no tables in the first place and hence no subquery to materialize, or b) the tables were optimized away. However, whenever tables_list is set to 0, the optimizer exits immediately and therefore never gets here (when considering the changes to [7] in the patch below).
[8 Oct 2009 14:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/86199 3649 Jorgen Loland 2009-10-08 Bug#46680 - Assertion failed in file item_subselect.cc, line 305 crashing on HAVING subquery The optimizer exits prematurely in some cases when it becomes clear that zero rows will satisfy the query. In these cases, subselects for which the optimizer has decided to use materialization strategy were not setup properly. However, if these query also had one or more aggregate functions, no GROUP BY clause and a subselect in the HAVING clause, the HAVING subselect had to be evaluated to decide if the query should return zero rows (HAVING evaluated to false) or one row with aggregates computed from NULL values. When the subquery was evaluated, an ASSERT failed because exec() was called before the subquery had been properly initialized. @ mysql-test/r/func_group.result Added regression test for BUG#46680 @ mysql-test/t/func_group.test Added regression test for BUG#46680 @ sql/sql_select.cc Call setup_subquery_materialization() in JOIN:optimize() if the optimizer decides to exit prematurely.
[9 Oct 2009 14:34]
Guilhem Bichot
approved with minor comments sent by mail
[13 Oct 2009 7:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/86645 3650 Jorgen Loland 2009-10-13 Bug#46680 - Assertion failed in file item_subselect.cc, line 305 crashing on HAVING subquery The optimizer exits prematurely in some cases when it becomes clear that zero rows will satisfy the query. In these cases, subselects for which the optimizer has decided to use materialization strategy were not setup properly. However, if these query also had one or more aggregate functions, no GROUP BY clause and a subselect in the HAVING clause, the HAVING subselect had to be evaluated to decide if the query should return zero rows (HAVING evaluated to false) or one row with aggregates computed from NULL values. When the subquery was evaluated, an ASSERT failed because exec() was called before the subquery had been properly initialized. @ mysql-test/r/func_group.result Added regression test for BUG#46680 @ mysql-test/t/func_group.test Added regression test for BUG#46680 @ sql/sql_select.cc Call setup_subquery_materialization() in JOIN:optimize() if the optimizer decides to exit prematurely.
[13 Oct 2009 10:57]
Jørgen Løland
Pushed to mysql-6.0-codebase-bugfixing
[31 Oct 2009 8:20]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:jorgen.loland@sun.com-20091013071623-dzu8vx13reye5ebj) (merge vers: 6.0.14-alpha) (pib:13)
[17 Nov 2009 23:09]
Paul DuBois
Noted in 6.0.14 changelog. The optimizer exited prematurely in some cases when it became clear that zero rows would satisfy the query. In these cases, subqueries for which the optimizer had decided to use a materialization strategy were not set up properly.
[6 Apr 2010 12:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105077 3061 oystein.grovlen@sun.com 2010-04-06 Bug#46680 - Assertion failed in file item_subselect.cc, line 305 crashing on HAVING subquery (Backporting of revid:jorgen.loland@sun.com-20091013071623-dzu8vx13reye5ebj) The optimizer exits prematurely in some cases when it becomes clear that zero rows will satisfy the query. In these cases, subselects for which the optimizer has decided to use materialization strategy were not setup properly. However, if these query also had one or more aggregate functions, no GROUP BY clause and a subselect in the HAVING clause, the HAVING subselect had to be evaluated to decide if the query should return zero rows (HAVING evaluated to false) or one row with aggregates computed from NULL values. When the subquery was evaluated, an ASSERT failed because exec() was called before the subquery had been properly initialized. @ mysql-test/r/func_group.result Added regression test for BUG#46680 @ mysql-test/t/func_group.test Added regression test for BUG#46680 @ sql/sql_select.cc Call setup_subquery_materialization() in JOIN:optimize() if the optimizer decides to exit prematurely.
[16 Aug 2010 6:40]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:20]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:22]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.