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
0 Comments