3

In MySql 5.6 I have a query similar to the following:

INSERT IGNORE INTO TABLE_1 (field_a,field_b)
SELECT field_a,field_b
FROM TABLE_2
WHERE
...
ORDER BY field_a
LIMIT 0,10

TABLE_1 is a temporary table that is used to store some tuples and then emptied. It doesn't have a PRIMARY KEY.

Since the process fills the table in multiple times, it may happen that it may contain tuples found throught the SELECT. Considering this, I thought about increasing the limit, so that I would be able to insert all the needed data.

Here is an example of the content of TABLE_1 and of the result of the SELECT query.

TABLE_1:

+---------+---------+
| field_a | field_b |
+---------+---------+
| foo     | 1       |
| foo     | 2       |
| foo     | 3       |
| foo     | 4       |
| bar     | 2       |
| bar     | 3       |
| bar     | 4       |
| bar     | 5       |
+---------+---------+

SELECT result (ignoring LIMIT):

+---------+---------+
| field_a | field_b |
+---------+---------+
| foo     | 4       |
| foo     | 5       |
| foo     | 6       |
| foo     | 7       |
| foo     | 8       |
| foo     | 9       |
| foo     | 10      |
| foo     | 11      |
| foo     | 12      |
| foo     | 13      |
| bar     | 5       |
| bar     | 6       |
| bar     | 7       |
| bar     | 8       |
+---------+---------+

So, considering that there are two duplicate tuples, (foo,4) and (bar,5), I expected 8 elements to be inserted... while, I actually found that the query inserted 10 elements, ignoring the two duplicates.

The point is that I can't find the reason of this behavior in the docs. I found here that LIMIT works in INSERT...SELECT only if there is an ORDER BY, which I have. But I was not able to find a precise description of this behavior, having together INSERT IGNORE, INSERT...SELECT, ORDER BY and LIMIT.

Can somebody help me to explain this unexpected behavior?

clami219
  • 2,958
  • 1
  • 31
  • 45
  • Please edit your question to include the `create table` statement. I don't believe that (`field_a`, `field_b`) are really the primary key on the table. – Gordon Linoff May 22 '14 at 11:17
  • TABLE_1 is just an example table, to explain my situation. But, even though I was pretty sure they are primary keys, you are right, they are not. I'm editing the question immediately! – clami219 May 22 '14 at 11:28
  • 1
    @clami219 With your data and a combined primary key on `(field_a, field_b)` for `table_1` I get `8 row(s) affected Records: 10 Duplicates: 2 Warnings: 0` – VMai May 22 '14 at 11:32
  • @VMai As pointed out by Gordon Linoff, I was actually wrong on the primary keys... sorry about that! – clami219 May 22 '14 at 11:36

1 Answers1

0

Manual for INSERT IGNORE:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored.

It is likely that there is no unique constraint on (field_a, field_b) on TABLE_1. No "duplicate" error can occur without such a constraint. TABLE_2's primary key (which is a special case of unique constraint) has no effect in your query.

Also, LIMIT n without ORDER BY "works", it just returns the first n random rows that the query happens to generate.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87