linux-traipu team mailing list archive

[Kinaan Khan Sherwani <mr.sherwani@xxxxxxxxx>]

** Branch linked: lp:~mr-sherwani/drizzle/trunk-bug626893

** Changed in: drizzle
       Status: Confirmed => Fix Committed

** Changed in: drizzle/7.0
       Status: Confirmed => Fix Committed

-- 
You received this bug notification because you are a member of UBUNTU -
AL - BR, which is subscribed to Drizzle.
https://bugs.launchpad.net/bugs/626893

Title:
  CREATE TABLE IF NOT EXISTS reports ERROR vs. WARNING if CREATE
  statement involves SELECT from target table

Status in A Lightweight SQL Database for Cloud Infrastructure and Web Applications:
  Fix Committed
Status in Drizzle 7.0 series:
  Fix Committed

Bug description:
  To clarify that headline:
  DROP TABLE t1;
  CREATE TABLE t1 (id INT NOT NULL);
  INSERT INTO t1 VALUES (1),(1);
  # The following should NOT produce an error because it has
  # an IF NOT EXISTS clause. 
  CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t1;
  # We get this error:
  # ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
  DROP TABLE t1;

  From the MySQL manual:
  http://dev.mysql.com/doc/refman/5.1/en/create-table.html
  For CREATE TABLE ... SELECT, if IF NOT EXISTS is given and the table already exists, MySQL handles the statement as follows:

  The table definition given in the CREATE TABLE part is ignored. No
  error occurs, even if the definition does not match that of the
  existing table.

  I would expect a warning here.  While it is good that things fail, the
  generated error might be confusing.  I am by no means advocating we
  adopt the rest of MySQL's behavior of 'forcing' the failed
  CREATE...SELECT data into the existing table.

  The behavior appears correct for an UPDATE statement:
  http://dev.mysql.com/doc/refman/5.1/en/update.html
  "Currently, you cannot update a table and select from the same table in a subquery."

  However, it seems as if the "IF NOT EXISTS" clause says we know it is possible that our query will fail and we'll see a warning rather than a straight-up error, like we see here:
  create table if not exists t1 (a int);

  Query OK, 0 rows affected, 1 warning (0 sec)

  drizzle> SHOW WARNINGS;
  +-------+------+---------------------------+
  | Level | Code | Message                   |
  +-------+------+---------------------------+
  | Note  | 1050 | Table 't1' already exists | 
  +-------+------+---------------------------+
  1 row in set (0 sec)

To manage notifications about this bug go to:
https://bugs.launchpad.net/drizzle/+bug/626893/+subscriptions