Nested union with LIMIT handles incorrectly

[breezewish]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table employee (employee_id int, name varchar(20), dept_id int);

insert into employee values (1, 'Furina', 1), (2, 'Klee', 1), (3, 'Eula', 1), (4, 'Diluc', 2), (5, 'Tartaglia', 2);

select * from employee where dept_id = 1
union all
(
  select * from employee where dept_id = 1 order by employee_id
)
union all
(
  select * from employee where dept_id = 1
  union all
  (
    select * from employee where dept_id = 1 order by employee_id
  ) limit 1
);

2. What did you expect to see? (Required)

MySQL 8:

+-------------+--------+---------+
| employee_id | name   | dept_id |
+-------------+--------+---------+
|           1 | Furina |       1 |
|           2 | Klee   |       1 |
|           3 | Eula   |       1 |
|           1 | Furina |       1 |
|           2 | Klee   |       1 |
|           3 | Eula   |       1 |
|           1 | Furina |       1 |
+-------------+--------+---------+
7 rows in set (0.00 sec)

3. What did you see instead (Required)

+-------------+--------+---------+
| employee_id | name   | dept_id |
+-------------+--------+---------+
|           1 | Furina |       1 |
|           2 | Klee   |       1 |
|           3 | Eula   |       1 |
|           1 | Furina |       1 |
|           2 | Klee   |       1 |
|           3 | Eula   |       1 |
|           1 | Furina |       1 |
|           2 | Klee   |       1 |
|           3 | Eula   |       1 |
|           1 | Furina |       1 |
|           2 | Klee   |       1 |
|           3 | Eula   |       1 |
+-------------+--------+---------+
12 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

master

[chrysan]

Limit is missed in TiDB's plan:

+--------------------------------+---------+-----------+----------------+--------------------------------+
| id                             | estRows | task      | access object  | operator info                  |
+--------------------------------+---------+-----------+----------------+--------------------------------+
| Union_22                       | 0.02    | root      |                |                                |
| ├─TableReader_26               | 0.01    | root      |                | data:Selection_25              |
| │ └─Selection_25               | 0.01    | cop[tikv] |                | eq(test.employee.dept_id, 1)   |
| │   └─TableFullScan_24         | 5.00    | cop[tikv] | table:employee | keep order:false, stats:pseudo |
| ├─Sort_28                      | 0.01    | root      |                | test.employee.employee_id      |
| │ └─TableReader_32             | 0.01    | root      |                | data:Selection_31              |
| │   └─Selection_31             | 0.01    | cop[tikv] |                | eq(test.employee.dept_id, 1)   |
| │     └─TableFullScan_30       | 5.00    | cop[tikv] | table:employee | keep order:false, stats:pseudo |
| └─Union_34                     | 0.01    | root      |                |                                |
|   ├─TableReader_38             | 0.01    | root      |                | data:Selection_37              |
|   │ └─Selection_37             | 0.01    | cop[tikv] |                | eq(test.employee.dept_id, 1)   |
|   │   └─TableFullScan_36       | 5.00    | cop[tikv] | table:employee | keep order:false, stats:pseudo |
|   └─Sort_40                    | 0.01    | root      |                | test.employee.employee_id      |
|     └─TableReader_44           | 0.01    | root      |                | data:Selection_43              |
|       └─Selection_43           | 0.01    | cop[tikv] |                | eq(test.employee.dept_id, 1)   |
|         └─TableFullScan_42     | 5.00    | cop[tikv] | table:employee | keep order:false, stats:pseudo |
+--------------------------------+---------+-----------+----------------+--------------------------------+
16 rows in set (0.00 sec)

[AilinKid]

a minimal case

select 1,1,1 union all (   select * from employee where dept_id = 1   union all   (     select * from employee where dept_id = 1 order by employee_id   ) limit 1 );

mysql

mysql> select 1,1,1 union all (   select * from employee where dept_id = 1   union all   (     select * from employee where dept_id = 1 order by employee_id   ) limit 1 );
+------+--------+------+
| 1    | 1      | 1    |
+------+--------+------+
|    1 | 1      |    1 |
|    1 | Furina |    1 |
+------+--------+------+
2 rows in set (0.00 sec)

tidb

MySQL [test]> select 1,1,1 union all (   select * from employee where dept_id = 1   union all   (     select * from employee where dept_id = 1 order by employee_id   ) limit 1 );
+------+--------+------+
| 1    | 1      | 1    |
+------+--------+------+
|    1 | 1      |    1 |
|    1 | Furina |    1 |
|    2 | Klee   |    1 |
|    3 | Eula   |    1 |
|    1 | Furina |    1 |
|    2 | Klee   |    1 |
|    3 | Eula   |    1 |
+------+--------+------+
7 rows in set (0.001 sec)

[AilinKid]

seems parser already lost it's children (SetOprStmt) ‘s limit and order