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)

        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.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Also repeatable in MySQL-5.7.8

                Show
                bar Alexander Barkov added a comment - Also repeatable in MySQL-5.7.8

                  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:

                      Agile