MySQL Bugs: #14387: SHOW COLUMNS doesn't work on temporary tables.

Bug #14387	SHOW COLUMNS doesn't work on temporary tables.
Submitted:	27 Oct 2005 11:46	Modified:	21 Dec 2005 17:59
Reporter:	Sergey Gondrya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16-BK, 5.0.15-standard	OS:	Linux (Linux)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
SHOW COLUMNS FROM temporary_table; 
does not work on temporary tables.

How to repeat:
mysql> CREATE TEMPORARY TABLE temporary_table SELECT 1 AS col1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM temporary_table;
+------+
| col1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM temporary_table;

Actual result:
ERROR 1146 (42S02): Table 'test.temporary_table' doesn't exist

Expected result:
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| col1  | bigint(1) |      |     | 0       |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

Thank you for a bug report. I was abple to repeat the problem you described also on newer 5.0.16-BK build (ChangeSet@1.1932.338.1, 2005-10-25 19:04:31+02:00, pgalbraith@mysql.com):

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> create temporary table temp_tab as select 1 as c1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from temp_tab;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> desc temp_tab;
ERROR 1146 (42S02): Table 'test.temp_tab' doesn't exist
mysql> show columns from temp_tab;
ERROR 1146 (42S02): Table 'test.temp_tab' doesn't exist
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16    |
+-----------+
1 row in set (0.02 sec)

It is most likely a bug, or, if this statement in intentionally not working with temporary tables, it should be clearly described in the manual (http://dev.mysql.com/doc/refman/5.0/en/show-columns.html)

Since the SHOW FULL COLUMNS command is supported with version 4.1.10, I'm waiting for this fix to complete a major porting project from Oracle to MySQL 5. I have opened Bug # 14615 on the same subject.

http://lists.mysql.com/internals/32821

Fixed in 5.0.18

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.18 changelog. Closed.