Details
Major 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 |
|
+----+-----+
|
【实际结果】
空
Attachments
Activity
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)
|
请尽快处理