MySQL Bugs: #69737: "INSERT INTO ... SELECT ... I_S ... ORDER BY" fails on "ERROR 1028"

Bug #69737	"INSERT INTO ... SELECT ... I_S ... ORDER BY" fails on "ERROR 1028"
Submitted:	13 Jul 2013 11:33	Modified:	15 Jul 2013 5:39
Reporter:	Shlomi Noach (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, insert-select, query

Description:
A particular query on INFORMATION_SCHEMA.COLUMNS fails on MySQL 5.6, that does not fail on 5.1 nor on 5.5:

How to repeat:
use test;

drop table if exists some_table;
drop table if exists t;

CREATE TABLE `some_table` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

CREATE TABLE `t` (
  `query` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

INSERT INTO test.t SELECT column_name AS st FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test_cs' AND TABLE_NAME='test_eval_aid' ORDER BY ORDINAL_POSITION;

> ERROR 1028 (HY000): Sort aborted: 

Suggested fix:
?

Works for me with 5.6.12 on Windows:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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 some_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>
mysql> CREATE TABLE `some_table` (
    ->   `col1` int(11) DEFAULT NULL,
    ->   `col2` int(11) DEFAULT NULL,
    ->   `col3` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.63 sec)

mysql>
mysql> CREATE TABLE `t` (
    ->   `query` text
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.36 sec)

mysql>
mysql> INSERT INTO test.t SELECT column_name AS st FROM INFORMATION_SCHEMA.COLUM
NS WHERE TABLE_SCHEMA='test_cs' AND TABLE_NAME='test_eval_aid' ORDER BY ORDINAL_
POSITION;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

we'd really need to see table structure for test_eval_aid :)

@Shane,
apologies, should of course be:

INSERT INTO test.t SELECT column_name AS st FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t' ORDER BY ORDINAL_POSITION;

Am testing on Linux x86_64

Have now verified this does not happen with 5.6.12; was using older, 5.6.10 version.
Thank you and keep up the good work.

Hello Shlomi,

Confirmed on local builds(5.6/5.5) too, no longer repeatable with latest GA. I am closing this bug now - please reopen, if needed.

Thanks,
Umesh