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

[13 Jul 2013 11:33] Shlomi Noach
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:
?
[13 Jul 2013 16:23] Valeriy Kravchuk
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
[13 Jul 2013 16:59] Shane Bester
we'd really need to see table structure for test_eval_aid :)
[13 Jul 2013 17:21] Shlomi Noach
@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
[13 Jul 2013 17:27] Shlomi Noach
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.
[15 Jul 2013 5:39] Umesh Shastry
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