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

Unexpected impossible WHERE for a condition on a ZEROFILL field

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.1.8
    • Optimizer
    • None

    Description

      This script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a INT ZEROFILL);
      		 
      INSERT INTO t1 VALUES (128);
      		 
      SELECT * FROM t1 WHERE a=128;
      		 
      SELECT * FROM t1 WHERE hex(a)='80';

      correctly returns one row for both SELECT queries.

      if I join the two conditions using AND:

      SELECT * FROM t1 WHERE a=128 AND hex(a)='80';

      it unexpectedly returns Empty set.

      EXPLAIN for the above query tells "Impossible WHERE":

      MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      		 
      |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8728 Fix a number of problems in equal field and equal expression propagation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            jkavalik Jiri Kavalik added a comment - - edited 

            explain extended SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
            		 
            show warnings;
            		 
            +-------+------+--------------------------------------------------------+
            		 
            | Level | Code | Message                                                |
            		 
            +-------+------+--------------------------------------------------------+
            		 
            | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 |
            		 
            +-------+------+--------------------------------------------------------+

            and without zerofil

            explain extended SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
            		 
            +-------+------+------------------------------------------------------------------------------+
            		 
            | Level | Code | Message                                                                      |
            		 
            +-------+------+------------------------------------------------------------------------------+
            		 
            | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 128) |
            		 
            +-------+------+------------------------------------------------------------------------------+

            jkavalik Jiri Kavalik added a comment - - edited explain extended SELECT * FROM t1 WHERE a=128 AND hex(a)='80'; show warnings; +-------+------+--------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 | +-------+------+--------------------------------------------------------+ and without zerofil explain extended SELECT * FROM t1 WHERE a=128 AND hex(a)='80'; +-------+------+------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 128) | +-------+------+------------------------------------------------------------------------------+
            bar Alexander Barkov added a comment - - edited

            The problem happens because optimize_cond() when propagating equalities replaces the condition to:

            WHERE a=128 AND hex('0000000128')='80'

            which later evaluates to FALSE because HEX() returns hex representations of the string rather than hex representation of the original number 128.

            convert_zerofill_number_to_string() is the function which actually replaces Item_field to Item_string is .

            bar Alexander Barkov added a comment - - edited The problem happens because optimize_cond() when propagating equalities replaces the condition to: WHERE a=128 AND hex('0000000128')='80' which later evaluates to FALSE because HEX() returns hex representations of the string rather than hex representation of the original number 128. convert_zerofill_number_to_string() is the function which actually replaces Item_field to Item_string is .
            bar Alexander Barkov added a comment -

            This script demonstrates a similar problem:

            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a INT(6) ZEROFILL);
            		 
            INSERT INTO t1 VALUES (1);
            		 
            SELECT * FROM t1 WHERE a=1;
            		 
            SELECT * FROM t1 WHERE DES_ENCRYPT('test',a)=_latin1 0x8104A375CC9BD44E7C COLLATE latin1_bin; SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 0x8104A375CC9BD44E7C COLLATE latin1_bin;

            The first and the second SELECT return one row. The third SELECT returns empty set.
            EXPLAIN for the third SELECT:

            EXPLAIN SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 0x8104A375CC9BD44E7C COLLATE latin1_bin;

            returns

            +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
            		 
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
            		 
            |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+------------------+

            bar Alexander Barkov added a comment - This script demonstrates a similar problem: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT(6) ZEROFILL); INSERT INTO t1 VALUES (1); SELECT * FROM t1 WHERE a=1; SELECT * FROM t1 WHERE DES_ENCRYPT('test',a)=_latin1 0x8104A375CC9BD44E7C COLLATE latin1_bin; SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 0x8104A375CC9BD44E7C COLLATE latin1_bin; The first and the second SELECT return one row. The third SELECT returns empty set. EXPLAIN for the third SELECT: EXPLAIN SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 0x8104A375CC9BD44E7C COLLATE latin1_bin; returns +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
            bar Alexander Barkov added a comment -

            A similar example with WEIGHT_STRING:

            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a INT(6) ZEROFILL);
            		 
            INSERT INTO t1 VALUES (1);
            		 
            SELECT * FROM t1 WHERE a=1;
            		 
            SELECT * FROM t1 WHERE WEIGHT_STRING(a) IS NULL;
            		 
            SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS  NULL;

            bar Alexander Barkov added a comment - A similar example with WEIGHT_STRING: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT(6) ZEROFILL); INSERT INTO t1 VALUES (1); SELECT * FROM t1 WHERE a=1; SELECT * FROM t1 WHERE WEIGHT_STRING(a) IS NULL; SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
            bar Alexander Barkov added a comment - - edited

            A similar example using FORMAT():

            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a BIGINT(20) ZEROFILL);
            		 
            INSERT INTO t1 VALUES (18446744073709551615);
            		 
            SELECT * FROM t1 WHERE a=18446744073709551615;
            		 
            SELECT * FROM t1 WHERE FORMAT(a,0)='18,446,744,073,709,551,615';
            		 
            SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';

            bar Alexander Barkov added a comment - - edited A similar example using FORMAT(): DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a BIGINT(20) ZEROFILL); INSERT INTO t1 VALUES (18446744073709551615); SELECT * FROM t1 WHERE a=18446744073709551615; SELECT * FROM t1 WHERE FORMAT(a,0)='18,446,744,073,709,551,615'; SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';

            People

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