MySQL Bugs: #71492: Expected ERROR 1104, none given with INSERT IGNORE

Bug #71492	Expected ERROR 1104, none given with INSERT IGNORE
Submitted:	27 Jan 2014 11:11	Modified:	28 Jan 2014 15:12
Reporter:	Dotan Cohen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.33	OS:	Linux (Linux ab 3.2.0-58-virtual #88-Ubuntu SMP Tue Dec 3 17:58:13 UTC 2013 x86_64 x86_64 x86_64 GNU/Linuxd)
Assigned to:		CPU Architecture:	Any

Description:
Despite the 'IGNORE', I would expect to be informed that the following query could not be performed due to using SAFE-UPDATES:

mysql> INSERT IGNORE INTO someTable SELECT foo, bar FROM oldTable;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_BIG_SELECTS=1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO someTable SELECT foo, bar FROM oldTable;
Query OK, 4648305 rows affected (2 min 8.42 sec)
Records: 9201328  Duplicates: 4553023  Warnings: 0

This is the error message that I would expect:
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

The 'IGNORE' is to continue processing _when_an_insert_fails_, not to suppress messages that the query cannot be run at all.

How to repeat:
1) Start MySQL with -U
2) Use INSERT IGNORE INTO to copy more than 1000 rows from one table to another.

Suggested fix:
Output the correct error message.

Sorry, but this is not a bug.

Keyword IGNORE serves one purpose only. It is there to instruct server to ignore any unique key violations, be it primary or unique key. It is not there to check and abort for any other problems, including MAX_JOIN_SIZE overflows.

Thank you for your interest.

@Sinisa: I agree with you completely. I think that you misread my comment. I do expect MySQL to return the error, but it does not!

Please reopen.

Dotan,

You are correct !!!

However, please check whether you have any of the strict modes enabled. Those are some that are by default in 5.6, but not in 5.5. In any case, check sql_mode, because, as our manual clearly states:

"
   With either of the strict mode options, you can cause errors to be
   treated as warnings by using INSERT IGNORE or UPDATE IGNORE rather
   than INSERT or UPDATE without IGNORE.
"

mysql> select @sql_mode;
+-----------+
| @sql_mode |
+-----------+
| NULL      |
+-----------+
1 row in set (0.00 se

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

I have quoted the wrong paragraph. I have now used, instead, the latest version of 5.5 manual and it clearly shows that all errors that occur with INSERT command are ignored when this keyword is used. Quotation:
"
•	If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example,  .... 
"
Then, several examples are provided, but these are just examples. What is important is that with IGNORE, all errors are turned into warnings.

Thank you, Sinisa. Note that neither an error nor a warning are provided.