Bug #30191 Partitions: Unexpected effect of IGNORE
Submitted: 1 Aug 2007 21:40 Modified: 29 Oct 2007 12:11
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.21-beta-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Jon Stephens CPU Architecture:Any
Tags: INSERT IGNORE, partitioning

[1 Aug 2007 21:40] Peter Gulutzan
Description:
I create a table with list partitioning.
There is no partition for value '2'.
I say INSERT IGNORE INTO table_name VALUES (2).
I get no warning for the partition error.

This means that IGNORE causes skipping of partition
errors, not just duplicate or invalid-data errors.

How to repeat:
mysql> create table t15 (s1 int) partition by list (s1) (partition p1 values in (1));
Query OK, 0 rows affected (0.06 sec)

mysql> insert ignore into t15 values (2);
Query OK, 0 rows affected (0.04 sec)
[1 Aug 2007 22:36] MySQL Verification Team
Thank you for the bug report. Verified as described.
[3 Aug 2007 17:51] Peter Gulutzan
To clarify: the INSERT has actually failed despite the 'OK' reply.
One can see that by selecting from the table.

mysql> create table t15 (s1 int) partition by list (s1) (partition p1 values in (1));
Query OK, 0 rows affected (0.06 sec)

mysql> insert ignore into t15 values (2);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t15;
Empty set (0.00 sec)
[8 Oct 2007 13:58] Jon Stephens
Following discussion with Mikael and Matthias, it was decided that this is not a bug in the server, but rather the documentation. At least in 5.1, this behaviour is the same as for duplicate key errors on INSERT, which IGNORE causes to fail silently.
[8 Oct 2007 19:26] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated INSERT and partitioning documentation.
[11 Oct 2007 18:10] Peter Gulutzan
I see that the manual is changed (thank you).
Now 17.2.2. LIST Partitioning has this example:

"
ysql> CREATE TABLE h2 (
    ->   c1 INT, 
    ->   c2 INT
    -> ) 
    -> PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.11 sec)
...
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5  Duplicates: 2  Warnings: 0
"

Duplicates: 2? What duplicates? Table h2 has no unique key.
The use of IGNORE still has an "unexpected effect".
Accordingly I have changed this bug from "Closed" back to "Verified".
[12 Oct 2007 14:55] Jon Stephens
I was informed by Mikael that skipping unmatched values by INSERT IGNORE is expected behaviour. What's in the documentation is an accurate depiction of what's shown in the mysql client.

The fact that these values are described as "duplicates" rather than "unmatched" (feel free to suggest a better adjective here) is IMO a different issue warranting a separate bug (Server:Partitioning, not Docs). Please file one.

When it's determined what should be used instead of "Duplicates:" in such cases, then I can amend the docs to show it.
[12 Oct 2007 15:19] Peter Gulutzan
From the previous comment by Jon:
"The fact that these values are described as "duplicates"
rather than "unmatched" (feel free to suggest a better adjective here)
is IMO a different issue warranting a separate
bug (Server:Partitioning, not Docs). Please file one."

From the previous comment by Peter:
"The use of IGNORE still has an "unexpected effect"."

Accordingly I have changed this bug from "Closed" back to "Verified".
But I have also changed the category back to 'Partitioning', not 'Docs'
(I apologize to Jon for not doing so immediately).
The unexpected effect is the message about 'Duplicates'.
The main point of the original complaint is solved, but if
it's not entirely solved, it shouldn't have to be a new bug.
[12 Oct 2007 15:47] Konstantin Osipov
INSERT IGNORE should not ignore a partition error.
[12 Oct 2007 16:43] Jon Stephens
Konstantin,

On the contrary, the behaviour is intentional and documented as such, and is not the subject of any debate.

The only misbehaviour here is is that the umatched partition key values should not  be reported as "duplicates" which they obviously are not.
[15 Oct 2007 23:34] Konstantin Osipov
We can not decide what is ignored by INSERT IGNORE on a case by case basis.
The fact that the current behavior is documented is not a satisfactory excuse.
[16 Oct 2007 12:12] Jon Stephens
Konstantin, 

I discussed this matter recently with Mikael, who advised me that the effect was "unexpected" because it had not been documented. I therefore documented it.

Please review the matter with him and CC: me on the discussion.

Thanks.
[16 Oct 2007 14:04] Konstantin Osipov
OK, I had a discussion with Mikael, support people and Monty about the expected behaviour of IGNORE clause.

The purpose of this clause is to insert as many records as possible, skipping records that violate various constraints (i.e. taking no action on those records).
The same principle applies to DELETE IGNORE, REPLACE IGNORE, UPDATE IGNORE.

However, this principle is not enforced consistently.
I.e. behavior of triggers, views with check options and InnoDB foreign keys is inconsistent with this principle and is not documented.

So there is at least a documentation issue with insufficient documentation and a potential server issue with inconsistent behavior of the server.

Therefore I am moving this bug to 'Documentation' and flag 'Additional QA needed'.
I shall provide highlights on the test plan required for this bug from QA in a separate bug comment.
[16 Oct 2007 14:21] Konstantin Osipov
It is necessary to test: 
- foreign key failures should cause the failed record to be skipped. Statement execution should continue. Right now it is not the case:

mysql> set @@storage_engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql>  create table t1 (a int primary key) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2 (a int primary key, b int, foreign key (b)
    -> references t1 (a) on delete restrict);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 (a) values (1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t2 (a, b) values (1,1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t2 (a, b) values (2,1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 (a) values (2);
Query OK, 1 row affected (0.09 sec)

mysql> insert into t2 (a, b) values (2,2);
ERROR 1582 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> insert into t2 (a, b) values (3,2);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t2 (a, b) values (4,2);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 (a) values (3);
Query OK, 1 row affected (0.10 sec)

mysql> delete ignore from t1;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> select * from t1;
+---+
| a |
+---+
| 1 | 
| 2 | 
| 3 | 
+---+
3 rows in set (0.00 sec)
-- no rows are deleted, the statement is aborted, but no error is produced (the error is converted to a warning

- if a record violates CHECK OPTION of a view, it should be skipped. 
- if an error happens in a BEFORE trigger, the record should be skipped.
In a transactional engine, effects of the trigger should be rolled back to the savepoint before start of the trigger. In a non-transactional engine execution should continue.
- If an error happens in AFTER trigger, the record and effects of BEFORE/AFTER triggers should be rolled back. In a non-transactional engine, execution should continue.
- if an error happens in a stored function, used to evaluate the value being inserted (for example: INSERT IGNORE INTO t1 (a) values (f1())), the record should be skipped. In a transactional engine, effects of the stored function should be rolled back. In a non-transactional engine, execution should continue.

A test is necessary for INSERT IGNORE, UPDATE IGNORE, DELETE IGNORE, REPLACE IGNORE, INSERT ... ON DUPLICATE KEY UPDATE, multi-DELTE statements, multi-UPDATE statements, the same statements applied to a view, in SQL modes: ansi, strict_all_tables, strict_trans_tables, error_for_division_by_zero and the default one.
[17 Oct 2007 17:22] Peter Gulutzan
Konstantin,

The requirement is an authoritative definition, and I don't
expect such things from the "architecture board".
I'll email possibly-interested parties with subject line
"Define IGNORE", real soon.
[18 Oct 2007 22:51] Jon Stephens
The issue here is not that the behaviour is wrong, but rather that IGNORE is not properly defined.

Therefore I've set this this to Docs/Analysing, until such time as we have a firm definition of IGNORE.
[29 Oct 2007 12:11] Jon Stephens
Further discussion of this issue is now taking place in WL #4103 - I'm therefore closing this bug. I'll update the Partitioning docs according to its outcome.