An SQL Puzzle?

jaypipes 13 Comments

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;
  • Maan

    a|b
    100|100

    • http://jpipes.com Jay Pipes

      And why? :)

      • Jay R.

        Table modification statements automatically commit transactions, so the insert is committed before the temporary table is created.

        Right?

  • http://www.thenoyes.com/littlenoise Scott Noyes

    A duplicate key error will roll back the statement. Is the puzzle why it doesn’t roll back the whole transaction, or why it even bothers to do the select given that the table already exists?

  • http://althack.org Darius Jahandarie

    This can be prevented in drizzle with protos and atomic DDL statements, right? ;-)

    • http://jpipes.com Jay Pipes

      LOL. Not yet! :)

  • Rob Wultsch

    Note
    CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.
    http://dev.mysql.com/doc/refman/5.1/en/create-table.html

    Implicit commit is evil and stupid. Ideally we the server should error and roll back, imho.

  • Øystein Grøvlen

    Since you are asking about what the behavior _should_ be, I would say that it should _not_ produce the error that you think are correct. In my opinion, if the table already exists, the select statement should not be executed. The existing MySQL behavior makes things pretty messy, especially when views are involved (see MySQL bugs 47132 and 49494).

    Given that, the select statement should give one row: (100, 100).

    • http://rpbouman.blogspot.com/ Roland Bouman

      Completely agree about the CREATE TABLE…SELECT statement, I also agree about the outcome: one row: (100,100)

      The fact that a statement is refused due to a constraint violation, does not mean the transaction should be rolled back. It should be up to the user to decide what to do with whatever happened before the violation. However, the statement that caused the violation should be refused wholly – not like MyISAM, where the rows up to the violation succeed, and the rest fails.

  • saurabh deshpande

    well the result is

    100,100
    1,1

    even with the autocommit on or off the result is same.

    • Øystein Grøvlen

      Looks like you are using MyISAM, not InnoDb.

  • Pingback: PHP-help » Log Buffer #182, a Carnival of the Vanities for DBAs()

  • Pingback: Log Buffer #182, A Carnival of the Vanities for DBAs()