MySQL Bugs: #27229: crash/incorrect result in subquery with aggregate(outer

Bug #27229	crash/incorrect result in subquery with aggregate(outer_column) (debug server)
Submitted:	16 Mar 2007 21:25	Modified:	11 Apr 2007 1:59
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.38/5.1BK	OS:	FreeBSD (freebsd)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	aggrecate, crash, group_concat, qc, suquery

Description:
See the SQL.

One think I couldnt find out from the docs

 select count(a), a,
 ( select count(a) from t2 where m = a ),
 from t1 group by a;

Should count(a) in the above refer to the outer query?
http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html
> Correlated subqueries cannot refer to the results
> of aggregate functions from the outer query

The above query certainly does.

OR
should the interpretation be, that just "a" is imported from the outer query, and then "count(a)", counts the rows in T2 for which a is not null?
(This behaviour can be seen, by "sum(a+0*m)")

On the outher hand, the fact that subqueries allow nesting of aggregates, implies it should be a reference.

-
The answer to the above decides what the next 2 queries, should, should not do.
if references are allowed, how should "count(*)" be handled

It does not work in the nested version, but it can be used in the where clause:
 select count(*), a,
    ->  ( select m  from t2 where m = count(*) )
    ->   from t1 group by a;
+----------+------+------------------------------------------+
| count(*) | a    | ( select m  from t2 where m = count(*) ) |
+----------+------+------------------------------------------+
|        2 |    2 |                                        2 |
|        3 |    3 |                                        2 |
|        1 |    4 |                                        2 |
+----------+------+------------------------------------------+
"count(*)" in the subquery is evaluated for the first call, the 2nd and 3rd line of the result, re-use the value "count(*)" did have for the first line, try
 insert into t1 values (2,2);
and repeat the query

So this query should either be refused (return an error), or the subquery should see a fresh "count(*)" for each call)

-
And the last query from below crashe a server with debug.
on a 5.0.33-non-debug it does return, but an incorrect result
 select count(*) c, a, ( select group_concat(count(a)) from t2 where m = a ) from t1 group by a;
+---+------+-------------------------------------------------------+
| c | a    | ( select group_concat(count(a)) from t2 where m = a ) |
+---+------+-------------------------------------------------------+
| 2 |    2 | 10301023977472,10301023977472                         |
| 3 |    3 | 10301023977472,10301023977472                         |
| 1 |    4 | 10301023977472,10301023977472                         |
+---+------+-------------------------------------------------------+

expected
+---+------+-------------------------------------------------------+
| c | a    | ( select group_concat(count(a)) from t2 where m = a ) |
+---+------+-------------------------------------------------------+
| 2 |    2 | 2
| 3 |    3 | 3
| 1 |    4 | 1,1 # t2 has 2 rows for m=4
+---+------+-------------------------------------------------------+

How to repeat:
--disable_warnings
drop table if exists t1, t2;
--enable_warnings

create table t1 (a int, b int);
create table t2 (m int, n int);
insert into t1 values (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
insert into t2 values (1,11), (2,22), (3,32), (4,44), (4,44);
set @@sql_mode='TRADITIONAL';

# Good Queries (if reference to the outer aggregate is wanted)
select count(a), a,
 ( select count(a) from t2 where m = a ),
 ( select count(*) from t2 where m = a )
  from t1 group by a;

select count(a), a,
 ( select count(count(a)) from t2 where m = a ),
 ( select count(*) from t2 where m = a )
  from t1 group by a;

select count(a), a,
 ( select sum(count(a)) from t2 where m = a ),
 ( select count(*) from t2 where m = a )
  from t1 group by a;

#Queries returning an Error (Expected)
select count(*), a,
 ( select count(count(*)) from t2 where m = a ),
 ( select count(*) from t2 where m = a )
  from t1 group by a;

# Querie returning, but Wrong result
select count(*), a,
 ( select m  from t2 where m = count(*) )
  from t1 group by a;

#Crash
select count(*) c, a,
 ( select group_concat(count(a)) from t2 where m = a )
 from t1 group by a; # crash

Suggested fix:
The crash, and the "count(*)" may be 2 completly seperate  Bugs.

But as I was unable, to find out the exact specified behaviour, I report it like this, and may follow up later

stacktrace for the crash:
#0  0x2849131b in pthread_testcancel () from /usr/lib/libpthread.so.1
(gdb) bt
#0  0x2849131b in pthread_testcancel () from /usr/lib/libpthread.so.1
#1  0x2847c1dd in pthread_kill () from /usr/lib/libpthread.so.1
#2  0x081d921a in write_core (sig=6) at stacktrace.c:244
#3  0x080ca58d in handle_segfault (sig=6) at mysqld.cc:2157
#4  0x28480e26 in sigaction () from /usr/lib/libpthread.so.1
#5  0x28480cab in sigaction () from /usr/lib/libpthread.so.1
#6  0x2848186c in sigaction () from /usr/lib/libpthread.so.1
#7  0x28481afe in sigaction () from /usr/lib/libpthread.so.1
#8  0x28489043 in pthread_mutexattr_init () from /usr/lib/libpthread.so.1
#9  0x28491a35 in pthread_testcancel () from /usr/lib/libpthread.so.1
#10 0x2847c1ee in pthread_kill () from /usr/lib/libpthread.so.1
#11 0x2847bbac in raise () from /usr/lib/libpthread.so.1
#12 0x28639c1b in abort () from /lib/libc.so.5
#13 0x286149ff in __assert () from /lib/libc.so.5
#14 0x080694bf in dump_leaf_key (key=0x9fcb8e9 "q\ufffd\t", count=1, item=0x9fa0820) at item_sum.cc:2934
#15 0x08069e0e in Item_func_group_concat::add (this=0x9fa0820) at item_sum.cc:3150
#16 0x0812fee1 in init_sum_functions (func_ptr=0x9fa1658, end_ptr=0x9fa165c) at item_sum.h:268
#17 0x0812ad4e in end_send_group (join=0x9f9be10, join_tab=0x9fcc570, end_of_records=false) at sql_select.cc:11406
#18 0x08129177 in evaluate_join_record (join=0x9f9be10, join_tab=0x9fcc3f8, error=167142400, report_error=0x0) at sql_select.cc:10542
#19 0x08128f66 in sub_select (join=0x9f9be10, join_tab=0x9fcc3f8, end_of_records=false) at sql_select.cc:10431
#20 0x08128aa6 in do_select (join=0x9f9be10, fields=0x9f9cb94, table=0x0, procedure=0x0) at sql_select.cc:10188
#21 0x08118edb in JOIN::exec (this=0x9f9be10) at sql_select.cc:1995
#22 0x080a3b57 in subselect_single_select_engine::exec (this=0x9fa0f50) at item_subselect.cc:1828
#23 0x0809fb5f in Item_subselect::exec (this=0x9fa0eb8) at item_subselect.cc:212
#24 0x080a059b in Item_singlerow_subselect::val_str (this=0x9fa0eb8, str=0xbfa33e74) at item_subselect.cc:520
#25 0x0805b7ec in Item::send (this=0x9fa0eb8, protocol=0x9f532f4, buffer=0xbfa33e74) at item.cc:4770
#26 0x080bc065 in select_send::send_data (this=0x9fa1378, items=@0x9f9bd7c) at sql_class.cc:1002
#27 0x0812a75f in end_send (join=0x9f9b010, join_tab=0x9fcc188, end_of_records=false) at sql_select.cc:11249
#28 0x08129177 in evaluate_join_record (join=0x9f9b010, join_tab=0x9fcc010, error=167142400, report_error=0x0) at sql_select.cc:10542
#29 0x08128f4c in sub_select (join=0x9f9b010, join_tab=0x9fcc010, end_of_records=false) at sql_select.cc:10425
#30 0x08128aa6 in do_select (join=0x9f9b010, fields=0x9f9bd7c, table=0x0, procedure=0x0) at sql_select.cc:10188
#31 0x08118edb in JOIN::exec (this=0x9f9b010) at sql_select.cc:1995
#32 0x081193ef in mysql_select (thd=0x9f53000, rref_pointer_array=0x9f53fac, tables=0x9fa1078, wild_num=0, fields=@0x9f53f1c, conds=0x0, og_num=1, order=0x0,
    group=0x9fa12f0, having=0x0, proc_param=0x0, select_options=2156153344, result=0x9fa1378, unit=0x9f53c50, select_lex=0x9f53e88) at sql_select.cc:2162
#33 0x081147e7 in handle_select (thd=0x9f53000, lex=0x9f53bec, result=0x9fa1378, setup_tables_done_option=0) at sql_select.cc:243
#34 0x080df750 in mysql_execute_command (thd=0x9f53000) at sql_parse.cc:2657
#35 0x080e67db in mysql_parse (thd=0x9f53000, inBuf=0x9fa0010 "select count(*) c, a, ( select group_concat(count(a)) from t2 where m = a ) from t1 group by a",
    length=167066604) at sql_parse.cc:5928
#36 0x080ddca5 in dispatch_command (command=COM_QUERY, thd=0x9f53000, packet=0x9f92001 "", packet_length=95) at sql_parse.cc:1795
#37 0x080dd5eb in do_command (thd=0x9f53000) at sql_parse.cc:1577
#38 0x080dc8a3 in handle_one_connection (arg=0x0) at sql_parse.cc:1191
#39 0x28489902 in pthread_mutexattr_init () from /usr/lib/libpthread.so.1
#40 0x00000000 in ?? ()

sorry wrong copy and paste. version 5.x only

Thank you for the bug report.

[New Thread -1257030752 (LWP 4873)]
070316 20:12:10 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.38-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1257231456 (LWP 4879)]
mysqld: item_sum.cc:2934: int dump_leaf_key(byte*, element_count, Item_func_group_concat*): Assertion `offset < table->s->reclength' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread -1257231456 (LWP 4879)]
0xffffe410 in __kernel_vsyscall ()
(gdb) bt full
#0  0xffffe410 in __kernel_vsyscall ()
No symbol table info available.
#1  0xb7d7d770 in raise () from /lib/tls/i686/cmov/libc.so.6
No symbol table info available.
#2  0xb7d7eef3 in abort () from /lib/tls/i686/cmov/libc.so.6
No symbol table info available.
#3  0xb7d76dbb in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
No symbol table info available.

The Windows version doesn't crash on my side:

mysql> #Crash
mysql> select count(*) c, a,
    ->  ( select group_concat(count(a)) from t2 where m = a )
    ->  from t1 group by a; # crash
+---+------+-------------------------------------------------------+
| c | a    | ( select group_concat(count(a)) from t2 where m = a ) |
+---+------+-------------------------------------------------------+
| 2 |    2 | -7531142204535799277                                  |
| 3 |    3 | -7531142204535799277                                  |
| 1 |    4 | -7531142204535799277,-7531142204535799277             |
+---+------+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.38    |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

c:\build\5.0>

Please see bug: http://bugs.mysql.com/bug.php?id=27257.

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

ChangeSet@1.2489, 2007-03-21 21:31:39-07:00, igor@olga.mysql.com +9 -0
  Fixed bug #27229: crash when a set function aggregated in outer
  context was used as an argument of GROUP_CONCAT.
  Ensured correct setting of the depended_from field in references
  generated for set functions aggregated in outer selects.
  A wrong value of this field resulted in wrong maps returned by 
  used_tables() for these references.
  Made sure that a temporary table field is added for any set function
  aggregated in outer context when creation of a temporary table is 
  needed to execute the inner subquery.

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

ChangeSet@1.2489, 2007-03-22 08:55:50-07:00, igor@olga.mysql.com +9 -0
  Fixed bug #27229: crash when a set function aggregated in outer
  context was used as an argument of GROUP_CONCAT.
  Ensured correct setting of the depended_from field in references
  generated for set functions aggregated in outer selects.
  A wrong value of this field resulted in wrong maps returned by 
  used_tables() for these references.
  Made sure that a temporary table field is added for any set function
  aggregated in outer context when creation of a temporary table is 
  needed to execute the inner subquery.

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

ChangeSet@1.2489, 2007-03-22 14:48:03-07:00, igor@olga.mysql.com +9 -0
  Fixed bug #27229: crash when a set function aggregated in outer
  context was used as an argument of GROUP_CONCAT.
  Ensured correct setting of the depended_from field in references
  generated for set functions aggregated in outer selects.
  A wrong value of this field resulted in wrong maps returned by 
  used_tables() for these references.
  Made sure that a temporary table field is added for any set function
  aggregated in outer context when creation of a temporary table is 
  needed to execute the inner subquery.

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

ChangeSet@1.2417, 2007-03-25 23:44:06-07:00, igor@olga.mysql.com +2 -0
  This is a fix for the memory corruption occurred in one of test cases
  from func_group.test after the patch for bug #27229 had been applied.
  The memory corruption happened because in some rare cases the function
  count_field_types underestimated the number of elements in
  in the array param->items_to_copy.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Noted in 5.0.40, 5.1.18 changelogs.

Use of an aggregate function from an outer context as an argument to
GROUP_CONCAT() caused a server crash.