Schedule Meeting

a

MariaDB RETURNING Statements

by | Aug 5, 2020 | MariaDB

Many DBMSs support a way to get information about a rows we just deleted, updated, or inserted. The syntax varies depending on the DBMS, and some of them allow more options than others. Currently MySQL has no similar feature. MariaDB supports the RETURNING keyword for the following statements:

Feature request: MDEV-5092 – Implement UPDATE with result set.

Alberobello, Puglia, Italy. These ancient houses are called trulli

MariaDB RETURNING statements

Let’s go through the supported RETURNING statements.

DELETE RETURNING

Sometimes we need to run a SELECT just before a DELETE. For example when we want to delete an email from a list and then print the message “The email jo**@do********.uk has been removed from our archives”.

Using DELETE RETURNING allows us to run one less query. Returning the number of queries that your application runs may be important for performance.

DELETE
    FROM mailing_list
    WHERE id = 24
    RETURNING email AS deleted_email;

Multi-table DELETEs do not support RETURNING.

INSERT RETURNING

INSERT RETURNING is very similar: it inserts a row and returns the columns we want to know, without running an additional SELECT. Actually the values we insert are written in our INSERT statement, but with some exceptions:

  • The value of the AUTO_INCREMENT primary key. This is the most common reason to use INSERT RETURNING.
  • DEFAULT values assigned by the database, and not know by the application. Since DEFAULT values are logically part of a column definition, it is correct to write them in the database. And since DEFAULT values may change over time (just like any other metadata), it is better to only write them in the database, to avoid the hassle of keeping defaults in sync in different places. But without INSERT RETURNING, the required effort would be unreasonable.
  • Generates columns values can be retrieved by the application, for the same reasons. Generated columns can be seen as DEFAULT values that cannot be changed by the user.
  • BEFORE INSERT triggers may modify the values we insert. INSERT RETURNING will return the modified values.
  • We hardly know for sure which rows are inserted when we run INSERT ... SELECT. Using INSERT SELECT RETURNING solves this problem.

Another case is INSERT ... ON DUPLICATE KEY UPDATE. It should always be used when we need to insert a row or change it if it exists, as it allows to do that with a single statement. (As mentioned elsewhere, we shouldn’t use REPLACE for this).

But then, there are additional reasons why we may not know the value of a column:

  • We insert a certain value (VALUES clause) if the row does not exist, and a different value (UPDATE clause) if the row exists.
  • We don’t modify a value in case the row exists: it value won’t change, but it is unknown.

Note that INSERT RETURNING works with multi-row inserts too.

MariaDB [test]> INSERT INTO user
    (name, is_smart) VALUES
    ('Boris Johnson', FALSE),
    ('Peter Capaldi', TRUE)
    RETURNING id AS pk;
+----+
| pk |
+----+
|  1 |
|  2 |
+----+

(These values are clearly the output of a random generator)

ORMs often run INSERT followed by a SELECT. This is probably because of the reasons listed above, in particular they probably want to know the the row’s id. My advice is to run INSERT RETURNING instead, when using MariaDB; or some equivalent syntax when using other DBMSs that support it.

REPLACE RETURNING

As mentioned above, I don’t believe it is a good idea to use REPLACE, in most cases. Use INSERT ... ON DUPLICATE KEY UPDATE instead.

That said, REPLACE RETURNING works like INSERT RETURNING.

Checking DELETEd or INSERTed rows

Using DELETE RETURNING or INSERT RETURNING to check how many rows were deleted/inserted is not necessary. We can use mysql_affected_rows() instead (every API has an equivalent function), or ROW_COUNT() in stored procedures. Also, if you use a transactional engine all rows are inserted or no row is.

Using DELETE RETURNING or INSERT RETURNING to check if the operation succeeded is not necessary. We can just check if the statement failed with an error instead.

State of the art

Let’s see RETURNING limitations and features that work fine with RETURNING.

This article will be updated if some limitations are lifted or UPDATE RETURNING is implemented.

RETURNING Limitations

Db2 supports a way to get the resultsets before triggers and foreign keys are executed. MariaDB does not have this feature.

RETURNING statements cannot be used in these contexts:

  • UPDATE RETURNING.
  • Multi-table DELETEs.
  • INSERT SELECT RETURNING.
  • CREATE TABLE SELECT RETURNING.
  • Subquery: SELECT * FROM (INSERT RETURNING) WHERE ....
  • With cursors in stored procedures.
  • RETURNING does not support aggregate functions (for example, this is not valid: RETURNING COUNT(*)).

Working features

The following features and syntax can be used in a RETURNING clause:

  • RETURNING * (return all columns).
  • AS (column aliases).
  • Built-in functions and operators, eg: RETURNING UPPER(name), price - 1.
  • Stored functions, eg: RETURNING my_stored_func(id).

RETURNING can also be used in these contexts:

  • Prepared statements.
  • Stored procedures body.

See also

Articles

Conclusions

RETURNING can often be used to run one less query, making our applications faster and saving resources.

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

The benefits of MariaDB ColumnStore

The benefits of MariaDB ColumnStore

Last week Richard announced our projects on MariaDB ColumnStore. Take a look at his great post, if you didn't already. Since then, I've got some questions from customers, colleagues and friends: why did you guys decide to robustly invest into ColumnStore, and offer...

Let’s go, MariaDB ColumnStore at Vettabase!

Let’s go, MariaDB ColumnStore at Vettabase!

I have been an avid user of Columnar based storage engines for as long as I have been a DBA. The advantage is instantly obvious once you and your teams start to write or convert queries which previously took tens of minutes, are now returning in fractions of a second....

MariaDB Catalogs: some use cases

MariaDB Catalogs: some use cases

This month I attended Monty’s talk about Catalogs at MariaDB Server Fest 2023 in Helsinki. He described this nice feature, which is still under development, and how it will work. But when he talked about the use cases, I was under the impression that catalogs have...

Services

2 Comments

  1. Deetta Carbonaro

    I admire your piece of work, thankyou for all the interesting posts.

    Reply
  2. LJCooper

    Amazing how under used or even known RETURNING is, and the lengths you would have go to without it. (When I say would I actually mean HAVE. Have gone to.. we all do it until we know that whiff in the air making your face scrunch further with every keystroke is “code smell”. When writing your own code has the same effect on your face as fresh dog poop! )

    Enjoying the read, clear, simple and immediately understood.so many many tabs to get to.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *