Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
Description
Maybe the query can be converted into something more meaningful, I just kept it minimal to demonstrate the problem. Or maybe it really only affects such useless queries, in this case feel free to decrease the priority.
CREATE TABLE t1 (a INT); |
INSERT INTO t1 WITH RECURSIVE cte AS (SELECT 1 AS f UNION ALL SELECT f+1 FROM cte WHERE f<10) SELECT * FROM cte; |
|
|
SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; |
SELECT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; |
The base query returns all 10 rows as expected. The one with SQL_BUFFER_RESULT returns 2 rows.
|
bb-10.6-refactor-limit-review 4bd13ff8 |
MariaDB [test]> SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; |
+---+ |
| f |
|
+---+ |
| 1 |
|
| 1 |
|
+---+ |
2 rows in set (0.001 sec) |
|
|
|
|
MariaDB [test]> SELECT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; |
+---+ |
| f |
|
+---+ |
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
| 1 |
|
+---+ |
10 rows in set (0.002 sec) |
Plans:
|
with SQL_BUFFER_RESULT |
MariaDB [test]> explain extended SELECT SQL_BUFFER_RESULT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using temporary | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------+ |
1 row in set, 1 warning (0.001 sec) |
|
|
MariaDB [test]> show warnings;
|
+-------+------+---------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+---------------------------------------------------------------------------------------------+ |
| Note | 1003 | select sql_buffer_result 1 AS `f` from `test`.`t1` order by '' fetch first 2 rows with ties | |
+-------+------+---------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
|
Base query |
MariaDB [test]> explain extended SELECT 1 AS f FROM t1 ORDER BY f FETCH NEXT 2 ROW WITH TIES; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ |
1 row in set, 1 warning (0.001 sec) |
|
|
MariaDB [test]> show warnings;
|
+-------+------+---------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+---------------------------------------------------------------------------+ |
| Note | 1003 | select 1 AS `f` from `test`.`t1` order by '' fetch first 2 rows with ties | |
+-------+------+---------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
Attachments
Issue Links
- relates to
-
MDEV-23908 Implement SELECT ... OFFSET ... FETCH ...
-
- Closed
-