Description:
The following seems to apply to all MySQL versions from 8.0.31. Last version tested 9.3.0. Tested in two environments
Environment 1:
- Windows 10 Enterprise; Inter Core i7-11800H 2.30 GHz / 32 GB
- MySQL: 8.0.32 MySQL Community Server - GPL
Environment 2:
- Virtualbox 6.1 virtual machine (2 CPU, 4 GB) running GNU/Linux Ubuntu 22.04-LTS 6.8.0-60-generic #63~22.04.1-Ubuntu x86_64
- Host machine: Windows 10 Pro; Inter Core i5-7300U 2.60-2.71 GHz / 8 GB
- MySQL: 9.3.0 MySQL Community Server - GPL
We have reviewed the reference manual and the mailing list archives but found neither similar cases nor any explanation for the observed behavior.
The following behavior was first encountered using real data in production tables but has been reproduced here using minimal examples for clarity. The test cases presented accurately replicate the behavior originally observed.
We have found that MySQL produces inconsistent results when evaluating `EXCEPT` or `INTERSECT` operations involving `NULL` values.
To the best of our knowledge, `EXCEPT` treats `NULL`s as equal. This explains why the following query:
```sql
(SELECT 1 UNION SELECT NULL) EXCEPT SELECT NULL;
````
returns a single row with the value `1`. This behavior is consistent with other relational databases such as PostgreSQL.
The following equivalent query, which wraps the same subquery, also returns `1`:
```sql
SELECT *
FROM ((SELECT 1 UNION SELECT NULL) EXCEPT SELECT NULL) T;
```
Given this behavior, one would reasonably expect the next query to return `TRUE`, since the set produced by the subquery should be `(1)`, and `2 NOT IN (1)` is logically true:
```sql
SELECT 2 NOT IN ((SELECT 1 UNION SELECT NULL) EXCEPT SELECT NULL);
```
However, this query returns `NULL`. We assume this is because, in this case, the subquery unexpectedly evaluates to two rows—`(1, NULL)`—instead of just `(1)` as in the previous examples.
A similar issue arises with `INTERSECT`:
```sql
SELECT 2 NOT IN ((SELECT 1 UNION SELECT NULL) INTERSECT SELECT 1);
```
Other operators, such as `<> ALL`, exhibit the same inconsistency.
Below the execution plans for two of the test cases above:
```
mysql> explain (SELECT 1 UNION SELECT NULL) EXCEPT SELECT NULL;
+----+---------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+---------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 4 | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
| 3 | EXCEPT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 5 | EXCEPT RESULT | <except4,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+---------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
mysql> explain SELECT 2 NOT IN ((SELECT 1 UNION SELECT NULL) EXCEPT SELECT NULL);
+----+---------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+---------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 3 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 5 | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
| 4 | EXCEPT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 6 | EXCEPT RESULT | <except5,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+---------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
```
How to repeat:
Please use the statements above.
(Test cases also available at: [https://www.db-fiddle.com/f/rTmdrRm4x3NRm1LRnUqxEp/0](https://www.db-fiddle.com/f/rTmdrRm4...))
