MySQL Bugs: #30191: Partitions: Unexpected effect of IGNORE

Bug #30191	Partitions: Unexpected effect of IGNORE
Submitted:	1 Aug 2007 23:40	Modified:	29 Oct 2007 13:11
Reporter:	Peter Gulutzan
Status:	Closed
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	5.1.21-beta-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Jon Stephens	Target Version:
Tags:	partitioning, INSERT IGNORE
Triage:	D4 (Minor)

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)

Thank you for the bug report. Verified as described.

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)

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.

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.

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".

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.

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.

INSERT IGNORE should not ignore a partition error.

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.

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.

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.

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.

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.

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.

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.

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.