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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL)
    • 10.0.29
    • Optimizer
    • 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

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova made changes -
          Description {noformat}
          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;
          {noformat}

          {noformat:title=Actual result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          Empty set (0.90 sec)
          {noformat}
          {noformat:title=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)
          {noformat}

          {noformat:tite=Expected result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          +------+
          | col1 |
          +------+
          | c |
          +------+
          1 row in set (4.71 sec)
          {noformat}           		{noformat}
          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;
          {noformat}

          {noformat:title=Actual result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          Empty set (0.90 sec)
          {noformat}
          {noformat:title=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)
          {noformat}

          {noformat:title=Expected result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          +------+
          | col1 |
          +------+
          | c |
          +------+
          1 row in set (4.71 sec)
          {noformat}
          igor Igor Babaev (Inactive) made changes -
          Assignee Sergei Petrunia [ psergey ] Igor Babaev [ igor ]
          igor Igor Babaev (Inactive) made changes -
          Description {noformat}
          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;
          {noformat}

          {noformat:title=Actual result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          Empty set (0.90 sec)
          {noformat}
          {noformat:title=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)
          {noformat}

          {noformat:title=Expected result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          +------+
          | col1 |
          +------+
          | c |
          +------+
          1 row in set (4.71 sec)
          {noformat}           		{noformat}
          set use_stat_tables='preferably';
          set optimizer_use_condition_selectivity=3;

          create table t1(col1 text);

          insert into t1 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h');
          analyze table t1;

          select * from t1 where col1 > 'b' and col1 < 'd';

          drop table t1;
          {noformat}

          {noformat:title=Actual result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          Empty set (0.90 sec)
          {noformat}
          {noformat:title=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)
          {noformat}

          {noformat:title=Expected result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          +------+
          | col1 |
          +------+
          | c |
          +------+
          1 row in set (4.71 sec)
          {noformat}
          igor Igor Babaev (Inactive) made changes -
          Description {noformat}
          set use_stat_tables='preferably';
          set optimizer_use_condition_selectivity=3;

          create table t1(col1 text);

          insert into t1 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h');
          analyze table t1;

          select * from t1 where col1 > 'b' and col1 < 'd';

          drop table t1;
          {noformat}

          {noformat:title=Actual result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          Empty set (0.90 sec)
          {noformat}
          {noformat:title=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)
          {noformat}

          {noformat:title=Expected result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          +------+
          | col1 |
          +------+
          | c |
          +------+
          1 row in set (4.71 sec)
          {noformat}           		{noformat}
          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;
          {noformat}

          {noformat:title=Actual result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          Empty set (0.90 sec)
          {noformat}
          {noformat:title=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)
          {noformat}

          {noformat:title=Expected result}
          MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd';
          +------+
          | col1 |
          +------+
          | c |
          +------+
          1 row in set (4.71 sec)
          {noformat}
          igor Igor Babaev (Inactive) made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          igor Igor Babaev (Inactive) added a comment -

          The fix for this bug was pushed into 10.0 tree.

          igor Igor Babaev (Inactive) added a comment - The fix for this bug was pushed into 10.0 tree.
          igor Igor Babaev (Inactive) made changes -
          Fix Version/s 10.0.29 [ 22312 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 74127 ] MariaDB v4 [ 150157 ]

          People

            igor Igor Babaev (Inactive)
            elenst Elena Stepanova
            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.