| Bug #27870 | join and subselect crash server | ||
|---|---|---|---|
| Submitted: | 17 Apr 2007 3:44 | Modified: | 24 Apr 2007 1:05 |
| Reporter: | Adam Dixon | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.40, 5.1.18 | OS: | Any |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
| Tags: | bfsm_2007_04_19, crash, join, regression, Signal 11, subselect | ||
[17 Apr 2007 7:02]
MySQL Verification Team
Verified, here's a stack trace: mysqld-nt.exe!Item_in_optimizer::val_int() mysqld-nt.exe!Item::val_bool() mysqld-nt.exe!Item_func_not::val_int() mysqld-nt.exe!evaluate_join_record() mysqld-nt.exe!sub_select() mysqld-nt.exe!do_select() mysqld-nt.exe!JOIN::exec() mysqld-nt.exe!mysql_select() mysqld-nt.exe!handle_select() mysqld-nt.exe!mysql_execute_command() mysqld-nt.exe!mysql_parse() mysqld-nt.exe!dispatch_command() mysqld-nt.exe!do_command() mysqld-nt.exe!handle_one_connection() mysqld-nt.exe!_pthread_start() mysqld-nt.exe!_threadstart() kernel32.dll!FlsSetValue()
[17 Apr 2007 7:05]
MySQL Verification Team
This is a regression! 5.0.27 and 5.0.24a don't crash.
[17 Apr 2007 7:38]
MySQL Verification Team
testcase: drop table if exists `t1`; drop table if exists `t2`; create table `t1` (`a` int); create table `t2` (`b` int,primary key(`b`)); insert into `t1` values (),(); insert into `t2` values (1),(2); select 1 from `t1`, `t2` where `a`=`b` and (`b` not in (select 1 from `t1`));
[17 Apr 2007 7:46]
Valeriy Kravchuk
Same stack trace on Linux: openxs@suse:~/dbs/5.0> bin/resolve_stack_dump -s /tmp/mysqld5.sym 27870.stack 0x81bbd46 handle_segfault + 646 0x8154a53 _ZN17Item_in_optimizer7val_intEv + 339 0x811eb09 _ZN4Item8val_boolEv + 121 0x8153f55 _ZN13Item_func_not7val_intEv + 21 0x8213372 _Z20evaluate_join_recordP4JOINP13st_join_tableiPc + 114 0x8213662 _Z10sub_selectP4JOINP13st_join_tableb + 354 0x821a5c1 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 353 0x822c4e8 _ZN4JOIN4execEv + 4536 0x8228cc8 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orde rSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 184 0x822d497 _Z13handle_selectP3THDP6st_lexP13select_resultm + 359 0x81d7b73 _Z21mysql_execute_commandP3THD + 16451 0x81dd5c1 _Z11mysql_parseP3THDPcj + 369 0x81df4ee _Z16dispatch_command19enum_server_commandP3THDPcj + 2366 0x81e0f0f handle_one_connection + 1919 0x40050aa7 _end + 932670611 0x40247c2e _end + 934731290
[17 Apr 2007 9:12]
Sergey Petrunya
The problem is caused by interplay between NULL-IN-SELECT() query handling and equality propagation.
The query is:
SELECT distinct t1.uid
FROM t1, t2
WHERE t2.id = t1.typeid and
(t2.id not in (select parentid from t2 where id = 101));
NULL-IN-SELECT rewrite code sees subquery's left_expr t2.id is may not be NULL
and does not make any provisions for execution of NULL IN (SELECT ...).
Then join order of (t1, t2) gets choosen.
Equality propagation uses "t2.id = t1.typeid" to change
t2.id NOT IN ...
into
t1.typeid NOT IN ...
This allows to attach the subquery predicate to table t1.
t1.typeid maybe NULL, and we get a crash when we try to evaluate the
NULL IN (SELECT ...)
[17 Apr 2007 11:38]
Sergey Petrunya
Fix suggestion 1
================
Equality propagation guarantees that the partial row combination we're
considering won't be in the query output. Therefore, we can just return
arbitrary value (e.g. NULL) as the value of subquery predicate.
Fix suggestion 2
================
Create an IS NOT NULL predicate and attach it to the appropriate table. That
is, suppose we have
Item_equal(t1.col1, t2.col2, ..., tN.colN).
Let's order them by the join order (suppose it's t1,t2,...)
t1.col1
t2.col2
...
The check "t1.col1=t2.col2" is performed as soon as we get records for t1 and
t2. However, if we got t1.col1==NULL, we can discard t1 row immediately.
This can be achieved by injecting the appropriate IS NOT NULL predicate.
The rule will be:
for each Item_equal // there is a list of them somewhere
{
In the list of equal fields,
find tableX.columnY with minimal tableX.position_in_join_order;
tableX.join_tab.cond
"tableX.columnY IS NOT NULL" AND tableX.join_tab.cond;
}
In addition to this rule we will also need to make this fix in the
equal field substitution:
- when calculating condition of table X, do not substitute X.col with
some_preceding_table.col
.
[18 Apr 2007 0:34]
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/24734 ChangeSet@1.2456, 2007-04-17 17:35:29-07:00, igor@olga.mysql.com +3 -0 Fixed bug #27870. The bug that causes crashes manifests itself at some conditions when executing an equijoin query with WHERE condition containing a subquery predicate of the form join_attr NOT IN (SELECT ...). To resolve a problem of the correct evaluation of the expression attr NOT IN (SELECT ...) an array of guards is created to make it possible to filter out some predicates of the EXISTS subquery into which the original subquery predicate is transformed, in the cases when a takes the NULL value. If attr is defined as a field that cannot be NULL than such an array is not needed and is not created. However if the field a occurred also an an equijoin predicate t2.a=t1.b and table t1 is accessed before table t2 then it may happen that the the EXISTS subquery is pushed down to the condition evaluated just after table t1 has been accessed. In this case any occurrence of t2.a is substituted for t1.b. When t1.b takes the value of NULL an attempt is made to turn on the corresponding guard. This action caused a crash as no guard array had been created. Now the code of Item_in_subselect::set_cond_guard_var checks that the guard array has been created before setting a guard variable on. Otherwise the method does nothing. It cannot results in returning a row that could be rejected as the condition t2.a=t1.b will be checked later anyway.
[18 Apr 2007 23:47]
Sergey Petrunya
The fix for this bug is an implementation of Fix suggestion #1. Fix Suggestion #2 aims for slightly better performance but actually it is trying to fix two problems at once - the 1st is the described crash, the second is the absense of NULL filtering. Solving one problem doesn't solve the other, so the second problem will be adressed separately as BUG#27939.
[19 Apr 2007 20:56]
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/24953 ChangeSet@1.2464, 2007-04-19 22:56:04+02:00, kent@mysql.com +2 -0 mysql.sln: Enable 'mysys' build if target 'Enterprise' item_subselect.h: Fixed bug #27870. The bug that causes crashes manifests itself at some conditions when executing an equijoin query with WHERE condition containing a subquery predicate of the form join_attr NOT IN (SELECT ...).
[19 Apr 2007 20:57]
Mads Martin Joergensen
This have gone into 5.0.40 release clone. When documented in 5.0.40 changelog, please set it back to Patch Queued.
[21 Apr 2007 15:19]
Bugs System
Pushed into 5.1.18-beta
[21 Apr 2007 15:20]
Bugs System
Pushed into 5.0.42
[24 Apr 2007 1:05]
Paul DuBois
Noted in 5.0.42, 5.1.18 changelogs. Some equi-joins containing a WHERE clause that included a NOT IN subquery caused a server crash.
[27 Apr 2007 9:21]
Bugs System
Pushed into 5.1.18-beta
[27 Apr 2007 9:24]
Bugs System
Pushed into 5.0.42
[1 May 2007 12:50]
Paul DuBois
Moved the 5.0.x changelog entry from 5.0.42 to 5.0.40.
Description: The following query crashes server with signal 11; Effects both 5.0.40bk and 5.1.18bk SELECT distinct t1.uid FROM t1, t2 WHERE t2.id = t1.typeid and (t2.id not in (select parentid from t2 where id = 101) ); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1450 Extra: Using where; Using temporary *************************** 2. row *************************** id: 1 select_type: PRIMARY table: t2 type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.t1.typeid rows: 1 Extra: Using index; Distinct *************************** 3. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 3 rows in set (0.00 sec) How to repeat: mysql> source reproduce.sql; mysql> SELECT distinct t1.uid FROM t1, t2 WHERE t2.id = t1.typeid and (t2.id not in (select parentid from t2 where id = 101) );