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

Conditions with SP local variables are not pushed into derived table

Details

    Description

      How to reproduce:

      CREATE TABLE `x`(
        `i` INT,
        `i2` INT,
        PRIMARY KEY(`i`),
        KEY (`i2`)
       );
      INSERT INTO 
          `x` (
              SELECT 
                  seq, 
                  FLOOR(0 + RAND() * (1000000 +1))
              FROM 
                  seq_1_to_10000000
              );
      DELIMITER $$
      CREATE PROCEDURE `testSp`()
          MODIFIES SQL DATA
      BEGIN
              DECLARE `indexId` INT DEFAULT 71;
              SELECT
                  `i2`
              FROM
                  (
                      SELECT
                          `x`.*,
                          ROW_NUMBER() OVER (
                              PARTITION BY `i2`
                              ORDER BY `i` ASC
                          ) AS `rowNumber`
                      FROM
                          `x`
                  ) AS `iq`
              WHERE 
                  `iq`.`i2` = `indexId`
              LIMIT 1;
          END$$
      DELIMITER ;
      

      If you run this procedure, it takes long time and performs a table scan instead of using the pushed down condition:

      MariaDB [tflt]> call testSp();
      +------+
      | i2   |
      +------+
      |   71 |
      +------+
      1 row in set (43.560 sec)
      

      This is also visible using SHOW EXPLAIN:

      MariaDB [(none)]> show explain for 16;
      +------+-------------+------------+-------+---------------+------+---------+------+---------+------------------------------+
      | id   | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows    | Extra                        |
      +------+-------------+------------+-------+---------------+------+---------+------+---------+------------------------------+
      |    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL | 9983371 | Using where                  |
      |    2 | DERIVED     | x          | index | NULL          | i2   | 5       | NULL | 9983371 | Using index; Using temporary |
      +------+-------------+------------+-------+---------------+------+---------+------+---------+------------------------------+
      2 rows in set, 1 warning (0.001 sec)
      

      If you run the query outside the stored procedure, condition pushdown works as expected, and query returns almost immediately.

      Explain from outside:

      +------+-------------+------------+------+---------------+------+---------+-------+------+------------------------------+
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref   | rows | Extra                        |
      +------+-------------+------------+------+---------------+------+---------+-------+------+------------------------------+
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL  | 16   | Using where                  |
      |    2 | DERIVED     | x          | ref  | i2            | i2   | 5       | const | 16   | Using index; Using temporary |
      +------+-------------+------------+------+---------------+------+---------+-------+------+------------------------------+
      2 rows in set (0.005 sec)
      

      Sample execution:

      SELECT
                  `i2`
              FROM
                  (
                      SELECT
                          `x`.*,
                          ROW_NUMBER() OVER (
                              PARTITION BY `i2`
                              ORDER BY `i` ASC
                          ) AS `rowNumber`
                      FROM
                          `x`
                  ) AS `iq`
              WHERE 
                  `iq`.`i2` = 71 LIMIT 1;
      +------+
      | i2   |
      +------+
      |   71 |
      +------+
      1 row in set (0.001 sec)
      

      It has been noted that the problem seems tied to the use of a local variable in the stored procedure. Using a constant or a session variable does not exhibit the problem.

      Attachments

        Activity

          igor Igor Babaev added a comment -

          A fix for this bug was pushed into 10.5. It has to be merged upstream as it is.

          igor Igor Babaev added a comment - A fix for this bug was pushed into 10.5. It has to be merged upstream as it is.

          People

            igor Igor Babaev
            rpizzi Rick Pizzi (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.