insert into table a select * from a does not work with innodb tables

[hamel]

I am testing out the innodb table types and I think that this statement should be able to work according to the transaction model for Innodb tables.


create table a (c1 char(10) type=innodb;
insert into a values ('aaa');
commit;
insert into a select * from a;

I get the following error:

ERROR 1066: Not unique table/alias: 'a'

[bcarl314]

Try this:

create table a (c1 char(10) type=innodb) ;
insert into a values ('aaa');
commit;
insert into a select * from a;

It looks like you missed a ")" at the end of your create table statement.

[hamel]

I did have a typo but it still does not work here is the full output:

create table a (c1 int) type=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values (1);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a select * from a;
ERROR 1066: Not unique table/alias: 'a'