[MDEV-30066] (limit + offset) union all (...) limit = incorrect result Created: 2022-11-22  Updated: 2022-12-02  Resolved: 2022-12-02

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.3.38, 10.4.28

Type: Bug Priority: Major
Reporter: niezhibiao Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: not-10.5, not-10.6
Environment:

ubuntu 16.04.4 LTS
MariaDB 10.4.6


 Description   

【重现步骤】
1、创建表 t1，插入数据

create table t1(id int primary key auto_increment, c1 int) engine=innodb;
 
insert into t1(c1) values(1),(2),(3);

2、执行以下查询

(select * from t1 where c1>=1 limit 2,1) union ALL (select * from t1 where c1>1 order by c1 desc);
 

+----+------+
 
| id | c1 |
 
+----+------+
 
| 3 | 3 |
 
| 2 | 2 |
 
| 3 | 3 |
 
+----+------+

3、加 limit ，检查结果

(select * from t1 where c1>=1 limit 2,1) union ALL (select * from t1 where c1>1 order by c1 desc) limit 2;

【预期结果】

+----+------+
 
| id | c1 |
 
+----+------+
 
| 3 | 3 |
 
| 2 | 2 |
 
+----+-----+

【实际结果】
空

 Comments   
Comment by niezhibiao [ 2022-11-22 ]

请尽快处理

Comment by Alice Sherepa [ 2022-11-22 ]

Thank you very much for the report!
It is repeatable on MariaDB 10.3,10.4, not on 10.5+, the problem is in an offset - in the example -offset 2 +limit 2 results in zero rows.

MariaDB [test]> create table t1(c1 int) ;
 
Query OK, 0 rows affected (0,059 sec)
 
 
 
MariaDB [test]> insert into t1 values(1),(2),(3);
 
Query OK, 3 rows affected (0,012 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> (select c1 from t1 limit 2,1) union ALL (select c1 from t1) limit 6;
 
+------+
 
| c1   |
 
+------+
 
|    3 |
 
|    1 |
 
|    2 |
 
|    3 |
 
+------+
 
4 rows in set (0,014 sec)
 
 
 
MariaDB [test]> (select c1 from t1 limit 2,1) union ALL (select c1 from t1) limit 3;
 
+------+
 
| c1   |
 
+------+
 
|    3 |
 
+------+
 
1 row in set (0,001 sec)

Comment by Oleksandr Byelkin [ 2022-11-29 ]

OK to push

Generated at Thu Feb 08 10:13:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.