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][1] 

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?


  [1]: https://github.com/lerocha/chinook-database