Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
Please add a RETURNING option to INSERT.
Example from PostgreSQL
postgres=# CREATE TABLE t1 (id SERIAL, name VARCHAR(100));
|
CREATE TABLE
|
postgres=# INSERT INTO t1(name) VALUES('test') RETURNING id;
|
id
|
----
|
1
|
(1 row)
|
|
|
INSERT 0 1
|
Inspired by: https://evertpot.com/writing-sql-for-postgres-mysql-sqlite/
This could make it easier to write statements which work with both MariaDB and PostgreSQL. And this might improve compatibility with Oracle RDBMS.
How it'll work:
- All variants of INSERT will work — INSERT ... VALUES, INSERT ... SET, INSERT ... SELECT
- INSERT ... ON DUPLICATE KEY UPDATE will work, will return the final row values (inserted or updated)
- REPLACE will return what's inserted
- INSERT IGNORE will return what it'll try to insert, (ignoring insert errors, as expected)
- INSERT DELAYED will, obviously, not work
- rows will be returned as they're being inserted. If the insert fails in the middle and is rolled back — rows might've been sent that didn't end up in the table
Attachments
Issue Links
- is duplicated by
-
MDEV-10422 INSERT RETURNING
-
- Closed
-
-
MDEV-20033
sql_mode="oracle" does not support INSERT INTO ... RETURNING
-
- Closed
-
- is part of
-
-
- In Progress
-
- links to
Activity
In reply to Daniël, this is extremely beneficial when you are bulk adding rows plus their relationships when innodb_autoinc_lock_mode = 2 (required for Galera clusters). As the insert IDs cannot be guaranteed with innodb_autoinc_lock_mode=2 the last insert id is not sufficient when adding multiple rows: To add 100 rows the way Daniël described it would cause 200 extra round trips.
Also, afaik the protocol only returns the LAST auto increment id of your query, not the auto increment ids of the rows in between.
With this feature implemented, you can multi-row insert 100 rows and get the corresponding ids back in 1 rtt.
Another new possibility with this feature: when doing an INSERT IGNORE .... RETURNING query you can see which rows actually inserted successfully, and trigger stuff in the application based on these results.
Very nice work rucha174 and everybody else involved, I am looking forward to it being released.
Could you please specify here in JIRA description:
- whether INSERT IGNORE ... RETURNING (also mentioned in the previous comment) is supported, and if so, what it should return – what was attempted to be inserted, or what actually was;
- whether REPLACE ... RETURNING is supported, and if so, what it should return – what was newly inserted or everything;
- what should happen when certain values are ignored upon INSERT (e.g. they were attempted to be inserted into virtual columns) – should they be returned or not;
- what should happen if INSERT failed half-way (e.g. while inserting into a non-transactional table and hitting a duplicate key, but can be on various reasons);
probably more questions to follow
Generally all is OK except returning non-inserted data, so tests should be added:
- INSERT IGNORE with some errors and it should not return what was not inserted
- INSERT with trigger and ON DUPLICATE which change inserted data and return should return changed values
- INSERT inside stored procedure (with multiple sets return)
- INSERT inside function and trigger (even if it is an error)
- "insert t1 set a=(select a+3);" will be turned to "insert t1 set a=(a+3)", so if we allow subqueries it will be better to put something which does not transform and have additional table "insert t1 set a=(select c from t2 where a=b)"
- in the following query it would be better to return val to see if it was changed
INSERT IGNORE INTO ins_duplicate(id,val) VALUES (3,'c'),(4,'d') ON DUPLICATEKEY UPDATE val='g' RETURNING id;
post-review fixes: https://github.com/MariaDB/server/commit/b4626e12e2df239051ab6738b4cbfb6c6e10ac3a
With this feature implemented, you can multi-row insert 100 rows and get the corresponding ids back in 1 rtt.
Is the result set guaranteed to be in the same order as value list?
For what I know this is not in the SQL:2003 standard.
http://www.savage.net.au/SQL/sql-2003-2.bnf.html#insert%20statement
This might save a roundtrip. But I do think last_insert_id sent to the client anyways, so a 'SELECT LAST_INSERT_ID()' should not be needed.
This could also allow multiple columns like it does in PostgreSQL. This could be efficient if defaults are used.
Example:
postgres=# CREATE TABLE t2(id SERIAL, name VARCHAR(100), language CHAR(2) DEFAULT 'en');CREATE TABLEpostgres=# INSERT INTO t2(name) VALUES('test') RETURNING id,language;id | language----+----------1 | en(1 row)INSERT 0 1So from a client perspective normally you would need to do this:
Often the solution to this is to keep all defaults client side to prevent the select, but then ad-hoc sql or other scripts might use other defaults. It should be possible to keep the defaults in the database w/o losing efficiency.