MariaDB: WITH TIES syntax

MariaDB implemented the WITH TIES syntax in version 10.6. This feature may not be a ground breaker, but it is useful in some cases and it increases compatibility with some other DBMSs. Let’s see what it does, and what use cases it has.

Some history

MariaDB (just like MySQL) traditionally supported a PostgreSQL-like syntax LIMIT ... OFFSET, which in real cases is usually ORDER BY ... LIMIT ... [OFFSET ...]. See the MariaDB KnowledgeBase page. The use of OFFSET is often an antipattern, but this is a subject for another time.

In MariaDB 10.6, support for an alternative syntax OFFSET ... ROWS FETCH FIRST ... ROWS was added. Funnily enough, it was apparently added for compatibility with PostgreSQL, but this syntax is supported by Oracle and other DBMSs, too. And Oracle compatibility seems to be very important for MariaDB,

This work was MDEV-23908, and WITH TIES was added in a different task that I can’t easily find.

What WITH TIES does

Explaining WITH TIES with an example is incredibly easier by using an example, rather than trying to explain its meaning in English.

As mentioned, normally you use FETCH ... ROWS after ORDER BY. For example:

SELECT id, title
    FROM book
    ORDER BY author
    FETCH FIRST 10 ROWS;

This query orders books by title, and returns the first 10 of them.

The WITH TIES version is:

SELECT id, title
    FROM book
    ORDER BY author
    FETCH FIRST 10 ROWS WITH TIES;

This query could return more than 10 rows. After sorting the rows, it will return the first ten rows, plus the rows having the same author as the 10th row.

Syntax notes

You can use ROW and ROWS interchangeably, if you want. In the next examples I’m going to use the singular/plural in correct English, but you don’t have to.

You can specify ONLY instead of WITH TIES. It’s an equivalent for dear old LIMIT. If you’re a developer you might find it useful if your application has to choose whether to use ONLY or WITH TIES based on a condition.

NEXT isn’t really supported. If you use it, it will work exactly like FIRST.

Optimiser notes

WITH TIES doesn’t have any performance drawback (unless the number of additional returned rows is big enough to cause problems, in which case you simply shouldn’t use WITH TIES).

Currently EXPLAIN will always return the same estimated rows number, regardless whether WITH TIES is used or not:

> EXPLAIN SELECT name FROM test_data2 ORDER BY name
FETCH NEXT 50 ROWS ONLY \G
*************************** 1. row ***************************
...
         rows: 50
...
> EXPLAIN SELECT name FROM test_data2 ORDER BY name
    -> FETCH NEXT 50 ROWS WITH TIES \G
*************************** 1. row ***************************
...
         rows: 50
...

WITH TIES use cases

Here are some practical use cases for WITH TIES.

Get all rows with the minimum or maximum value

Getting the row containing the maximum value is easy:

SELECT id, email
    FROM user
    ORDER BY registration_date DESC
    LIMIT 1;

But if there are several users that could match this condition, only one of them will be returned. To SELECT them all:

SELECT id, email
    FROM user
    ORDER BY registration_date DESC
    FETCH FIRST 1 ROW WITH TIES;

Without WITH TIES, the query would be something like this, and it would be slower that necessary:

SELECT id, email
    FROM user
    WHERE registration_date = (
        SELECT MAX(registration_date) FROM user
    );

Aggregating data in the application

DBMSs are very good at aggregating data (GROUP BY). And they have the aggregate functions that you normally want to use: minimum, maximum, arithmetic mean…

But what is you want to use a function that is not implemented, like the harmonic mean? Well, MariaDB supports stored aggregate functions, which means that you can implement the harmonic mean by yourself, in procedural SQL. Normally that would be the way to go, for example because it can avoid a lot of data roundtrip and lock time. But many teams don’t use procedural SQL for various reasons.

The way to emulate GROUP BY in an application would be something like this (pseudocode):

previousMonth = null
results = run_query(
        'SELECT month, cost FROM purchase WHERE year = 2021;'
    )
series = [ ]
while (row = results.fetch_row()):
    if previousMonth == row.month:
        series.push(previousMonth)
    else:
        print(harmonicMean(series))
        series = [ ]

This is not a sensible use of harmonic means. Please focus on the logic only.

The problem here is that selecting all the rows at once could lead to terrible performance. We need to limit the number of rows we get at once, but the number of rows to get is probably different for every month.

Again, we can use WITH TIES:

SELECT month, cost
    FROM purchase
    WHERE year = 2021 AND month = 1
    ORDER BY year, month
    FETCH FIRST 1 ROW WITH TIES;

Loop over non-continuous, non-unique values

In the simplest case, to loop over rows based on one column, you’d do something like this:

loop i in range(1, 100):
    run_query('SELECT id, full_name FROM employee WHERE category_id = ' + str(i))

But how would you do the same kind of loop over a videogame table, with based on the score column? The values would theoretically be anything from zero to a high number, with many holes in the middle, and the same scores may occur many times.

Again, we’ll use WITH TIES:

SELECT player, score FROM game WHERE score > @last_score FETCH FIRST 1 ROW WITH TIES;

WITH TIES bugs in MariaDB

MariaDB doesn’t have any relevant open bugs involving WITH TIES. If such bugs are reported in the future, I will try to keep this page up to date.

Last checked: 2021-12-12.

Conclusions

We discussed what the WITH TIES syntax does, and a couple of use cases. If you have more use cases, please drop a comment. See the WITH TIES bugs in MariaDB section if you notice any wrong / weird behaviour.

To master advanced SQL and query optimisation, consider our SQL optimisation training for teams.

Federico Razzoli

Did you like this article?