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

Using NAME_CONST() (or executing query from the stored procedure and referring to a local variable) changes the plan and may make execution slower

Details

    Description

      Consider the following simple table:

      Yuliyas-Air:maria10.6 Valerii$ bin/mysql test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 3
      Server version: 10.6.18-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> select * from tt1;
      +------+------+------+
      | id   | c1   | c2   |
      +------+------+------+
      |    1 |    3 | 1    |
      |    2 |    2 | 3    |
      +------+------+------+
      2 rows in set (0.014 sec)
       
      MariaDB [test]> show create table tt1\G
      *************************** 1. row ***************************
             Table: tt1
      Create Table: CREATE TABLE `tt1` (
        `id` int(11) DEFAULT NULL,
        `c1` int(11) DEFAULT NULL,
        `c2` varchar(100) DEFAULT NULL,
        KEY `c1` (`c1`),
        KEY `c2` (`c2`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
      1 row in set (0.004 sec)
       
      MariaDB [test]> insert into tt1 select * from tt1;
      Query OK, 2 rows affected (0.016 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into tt1 select * from tt1;
      Query OK, 4 rows affected (0.003 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      ...
       
      MariaDB [test]> insert into tt1 select * from tt1;
      Query OK, 32768 rows affected (0.297 sec)
      Records: 32768  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> update tt1 set c1 = 4 limit 1;
      Query OK, 1 row affected (0.012 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> select c1, count(*) from tt1 group by c1;
      +------+----------+
      | c1   | count(*) |
      +------+----------+
      |    2 |    32768 |
      |    3 |    32767 |
      |    4 |        1 |
      +------+----------+
      3 rows in set (0.042 sec)
      

      The following queries get expected execution plans:

      MariaDB [test]> explain select * from tt1 where c1 = 3;
      +------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
      |    1 | SIMPLE      | tt1   | ALL  | c1            | NULL | NULL    | NULL | 65758 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> explain select * from tt1 where c1 = 4;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      |    1 | SIMPLE      | tt1   | ref  | c1            | c1   | 5       | const | 1    |       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      1 row in set (0.003 sec)
       
      MariaDB [test]> explain select * from tt1 where c1 = '4';
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      |    1 | SIMPLE      | tt1   | ref  | c1            | c1   | 5       | const | 1    |       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------+
      1 row in set (0.000 sec)
      

      but if we add name_const() as it happens when the query is executed from the stored routine the plan changes to use index condition pushdown:

      MariaDB [test]> explain select * from tt1 where c1 = name_const('a',4);
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      |    1 | SIMPLE      | tt1   | ref  | c1            | c1   | 5       | const | 1    | Using index condition |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      1 row in set (0.004 sec)
       
      MariaDB [test]> explain select * from tt1 where c1 = name_const('a','4');
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      |    1 | SIMPLE      | tt1   | ref  | c1            | c1   | 5       | const | 1    | Using index condition |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      1 row in set (0.000 sec)
      

      which is strange and not expected. Moreover, we may note somewhat slower execution as a result:

      MariaDB [test]> analyze format=json select * from tt1 where c1 = 4\G
      *************************** 1. row ***************************
      ANALYZE: {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 2.356,
          "table": {
            "table_name": "tt1",
            "access_type": "ref",
            "possible_keys": ["c1"],
            "key": "c1",
            "key_length": "5",
            "used_key_parts": ["c1"],
            "ref": ["const"],
            "r_loops": 1,
            "rows": 1,
            "r_rows": 1,
            "r_table_time_ms": 0.203,
            "r_other_time_ms": 0.01,
            "r_engine_stats": {
              "pages_accessed": 4
            },
            "filtered": 100,
            "r_filtered": 100
          }
        }
      }
      1 row in set (0.004 sec)
       
      MariaDB [test]> analyze format=json select * from tt1 where c1 = name_const('a',4)\G
      *************************** 1. row ***************************
      ANALYZE: {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 2.423,
          "table": {
            "table_name": "tt1",
            "access_type": "ref",
            "possible_keys": ["c1"],
            "key": "c1",
            "key_length": "5",
            "used_key_parts": ["c1"],
            "ref": ["const"],
            "r_loops": 1,
            "rows": 1,
            "r_rows": 1,
            "r_table_time_ms": 2.396,
            "r_other_time_ms": 0.02,
            "r_engine_stats": {
              "pages_accessed": 4
            },
            "filtered": 100,
            "r_filtered": 100,
            "index_condition": "tt1.c1 = 4"
          }
        }
      }
      1 row in set (0.003 sec)
      

      Why all these happens and can we force the plans to be the same for such cases?

      Attachments

        Activity

          When trying to see if we could always remove NAME_CONST, I've stumbled on a case where removing it creates issues even in the WHERE clause:

          create procedure p1(var1 varchar(10)) 
          select coercibility(var1);
          call p1('abc');
          coercibility(var1)
          2
          

          SP variable has coercibility =2.

          COERCIBILITY(NAME_CONST('name','test'))
          2
          

          NAME_CONST has the same, good.
          String literal has:

          SELECT COERCIBILITY('test');
          COERCIBILITY('test')
          4
          

          Moreover, one can get NAME_CONST with string literals with explicit collation (which have COERCIBILITY=0):

          --source include/have_log_bin.inc
           
          create table t1 (
            a varchar(100) collate utf8_unicode_ci,
            b int
          );
          insert into t1 values ('foo', 1),('bar', 1);
           
          create procedure p1(var1 varchar(10)) 
            update t1 set b=b+1 where a=var1;
           
          call p1('foo');
          drop procedure p1;
           
          drop table t1;
          show binlog events;
          

          gives

          master-bin.000001       1003    Query   1       1158    use `test`; 
          update t1 set b=b+1 where a= NAME_CONST('var1',_latin1'foo' COLLATE 'latin1_swedish_ci')
          

          Trying to see how replication slave would work:

          --source include/have_log_bin.inc
          create table t1 (
            a varchar(100) collate utf8_unicode_ci,
            b int
          );
          insert into t1 values ('foo', 1),('bar', 1);
           
          explain format=json
          update t1 set b=b+1 where a= NAME_CONST('var1',_latin1'foo' COLLATE 'latin1_swedish_ci');
           
          drop table t1;
          

          before this MDEV, I get:

                "attached_condition": "t1.a = convert(NAME_CONST('var1',_latin1'foo' collate latin1_swedish_ci) using utf8mb3)"
          

          after this MDEV, I get:

          mysqltest: At line 8: query 'explain format=json 
          update t1 set b=b+1 where a= NAME_CONST('var1',_latin1'foo' COLLATE 'latin1_swedish_ci')' failed: ER_CANT_AGGREGATE_2COLLATIONS (1267): Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='
          

          Gosselin, any thoughts?

          psergei Sergei Petrunia added a comment - When trying to see if we could always remove NAME_CONST, I've stumbled on a case where removing it creates issues even in the WHERE clause: create procedure p1(var1 varchar (10)) select coercibility(var1); call p1( 'abc' ); coercibility(var1) 2 SP variable has coercibility =2. COERCIBILITY(NAME_CONST('name','test')) 2 NAME_CONST has the same, good. String literal has: SELECT COERCIBILITY('test'); COERCIBILITY('test') 4 Moreover, one can get NAME_CONST with string literals with explicit collation (which have COERCIBILITY=0): --source include/have_log_bin.inc   create table t1 ( a varchar (100) collate utf8_unicode_ci, b int ); insert into t1 values ( 'foo' , 1),( 'bar' , 1);   create procedure p1(var1 varchar (10)) update t1 set b=b+1 where a=var1;   call p1( 'foo' ); drop procedure p1;   drop table t1; show binlog events; gives master-bin.000001 1003 Query 1 1158 use `test`; update t1 set b=b+1 where a= NAME_CONST('var1',_latin1'foo' COLLATE 'latin1_swedish_ci') Trying to see how replication slave would work: --source include/have_log_bin.inc create table t1 ( a varchar (100) collate utf8_unicode_ci, b int ); insert into t1 values ( 'foo' , 1),( 'bar' , 1);   explain format=json update t1 set b=b+1 where a= NAME_CONST( 'var1' ,_latin1 'foo' COLLATE 'latin1_swedish_ci' );   drop table t1; before this MDEV, I get: "attached_condition": "t1.a = convert(NAME_CONST('var1',_latin1'foo' collate latin1_swedish_ci) using utf8mb3)" after this MDEV, I get: mysqltest: At line 8: query 'explain format=json update t1 set b=b+1 where a= NAME_CONST('var1',_latin1'foo' COLLATE 'latin1_swedish_ci')' failed: ER_CANT_AGGREGATE_2COLLATIONS (1267): Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '=' Gosselin , any thoughts?

          Got questions on the updated pull request.

          psergei Sergei Petrunia added a comment - Got questions on the updated pull request.
          psergei Sergei Petrunia added a comment - - edited

          The patch uses

             thd->change_item_tree(ref, value_item);
          

          call.
          Should it do this or just do ?

            *ref=value_item;
          

          instead?

          Discussed this with sanja_byelkin. Take-aways:
          change_item_tree() is to be used when we want/need the change to be rolled back at the end of execution.
          In our case, this seems to be unnecessary. Although it's not harmful, either.

          or wait.. what if our argument is Item_func_set_collation...

          psergei Sergei Petrunia added a comment - - edited The patch uses thd->change_item_tree( ref , value_item); call. Should it do this or just do ? * ref =value_item; instead? Discussed this with sanja_byelkin . Take-aways: change_item_tree() is to be used when we want/need the change to be rolled back at the end of execution. In our case, this seems to be unnecessary. Although it's not harmful, either. or wait.. what if our argument is Item_func_set_collation...
          Gosselin Dave Gosselin added a comment -

          If there's no harm in leaving it as is, then let's leave it, especially if that's the more general case anyhow.

          Gosselin Dave Gosselin added a comment - If there's no harm in leaving it as is, then let's leave it, especially if that's the more general case anyhow.

          Review input provided. Almost there.

          psergei Sergei Petrunia added a comment - Review input provided. Almost there.

          People

            Gosselin Dave Gosselin
            valerii Valerii Kravchuk
            Votes:
            1 Vote for this issue
            Watchers:
            11 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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