[#MDEV-25441] WITH TIES is not respected with SQL_BUFFER

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)

[MDEV-25441] WITH TIES is not respected with SQL_BUFFER_RESULT and constant in ORDER BY Created: 2021-04-17 Updated: 2021-05-08 Resolved: 2021-05-08
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	N/A
Fix Version/s:	10.6.0

[MDEV-25441] WITH TIES is not respected with SQL_BUFFER_RESULT and constant in ORDER BY Created: 2021-04-17 Updated: 2021-05-08 Resolved: 2021-05-08