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.
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
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.
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.
You can use
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
WITH TIES based on a condition.
NEXT isn’t really supported. If you use it, it will work exactly like
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
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
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;
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
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
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.
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.