What would you expect the following to fail with?
CREATE TABLE t1 (a int, b int);
insert into t1 values (100,100);
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
BEGIN;
INSERT INTO t2 values(100,100);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
If you answered ER_DUP_ENTRY then you are correct.
Note
If you use IF NOT EXISTS in a CREATE TABLE ... SELECT statement, any rows selected by the SELECT part are inserted regardless of whether the table already exists.
Does anybody else find this behaviour “interesting”?
I think “disturbing” is a better word.
Wow. Weird one!
I think it makes sense. It’s a two phase procedure and you just told the server to ignore errors in phase 1, nothing else.
I needed to read over the sentence twice (who knows, perhaps it was me who write it in the first place, long ago ;-) but I think it makes sense.
Now, usually you’d utilise the IF NOT EXISTS with CREATE TABLE statement for non-TEMPORARY tables. You want to create a table if it doesn’t yet exist, the ‘if not exists’ merely makes sure there’s no error if it exists already. Simple enough.
Then, you do stuff with the table (which either pre-existed or was just created).
So a CREATE TABLE IF NOT EXISTS … SELECT … will do exactly that, as described.
I think the perceived weirdness if merely a result of the interaction between the (in themselves sane) operation of temporary tables, and the if not exists construct.
More to the question: is it fixed in Drizzle? ;)
Here is another oddity.
CREATE TABLE t1(a int, b varchar(10));
INSERT INTO t1 VALUES (1, ‘test’);
UPDATE t1 SET a = 2 AND b = ‘bla’ WHERE a = 1;
What would you expect the result to be?
As for “fixed in Drizzle”… I accidently broke it (found out by a test case of all things).
So we haven’t changed it… yet…
But once we have the new protocol that can do batching, there is *no* reason for this to exist. The correct way will be to send two statements at once, the create table if not exists and then the insert.
@Joe… that’s not an oddity, it’s simply evaluating the expression like this:
UPDATE t1 SET a = (2 AND b = ‘bla’) WHERE a = 1
Which may not be what you intended, but it is what you wrote… that’s what programs do, follow instructions.
I do not think you have learned an SQL oddity, but a MySQL oddity ;-)
@Arjen…It is an oddity if you don’t realize how it is being evaluated. Just like the example provided in the post, if you don’t understand what is going on it seems odd, and I was just pointing it out for others :)