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

optimizer stops working inside stored procedure in another database

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            philip_38 Philip orleans
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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