Description:
Two tables containing identical data produce different query results depending on whether a `PRIMARY KEY` is defined.
When a table with a `PRIMARY KEY` is used, an `INTERSECT` query wrapped in a derived table unexpectedly returns a row.
However, the same query correctly returns an empty set when executed directly.
If the `PRIMARY KEY` is removed (while keeping the table data identical), the derived-table query also returns an empty set.
Since the underlying data is the same in both cases, the results should be identical.
This indicates that MySQL may incorrectly evaluate or optimize the `INTERSECT` operation inside a derived table when a `PRIMARY KEY` is present.
How to repeat:
Case 1: Table with `PRIMARY KEY`
```sql
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t0(
c0 FLOAT,
c1 DOUBLE PRIMARY KEY
);
INSERT INTO t0(c1) VALUES(1);
SELECT 1 AS c6
FROM (
SELECT tom2.c1 AS c1
FROM t0 AS tom2
INTERSECT
SELECT JSON_OBJECTAGG('2025','2025') AS c4
FROM t0
WHERE LEAST(X'68656C6C6F', X'68656C6C6F')
) AS tom6;
```
Result:
```
+----+
| c6 |
+----+
| 1 |
+----+
1 row in set
```
Direct execution of the `INTERSECT` query:
```sql
SELECT tom2.c1 AS c1
FROM t0 AS tom2
INTERSECT
SELECT JSON_OBJECTAGG('2025','2025') AS c4
FROM t0
WHERE LEAST(X'68656C6C6F', X'68656C6C6F');
```
Result:
```
Empty set
```
Case 2: Table without `PRIMARY KEY` (same data)
```sql
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t0(
c0 FLOAT,
c1 DOUBLE
);
INSERT INTO t0(c1) VALUES(1);
SELECT 1 AS c6
FROM (
SELECT tom2.c1 AS c1
FROM t0 AS tom2
INTERSECT
SELECT JSON_OBJECTAGG('2025','2025') AS c4
FROM t0
WHERE LEAST(X'68656C6C6F', X'68656C6C6F')
) AS tom6;
```
Result:
```
Empty set
```
The two tables contain identical data, so the query results should be the same.
Description: Two tables containing identical data produce different query results depending on whether a `PRIMARY KEY` is defined. When a table with a `PRIMARY KEY` is used, an `INTERSECT` query wrapped in a derived table unexpectedly returns a row. However, the same query correctly returns an empty set when executed directly. If the `PRIMARY KEY` is removed (while keeping the table data identical), the derived-table query also returns an empty set. Since the underlying data is the same in both cases, the results should be identical. This indicates that MySQL may incorrectly evaluate or optimize the `INTERSECT` operation inside a derived table when a `PRIMARY KEY` is present. How to repeat: Case 1: Table with `PRIMARY KEY` ```sql DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t0( c0 FLOAT, c1 DOUBLE PRIMARY KEY ); INSERT INTO t0(c1) VALUES(1); SELECT 1 AS c6 FROM ( SELECT tom2.c1 AS c1 FROM t0 AS tom2 INTERSECT SELECT JSON_OBJECTAGG('2025','2025') AS c4 FROM t0 WHERE LEAST(X'68656C6C6F', X'68656C6C6F') ) AS tom6; ``` Result: ``` +----+ | c6 | +----+ | 1 | +----+ 1 row in set ``` Direct execution of the `INTERSECT` query: ```sql SELECT tom2.c1 AS c1 FROM t0 AS tom2 INTERSECT SELECT JSON_OBJECTAGG('2025','2025') AS c4 FROM t0 WHERE LEAST(X'68656C6C6F', X'68656C6C6F'); ``` Result: ``` Empty set ``` Case 2: Table without `PRIMARY KEY` (same data) ```sql DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t0( c0 FLOAT, c1 DOUBLE ); INSERT INTO t0(c1) VALUES(1); SELECT 1 AS c6 FROM ( SELECT tom2.c1 AS c1 FROM t0 AS tom2 INTERSECT SELECT JSON_OBJECTAGG('2025','2025') AS c4 FROM t0 WHERE LEAST(X'68656C6C6F', X'68656C6C6F') ) AS tom6; ``` Result: ``` Empty set ``` The two tables contain identical data, so the query results should be the same.