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.