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

Non-optimal execution of WHERE num_field=const AND num_field=const

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Data types, Optimizer
    • None

    Description

      Usually, WHERE condition gets optimized by removing reundant parts. But in some cases optimization does not work.

      Good example: int_field=int_const1 AND int_field=int_const1

      CREATE OR REPLACE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=1;
      		 
      SHOW WARNINGS;


      +-------+------+--------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                  |
      		 
      +-------+------+--------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 |
      		 
      +-------+------+--------------------------------------------------------------------------+

      The redundant part was correctly removed.

      Good example: int_field=int_const1 AND int_field=int_const2

      CREATE OR REPLACE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=2;
      		 
      SHOW WARNINGS;


      +-------+------+--------------------------------------------------------+
      		 
      | Level | Code | Message                                                |
      		 
      +-------+------+--------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 |
      		 
      +-------+------+--------------------------------------------------------+

      The condition was correctly detected as impossible.

      Good example: decimal_field=decimal_const1 AND decimal_field=int_const1

      CREATE OR REPLACE TABLE t1 (a DECIMAL(10,3));
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1.0 AND a=1;
      		 
      SHOW WARNINGS;


      +-------+------+----------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                    |
      		 
      +-------+------+----------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.0 |
      		 
      +-------+------+----------------------------------------------------------------------------+

      The redundant part was correctly removed.

      Good example: decimal_field=decimal_const1 AND decimal_field=int_const2

      CREATE OR REPLACE TABLE t1 (a DECIMAL(10,3));
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1.0 AND a=2;
      		 
      SHOW WARNINGS;


      +-------+------+--------------------------------------------------------+
      		 
      | Level | Code | Message                                                |
      		 
      +-------+------+--------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 |
      		 
      +-------+------+--------------------------------------------------------+

      The condition was correctly detected as impossible.

      Bad example: int_field=int_const1 AND int_field=decimal_const1

      CREATE OR REPLACE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=1.0;
      		 
      SHOW WARNINGS;


      +-------+------+----------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                            |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = 1.0 |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------+

      The redundant part was not removed.

      Bad example: int_field=int_const1 AND int_field=decimal_const2

      CREATE OR REPLACE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=2.0;
      		 
      SHOW WARNINGS;


      +-------+------+----------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                            |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = 2.0 |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------+

      The condition was not detected as impossible.

      CREATE OR REPLACE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=1.1;
      		 
      SHOW WARNINGS;


      +-------+------+----------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                            |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = 1.1 |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------+

      The condition was not detected as impossible.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18898 SELECT using wrong index when using operator IN with mixed types

          • Major - Major loss of function.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18968 Both (WHERE 0.1) and (WHERE NOT 0.1) return empty set

          • Major - Major loss of function.
          • Closed

          Activity

            There are no comments yet on this issue.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.