MySQL Bugs: #51487: Assertion failure when semi-join flattening occurs for a subquery in HAVING

Bug #51487	Assertion failure when semi-join flattening occurs for a subquery in HAVING
Submitted:	25 Feb 2010 8:14	Modified:	23 Nov 2010 3:21
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	optimizer_switch, semijoin, subquery

Description:
After the fix for BUG#49198, the server will fail an assert for queries in form:

select ... 
having ( semi-join parent select (semi-join child select with 
                                  reference to grandparent select))

How to repeat:
Run this:

create table t1 (a int);
insert into t1 values (1),(2);
alter table t1 add b int;
create table t2 as select * from t1;
create table t3 as select * from t1;
create table t4 as select * from t1;
select count(*) from t4 group by t4.a having t4.a in (select t3.a from t3 where t3.b in (select b from t2 where t2.a=t4.a));

And get this:
mysqld: item.cc:6687: virtual void Item_ref::fix_after_pullout(st_select_lex*, Item**): Assertion `false' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 0xad95c790 (LWP 24846)]
0xb805b430 in __kernel_vsyscall ()
(gdb) wher
#0  0xb805b430 in __kernel_vsyscall ()
#1  0xb7d736d0 in raise () from /lib/tls/i686/cmov/libc.so.6
#2  0xb7d75098 in abort () from /lib/tls/i686/cmov/libc.so.6
#3  0xb7d6c5ce in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
#4  0x081e01d2 in Item_ref::fix_after_pullout (this=0xb23e898, new_parent=0xb22bd68, refptr=0xb23e37c)
    at item.cc:6687
#5  0x0820770e in Item_func::fix_after_pullout (this=0xb23e320, new_parent=0xb22bd68, ref=0xb23ffd4)
    at item_func.cc:219
#6  0x08222c07 in Item_cond::fix_after_pullout (this=0xb23ff28, new_parent=0xb22bd68, ref=0xb23f9c4)
    at item_cmpfunc.cc:4232
#7  0x0836a64e in convert_subq_to_sj (parent_join=0xb26ea70, subq_pred=0xb23e418) at sql_select.cc:3498
#8  0x0836ab5e in JOIN::flatten_subqueries (this=0xb26ea70) at sql_select.cc:3656
#9  0x0836bfdb in JOIN::optimize (this=0xb26ea70) at sql_select.cc:1443
#10 0x0826264e in subselect_single_select_engine::exec (this=0xb23e5d0) at item_subselect.cc:2241
#11 0x082671b9 in Item_subselect::exec (this=0xb23e508) at item_subselect.cc:283
#12 0x08267328 in Item_in_subselect::exec (this=0xb23e508) at item_subselect.cc:333
#13 0x0825f9d4 in Item_in_subselect::val_bool (this=0xb23e508) at item_subselect.cc:929
#14 0x081f319a in Item::val_bool_result (this=0xb23e508) at ../item.h:767
#15 0x08224e91 in Item_in_optimizer::val_int (this=0xb23e988) at item_cmpfunc.cc:1824
#16 0x083070e8 in SQL_SELECT::skip_record (this=0xb23f7c8) at opt_range.h:763
#17 0x0841dc97 in find_all_keys (param=0xad959b84, select=0xb23f7c8, sort_keys=0xb2419b0, 
    buffpek_pointers=0xad959c54, tempfile=0xad959d44, indexfile=0x0) at filesort.cc:624
#18 0x0841e71c in filesort (thd=0xb190148, table=0xb2401c0, sortorder=0xb23f910, s_length=1, select=0xb23f7c8, 
    max_rows=18446744073709551615, sort_positions=false, examined_rows=0xad959eb0) at filesort.cc:248
#19 0x0835444a in create_sort_index (thd=0xb190148, join=0xb269c10, order=0xb22bca0, 
    filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=true)
    at sql_select.cc:19439
#20 0x08375fff in JOIN::exec (this=0xb269c10) at sql_select.cc:2905
#21 0x08370b3c in mysql_select (thd=0xb190148, rref_pointer_array=0xb191760, tables=0xb22b7b8, wild_num=0, 
    fields=@0xb1916f0, conds=0x0, og_num=1, order=0x0, group=0xb22bca0, having=0xb23e508, proc_param=0x0, 
    select_options=2147748608, result=0xb23e668, unit=0xb191198, select_lex=0xb19165c) at sql_select.cc:3153
#22 0x083765b2 in handle_select (thd=0xb190148, lex=0xb19113c, result=0xb23e668, setup_tables_done_option=0)
    at sql_select.cc:304
#23 0x082c8ef4 in execute_sqlcom_select (thd=0xb190148, all_tables=0xb22b7b8) at sql_parse.cc:4890

Thank you for the bug report. Verified just as described on Mac OS X with recent mysql-6.0-codebase tree.

Problem goes away when disabling semijoin, so according to our rules, I remove the regression tag.

See also bug#49198

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/102547

3798 Tor Didriksen	2010-03-08
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove unused virtual function (the DBUG_ASSERT was wrong)
     @ sql/item.h
        Remove unused virtual function.

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/102554

3800 Tor Didriksen	2010-03-08
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove unused virtual function (the DBUG_ASSERT was wrong)
     @ sql/item.h
        Remove unused virtual function.

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/103200

3822 Tor Didriksen	2010-03-15
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
      
      We now handle fix_after_pullout() in Item_ref, rather than Item_direct_view_ref.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove unused virtual function (the DBUG_ASSERT was wrong)
     @ sql/item.h
        Remove unused virtual function.

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/103226

3824 Tor Didriksen	2010-03-15
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
      
      We now handle fix_after_pullout() in Item_ref, rather than Item_direct_view_ref.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove virtual function Item_direct_view_ref::fix_after_pullout()
     @ sql/item.h
        Remove virtual function Item_direct_view_ref::fix_after_pullout()

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/103402

3821 Tor Didriksen	2010-03-15
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
      
      We now handle fix_after_pullout() in Item_ref, rather than Item_direct_view_ref.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove virtual function Item_direct_view_ref::fix_after_pullout()
     @ sql/item.h
        Remove virtual function Item_direct_view_ref::fix_after_pullout()

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/103648

3832 Tor Didriksen	2010-03-15
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
      
      We now handle fix_after_pullout() in Item_ref, rather than Item_direct_view_ref.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove virtual function Item_direct_view_ref::fix_after_pullout()
     @ sql/item.h
        Remove virtual function Item_direct_view_ref::fix_after_pullout()

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/103803

3834 Tor Didriksen	2010-03-19
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
      
      Remove Item_ref::fix_after_pullout, use the (empty) one inherited from Item
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove virtual function Item_ref::fix_after_pullout()
     @ sql/item.h
        Remove virtual function Item_ref::fix_after_pullout()

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/103860

3837 Tor Didriksen	2010-03-19
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove DBUG_ASSERT(false) from Item_ref::fix_after_pullout()
        Add some @todo comments/asserts, based on discussions with reviewers.

Pushed to
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)

Noted in 6.0.14 changelog.

For debug builds, semijoin flattening for a subquery in a HAVING
clause could raise an assertion.

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/105722

3077 Tor Didriksen	2010-04-15
      Bug #51487 Assertion failure when semi-join flattening occurs for a subquery in HAVING 
      
      Backport of tor.didriksen@sun.com-20100319145250-fgnaangdtr6q3w2r
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/item.cc
        Remove DBUG_ASSERT(false) from Item_ref::fix_after_pullout()
        Add some @todo comments/asserts, based on discussions with reviewers.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.