Bug #48800 CREATE TABLE t...SELECT fails if t is a temporary table

• Submitted: 16 Nov 2009 11:30
• Modified: 17 Jun 2010 1:09
• Reporter: Sven Sandberg
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.1
• OS: Any
• Assigned to: Magne Mæhre
• CPU Architecture: Any

[16 Nov 2009 11:30] Sven Sandberg

Description:
If t exists and is a temporary table, then 'CREATE TABLE t ... SELECT' fails with the error message "Table 't4' already exists". It would be better if the statement succeeded: this is how it works in all similar cases (e.g., create table without select, create view, or if the base table is created first and the temporary table later).

How to repeat:
CREATE TEMPORARY TABLE t1 (a INT);
CREATE TABLE t1 (a INT);            # OK

CREATE TEMPORARY TABLE t2 (a INT);
CREATE VIEW t2 AS SELECT 1;         # OK

CREATE TABLE t3 (a INT);
CREATE TEMPORARY TABLE t3 SELECT 1; # OK

CREATE TEMPORARY TABLE t4 (a INT);
CREATE TABLE t4 AS SELECT 1;        # FAILS!!!

[16 Nov 2009 12:07] MySQL Verification Team

Thank you for the bug report. Verified as described.

[4 May 2010 20:50] Konstantin Osipov

This is not present in 5.5.
Since it's a "checked" bug, Magne, please add a test case to trunk-runtime and move the bug to documenting.

[5 May 2010 10:17] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107460

3012 Magne Mahre	2010-05-05
      Bug#48800 CREATE TABLE t...SELECT fails if t is a temporary
                table
      
      If a temporary table A exists, and a (permanent) table 
      with the same name is attempted created with 
      "CREATE TABLE ... AS SELECT", the create would fail with 
      an error.
         1050: Table 'A' already exists
      
      The error occured in MySQL 5.1 releases, but is not
      present in MySQL 5.5.   This patch adds a regression
      test to ensure that the problem does not reoccur.

[5 May 2010 14:08] Paul Dubois

Test case only. No changelog entry needed.

[15 Jun 2010 8:14] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:30] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 1:09] Paul Dubois

No changelog entry needed.