| Bug #33062 | subquery in stored routine cause crash | ||
|---|---|---|---|
| Submitted: | 7 Dec 2007 13:27 | Modified: | 20 Nov 2010 23:05 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 6.0.3-debug, 6.0.4-debug | OS: | Any |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | assertion | ||
[7 Dec 2007 16:38]
MySQL Verification Team
Thank you for the bug report. Verified on main tree and team tree:
[miguel@skybr dbs]$ cd 6.0f/
[miguel@skybr 6.0f]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> delimiter ;
mysql>
mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop table if exists `t2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table `t1`(`a` int)engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table `t2`(`c` int)engine=myisam;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> drop procedure if exists `p1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> delimiter //
mysql>
mysql> create procedure `p1`(`v1` int)
-> begin
-> select 1 from `t1` where `a`=v1 and `a` in (select `c` from t2);
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call `p1`(1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye
[miguel@skybr 6.0f]$ cd ..
[miguel@skybr dbs]$ cd 6.0
[miguel@skybr 6.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> delimiter ;
mysql>
mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop table if exists `t2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table `t1`(`a` int)engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql> create table `t2`(`c` int)engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> drop procedure if exists `p1`;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> delimiter //
mysql>
mysql> create procedure `p1`(`v1` int)
-> begin
-> select 1 from `t1` where `a`=v1 and `a` in (select `c` from t2);
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call `p1`(1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[23 Jan 2008 14:45]
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/41155 ChangeSet@1.2767, 2008-01-22 17:26:40+01:00, mhansson@linux-st28.site +8 -0 Bug#33062
[29 Jan 2008 13:17]
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/41362 ChangeSet@1.2767, 2008-01-29 14:17:14+01:00, mhansson@client-10-129-10-216.upp.off.mysql.com +8 -0 Bug#33062: subquery in stored routine cause crash Subquery flattening maintained a pointer into the list structure representing a WHERE clause, which introduces a dependency on this structure not changing. This caused crashed in stored procedures. Fixed by doing a lookup in the list instead instead of relying on a pointer.
[20 Feb 2008 7:41]
Sergey Petrunya
Analysis: The problem was caused by this execution scenario:
Item_in_subselect::fix_fields( ref={pointer to location of this item in WHERE})
{
...
// remember where this is referred from
ref_ptr= ref;
}
...
st_select_lex::fix_prepare_information()
{
// This branch is why it crashes only in SPs:
if (!thd->stmt_arena->is_conventional() && first_execution)
{
// Replace the WHERE AND/OR structure with its copy.
where= prep_where->copy_andor_structure(thd);
}
}
...
bool JOIN::flatten_subqueries()
{
...
// This replaces in the saved copy of the WHERE condition, i.e. in the
// prep_where. The substitution is not performed for the active copy of the
// WHERE clause (which could be found in e.g. join->conds), which causes
// assertion failure on attempt to evaluate the WHERE clause.
*((*in_subq)->ref_ptr)= new Item_int(1);
...
}
[20 Feb 2008 8:33]
Sergey Petrunya
The patch causes the substitution to be performed in the active copy of the WHERE clause (i.e. in join->conds), but not in the saved copy (join->select_lex->prep_where). That is ok, because we do another prep_where= copy_andor_structure(...) call after the JOIN::flatten_subqueries() has finished (right after simplify_joins()). The second copying allows the s/Item_subselect/Item_int(1)/ change to be propagated in to the saved copy of the WHERE clause.
[21 Feb 2008 8:31]
Sergey Petrunya
Ok to push after feedback provided by email/irc is addressed.
[21 Feb 2008 10:27]
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/42739 ChangeSet@1.2767, 2008-02-21 11:26:58+01:00, mhansson@client-10-129-10-216.mysql.com +5 -0 Bug#33062: subquery in stored routine cause crash Subquery flattening maintained a pointer into the list structure representing a WHERE clause, which introduces a dependency on this structure not changing. This caused crashes in stored procedures. Fixed by doing a lookup in the list instead instead of relying on a pointer.
[25 Feb 2008 10:54]
MySQL Verification Team
another testcase:
drop table if exists a;
drop table if exists t3;
create table a(c1 tinyblob)engine=myisam;
create table t3(e tinyint,f tinytext)engine=myisam;
insert into a values (null),('hi');
insert into t3 values (1,'test'),(2,null),(-4,'ww');
select 1 from a,t3
where `f` in(select 1 from a) and
`c1` not in(select 1 from a) and
`e` = any(select 1 from t3,a);
[13 Mar 2008 19:27]
Bugs System
Pushed into 6.0.5-alpha
[10 Apr 2008 13:39]
Paul DuBois
Could you provide a sentence or two for the changelog entry that will be meaningful to users? (Not phrased in terms of internal data structures.) Thanks.
[10 Apr 2008 15:02]
Martin Hansson
Hi Paul, that would be very hard, since this is indeed a bug inside an internal data structure. But let's give it a shot anyway: MySQL flattens subqueries into semijoins by rewriting an SQL query internally. This is done by rewriting what is called the active copy of the WHERE clause. (Every stored procedure has its own copy of the WHERE clause). Previously we had no control of which copy was the current one, whereas now we make sure to rewrite the correct one. Hope this helps! cheers Martin
[23 Apr 2008 18:58]
Sergey Petrunya
Notes for the changelog: One could cause crash by taking a query with a semi-join subquery and repeatedly re-running it, e.g. running it in a loop in a stored procedure and/or preparing a prepared statement and then re-executing it several times.
[25 Apr 2008 17:19]
Paul DuBois
Noted in 6.0.5 changelog. Repeatedly executing a query with a semi-join subquery could cause a server crash.
[10 May 2010 13:49]
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/107857 3158 Martin Hansson 2010-05-10 Bug#33062: subquery in stored routine cause crash Subquery flattening maintained a pointer into the list structure representing a WHERE clause, which introduces a dependency on this structure not changing. This caused crashes in stored procedures. Fixed by doing a lookup in the list instead instead of relying on a pointer.
[11 May 2010 7:39]
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/107914 3166 Martin Hansson 2010-05-11 Bug#33062: subquery in stored routine cause crash Subquery flattening maintained a pointer into the list structure representing a WHERE clause, which introduces a dependency on this structure not changing. This caused crashes in stored procedures. Fixed by doing a lookup in the list instead instead of relying on a pointer.
[4 Aug 2010 12:23]
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/115006 3203 Martin Hansson 2010-08-04 Bug#33062: subquery in stored routine cause crash Post back-port fix. The semantics for replace_where_subcondition has changed, so the name and documentation has been changed to reflect the new. Some warnings are also eliminated.
[4 Aug 2010 15:00]
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/115032 3203 Martin Hansson 2010-08-04 Bug#33062: subquery in stored routine cause crash Post back-port fix. The semantics for replace_where_subcondition has changed, so the name and documentation has been changed to reflect the new. Some warnings are also eliminated.
[11 Aug 2010 14:12]
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/115499 3203 Martin Hansson 2010-08-11 Bug#33062: subquery in stored routine cause crash Post back-port fix. The semantics for replace_where_subcondition has changed, so the name and documentation has been changed to reflect the new. Some warnings are also eliminated.
[12 Aug 2010 12:49]
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/115581 3203 Martin Hansson 2010-08-12 Bug#33062: subquery in stored routine cause crash Post back-port fix. The semantics for replace_where_subcondition has changed, so the name and documentation has been changed to reflect the new. Some warnings are also eliminated.
[12 Aug 2010 13:39]
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/115591 3203 Martin Hansson 2010-08-12 Bug#33062: subquery in stored routine cause crash Post back-port fix. The semantics for replace_where_subcondition has changed, so the name and documentation has been changed to reflect the new. Some warnings are also eliminated.
[13 Aug 2010 11:07]
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/115668 3228 Martin Hansson 2010-08-13 Bug#33062: subquery in stored routine cause crash Post back-port fix. The semantics for replace_where_subcondition has changed, so the name and documentation has been changed to reflect the new. Some warnings are also eliminated.
[16 Aug 2010 6:36]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[2 Oct 2010 18:15]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:24]
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)
[20 Nov 2010 23:05]
Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:08]
Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.
Description: crash in stored procedure when selecting and a variable + subquery is involved: Version: '6.0.3-alpha-community-debug' socket: '' port: 3306 MySQL Community Server - Debug (GPL) Assertion failed: 0, file .\item_subselect.cc, line 795 mysqld-debug.exe!_NMSG_WRITE mysqld-debug.exe!abort mysqld-debug.exe!_assert mysqld-debug.exe!Item_in_subselect::val_int mysqld-debug.exe!eval_const_cond mysqld-debug.exe!remove_eq_conds mysqld-debug.exe!remove_eq_conds mysqld-debug.exe!optimize_cond mysqld-debug.exe!JOIN::optimize mysqld-debug.exe!mysql_select mysqld-debug.exe!handle_select mysqld-debug.exe!execute_sqlcom_select mysqld-debug.exe!mysql_execute_command mysqld-debug.exe!sp_instr_stmt::exec_core mysqld-debug.exe!sp_lex_keeper::reset_lex_and_exec_core mysqld-debug.exe!sp_instr_stmt::execute mysqld-debug.exe!sp_head::execute mysqld-debug.exe!sp_head::execute_procedure mysqld-debug.exe!mysql_execute_command mysqld-debug.exe!mysql_parse mysqld-debug.exe!dispatch_command mysqld-debug.exe!do_command mysqld-debug.exe!handle_one_connection mysqld-debug.exe!pthread_start mysqld-debug.exe!_threadstart How to repeat: #use debug build of 6.0.3 or higher: delimiter ; drop table if exists `t1`; drop table if exists `t2`; create table `t1`(`a` int)engine=myisam; create table `t2`(`c` int)engine=myisam; drop procedure if exists `p1`; delimiter // create procedure `p1`(`v1` int) begin select 1 from `t1` where `a`=v1 and `a` in (select `c` from t2); end // delimiter ; call `p1`(1);