Difference between MariaDB and MySQL concerning where-clauses

Question

I'm developing software for a very old version of MariaDB and also a local MySQL testing database. I need to get the index of an entry in my table, after sorting it by a column. Usually I would just create a ROW_NUMBER and use it there, but because my database is on a very old version, this function is not available to me. I cannot update my databases to a newer version.

I have a table that stores a uuid and an amount. I now need to get the index of the uuid, after all entry's to put it on a fictional toplist. This is the query I built, that does work perfectly fine on MariaDB:

SELECT ssel.* 
FROM ( SELECT *, 
              @rn:= @rn + 1 AS row_num 
       FROM FireflyCatchPlayer,
            ( SELECT @rn:= 0 AS num ) AS c 
       ORDER BY amountCaught DESC ) ssel 
where ssel.playerUUID = '4533a889-8e94-4968-84fa-0b48ec795857'

However, on MySQL it does not do the correct thing.

If I remove the where clause that filters for the specific UUID, I get a correctly sorted Table on both Databases:

You can see here, that the rows are correctly sorted by amountCaught. If I however add the where Clause in my MySQL database, this is the result:

I noticed, that the where clause is taken into account when deciding, which entry's will be used to calculate the row numbers, therefore my (in this case only) value always gets the index 1. How can I avoid this, and truly filter on my ssel-Table in MySQL

Edit:

This is theoutput of MySQL when I added an entry with the same UUID, the row_num counts only the ones that are selected by UUID

Please see https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 for SQL related questions. — Progman, Dec 12 '21 at 16:07
Have you tried limit inside subquery? Like:`amountCaught desc limit 1000000` ? — Ergest Basha, Dec 12 '21 at 16:20
This is a norma. Server may optimize the execution, and it will do this. And the optimization will break the execution flow which you want to see. Try to use HAVING instead of WHERE. If this won't help then try to add GROUP BY to outer query by the column which is PK in source table additionally. From the other side your ORDER BY in the inner subquery does not provide the rows uniqueness, hence your query is not deterministic. — Akina, Dec 12 '21 at 16:27
Your testing database should be the same vendor and version as your production database, or it isn't much good for testing — ysth, Dec 12 '21 at 17:45
See e.g. my answer at [Same query and data structure, but MySQL 8 returns different result with MySQL 5?](https://stackoverflow.com/q/69835632). As Ergest Basha mentioned, the general workaround is to disable optimization by adding a limit inside your subquery. Apart from that, using `@rn :=` inside a query has never had a specified behaviour. It either worked as expected - or didn't. And will be removed completely from MySQL in some future version.. — Solarflare, Dec 12 '21 at 17:48

Difference between MariaDB and MySQL concerning where-clauses

0 Answers0