maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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