List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 8 1999 2:14am
Subject:Bug (maybe?): Create ... select ... syntax
View as plain text  
>>>>> "Richard" == Richard Ellerbrock <richarde@stripped> writes:

Richard> I want to create a new table from an existing table with a select as follows:
Richard> create temporary table ndsdup 
Richard>                             select substring_index(dn, ',',1) as cn
Richard>                             from nds
Richard>                             LEFT JOIN regnds
Richard>                               ON lower(nds.dn) REGEXP regnds.exclude
Richard>                             where
Richard>                               regnds.exclude IS NULL and
Richard>                               lower(dn) rlike '$name'
Richard>                             group by cn

Richard> You can see that cn is the field that is created in the new table. I want to
> override the definition of cn in the temp table as follows:


Richard> create temporary table ndsdup (cn varchar(40))
Richard>                             select substring_index(dn, ',',1) as cn
Richard>                             from nds
Richard>                             LEFT JOIN regnds
Richard>                               ON lower(nds.dn) REGEXP regnds.exclude
Richard>                             where
Richard>                               regnds.exclude IS NULL and
Richard>                               lower(dn) rlike '$name'
Richard>                             group by cn

Richard> This generates an error:

Richard> ERROR 1060: Duplicate column name 'cn'

Richard> According to the manual, this syntax is valid. A simpler example as follows
> also generates an error:

Richard> create temporary table ndsdup (dn varchar(100)) 
Richard>   select dn from nds where dn like 'cn=eller%';

Richard> If I remove the override, all works fine. The 'temporary' has no effect, a
> normal create has the same problem.

Richard> I am using 3.23.3 alpha with the sum()-groub by patches applied.

Hi!

The problem is that you can't change a field definition when you
create a temporary table, you can only add new columns and columns to
the result set.

The only way you can, for the moment, change the type is to use ALTER
TABLE after you have created the table.  You can even do:

create temporary table ndsdup select dn from nds limit 0
ALTER TABLE ndsdup modify dn varchar(100);
INSERT INTO ndsdup select dn from nds where dn like 'cn=eller%';

Not nice, but it should work.

Regards,
Monty
Thread
Bug (maybe?): Create ... select ... syntaxRichard Ellerbrock7 Oct
  • Bug (maybe?): Create ... select ... syntaxMichael Widenius8 Oct