Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25441

WITH TIES is not respected with SQL_BUFFER_RESULT and constant in ORDER BY

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.6.0
    • Component/s: Optimizer
    • Labels:
      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

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: