Description:
EXCEPT queries return inconsistent results depending on:
1. Whether the table has a composite PRIMARY KEY/UNIQUE KEY
2. Whether logically equivalent WHERE clauses are used
3. Block Nested Loop optimizer switch settings
**Correct behavior**: All tests should return the same 5 rows: (-1641789992, -1043093747, -180375844, 409848919, 1085725080), while they all miss 1 row.
How to repeat:
CREATE TABLE t161 (
c1 INTEGER UNSIGNED PRIMARY KEY,
c2 LONGBLOB NULL
);
CREATE TABLE t163 (
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (c1, c2), -- COMPOSITE PRIMARY KEY
UNIQUE KEY (c1)
);
CREATE TABLE t163_no_composite_pk (
c1 INT UNIQUE,
c2 INT NOT NULL
);
CREATE TABLE t163_no_index (
c1 INT,
c2 INT NOT NULL
);
INSERT INTO t161 (c1,c2) VALUES
(1244139115,'lnbsESgXknh7HV43Uf'),
(1969040957,'AG8hAe3ZDEdjpnQNjjWXyEcx'),
(2116127649,'d99LhuzIrwlR3ttpVbJHNtPRUzuOlU'),
(3367816932,'dHh8'),
(3707611011,'8YoEpiXaCcJz6pAqM');
INSERT INTO t163 (c1,c2) VALUES
(-1641789992,-899637389),
(-1043093747,1937706198),
(-180375844,-435718419),
(409848919,850367332),
(1085725080,2087110582);
INSERT INTO t163_no_composite_pk (c1,c2) VALUES
(-1641789992,-899637389),
(-1043093747,1937706198),
(-180375844,-435718419),
(409848919,850367332),
(1085725080,2087110582);
INSERT INTO t163_no_index (c1,c2) VALUES
(-1641789992,-899637389),
(-1043093747,1937706198),
(-180375844,-435718419),
(409848919,850367332),
(1085725080,2087110582);
### Test 1: Whether logically equivalent WHERE clauses are used
mysql> SELECT ta2.ca3 AS ca1
-> FROM (SELECT c2 AS ca2 FROM t161) AS ta1
-> NATURAL JOIN
-> (SELECT c1 AS ca3 FROM t163 WHERE c1 NOT LIKE c2) AS ta2
-> EXCEPT
-> SELECT NULL;
+-------------+
| ca1 |
+-------------+
| 1085725080 |
| 409848919 |
| -180375844 |
| -1043093747 |
+-------------+
4 rows in set (0.002 sec)
-- Verify: c1 NOT LIKE c2 is TRUE for all rows
mysql> SELECT c1 NOT LIKE c2 FROM t163;
+----------------+
| c1 NOT LIKE c2 |
+----------------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+----------------+
5 rows in set (0.002 sec)
mysql> SELECT ta2.ca3 AS ca1
-> FROM (SELECT c2 AS ca2 FROM t161) AS ta1
-> NATURAL JOIN
-> (SELECT c1 AS ca3 FROM t163 WHERE TRUE) AS ta2
-> EXCEPT
-> SELECT NULL;
+-------------+
| ca1 |
+-------------+
| -1641789992 |
| -1043093747 |
| -180375844 |
| 409848919 |
+-------------+
4 rows in set (0.002 sec)
### Test 2: Block Nested Loop Optimizer Impact
mysql> SET SESSION optimizer_switch='block_nested_loop=off';
mysql> SELECT ta2.ca3 AS ca1
-> FROM (SELECT c2 AS ca2 FROM t161) AS ta1
-> NATURAL JOIN
-> (SELECT c1 AS ca3 FROM t163 WHERE c1 NOT LIKE c2) AS ta2
-> EXCEPT
-> SELECT NULL;
+-------------+
| ca1 |
+-------------+
| -1641789992 |
| -1043093747 |
| -180375844 |
| 409848919 |
+-------------+
4 rows in set (0.002 sec)
### Test 3: PRIMARY/UNIQUE KEY
mysql> SELECT ta2.ca3 AS ca1 FROM (SELECT c2 AS ca2 FROM t161) AS ta1 NATURAL JOIN (SELECT c1 AS ca3 FROM t163_no_composite_pk WHERE c1 NOT LIKE c2) AS ta2 EXCEPT SELECT NULL;
+-------------+
| ca1 |
+-------------+
| 1085725080 |
| 409848919 |
| -180375844 |
| -1043093747 |
| -1641789992 |
+-------------+
5 rows in set (0.002 sec)
mysql> SELECT ta2.ca3 AS ca1 FROM (SELECT c2 AS ca2 FROM t161) AS ta1 NATURAL JOIN (SELECT c1 AS ca3 FROM t163_no_index WHERE c1 NOT LIKE c2) AS ta2 EXCEPT SELECT NULL;
+-------------+
| ca1 |
+-------------+
| 1085725080 |
| 409848919 |
| -180375844 |
| -1043093747 |
| -1641789992 |
+-------------+
5 rows in set (0.001 sec)
Description: EXCEPT queries return inconsistent results depending on: 1. Whether the table has a composite PRIMARY KEY/UNIQUE KEY 2. Whether logically equivalent WHERE clauses are used 3. Block Nested Loop optimizer switch settings **Correct behavior**: All tests should return the same 5 rows: (-1641789992, -1043093747, -180375844, 409848919, 1085725080), while they all miss 1 row. How to repeat: CREATE TABLE t161 ( c1 INTEGER UNSIGNED PRIMARY KEY, c2 LONGBLOB NULL ); CREATE TABLE t163 ( c1 INT NOT NULL, c2 INT NOT NULL, PRIMARY KEY (c1, c2), -- COMPOSITE PRIMARY KEY UNIQUE KEY (c1) ); CREATE TABLE t163_no_composite_pk ( c1 INT UNIQUE, c2 INT NOT NULL ); CREATE TABLE t163_no_index ( c1 INT, c2 INT NOT NULL ); INSERT INTO t161 (c1,c2) VALUES (1244139115,'lnbsESgXknh7HV43Uf'), (1969040957,'AG8hAe3ZDEdjpnQNjjWXyEcx'), (2116127649,'d99LhuzIrwlR3ttpVbJHNtPRUzuOlU'), (3367816932,'dHh8'), (3707611011,'8YoEpiXaCcJz6pAqM'); INSERT INTO t163 (c1,c2) VALUES (-1641789992,-899637389), (-1043093747,1937706198), (-180375844,-435718419), (409848919,850367332), (1085725080,2087110582); INSERT INTO t163_no_composite_pk (c1,c2) VALUES (-1641789992,-899637389), (-1043093747,1937706198), (-180375844,-435718419), (409848919,850367332), (1085725080,2087110582); INSERT INTO t163_no_index (c1,c2) VALUES (-1641789992,-899637389), (-1043093747,1937706198), (-180375844,-435718419), (409848919,850367332), (1085725080,2087110582); ### Test 1: Whether logically equivalent WHERE clauses are used mysql> SELECT ta2.ca3 AS ca1 -> FROM (SELECT c2 AS ca2 FROM t161) AS ta1 -> NATURAL JOIN -> (SELECT c1 AS ca3 FROM t163 WHERE c1 NOT LIKE c2) AS ta2 -> EXCEPT -> SELECT NULL; +-------------+ | ca1 | +-------------+ | 1085725080 | | 409848919 | | -180375844 | | -1043093747 | +-------------+ 4 rows in set (0.002 sec) -- Verify: c1 NOT LIKE c2 is TRUE for all rows mysql> SELECT c1 NOT LIKE c2 FROM t163; +----------------+ | c1 NOT LIKE c2 | +----------------+ | 1 | | 1 | | 1 | | 1 | | 1 | +----------------+ 5 rows in set (0.002 sec) mysql> SELECT ta2.ca3 AS ca1 -> FROM (SELECT c2 AS ca2 FROM t161) AS ta1 -> NATURAL JOIN -> (SELECT c1 AS ca3 FROM t163 WHERE TRUE) AS ta2 -> EXCEPT -> SELECT NULL; +-------------+ | ca1 | +-------------+ | -1641789992 | | -1043093747 | | -180375844 | | 409848919 | +-------------+ 4 rows in set (0.002 sec) ### Test 2: Block Nested Loop Optimizer Impact mysql> SET SESSION optimizer_switch='block_nested_loop=off'; mysql> SELECT ta2.ca3 AS ca1 -> FROM (SELECT c2 AS ca2 FROM t161) AS ta1 -> NATURAL JOIN -> (SELECT c1 AS ca3 FROM t163 WHERE c1 NOT LIKE c2) AS ta2 -> EXCEPT -> SELECT NULL; +-------------+ | ca1 | +-------------+ | -1641789992 | | -1043093747 | | -180375844 | | 409848919 | +-------------+ 4 rows in set (0.002 sec) ### Test 3: PRIMARY/UNIQUE KEY mysql> SELECT ta2.ca3 AS ca1 FROM (SELECT c2 AS ca2 FROM t161) AS ta1 NATURAL JOIN (SELECT c1 AS ca3 FROM t163_no_composite_pk WHERE c1 NOT LIKE c2) AS ta2 EXCEPT SELECT NULL; +-------------+ | ca1 | +-------------+ | 1085725080 | | 409848919 | | -180375844 | | -1043093747 | | -1641789992 | +-------------+ 5 rows in set (0.002 sec) mysql> SELECT ta2.ca3 AS ca1 FROM (SELECT c2 AS ca2 FROM t161) AS ta1 NATURAL JOIN (SELECT c1 AS ca3 FROM t163_no_index WHERE c1 NOT LIKE c2) AS ta2 EXCEPT SELECT NULL; +-------------+ | ca1 | +-------------+ | 1085725080 | | 409848919 | | -180375844 | | -1043093747 | | -1641789992 | +-------------+ 5 rows in set (0.001 sec)