MySQL Bugs: #59679: Assertion failed: aggregator == aggr->Aggrtype()

Bug #59679	Assertion failed: aggregator == aggr->Aggrtype()
Submitted:	23 Jan 2011 16:24	Modified:	2 Feb 2011 9:42
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.10-debug	OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	regression

Description:
bug #52123 still crashes 5.5

Version: '5.5.8-debug'  socket: ''  port: 3306  MySQL Community Server - Debug (GPL)
Assertion failed: aggregator == aggr->Aggrtype(), file ..\..\mysql-5.5.8\sql\item_sum.cc, line 567

mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.c:521]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:163]
mysqld-debug.exe!Item_sum::set_aggregator()[item_sum.cc:567]
mysqld-debug.exe!prepare_sum_aggregators()[sql_select.cc:15809]
mysqld-debug.exe!JOIN::exec()[sql_select.cc:2238]
mysqld-debug.exe!mysql_select()[sql_select.cc:2572]
mysqld-debug.exe!handle_select()[sql_select.cc:297]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:4452]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2046]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5496]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1032]
mysqld-debug.exe!do_command()[sql_parse.cc:772]
mysqld-debug.exe!do_handle_one_connection()[sql_connect.cc:745]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:684]
mysqld-debug.exe!pthread_start()[my_winthread.c:61]
mysqld-debug.exe!_callthreadstartex()[threadex.c:348]
mysqld-debug.exe!_threadstartex()[threadex.c:331]

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` decimal(20,20),key(`a`)) engine=myisam;
insert into `t1` values (1),(1);
select 1 from `t1` order by avg(distinct `a`);

Another testcase for the testsuite;

drop table if exists g1;
create table `g1`(`a` int key)engine=myisam;
insert into `g1` values (1),(2),(3);
select 1 from `g1` where 1 <> 
 (select avg(distinct `a`)
 from `g1` order by 1)
;

Verified on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.10-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1`(`a` decimal(20,20),key(`a`)) engine=myisam;
Query OK, 0 rows affected (0.24 sec)

mysql> insert into `t1` values (1),(1);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select 1 from `t1` order by avg(distinct `a`);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 110123 18:57:40 mysqld_safe mysqld restarted

mysql> exit
Bye
macbook-pro:5.5 openxs$ tail -80 data/macbook-pro.err 
...
110123 18:57:18 [Note] /Users/openxs/dbs/5.5/bin/mysqld: ready for connections.
Version: '5.5.10-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (aggregator == aggr->Aggrtype()), function set_aggregator, file /Users/openxs/bzr2/mysql-5.5-work/sql/item_sum.cc, line 567.
...

MySQL 5.1 is NOT affected:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1`(`a` decimal(20,20),key(`a`)) engine=myisam;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into `t1` values (1),(1);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select 1 from `t1` order by avg(distinct `a`);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> drop table if exists g1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `g1`(`a` int key)engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into `g1` values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select 1 from `g1` where 1 <> 
    ->  (select avg(distinct `a`)
    ->  from `g1` order by 1)
    -> ;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.00 sec)

So, this is a regression bug.

Neither of Shane's queries crash for me on 5.5.8 (also not with SET NAMES latin1;  :-) ) on Windows 64 bit server.

PeterL: try mysqld-debug :)  Now i realize the second testcase is the same as first really.

@Shane .. I trust you! :-)

The patch for Bug#52123 also fixes this issue.  Hence, it should probably be backported to 5.5.

Tor has already fixed this in trunk.

Backported and pushed fix for
Bug #52123  Assertion failed: aggregator == aggr->Aggrtype()

Duplicate of
Bug #52123  	Assertion failed: aggregator == aggr->Aggrtype(),