MySQL Bugs: #25154: crash in alter table

Bug #25154	crash in alter table
Submitted:	18 Dec 2006 18:30	Modified:	20 Mar 2007 11:26
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.1.23-BK	OS:	Linux (Linux, freebsd)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	ALTER TABLE, bfsm_2006_12_21, crash, qc

Description:
see the statement in how to repeat.

The sum only crashes 4.1, IF us "use db_name" was issued immediatly before

The subquery crashes all servers;

It doesnt matter tht the table for the subquery does not exist
 (nor if it does exist)

How to repeat:
drop table if exists t1;
create table t1 (a int);
insert into t1 values (1), (2);

alter table t1 order by sum(a);
# ERROR 1111 (HY000): Invalid use of group function

alter table t1 order by (select a from tssdw);
# ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
-

Thank you for a bug report. Verified just as described with latest 5.0.32-BK on Linux:

openxs@suse:~/dbs/5.0> 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.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int);
iQuery OK, 0 rows affected (0.01 sec)

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

mysql> alter table t1 order by sum(a);
ERROR 1111 (HY000): Invalid use of group function
mysql> alter table t1 order by (select a from tssdw);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
061218 20:24:34  mysqld restarted

Resolved stack trace:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump  -s /tmp/mysqld50.sym -n 25154.stack
0x81d93f0 handle_segfault + 412
0x8220df5 _Z12setup_tablesP3THDP23Name_resolution_contextP4ListI13st_table_listE
PS4_PP4ItemPS7_b + 227
0x8221167 _Z29setup_tables_and_check_accessP3THDP23Name_resolution_contextP4List
I13st_table_listEPS4_PP4ItemPS7_bmm + 59
0x822ac03 _ZN4JOIN7prepareEPPP4ItemP13st_table_listjS1_jP8st_orderS7_S1_S7_P13st
_select_lexP18st_select_lex_unit + 405
0x81a8017 _ZN30subselect_single_select_engine7prepareEv + 357
0x81a4489 _ZN14Item_subselect10fix_fieldsEP3THDPP4Item + 145
0x824783d _Z18find_order_in_listP3THDPP4ItemP13st_table_listP8st_orderR4ListIS1_
ESA_b + 795
0x824792b _Z11setup_orderP3THDPP4ItemP13st_table_listR4ListIS1_ES8_P8st_order +
55
0x82e8522 _Z24copy_data_between_tablesP8st_tableS0_R4ListI12create_fieldEbjP8st_
orderPmS7_22enum_enable_or_disable + 1260
0x82e7712 _Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP13st_table_
listR4ListI12create_fieldERS6_I3KeyEjP8st_orderbP13st_alt + 8092
0x81f2a40 _Z21mysql_execute_commandP3THD + 6120
0x81f91bb _Z11mysql_parseP3THDPcj + 475
0x81efb19 _Z16dispatch_command19enum_server_commandP3THDPcj + 1951
0x81ef36a _Z10do_commandP3THD + 526
0x81ee556 handle_one_connection + 982
0x40050aa7 _end + 932066215
0x40247c2e _end + 934126894

Sorry, latest version is 5.0.34-BK.

Probably a duplicate.  Bug#24562?

At least for 5.x it will work with the same fix as in Bug#24562. If the parser does no longer allow a sub-select, then 5.x is fixed.

Bug#24562 does not address 4.1, If the issue is critical enough to be relevant, the fix should be down-ported.

Also if 4.1 is still relevant:
The crash with "order by sum" immediately after a "use database", may be different. It would be fixed by down porting the above fix.
But it reminds me of Bug #13180 where some thread variables were not properly initialised. I don't know if this is worth looking at, since it would probably no longer be exploitable with the fix from above.

If it is worth looking at 4.1, make this a 4.1 bug, only for the "order by sum()" part.

Otherwise please close as a duplicate.

This bug still affects 4.1.23-BK:

openxs@suse:~/dbs/4.1> 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 to server version: 4.1.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)

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

mysql> alter table t1 order by sum(a);
ERROR 1111 (HY000): Invalid use of group function
mysql> alter table t1 order by sum(select a from t1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selec
t a from t1)' at line 1
mysql> alter table t1 order by sum(select a from mysql.user);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selec
t a from mysql.user)' at line 1
mysql> alter table t1 order by sum(select a from tssdw);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selec
t a from tssdw)' at line 1
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table t1 order by sum(a);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
070220 10:51:28  mysqld restarted

The remaining part of this bug (USE db; ALTER ... ORDER BY sum();) affects 4.1 only.  I'm adjusting the version information, and leaving this in Verified state for now.

This is a duplicate of BUG#24562 (ALTER TABLE ... ORDER BY ...
with complex expression asserts), which is already fixed by
Marc Alff.