Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
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.