[MDEV-25217] optimizer stops working inside stored procedure in another database Created: 2021-03-22  Updated: 2021-03-30

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.5.9
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Philip orleans Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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.



 Comments   
Comment by Elena Stepanova [ 2021-03-28 ]

Could you please paste the output of SHOW CREATE DATABASE for both databases?

I think the difference is caused not by having the procedure in a different database, but having it in a database with a different charset (e.g. utf8<something> while your table is latin1).
Somewhat like this:

CREATE OR REPLACE TABLE `shortlrn` (
 
  `did` varchar(10) NOT NULL,
 
  `lrn` int(11) unsigned DEFAULT NULL,
 
  PRIMARY KEY (`did`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
INSERT INTO shortlrn SELECT CONCAT('s',seq), seq FROM seq_1_to_100;
 
 
 
CREATE OR REPLACE DATABASE db_latin1 CHARSET latin1;
 
CREATE OR REPLACE DATABASE db_utf8 CHARSET utf8;
 
 
 
 
 
DELIMITER $
 
 
 
CREATE OR REPLACE PROCEDURE db_latin1.querytest(IN didv VARCHAR(20))
 
BEGIN
 
  DECLARE didx varchar(10) DEFAULT RIGHT(didv,10);
 
  EXPLAIN select lrn FROM test.shortlrn WHERE did=didx;
 
END $
 
 
 
CREATE OR REPLACE PROCEDURE db_utf8.querytest(IN didv VARCHAR(20))
 
BEGIN
 
  DECLARE didx varchar(10) DEFAULT RIGHT(didv,10);
 
  EXPLAIN select lrn FROM test.shortlrn WHERE did=didx;
 
END $
 
 
 
DELIMITER ;
 
 
 
CALL db_latin1.querytest('s32');
 
CALL db_utf8.querytest('s32');

10.6 2f9a0146e1

 
MariaDB [test]> CALL db_latin1.querytest('s32');
 
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
 
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
 
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
 
|    1 | SIMPLE      | shortlrn | const | PRIMARY       | PRIMARY | 12      | const | 1    |       |
 
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
 
1 row in set (0.002 sec)
 
 
 
Query OK, 0 rows affected (0.003 sec)
 
 
 
MariaDB [test]> CALL db_utf8.querytest('s32');
 
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | shortlrn | ALL  | NULL          | NULL | NULL    | NULL | 100  | Using where |
 
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.002 sec)

Comment by Philip orleans [ 2021-03-29 ] 

show create database lrn;
 
+----------+----------------------------------------------------------------+
 
| Database | Create Database                                                |
 
+----------+----------------------------------------------------------------+
 
| lrn      | CREATE DATABASE `lrn` /*!40100 DEFAULT CHARACTER SET latin1 */ |
 
+----------+----------------------------------------------------------------+
 
1 row in set (0.000 sec)


MariaDB [lrn]> show create database asterisk;
 
+----------+-------------------------------------------------------------------+
 
| Database | Create Database                                                   |
 
+----------+-------------------------------------------------------------------+
 
| asterisk | CREATE DATABASE `asterisk` /*!40100 DEFAULT CHARACTER SET utf8 */ |
 
+----------+-------------------------------------------------------------------+

You are right. But this should not be the case.
How do you change the default character set? and why the optimizer completely loses it?
It seems to be a big bug anyway, maybe by design?.
I checked MsSQL and it works fine in all circumstances.

Comment by Philip orleans [ 2021-03-30 ]

One question, Elena. I need to install Mariadb from the installer, but as root, because this is a non-privileged container and I can't give rights to user mysql on /proc/, and it needs to read some stuff.
What should I do so the user mysql has full administrative rights on par with root?

Generated at Thu Feb 08 09:36:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.