I apologize if this has been discussed or reported as a bug (I searched a bit but couldn't find mention of it).
When you create a table using the CREATE TABLE IF NOT EXISTS...SELECT syntax, what is the correct behavior if the table already exists? I had thought that no action at all would be taken. It turns out that MySQL adds the rows in the SELECT statement to the table. This seems to be a bug, but I think it could be argued that this is the correct behavior (though I'm not sure I'd agree). I've tested in both 4.0.17 and 4.1 under Windows and get the same result: mysql> create temporary table test select 1; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> create temporary table if not exists test select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test; +---+ | 1 | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) The way MySQL currently functions is equivalent to two separate statements, a CREATE TABLE and a SELECT INTO where "IF NOT EXISTS" applies only to the CREATE TABLE portion and the SELECT INTO happens regardless. If this is not a bug and not a standard compliance thing, I'd argue that this behavior is not a good choice since you can easily get this behavior simply by issuing the CREATE TABLE IF NOT EXISTS and SELECT INTO statements separately. If your want to avoid adding additional rows to the table if the table already exists (for example, if you initialize the table with some rows but can't be certain if this initialization has already occurred), then I believe it is difficult to create this behavior under the current implementation without external logic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
