Bug #102291 Subquery produces wrong result when set big_tables = 1
Submitted: 19 Jan 2021 9:17 Modified: 19 Jan 2021 9:43
Reporter: xiaoyang chen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0, 8.0.22, 8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[19 Jan 2021 9:17] xiaoyang chen
Description:
When setting big_tables = 1, subquery with LIMT/OFFSET may produce wrong result. 

```
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2);
CREATE TABLE t0 AS SELECT * FROM t1;
CREATE TABLE t3(a INT, b INT);
INSERT INTO t3 VALUES (1,3), (2,3);
ANALYZE TABLE t1, t2, t0, t3;

set big_tables = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t1 join (select count(a) as cnt from t2 union select 1 limit 1, 1) as derived_1_2(cnt) on (true) where t1.a > derived_1_2.cnt;
+------+
| a    |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)

mysql> select a from t1 join (select count(a) as cnt from t2 union select 1 limit 1, 1) as derived_1_2(cnt) on (true) where t1.a > derived_1_2.cnt;
+------+
| a    |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.01 sec)

```

How to repeat:
Follow the above SQL, one can repeat this problem.
[19 Jan 2021 9:17] xiaoyang chen
The second query is 

```
mysql> set big_tables = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t1 join (select count(a) as cnt from t2 union select 1 limit 1, 1) as derived_1_2(cnt) on (true) where t1.a > derived_1_2.cnt;
+------+
| a    |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.01 sec)
```
[19 Jan 2021 9:43] MySQL Verification Team
Hello xiaoyang chen,

Thank you for the report and test case.
Verified as described. 

regards,
Umesh