Bug #110244 Nesting queries in a projection results in different output
Submitted: 1 Mar 2023 3:46 Modified: 1 Mar 2023 6:28
Reporter: Pinhan Zhao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[1 Mar 2023 3:46] Pinhan Zhao
Description:
With the following database:

```
CREATE TABLE FRIENDSHIP (
  USER1_ID INTEGER, 
  USER2_ID INTEGER,
  PRIMARY KEY (USER1_ID, USER2_ID)
);
INSERT INTO FRIENDSHIP VALUES (1, 0);

CREATE TABLE LIKES (
  USER_ID INTEGER, 
  PAGE_ID INTEGER,
  PRIMARY KEY (USER_ID, PAGE_ID)
);
INSERT INTO LIKES VALUES (2, 0);
```

By executing the following query,
```
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B 
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID) 
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP 
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1);
```

the output is:

+---------+---------+
| USER_ID | PAGE_ID |
+---------+---------+
|    NULL |    NULL |
+---------+---------+

However, if we nest this SQL query just in a SELECT * FROM:

```
SELECT * FROM (
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B 
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID) 
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP 
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1)
) AS T;
```

The result becomes empty:
Empty set (0.00 sec)

I think the second query should produce the same output as the first query?

How to repeat:
Example database:
CREATE TABLE FRIENDSHIP (
  USER1_ID INTEGER, 
  USER2_ID INTEGER,
  PRIMARY KEY (USER1_ID, USER2_ID)
);
INSERT INTO FRIENDSHIP VALUES (1, 0);

CREATE TABLE LIKES (
  USER_ID INTEGER, 
  PAGE_ID INTEGER,
  PRIMARY KEY (USER_ID, PAGE_ID)
);
INSERT INTO LIKES VALUES (2, 0);

Q1:
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B 
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID) 
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP 
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1);

Q2:
SELECT * FROM (
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B 
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID) 
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP 
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1)
) AS T;

The output of Q1 is different from Q2
[1 Mar 2023 6:28] MySQL Verification Team
Hello Luca Zhao,

Thank you for the report and test case.

regards,
Umesh