Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Nested union with LIMIT handles incorrectly #49377

Closed
breezewish opened this issue Dec 12, 2023 · 3 comments · Fixed by #49421
Closed

Nested union with LIMIT handles incorrectly #49377

breezewish opened this issue Dec 12, 2023 · 3 comments · Fixed by #49421
Assignees

Comments

@breezewish
Copy link
Member

breezewish commented Dec 12, 2023

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

@breezewish breezewish added the type/bug The issue is confirmed as a bug. label Dec 12, 2023
@chrysan
Copy link
Contributor

chrysan commented Dec 12, 2023

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
Copy link
Contributor

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
Copy link
Contributor

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

@AilinKid AilinKid added component/parser and removed sig/planner SIG: Planner labels Dec 13, 2023
@jebter jebter added the sig/sql-infra SIG: SQL Infra label Dec 14, 2023
ti-chi-bot bot pushed a commit that referenced this issue Dec 15, 2023
AilinKid added a commit to AilinKid/tidb that referenced this issue Dec 19, 2023
ti-chi-bot bot pushed a commit that referenced this issue Dec 22, 2023
ti-chi-bot bot pushed a commit that referenced this issue Feb 20, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants