Bug #70901 ORDER BY of a TEXT column doesn't use the same ordering as the < > = operators
Description:
MySQL's CHAR columns are literally padded with spaces.
MySQL's VARCHAR columns aren't literally padded, but they still compare via < > = as if they were space-padded; for example "a" = "a " and "a\n" < "a". Doing an ORDER BY a VARCHAR column sorts the rows according to this ordering.
MySQL's TEXT columns use the same space-padding < > = comparisons as VARCHAR columns, but when you ORDER BY a TEXT column, the order you get is actually a strict dictionary ordering (each string `x` compares strictly greater-than each proper prefix of `x`).
This last fact means that TEXT columns sort (and apparently are indexed) in an order that's not equivalent to how they compare with < > =. This is extremely surprising behavior. I imagine it also breaks various range-scan optimizations (SELECT ... WHERE mytext BETWEEN foo AND bar), although I haven't tried looking for the breaks.
How to repeat:
create database if not exists d;
use d;
drop table if exists t;
create table t (v text collate utf8_bin);
insert into t values ('');
insert into t values ('\n\n');
select hex(t1.v), hex(t2.v), t1.v < t2.v as `<`, t1.v = t2.v as `=`, t1.v > t2.v as `>` from t t1 join t t2 order by t1.v, t2.v;
+-----------+-----------+------+------+------+
| hex(t1.v) | hex(t2.v) | < | = | > |
+-----------+-----------+------+------+------+
| | | 0 | 1 | 0 |
| | 0A0A | 0 | 0 | 1 |
| 0A0A | | 1 | 0 | 0 |
| 0A0A | 0A0A | 0 | 1 | 0 |
+-----------+-----------+------+------+------+
Notice that according to the right-hand columns, "" > "\n\n" and "\n\n" < "", but according to the left-hand columns, "" sorts earlier than "\n\n" in an ORDER BY.
Suggested fix:
I suppose this behavior might be documented somewhere. It probably can't be "fixed" at this point. However, it would be a good idea to audit the code for optimizations that assume ORDER BY and/or indexes use the same collation order as < > =, and fix them up as they're found.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: MySQL's CHAR columns are literally padded with spaces. MySQL's VARCHAR columns aren't literally padded, but they still compare via < > = as if they were space-padded; for example "a" = "a " and "a\n" < "a". Doing an ORDER BY a VARCHAR column sorts the rows according to this ordering. MySQL's TEXT columns use the same space-padding < > = comparisons as VARCHAR columns, but when you ORDER BY a TEXT column, the order you get is actually a strict dictionary ordering (each string `x` compares strictly greater-than each proper prefix of `x`). This last fact means that TEXT columns sort (and apparently are indexed) in an order that's not equivalent to how they compare with < > =. This is extremely surprising behavior. I imagine it also breaks various range-scan optimizations (SELECT ... WHERE mytext BETWEEN foo AND bar), although I haven't tried looking for the breaks. How to repeat: create database if not exists d; use d; drop table if exists t; create table t (v text collate utf8_bin); insert into t values (''); insert into t values ('\n\n'); select hex(t1.v), hex(t2.v), t1.v < t2.v as `<`, t1.v = t2.v as `=`, t1.v > t2.v as `>` from t t1 join t t2 order by t1.v, t2.v; +-----------+-----------+------+------+------+ | hex(t1.v) | hex(t2.v) | < | = | > | +-----------+-----------+------+------+------+ | | | 0 | 1 | 0 | | | 0A0A | 0 | 0 | 1 | | 0A0A | | 1 | 0 | 0 | | 0A0A | 0A0A | 0 | 1 | 0 | +-----------+-----------+------+------+------+ Notice that according to the right-hand columns, "" > "\n\n" and "\n\n" < "", but according to the left-hand columns, "" sorts earlier than "\n\n" in an ORDER BY. Suggested fix: I suppose this behavior might be documented somewhere. It probably can't be "fixed" at this point. However, it would be a good idea to audit the code for optimizations that assume ORDER BY and/or indexes use the same collation order as < > =, and fix them up as they're found.