Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.0
-
None
-
ubuntu 22.04
Description
Anti-join incorrectly handles ASCII control characters, such as "SOH" (start of heading), by failing to respect their correct ordering (e.g., "SOH" < a < b < d < e). This results in incorrect output.
Create a file test.sql and fill test.sql with follow sql statements. Notably, there is a ASCII control character "SOH"(start of heading),before letter "c".
CREATE TABLE IF NOT EXISTS t0(c0 TINYTEXT);
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
INSERT INTO t0(c0) VALUES("b"), ("c"), ("d");
INSERT INTO t1(c0) VALUES('a'), ('e');
CREATE INDEX i1 ON t0(c0(1));
Then, execute SQL from file. For example: source /test.sql
The result set of a left join should equal the union of an anti-join and a inner-join. However, the results do not fit this equation, so we find this bug.
SELECT t1.c0 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t0 WHERE t1.c0 < t0.c0);
–
SELECT t1.c0 FROM t1 INNER JOIN t0 ON t1.c0 < t0.c0;
–
SELECT t1.c0 FROM t1 LEFT JOIN t0 ON t1.c0 < t0.c0;
–
Minor query plan difference but both have same index and assuming comparison.
10.6
MariaDB [test]> source /home/dan/Downloads/test.sqlMariaDB [test]> select hex(c0) from t1;+---------+| hex(c0) |+---------+| 61 || 65 |+---------+2 rows in set (0.001 sec)MariaDB [test]> select hex(c0) from t0;+---------+| hex(c0) |+---------+| 62 || 0163 || 64 |+---------+MariaDB [test]> explain SELECT t1.c0 FROM t1 LEFT JOIN t0 ON t1.c0 < t0.c0;+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | || 1 | SIMPLE | t0 | ALL | i1 | NULL | NULL | NULL | 3 | Range checked for each record (index map: 0x1) |+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+2 rows in set (0.001 sec)MariaDB [test]> explain SELECT t1.c0 FROM t1 INNER JOIN t0 ON t1.c0 < t0.c0;+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | || 1 | SIMPLE | t0 | ALL | i1 | NULL | NULL | NULL | 3 | Range checked for each record (index map: 0x1) |+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+2 rows in set (0.002 sec)MariaDB [test]> explain SELECT t1.c0 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t0 WHERE t1.c0 < t0.c0);+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where || 2 | DEPENDENT SUBQUERY | t0 | ALL | i1 | NULL | NULL | NULL | 3 | Using where |+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+