Hi Sanja, On Thu, Jun 23, 2016 at 05:50:08PM +0200, Oleksandr Byelkin wrote:

revision-id: c584f1655ef45dafd2b5d0eb55ca21421de9a266 (mariadb-10.1.14-25-gc584f16) parent(s): 773ce408762b5f8256d4053b6d0d418d15657b92 committer: Oleksandr Byelkin timestamp: 2016-06-23 17:50:07 +0200 message:

MDEV-10017: Get unexpected `Empty Set` for correlated subquery with aggregate functions (part 1)

Make aggregate function dependency visible.

* I was able to get a crash with the patch. The details are below. * The wording of the warning feels odd:

Field or reference 'test.t10.b' of SELECT #3 was resolved in SELECT #1 ... Aggregate function 'max()' of SELECT #3 belongs to SELECT #1

+ I like how the wording of the new warning is similar to the old one. - It is not immediately clear what does "belong to" mean. I would change it to " Aggregate function 'max()' of SELECT #3 is aggregated in SELECT #1 " This looks much clearer. OTOH, "Aggregate .. is aggregated" doesn't sound very good. We probably need to consult a native speaker (of both English and SQL :). == Details about the crash == create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t10 (a int , b int, c int); insert into t10 select a,a,a from ten; create table t11 as select * from t10; create table t2 as select * from t10; explain extended select a from t10 where c<3 or a in (select c from t12 union select max(t10.b) from t11 group by t11.c); Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7ffff7f1fb00 (LWP 4805)] 0x0000555555d0dd3f in Item_sum::register_sum_func (this=0x7fff89845680, thd=0x7fff9cbf6070, ref=0x7fff898457d0) at /home/psergey/dev-git/10.1/sql/item_sum.cc:391 (gdb) wher #0 0x0000555555d0dd3f in Item_sum::register_sum_func (this=0x7fff89845680, thd=0x7fff9cbf6070, ref=0x7fff898457d0) at /home/psergey/dev-git/10.1/sql/item_sum.cc:391 #1 0x0000555555d0d6f2 in Item_sum::check_sum_func (this=0x7fff89845680, thd=0x7fff9cbf6070, ref=0x7fff898457d0) at /home/psergey/dev-git/10.1/sql/item_sum.cc:177 #2 0x0000555555d1041e in Item_sum_hybrid::fix_fields (this=0x7fff89845680, thd=0x7fff9cbf6070, ref=0x7fff898457d0) at /home/psergey/dev-git/10.1/sql/item_sum.cc:1222 #3 0x00005555559c3cf2 in setup_fields (thd=0x7fff9cbf6070, ref_pointer_array=0x7fff898480f0, fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fff89847ec8, allow_sum_func=true) at /home/psergey/dev-git/10.1/sql/sql_base.cc:7899 #4 0x0000555555a5b886 in JOIN::prepare (this=0x7fff89847b68, rref_pointer_array=0x7fff89845138, tables_init=0x7fff89845830, wild_num=0, conds_init=0x0, og_num=1, order_init=0x0, skip_order_by=true, group_init=0x7fff89845f38, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff89844ec0, unit_arg=0x7fff89844078) at /home/psergey/dev-git/10.1/sql/sql_select.cc:795 #5 0x0000555555af7082 in st_select_lex_unit::prepare (this=0x7fff89844078, thd_arg=0x7fff9cbf6070, sel_result=0x7fff89846118, additional_options=268435456) at /home/psergey/dev-git/10.1/sql/sql_union.cc:454 #6 0x0000555555d04d2c in subselect_union_engine::prepare (this=0x7fff89846140, thd_arg=0x7fff9cbf6070) at /home/psergey/dev-git/10.1/sql/item_subselect.cc:3590 #7 0x0000555555cfabe4 in Item_subselect::fix_fields (this=0x7fff89845f88, thd_param=0x7fff9cbf6070, ref=0x7fff89846270) at /home/psergey/dev-git/10.1/sql/item_subselect.cc:262 #8 0x0000555555d03f3d in Item_in_subselect::fix_fields (this=0x7fff89845f88, thd_arg=0x7fff9cbf6070, ref=0x7fff89846270) at /home/psergey/dev-git/10.1/sql/item_subselect.cc:3253 #9 0x0000555555c999cf in Item_cond::fix_fields (this=0x7fff89846178, thd=0x7fff9cbf6070, ref=0x7fff89846e98) at /home/psergey/dev-git/10.1/sql/item_cmpfunc.cc:4523 #10 0x00005555559c5bf1 in setup_conds (thd=0x7fff9cbf6070, tables=0x7fff89843330, leaves=..., conds=0x7fff89846e98) at /home/psergey/dev-git/10.1/sql/sql_base.cc:8627 #11 0x0000555555aa3eab in setup_without_group (thd=0x7fff9cbf6070, ref_pointer_array=0x7fff89846fd8, tables=0x7fff89843330, leaves=..., fields=..., all_fields=..., conds=0x7fff89846e98, order=0x0, group=0x0, hidden_group_fields=0x7fff89846d78, reserved=0x7fff9cbfa454) at /home/psergey/dev-git/10.1/sql/sql_select.cc:645 #12 0x0000555555a5b94b in JOIN::prepare (this=0x7fff89846a50, rref_pointer_array=0x7fff9cbfa430, tables_init=0x7fff89843330, wild_num=0, conds_init=0x7fff89846178, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff9cbfa1b8, unit_arg=0x7fff9cbf9ab8) at /home/psergey/dev-git/10.1/sql/sql_select.cc:799 #13 0x0000555555a64f91 in mysql_select (thd=0x7fff9cbf6070, rref_pointer_array=0x7fff9cbfa430, tables=0x7fff89843330, wild_num=0, fields=..., conds=0x7fff89846178, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff89846378, unit=0x7fff9cbf9ab8, select_lex=0x7fff9cbfa1b8) at /home/psergey/dev-git/10.1/sql/sql_select.cc:3415 #14 0x0000555555a9a6f5 in mysql_explain_union (thd=0x7fff9cbf6070, unit=0x7fff9cbf9ab8, result=0x7fff89846378) at /home/psergey/dev-git/10.1/sql/sql_select.cc:24737 #15 0x0000555555a2a9aa in execute_sqlcom_select (thd=0x7fff9cbf6070, all_tables=0x7fff89843330) at /home/psergey/dev-git/10.1/sql/sql_parse.cc:5834 #16 0x0000555555a20c72 in mysql_execute_command (thd=0x7fff9cbf6070) at /home/psergey/dev-git/10.1/sql/sql_parse.cc:2961 #17 0x0000555555a2e4be in mysql_parse (thd=0x7fff9cbf6070, rawbuf=0x7fff89843088 "explain extended select a from t10 where c<3 or a in (select c from t12 union select max(t10.b) from t11 group by t11.c)", length=120, parser_state=0x7ffff7f1e630) at /home/psergey/dev-git/10.1/sql/sql_parse.cc:7319 BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog