Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.4, 11.7(EOL)
-
MariaDB: reproduced with 10.x & 11.x, up to 11.5
OS: any
Description
New Description
A table's index is not considered as a "possible_key" (that is, a key in table that could be used to find rows in that table) to be
used during a prepared statement's execution when the statement's parameter is bound to that index column via a comparison, such as equality.
Preparation for repro:
CREATE TABLE my_table (id INT UNSIGNED AUTO_INCREMENT, type INT NOT NULL, PRIMARY KEY (id));
|
CREATE INDEX my_idx ON my_table (type);
|
INSERT INTO my_table (type) VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (2), (2), (2), (2), (2), (2), (2), (2), (3);
|
Reproduction of issue with a prepared statement:
PREPARE stmt FROM "EXPLAIN EXTENDED SELECT *
|
FROM (
|
SELECT * FROM my_table
|
UNION ALL
|
SELECT * FROM my_table
|
) q
|
WHERE q.type = ?;";
|
EXECUTE stmt USING (1);
|
+------+-------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 38 | 100.00 | Using where |
|
| 2 | DERIVED | my_table | index | NULL | my_idx | 4 | NULL | 19 | 100.00 | Using index |
|
| 3 | UNION | my_table | index | NULL | my_idx | 4 | NULL | 19 | 100.00 | Using index |
|
+------+-------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|
3 rows in set, 1 warning (0.001 sec)
|
Now compare with the same, but outside of a prepared statement:
EXPLAIN EXTENDED SELECT *
|
FROM (
|
SELECT * FROM my_table
|
UNION ALL
|
SELECT * FROM my_table
|
) q
|
WHERE q.type = 1;
|
+------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | Using where |
|
| 2 | DERIVED | my_table | ref | my_idx | my_idx | 4 | const | 9 | 100.00 | Using index |
|
| 3 | UNION | my_table | ref | my_idx | my_idx | 4 | const | 9 | 100.00 | Using index |
|
+------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|
3 rows in set, 1 warning (0.004 sec)
|
Old Description
Reproducer query:
SELECT * |
FROM ( |
SELECT * FROM my_table |
UNION ALL |
SELECT * FROM my_table |
) q
|
WHERE q.column_with_index = ?; |
I am using PHP to execute this query. Everything works fine when using PDO's emulated parameter bindings. The execution is fast and "EXPLAIN SELECT ..." reveals that the index on column "column_with_index" is being used.
Things change when I set "PDO::ATTR_EMULATE_PREPARES" to "false": When using MariaDB's native parameter binding protocol, the index is not being used.
This behavior changes again, depending on the exact parameter binding protocol + charset + database API combination.
I've posted my question to StackOverflow and somebody suspected this to be a bug in MariaDB.
The post can be found here: https://stackoverflow.com/q/79245146/1529133
I've also prepared a reproducer here:
https://phpize.online/sql/mariadb115/a1c61a1321ba2adfc689bd9e87e75ad9/php/php82/c602157f683d216f384ec3e56833df9b/