Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.9
-
None
-
Linux
Description
I have a table with two fields
CREATE TABLE `shortlrn` ( |
`did` varchar(10) NOT NULL, |
`lrn` int(11) unsigned DEFAULT NULL, |
PRIMARY KEY (`did`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 | |
I write a simple stored procedure
show create procedure querytest;
|
+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
|
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
|
+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
|
| querytest | ONLY_FULL_GROUP_BY | CREATE DEFINER=`root`@`%` PROCEDURE `querytest`(IN didv VARCHAR(20))
|
BEGIN
|
DECLARE didx varchar(10) DEFAULT RIGHT(didv,10);
|
EXPLAIN select lrn from lrn.shortlrn where did=didx;
|
END | latin1 | latin1_swedish_ci | latin1_swedish_ci
|
and the explanation is correct, it uses the primary key
now I create the same exact stored procedure in another database, and the optimizer never finds the proper key, and even if I use force index or force key, it takes forever to find the data, maybe hours:
+------+-------------+----------+------+---------------+------+---------+------+-----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+------+---------------+------+---------+------+-----------+-------------+
|
| 1 | SIMPLE | shortlrn | ALL | NULL | NULL | NULL | NULL | 871477749 | Using where |
|
+------+-------------+----------+------+---------------+------+---------+------+-----------+-------------+
|
So the optimizer cannot function when a query is fired from a stored procedure in a different database, only on its own database?
This a big bug
I can give Elena Stepanova access to my box remotely so she can run a test.