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

Wrong result (missing rows) with TEXT field, optimizer_use_condition_selectivity >= 3

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.0
    • Fix Version/s: 10.0.29
    • Component/s: Optimizer
    • Labels:
      None

      Description

      set use_stat_tables='preferably';
      set optimizer_use_condition_selectivity=3;
       
      create table t1(col1 text);
       
      insert into t1 values ('a'),('b'),('c'),('d');
      analyze table t1;
       
      select * from t1 where col1 > 'b' and col1 < 'd';
       
      drop table t1;
      

      Actual result

      MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
      Empty set (0.90 sec)
      

      Plan

      MariaDB [test]> explain extended select * from t1 where col1 > 'b' and col1 < 'd';
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      1 row in set, 1 warning (1.29 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+--------------------------------------------------------------+
      | Level | Code | Message                                                      |
      +-------+------+--------------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where 0 |
      +-------+------+--------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      Expected result

      MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
      +------+
      | col1 |
      +------+
      | c    |
      +------+
      1 row in set (4.71 sec)
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: