Is it specified that INSERT IGNORE... SELECT is performed in order?

Question

The MariaDB INSERT IGNORE... SELECT syntax is defined at https://mariadb.com/kb/en/insert/.

I am also relying on the assumption that each INSERT in this situation will be performed in the order of the SELECT clause result set.

Test case:

DROP TEMPORARY TABLE IF EXISTS index_and_color;
CREATE TEMPORARY TABLE index_and_color (`index` INT PRIMARY KEY, color TEXT);
INSERT IGNORE INTO index_and_color SELECT 5, "Red" UNION ALL SELECT 5, "Blue";
SELECT * FROM index_and_color;

Intuitively, I see that the "first" row in the SELECT clause result set has (5, "Red") and then the "second" row is ignored, containing the same key with "Blue".

From what I see, this is undefined behavior because another server implementing the same documentation could handle the rows in a different order.

Is my usage here indeed relying on undefined behavior?

I have asked this same question regarding MySQL at https://stackoverflow.com/questions/56841179/is-the-order-of-inserts-specified-for-insert-ignore-select. And strictly speaking, it is undefined behavior there. — William Entriken, Oct 26 '20 at 15:15

score 1 · Answer 1 · answered Oct 26 '20 at 15:21

1

What is "undefined behavior" is the ordering of the rows returned by an UNION ALL query. Use an ORDER BY clause if you want a stable result:

INSERT IGNORE INTO index_and_color (`index`, color)
SELECT 5 `index`, 'Red' color UNION ALL SELECT 5, 'Blue' ORDER BY color;

answered Oct 26 '20 at 15:21

GMB

216,147
25
84
135

I understand that the result set can be ordered by color. But nowhere in the INSERT documentation does it say "the first result of the result set will be inserted first". – William Entriken Nov 05 '20 at 17:31
This means it would be perfectly acceptable for the implementation to do "a random row from the result set will be inserted, continuing until all results are inserted" – William Entriken Nov 05 '20 at 17:32

Georg Richter · Answer 2 · 2020-10-27T16:33:24.363

The SQL statement SELECT * FROM index_and_color should give you exactly for what you asked: all data rows from the given table.

If you expect that the resultset will be delivered in a specific order, then you need to add an ORDER BY clause, otherwise it might be ordered by index, or based on whichever algorithm the optimizer expects to produce the data the fastest.

For example::

CREATE TABLE t1 (a int, b int, index(a));
INSERT INTO t1 VALUES (2,1),(1,2);

/* Here we get the order from last insert statement */
SELECT a,b FROM t1;
MariaDB [test]> select a,b from t1;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    1 |    2 |
+------+------+

/* Here we get the another order since the optimizer will deliver results from index */
SELECT a FROM t1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

Is it specified that INSERT IGNORE... SELECT is performed in order?

2 Answers2