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

different results when using value of optimizer_use_condition_selectivity=4 and =1

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2
    • Fix Version/s: 10.1.32
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      10.1.32

      Description

      from t/type_time.test

      SET @@old_mode=zero_date_time_cast;
      CREATE TABLE t1 (a TIME);
      INSERT INTO t1 VALUES ('0000-00-00 10:20:30'),('0000-00-00 10:20:31');
      INSERT INTO t1 VALUES ('0000-00-01 10:20:30'),('0000-00-01 10:20:31');
      INSERT INTO t1 VALUES ('31 10:20:30'),('32 10:20:30'),('33 10:20:30'),('34 10:20:30');
       
      SET @@optimizer_use_condition_selectivity=1;
      SELECT * FROM t1 WHERE a='0000-00-01 10:20:30';
      SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
       
      SET @@optimizer_use_condition_selectivity=4;
      SELECT * FROM t1 WHERE a='0000-00-01 10:20:30';
      SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
      

      MariaDB [test]> SET @@optimizer_use_condition_selectivity=1;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' ;
      +----------+
      | a        |
      +----------+
      | 34:20:30 |
      +----------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SET @@optimizer_use_condition_selectivity=4;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1 WHERE a='0000-00-01 10:20:30';
      Empty set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                alice Alice Sherepa
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: