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

session optimizer_use_condition_selectivity=5 causing SQL Error (1918): Encountered illegal value '' when converting to DECIMAL

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0, 10.1
    • Fix Version/s: 10.0.35
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      docker mariadb:latest container
    • Sprint:
      10.2.13

      Description

      select version(),@@histogram_size, @@use_stat_tables, @@optimizer_use_condition_selectivity\G
                                  version(): 10.1.12-MariaDB-1~jessie
                           @@histogram_size: 127
                          @@use_stat_tables: PREFERABLY
      @@optimizer_use_condition_selectivity: 5
      

      I'm using docker mariadb:latest container with

      histogram_size=127
      use_stat_tables=preferably
      optimizer_use_condition_selectivity=5
      in [mysqld] section.
      

      drop table if exists a;
      create table a (id int(10),cost decimal(9,2)) engine=innodb;
      ANALYZE TABLE a PERSISTENT FOR ALL;
      drop table if exists b;
      create temporary table b (id int);
      insert into b (id) select id from a where cost > 0;
      

      Last query producing warnings:

      Query OK, 0 rows affected, 3 warnings (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 3
       
      MariaDB [test]> show warnings;
      +---------+------+---------------------------------------------------------+
      | Level   | Code | Message                                                 |
      +---------+------+---------------------------------------------------------+
      | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
      | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
      | Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
      

      Warnings are not generated for the sequence (select query added before insert select):

      drop table if exists a;
      create table a (id int(10),cost decimal(9,2)) engine=innodb;
      ANALYZE TABLE a PERSISTENT FOR ALL;
      drop table if exists b;
      create temporary table b (id int);
      select id from a where cost > 0;
      insert into b (id) select id from a where cost > 0;
      

      Also warnings are not generated normal (not temporary) table b.
      set session optimizer_use_condition_selectivity=x; where x <= 2 also eliminates warnings.

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              ihanick Nickolay Ihalainen
            • Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: