Bug #32680 Wrong subselect result
Submitted: 23 Nov 2007 20:00 Modified: 20 Nov 2010 18:05
Reporter: Evgeny Potemkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any

[23 Nov 2007 20:00] Evgeny Potemkin
Description:
Test case for the bug#31048 returns non-empty result while it shouldn't.

How to repeat:
Uncomment it in the subselect.test and run.

Suggested fix:
no idea yet.
[2 May 2008 19:22] Sergey Petrunya
The queries in question are single-row subqueries which are not covered by new 6.0 subquery optimizations. The execution strategies should be the same as in 5.1
[2 May 2008 20:05] Sergey Petrunya
Can't repeat the wrong result. Depending on the depth of nesting, the query either successfully runs or crashes.
[2 May 2008 20:05] Sergey Petrunya
... or produces this error:

ERROR 1436 (HY000): Thread stack overrun:  180232 bytes used of a 196608 byte st
ack, and 24000 bytes needed.  Use 'mysqld -O thread_stack=#' to specify a bigger
 stack.
[2 May 2008 20:43] Sergey Petrunya
Here's how it has crashed:
  Program received signal SIGSEGV, Segmentation fault.
  [Switching to Thread 0xad142390 (LWP 28466)]
  0xb7d2ab63 in vfprintf () from /lib/libc.so.6
(gdb) wher
  #0  0xb7d2ab63 in vfprintf () from /lib/libc.so.6
  #1  0xb7d47c2b in vsprintf () from /lib/libc.so.6
  #2  0xb7d33a6e in sprintf () from /lib/libc.so.6
  #3  0x08327f55 in check_stack_overrun (thd=0x9361258, margin=24000, buf=0x0) at sql_parse.cc:5430
  #4  0x08437b50 in SQL_SELECT::test_quick_select (this=0x94519d0, thd=0x9361258, keys_to_use={map = 3}, prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at opt_range.cc:2218
  #5  0x083a6fb5 in get_quick_record_count (thd=0x9361258, select=0x94519d0, table=0x93faff8, keys=0x9451768, limit=18446744073709551615) at sql_select.cc:3725
  #6  0x083abb36 in make_join_statistics (join=0x94266d0, tables=0x0, conds=0x93cec50, keyuse_array=0x94279c8) at sql_select.cc:4151
  #7  0x083ae7f6 in JOIN::optimize (this=0x94266d0) at sql_select.cc:1534
  #8  0x082cce3b in subselect_single_select_engine::exec (this=0x93cef10) at item_subselect.cc:2199
  #9  0x082c9b9f in Item_subselect::exec (this=0x93cee70) at item_subselect.cc:280
  #10 0x082c9eb8 in Item_singlerow_subselect::val_decimal (this=0x93cee70, decimal_value=0xad0e55c4) at item_subselect.cc:658
  #11 0x0825c01d in Item::save_in_field (this=0x93cee70, field=0x93fa238, no_conversions=true) at item.cc:4835
  #12 0x08250198 in Item::save_in_field_no_warnings (this=0x93cee70, field=0x93fa238, no_conversions=true) at item.cc:974
  #13 0x0842fc55 in get_mm_leaf (param=0xad0e6350, conf_func=0x93cef38, field=0x93fa238, key_part=0x945d5a0, type=Item_func::GT_FUNC, value=0x93cee70) at opt_range.cc:5673
  #14 0x084304bd in get_mm_parts (param=0xad0e6350, cond_func=0x93cef38, field=0x93fa238, type=Item_func::GT_FUNC, value=0x93cee70, cmp_type=INT_RESULT) at opt_range.cc:5482
  #15 0x08430d64 in get_func_mm_tree (param=0xad0e6350, cond_func=0x93cef38, field=0x93fa238, value=0x93cee70, cmp_type=INT_RESULT, inv=false) at opt_range.cc:5161
  #16 0x08430f1d in get_full_func_mm_tree (param=0xad0e6350, cond_func=0x93cef38, field_item=0x93c87b8, value=0x93cee70, inv=false) at opt_range.cc:5260
  #17 0x08431c60 in get_mm_tree (param=0xad0e6350, cond=0x93cef38) at opt_range.cc:5450
  #18 0x0843805a in SQL_SELECT::test_quick_select (this=0x94515b0, thd=0x9361258, keys_to_use={map = 3}, prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at opt_range.cc:2305
  #19 0x083a6fb5 in get_quick_record_count (thd=0x9361258, select=0x94515b0, table=0x93f9998, keys=0x9451348, limit=18446744073709551615) at sql_select.cc:3725
  #20 0x083abb36 in make_join_statistics (join=0x94252e0, tables=0x0, conds=0x93cef38, keyuse_array=0x94265d8) at sql_select.cc:4151
  #21 0x083ae7f6 in JOIN::optimize (this=0x94252e0) at sql_select.cc:1534
  #22 0x082cce3b in subselect_single_select_engine::exec (this=0x93cf238) at item_subselect.cc:2199
  #23 0x082c9b9f in Item_subselect::exec (this=0x93cf198) at item_subselect.cc:280
  #24 0x082c9eb8 in Item_singlerow_subselect::val_decimal (this=0x93cf198, decimal_value=0xad0e9534) at item_subselect.cc:658
  #25 0x0825c01d in Item::save_in_field (this=0x93cf198, field=0x93f8bd8, no_conversions=true) at item.cc:4835
  #26 0x08250198 in Item::save_in_field_no_warnings (this=0x93cf198, field=0x93f8bd8, no_conversions=true) at item.cc:974
  #27 0x0842fc55 in get_mm_leaf (param=0xad0ea2c0, conf_func=0x93cf260, field=0x93f8bd8, key_part=0x945c188, type=Item_func::GT_FUNC, value=0x93cf198) at opt_range.cc:5673
  #28 0x084304bd in get_mm_parts (param=0xad0ea2c0, cond_func=0x93cf260, field=0x93f8bd8, type=Item_func::GT_FUNC, value=0x93cf198, cmp_type=INT_RESULT) at opt_range.cc:5482
  #29 0x08430d64 in get_func_mm_tree (param=0xad0ea2c0, cond_func=0x93cf260, field=0x93f8bd8, value=0x93cf198, cmp_type=INT_RESULT, inv=false) at opt_range.cc:5161
  #30 0x08430f1d in get_full_func_mm_tree (param=0xad0ea2c0, cond_func=0x93cf260, field_item=0x93c7ee8, value=0x93cf198, inv=false) at opt_range.cc:5260
  #31 0x08431c60 in get_mm_tree (param=0xad0ea2c0, cond=0x93cf260) at opt_range.cc:5450
  #32 0x0843805a in SQL_SELECT::test_quick_select (this=0x9451190, thd=0x9361258, keys_to_use={map = 3}, prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at opt_range.cc:2305
....
  #334 0x083349bc in mysql_parse (thd=0x9361258, inBuf=0x93b7350 "select sum(a),a from t1 where a> (\n  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (\n  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (\n  select sum(a) from t1 where"..., length=1692, found_semicolon=0xad141a60) at sql_parse.cc:5768
  #335 0x08335414 in dispatch_command (command=COM_QUERY, thd=0x9361258, packet=0x93af2f1 "select sum(a),a from t1 where a> (\n  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (\n  select sum(a) from t1 where a> ( select sum(a) from t1 where a> (\n  select sum(a) from t1 where"..., packet_length=1692) at sql_parse.cc:1044
  #336 0x08336674 in do_command (thd=0x9361258) at sql_parse.cc:721
  #337 0x08322927 in handle_one_connection (arg=0x9361258) at sql_connect.cc:1134
  #338 0xb7fa418b in start_thread () from /lib/libpthread.so.0
  #339 0xb7db009e in clone () from /lib/libc.so.6
(gdb)
[2 May 2008 21:31] Sergey Petrunya
#3  0x08327f55 in check_stack_overrun (thd=0x9361258, margin=24000, buf=0x0) at sql_parse.cc:5430
(gdb) print stack_used
  $5 = 391352
(gdb) p sizeof(PARAM)
  $6 = 10896
(gdb) up
  #4  0x08437b50 in SQL_SELECT::test_quick_select (this=0x94519d0, thd=0x9361258, keys_to_use={map = 3}, prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at opt_range.cc:2218
(gdb) p &param
  $7 = (PARAM *) 0xad0e23e0
(gdb) up
  #5  0x083a6fb5 in get_quick_record_count (thd=0x9361258, select=0x94519d0, table=0x93faff8, keys=0x9451768, limit=18446744073709551615) at sql_select.cc:3725
(gdb) up
  #6  0x083abb36 in make_join_statistics (join=0x94266d0, tables=0x0, conds=0x93cec50, keyuse_array=0x94279c8) at sql_select.cc:4151
(gdb) up
  #7  0x083ae7f6 in JOIN::optimize (this=0x94266d0) at sql_select.cc:1534
(gdb) up
  #8  0x082cce3b in subselect_single_select_engine::exec (this=0x93cef10) at item_subselect.cc:2199
(gdb) up
  #9  0x082c9b9f in Item_subselect::exec (this=0x93cee70) at item_subselect.cc:280
(gdb) up
  #10 0x082c9eb8 in Item_singlerow_subselect::val_decimal (this=0x93cee70, decimal_value=0xad0e55c4) at item_subselect.cc:658
(gdb) up
  #11 0x0825c01d in Item::save_in_field (this=0x93cee70, field=0x93fa238, no_conversions=true) at item.cc:4835
(gdb) up
  #12 0x08250198 in Item::save_in_field_no_warnings (this=0x93cee70, field=0x93fa238, no_conversions=true) at item.cc:974
(gdb) up
  #13 0x0842fc55 in get_mm_leaf (param=0xad0e6350, conf_func=0x93cef38, field=0x93fa238, key_part=0x945d5a0, type=Item_func::GT_FUNC, value=0x93cee70) at opt_range.cc:5673
(gdb) up
  #14 0x084304bd in get_mm_parts (param=0xad0e6350, cond_func=0x93cef38, field=0x93fa238, type=Item_func::GT_FUNC, value=0x93cee70, cmp_type=INT_RESULT) at opt_range.cc:5482
(gdb) up
  #15 0x08430d64 in get_func_mm_tree (param=0xad0e6350, cond_func=0x93cef38, field=0x93fa238, value=0x93cee70, cmp_type=INT_RESULT, inv=false) at opt_range.cc:5161
(gdb) up
  #16 0x08430f1d in get_full_func_mm_tree (param=0xad0e6350, cond_func=0x93cef38, field_item=0x93c87b8, value=0x93cee70, inv=false) at opt_range.cc:5260
(gdb) up
  #17 0x08431c60 in get_mm_tree (param=0xad0e6350, cond=0x93cef38) at opt_range.cc:5450
(gdb) up
  #18 0x0843805a in SQL_SELECT::test_quick_select (this=0x94515b0, thd=0x9361258, keys_to_use={map = 3}, prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at opt_range.cc:2305
(gdb) p &param
  $8 = (PARAM *) 0xad0e6350
(gdb) p 0xad0e6350 - 0xad0e23e0
  $9 = 16240

So, one level of nesting takes ~16K of stack, of which about 10K are taken by the range analyzer.

There are two approaches to solve this:

A) Let stack overflow protection code take into account that range optimization   occupies 10K (or more) on the stack.

B) Make the range optimizer to not evaluate the subqueries.

We're leaning towards solution B, as it also solves the problem of EXPLAIN running too long if it decides to evaluate a subquery.
[3 May 2008 3:27] Sergey Petrunya
Decided go for solution #A after all - we don't want to disable all subqueries at all but we can't easily tell expensive subqueries from inexpensive atm.
[3 May 2008 3:33] 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/46322

ChangeSet@1.2636, 2008-05-03 07:32:10+04:00, sergefp@mysql.com +1 -0
  BUG#32680: Wrong subselect result (or crash)
  - Make the range optimizer run only if there is plenty (3*STACK_MIN_SIZE) 
    free space for the stack.
  - There is no testcase as the problematic query depends on thread-stack 
    setting and doesn't fail with the default.
[28 May 2008 10:02] Bugs System
Pushed into 6.0.6-alpha
[29 May 2008 3:29] Paul DuBois
Noted in 6.0.6 changelog.

Deeply nested subqueries could cause stack overflow or a server crash.
[16 Aug 2010 6:41] 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:21] 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 18:05] Paul DuBois
Noted in 5.6.1 changelog.