bug: mysql order doesn't respec ASCII values

[ncclementi]

What happened?

In mysql when ordering a column that has uppercase and lowercase strings, the order doesn't follow ASCII orders.

Minimal reproducer:

• just up mysql

import ibis
from ibis import _
import os

MYSQL_USER = os.environ.get("IBIS_TEST_MYSQL_USER", "ibis")
MYSQL_PASS = os.environ.get("IBIS_TEST_MYSQL_PASSWORD", "ibis")
MYSQL_HOST = os.environ.get("IBIS_TEST_MYSQL_HOST", "localhost")
MYSQL_PORT = int(os.environ.get("IBIS_TEST_MYSQL_PORT", 3306))
IBIS_TEST_MYSQL_DB = os.environ.get("IBIS_TEST_MYSQL_DATABASE", "ibis_testing")

con = ibis.mysql.connect(
user=MYSQL_USER,
password=MYSQL_PASS,
host=MYSQL_HOST,
port=MYSQL_PORT,
database=IBIS_TEST_MYSQL_DB,
)

 t = ibis.memtable(
 {
 "col1": [1, 3, 2, 1, 3, 1],
 "col2": ["a","a", "B", "c", "D", "a"],
 }
 )
 
expr = t.order_by([_.col2.desc()])

con.execute(expr)

   col1 col2
0     3    D
1     1    c
2     2    B
3     1    a
4     3    a
5     1    a
  

What version of ibis are you using?

main

What backend(s) are you using, if any?

mysql

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

This is almost certainly a MySQL-ism and not something we can control. I vaguely recall some nonsense around collation being case-INsensitive.

[cpcloud]

Ok, well, it IS as MySQL-ism, but perhaps we can control it :)

https://dev.mysql.com/doc/refman/8.4/en/sorting-rows.html

Adding in the text of the above for searchability:

On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.

[ncclementi]

Based on this #9385 (comment). Should we close this one? or Does mysql still deserves the chance to try to sort this out? (pun intended)

[gforsyth]

Our test suite wasn't catching this because we don't have mixed-case strings in our test data.

[gforsyth]

Closing this out as it is the defined behavior in MySQL -- to get ASCII sorting, first cast to binary.