MySQL Bugs: #64436: No results for creation/insertion of a select based on the information schema

Bug #64436	No results for creation/insertion of a select based on the information schema
Submitted:	23 Feb 2012 17:00	Modified:	23 Feb 2012 19:52
Reporter:	Andrew Tan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.5.14-55	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
1. An insert or a create statement based on a select query of an information_schema table gets run.
CREATE TABLE _db_schemas SELECT TABLE_SCHEMA AS `db_schema_ln` FROM information_schema.COLUMNS GROUP BY TABLE_SCHEMA;

2. I expected to create a table with the unique schemas populated within the table.

3. No table is created and no errors are returned.  

How to repeat:
1. Attempt to create the following table:
CREATE TABLE _db_schemas SELECT TABLE_SCHEMA AS `db_schema_ln` FROM information_schema.COLUMNS GROUP BY TABLE_SCHEMA;
2. Observe lack of table and errors

Issue does not occur on a different MySQL database version 5.1.58.  I've only been able to consistently reproduce the issue on an instance of version 5.5.14-55.

The following statement works on both versions.  However, I'm still not sure why the initial create statement would fail since both of the queries run as select statements work just fine.  

CREATE TABLE _db_schemas SELECT SCHEMA_NAME AS `db_schema_ln` FROM information_schema.SCHEMATA;

Please, check if this problem ever happens with a recent version, 5.5.21. I can not repeat this:

macbook-pro:5.5 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 6
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, 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> CREATE TABLE _db_schemas SELECT TABLE_SCHEMA AS `db_schema_ln` FROM
    -> information_schema.COLUMNS GROUP BY TABLE_SCHEMA;
Query OK, 5 rows affected (1.24 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from _db_schemas;
+--------------------+
| db_schema_ln       |
+--------------------+
| collation_test     |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

Created an instance of 5.5.2 and the issue does not persist to that version.  Closing ticket.