Exactly the same `A < B` expression unexpectedly returns different results in different contexts in MySQL

  Kiến thức lập trình

Today I have encountered an extremely counter-intuitive behavior of < operator in MySQL and I have no idea why.

Specifying exactly the same operands for < gives different results depending on the contexts.

Can anyone explain the reason? This is so counter-intuitive that I even suspect it’s a bug of MySQL.

SQL

DROP TABLE IF EXISTS t1;

CREATE TABLE
    t1 (s VARCHAR(10));

INSERT INTO
    t1 (s)
VALUES
    ('0001/a'),
    ('0001/b'),
    ('0002/a'),
    ('0002/b'),
    ('0003/a'),
    ('0003/b');

SELECT ('/' < ':'); -- => `TRUE`

SELECT ('0002/a' < '0002:'); -- => `TRUE`

SELECT ('0002/b' < '0002:'); -- => `TRUE`

SELECT * FROM t1 WHERE (s < '0002:'); -- => What will the result be?

Expected Result

+--------+
| s      |
+--------+
| 0001/a |
| 0001/b |
| 0002/a |
| 0002/b |
+--------+

Actual Result

+--------+
| s      |
+--------+
| 0001/a |
| 0001/b |
+--------+

Environment

$ docker run -d -p 3306:3306 --name mysql -e 'MYSQL_DATABASE=t' -e 'MYSQL_ROOT_PASSWORD=password' --restart always mysql

  • mysql

LEAVE A COMMENT