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'

    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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: