mariadb order by a user function gives wrong result

Question

Using MySQL/MariaDB, I usually do this kind of query below to get the rank of a specific record so that I can display the proper page in an application:

SET @rownum := 0;
SELECT  rank
    FROM  
      ( SELECT  @rownum := @rownum+1 AS rank, ordid
            FROM  ord
            order by  ord_status, ordid
      ) AS derived_table
    WHERE  ordid = 1234
    limit  1;

I used it for years and it usually works just fine.

However, today, I tried to sort the query according to the description of the order status instead of the order status id (field ord_status). So, I had to sort data using the user funtion named getStatusDescription() that I created in my database. Here is my new query:

SET @rownum := 0;
SELECT  rank
    FROM  
      ( SELECT  @rownum := @rownum+1 AS rank, ordid
            FROM  ord
            order by  getStatusDescription(ord_status), ordid
      ) AS derived_table
    WHERE  ordid = 1234
    limit  1;

For an unknown reason, the rank result is wrong and I do not understand why it is not working. Is it possible that there is a problem or a limitation with MariaDB ?

I'm using MariaDB 10.0.17 on a Centos 7 machine as my development plaftform.

For your information, my function getStatusDescription() just receive a parameter (the order status id) then according to the parameter received select the proper varchar(35) field from a specific table then just return it.

Any help is very welcome.

Guylain Plante

Possibly relevant: https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/ — Rick James, Mar 03 '16 at 03:50

score 0 · Answer 1 · answered Mar 01 '16 at 02:12

Your method of getting rank is fine, but you can also use a subquery method:

select count(*)
from ord cross join
     (select ordid, getStatusDescription(o2.ord_status) as gsd
      from ord o2
      where o2.ordid = 1234
     ) oo
where getStatusDescription(ord_status) < oo.gsd or
      (getStatusDescription(ord_status) = oo.gsd and o.ordid <= oo.ordid)

As for your situation, I don't know about the root cause. However, sometimes GROUP BY is problematic with variables and an additional subquery fixes the probelm:

SELECT rank
FROM (SELECT @rownum := @rownum+1 AS rank, ordid
      FROM (SELECT ord.*
            FROM ord 
            ORDER BY getStatusDescription(ord_status), ordid
           ) derived_table CROSS JOIN
           (SELECT @rownum := 0) params
     ) o
WHERE ordid = 1234 
LIMIT 1;

score 0 · Answer 2 · edited May 23 '17 at 11:50

Finally, I remembered that I had to put a LIMIT (and also a sub-query) so that the database have a good reason to apply the sort order.

The following query works fine now:

SELECT rank FROM (SELECT @rownum := @rownum+1 AS rank, ordid  
                  FROM   (SELECT ordid FROM ord order by getStatusDescription(ord_status), ordid LIMIT 9999999999999
                         ) as t2
                 ) AS derived_table 
WHERE ordid = 1234 limit 1;

See the following thread as well: mysql - order by inside subquery

mariadb order by a user function gives wrong result

2 Answers2