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

    • 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

            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.