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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0, 8.0.22, 8.0.23
• OS: Any
• 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