I'm investigating the difference in the results given by the same SQL query on two servers running slightly different versions of MariaDB (10.5 and 10.11).
Test query is:
SELECT a.Name
FROM Artist a JOIN Album a2 ON a.ArtistId = a2.ArtistId
GROUP BY a.ArtistId, a.Name
HAVING COUNT(a2.AlbumId) IN (
SELECT * FROM(
SELECT COUNT(a2.AlbumId)
FROM Artist a JOIN Album a2 ON a.ArtistId = a2.ArtistId
GROUP BY a.ArtistId
ORDER BY COUNT(a2.AlbumId) DESC
LIMIT 4) AS t
)
ORDER BY a.Name;
against the publicly available Chinook database
Subquery returns (21,14,11,10).
Replacing the subquery by these results:
SELECT ar.Name
FROM Artist ar
JOIN Album al ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistId, ar.Name
HAVING COUNT(al.AlbumId) IN (21,14,11,10)
ORDER BY ar.Name;
gives:
Name |
------------+
Deep Purple |
Iron Maiden |
Led Zeppelin|
Metallica |
U2 |
which is the expected result.
Now, the problem is that when running the first query on MariaDB version 10.11.4 it gives no results (0 rows), but when running it in MariaDB version 10.5.21 it gives the 5 expected rows.
Note that the query in itself is not important. What I'm investigating here is the inconsistency in results between two servers that have similar software versions.
Also note that the following slightly modified query works (5 rows) on both versions:
SELECT ar.Name, COUNT(al.AlbumId) as num_albums
FROM Artist ar
JOIN Album al ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistId, ar.Name
HAVING num_albums IN (
SELECT * FROM (
SELECT COUNT(al2.AlbumId)
FROM Album al2
GROUP BY al2.ArtistId
ORDER BY COUNT(al2.AlbumId) DESC
LIMIT 4
) AS t
)
ORDER BY ar.Name;
Is there a recent change in MariaDB that explains the first query results? Or is this a bug in MariaDB that should be reported?
EDITED: results are back on server version 11.2. If it was a bug, seems it's solved now.
The affected version is the current default on Debian 12 bookworm, so if you encounter a similar problem, updating from the official repository may be a good solution.