CODEWITHSUNDEEP
mysqlsortingcastingsql-order-bymariadb
Maria Matthews
Maria Matthews

Reputation: 120

MySQL (10.1.24-MariaDB) ORDER BY Condition autocasts as string

I am having a dynamic MySQL code that does have an ORDER BY clause. The table contains multiple columns but the ones that are important are stat and quality.

The stat column is mathematical calculation in select statement with type of INT type while the quality column is of STRING type.

Now I do have a statement of such:

SELECT (col1+col2+col3) as cast(STAT as unsigned), QUALITY FROM GEAR
ORDER BY CASE WHEN 1=1 THEN stat
              WHEN 1=0 THEN quality END  DESC

Now I do expect the code in above scenario to sort the results by the stat column in integer way:

stat    quality
1       normal
2       normal
3       better
4       better

However the actual way the results are displayed is as if the integer was autocasted to string:

stat    quality
1       normal
11      normal
12      better
2       better

Now if I:

  1. Remove the second case 1=0 statement (which is never true)
  2. Cast the "quality" column to int (from string)
  3. Change the "quality" value inside WHEN 1=0 THEN QUALITY END DESC to any other numeric column

The statement will work correctly and sort the output by STAT in the integer way.

I am trying to understand why is MySQL (MariaDB) deciding to sort the stat integer type column in a string way if the CASE/IF statement inside ORDER BY contains string type column in the other WHEN and find a way for this to not happen.

Maria

Upvotes: 0

Views: 919

Answers (1)

Rick James
Rick James

Reputation: 142296

Punt. Don't do it that way.

I assume 1=1 and 1=0 comes from code that is trying to control which column to order by? Make your code a tiny bit smarter -- so that it generates either

ORDER BY stat

or

ORDER BY quality

Or, if you need a combo, then be smart enough to generate one of these:

ORDER BY stat, quality
ORDER BY quality, stat

Upvotes: 1

Related Questions