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

Wrong result for WHERE 2016 < SOME (SELECT CAST(time_column AS DATETIME) FROM t1)

    XMLWordPrintable

    Details

    • Sprint:
      10.1.8-4

      Description

      SET timestamp=UNIX_TIMESTAMP('2015-01-01 00:00:00');
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME);
      INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');
      SELECT 1 FROM t1 WHERE 2016 > SOME (SELECT CAST(a AS DATETIME) FROM t1);

      returns two rows with no warnings:

      +---+
      | 1 |
      +---+
      | 1 |
      | 1 |
      +---+
      2 rows in set (0.01 sec)

      Now let's check which of the rows in SOME actually generated true condition:

      SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME);
      SHOW WARNINGS;

      Opps. It returns empty set with a warning:

      +---------+------+----------------------------------+
      | Level   | Code | Message                          |
      +---------+------+----------------------------------+
      | Warning | 1292 | Incorrect datetime value: '2016' |
      +---------+------+----------------------------------+
      1 row in set (0.00 sec)

      The result of the query with SOME is obviously wrong. It should have returned empty set with warnings.

        Attachments

          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: