Bug #70881 Insert into select produces error(or just warnings)

• Submitted: 11 Nov 2013 15:40
• Modified: 11 Nov 2013 16:23
• Reporter: Marco Niese
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.5.25; 5.6
• OS: Windows (7 SP1)
• CPU Architecture: Any
• Tags: error, insert, null, warning

[11 Nov 2013 15:40] Marco Niese

Description:
Tested with MySql Workbench 6.0.7.11215; MySql QueryBrowser 1.2.17 (and HeidiSql 8.0)

Insert into select produces error(or just warnings) when trying to insert values from one table(tmp1) into another table(tmp2). Table "tmp2" has a primary key constraint on one column(here "letters").

If table "tmp1", column "letters" contains a null value in the first row, the insert produces an error as expected as it is not allowed to insert null values into a primary key.
If you do the insert after sorting "tmp1" such that the null value is not in the first row, you only get a warning "Column cannot be null".

See sql below: 

How to repeat:
DROP TABLE TMP2;
DROP TABLE TMP1;

-- Letzte Reinigung
CREATE TABLE TMP1 (
  letters CHAR(1),
  numbers INTEGER  
) ENGINE = Memory;

INSERT INTO TMP1 (letters, numbers) VALUES (null,1),('A',2);

CREATE TABLE TMP2 (
  letters CHAR(1),
  numbers INTEGER,
  PRIMARY KEY (letters)
) ENGINE = Memory;

-- ERROR HERE
INSERT INTO TMP2 (letters, numbers)
SELECT
  t.letters,
  t.numbers
FROM
  TMP1 as t
ORDER BY t.numbers ASC;

-- ONLY WARNINGS HERE
INSERT INTO TMP2 (letters, numbers)
SELECT
  t.letters,
  t.numbers
FROM
  TMP1 as t
ORDER BY t.numbers DESC;

Suggested fix:
Show an error in each case.

[11 Nov 2013 16:23] Todd Farmer

Thank you for taking the time to write to us, but this is not a bug.  Please see the comments regarding multi-row INSERT statements here:

http://dev.mysql.com/doc/refman/5.6/en/insert.html

And the notes regarding the impact of various SQL Modes on multi-row INSERT statements here:

http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_strict_trans_tables

This is not specific to INSERT ... SELECT statements; it is true with any mutli-row INSERT statement:

mysql> CREATE TABLE TMP2 (
    ->   letters CHAR(1),
    ->   numbers INTEGER,
    ->   PRIMARY KEY (letters)
    -> ) ENGINE = Memory;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO TMP2 (letters, numbers) VALUES (null,1),('A',2);
Query OK, 2 rows affected, 1 warning (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> TRUNCATE TMP2;
Query OK, 0 rows affected (0.03 sec)

mysql> SET SESSION sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO TMP2 (letters, numbers) VALUES (null,1),('A',2);
ERROR 1048 (23000): Column 'letters' cannot be null

Note that you can set the SQL Mode for a given connection in Workbench, under the "Advanced" tab for each connection.