All, Drizzle, MySQL

An SQL Puzzle?

  • 2010-03-08
  • jaypipes
  • 13 responses.

Dear Lazy Web,

What should the result of the SELECT be below? Assume InnoDB for all storage engines.

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(1,2);
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;
 
# The above statement will correctly produce an ERROR 23000: Duplicate entry '1' FOR KEY 'PRIMARY'
# What should the below RESULT be?
 
SELECT * FROM t2;
COMMIT;