| Bug #119483 | INTERSECT Returns Row in MySQL 9.3.0 but Not in 9.4.0/9.5.0 | ||
|---|---|---|---|
| Submitted: | 1 Dec 12:47 | Modified: | 2 Dec 10:24 |
| Reporter: | jinhui lai | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 9.3.0 | OS: | Ubuntu (22.04) |
| Assigned to: | CPU Architecture: | Any | |
[2 Dec 9:12]
Knut Anders Hatlen
Thanks for the bug report.
Unfortunately, I am not able to reproduce the reported behaviour. For me, the query returns one row both in MySQL 9.3.0 and in MySQL 9.4.0.
It's not so easy to see in the bug report, but the string literal '6' in the first INSERT statement actually consists of two characters. The first character is Unicode code point U+202A (LEFT-TO-RIGHT EMBEDDING), which at least for me was invisible in the web browser. The second character is U+0036 (DIGIT SIX).
To make sure the U+202A code point was not lost when copying and pasting into the client, I also tested with this alternative INSERT statement in plain ASCII:
INSERT INTO t0(c0) VALUES (CONCAT(_utf8mb4 x'E280AA', '6')), (6);
The results were the same, though. The query returned one row both in 9.3.0 and in 9.4.0.
[2 Dec 10:24]
jinhui lai
Thanks for your reply. For any given row pair, t1.c0 LIKE t0.c0 and t1.c0 NOT LIKE t0.c0 are mutually exclusive. A row cannot simultaneously satisfy both conditions. Therefore, the INTERSECT of these two queries should return empty. The root cause may be the implementation of INTERSECT. I did observe the differences in MySQL 9.3.0, MySQL 9.4.0, and MySQL 9.5.0; perhaps PG's results can also be used as a reference. Maybe you can run the case on the following website. PostgreSQL 18: https://phpize.online/sql/psql18/ebaca6079c7c289c49cff7d5dbf298a9/php/php81/6946c8eb31db0b...
[2 Dec 11:42]
Knut Anders Hatlen
Note that LIKE and INTERSECT use different rules for determining equality, so even though two values are considered different by LIKE, they are not necessarily considered different by INTERSECT. https://dev.mysql.com/doc/refman/8.4/en/string-comparison-functions.html#operator_like says: > Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator It depends on the collation used, but assuming MySQL's default collation (utf8mb4_0900_ai_ci), we have that CONCAT(_utf8mb4 x'E280AA', '6') = '6' -> TRUE CONCAT(_utf8mb4 x'E280AA', '6') LIKE '6' -> FALSE The two strings are = each other, but they are not LIKE each other. INTERSECT uses the same logic as = for comparing, hence the rows returned by the two joins are considered equal by INTERSECT. PostgreSQL probably has a different default collation than MySQL, so results depending on collation are expected to vary between those two.
Description: Hi, MySQL developers. I have found a potential bug in MySQL regarding the INTERSECT operation. The following query returns a row in MySQL versions 9.0.0 through 9.3.0 and MariaDB 12.2.1. However, in MySQL versions 9.4.0 and 9.5.0, the query yields no results. Since this change is not documented as a fix in the release notes for MySQL 9.4.0/9.5.0, I'm not sure which result is correct. How to repeat: CREATE TABLE t0(c0 VARCHAR(2)) ; CREATE TABLE t1 LIKE t0; INSERT INTO t0(c0) VALUES('6'),(6); INSERT INTO t1(c0) VALUES(6); -- MySQL 9.0.0-9.3.0, MariaDB 12.2.1(latest) SELECT * FROM t1 JOIN t0 ON t1.c0 LIKE t0.c0 INTERSECT SELECT * FROM t1 JOIN t0 ON t1.c0 NOT LIKE t0.c0; +------+------+ | c0 | c0 | +------+------+ | 6 | 6 | +------+------+ -- MySQL 9.4.0,9.5.0 SELECT * FROM t1 JOIN t0 ON t1.c0 LIKE t0.c0 INTERSECT SELECT * FROM t1 JOIN t0 ON t1.c0 NOT LIKE t0.c0;