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

Optimizer does wrong cast from number to string

    XMLWordPrintable

Details

    Description

      Optimizer does a wrong cast from column string to number instead of constant number to string:

      source sales_flat_order_dump.sql
      analyze table sales_flat_order;
       
      explain  
      SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (`increment_id` >= 238900 AND `increment_id` <= 238907)
      ;
      +------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
      | id   | select_type | table      | type | possible_keys                     | key  | key_len | ref  | rows   | Extra       |
      +------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
      |    1 | SIMPLE      | main_table | ALL  | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | NULL | NULL    | NULL | 260559 | Using where |
      +------+-------------+------------+------+-----------------------------------+------+---------+------+--------+-------------+
      
      

      Execution time:
      3 rows in set (1.467 sec)

      explain  
      SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (`increment_id` >= '238900' AND `increment_id` <= '238907')
      ;
      +------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
      | id   | select_type | table      | type  | possible_keys                     | key                               | key_len | ref  | rows | Extra                 |
      +------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
      |    1 | SIMPLE      | main_table | range | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | UNQ_SALES_FLAT_ORDER_INCREMENT_ID | 153     | NULL | 3    | Using index condition |
      +------+-------------+------------+-------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------+
      
      

      Execution time:
      3 rows in set (0.002 sec)

      This is reproducible at will. See example attached.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              oli Oli Sennhauser
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.