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

Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'

    XMLWordPrintable

Details

    Description

      Equal field propagation does not work well in a number of cases.

      Trailing garbage in string literals

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DATE);
      		 
      INSERT INTO t1 VALUES ('2001-01-01');
      		 
      SELECT * FROM t1 WHERE HEX(a) !='323030312D30312D3031';

      returns empty set.
      Now If I make the condition even stronger:

      SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x';

      it erroneously returns one row:

      +------------+
      		 
      | a          |
      		 
      +------------+
      		 
      | 2001-01-01 |
      		 
      +------------+

      Trailing fractional digits in string literals

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a TIME);
      		 
      INSERT INTO t1 VALUES ('00:00:00');
      		 
      SELECT * FROM t1 WHERE LENGTH(a)!=8;

      returns empty set.
      Now if I make the condition even stronger:

      SELECT a, LENGTH(a) FROM t1 WHERE LENGTH(a)!=8 AND a='00:00:00.000000';

      it erroneously returns one row:

      +----------+-----------+
      		 
      | a        | LENGTH(a) |
      		 
      +----------+-----------+
      		 
      | 00:00:00 |         8 |
      		 
      +----------+-----------+

      Trailing fractional digits in temporal literals

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a TIME);
      		 
      INSERT INTO t1 VALUES ('00:00:00');
      		 
      SELECT * FROM t1 WHERE LENGTH(a)!=8;
      		 
      SELECT * FROM t1 WHERE LENGTH(a)!=8 AND a=TIME'00:00:00.000000';

      The first SELECT query returns empty set, the second SELECT query erroneously returns one row.

      Leading spaces in string literals

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DATE);
      		 
      INSERT INTO t1 VALUES ('2001-01-01');
      		 
      SELECT * FROM t1 WHERE LENGTH(a)=11;
      		 
      SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01';

      The first query correctly returns empty set, the second query erroneously returns one row.

      Numeric format in string literals

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DATE);
      		 
      INSERT INTO t1 VALUES ('2001-01-01');
      		 
      SELECT * FROM t1 WHERE LENGTH(a)=8;
      		 
      SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101';

      The first query correctly returns empty set, the second query erroneously returns one row.

      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

            People

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