Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.11
-
None
-
Unexpected results
-
Q4/2025 Server Maintenance
Description
A retelling of case 3 from this comment.
Setup:
CREATE TABLE t1 (c1 TEXT, UNIQUE (c1)) ENGINE=InnoDB;
|
INSERT INTO t1 (c1) VALUES ('a');
|
Queries:
SELECT c1 as r2 FROM t1 WHERE CAST(c1 AS BINARY(7)) NOT IN (SELECT c1 FROM t1); -- {a}
|
+------+
|
| r2 |
|
+------+
|
| a |
|
+------+
|
1 row in set (0.004 sec)
|
 |
SELECT c1 as r2 FROM t1 WHERE CAST(c1 AS BINARY(1)) NOT IN (SELECT c1 FROM t1); -- {}
|
SELECT c1 FROM t1 WHERE CAST(c1 AS BINARY) NOT IN (SELECT c1 FROM t1); -- {}
|
The first SELECT should return an empty set. The second and third SELECTs behaves as expected and are equivalent. NOTE: BINARY(M) specifies the width of the field in bytes; when not specified, M is 1 by default.
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- relates to
-
-
- Confirmed
-