Search code examples
mysqlsqlsubquerymariadbcorrelated-subquery

Truncation of DateTimes in correlated subqueries in MariaDB

One of my group-wise maximum queries was returning unexpected results. I distilled it down to the following issue:

DROP TABLE IF EXISTS maria_bug;
CREATE TABLE maria_bug (
  id INT NOT NULL,
  update_time TIMESTAMP(6) NOT NULL
);

INSERT INTO maria_bug VALUES(1, '2001-01-01 00:00:00.200000');
INSERT INTO maria_bug VALUES(1, '2001-01-01 00:00:00.100000');

SELECT
  m1.id,
  m1.update_time t1,
  (SELECT MAX(m2.update_time) FROM maria_bug m2 WHERE m1.id = m2.id) t2
FROM maria_bug m1;

On MySQL 5.6, I get this:

1   2001-01-01 00:00:00.200000  2001-01-01 00:00:00.200000
1   2001-01-01 00:00:00.100000  2001-01-01 00:00:00.200000

On MariaDB (10.0.13):

1   2001-01-01 00:00:00.200000  2001-01-01 00:00:00.200000
1   2001-01-01 00:00:00.100000  2001-01-01 00:00:00.000000

Why is this happening?? The MYSQL behavior is what I would expect. Is there a setting I am missing somewhere in MariaDB? Is this documented somewhere? Surely, it can't be a bug in the database since this is a very common query:

SELECT COUNT(*) FROM maria_bug m1
WHERE update_time < (SELECT MAX(m2.update_time) FROM maria_bug m2 WHERE m1.id = m2.id)

The above query returns 1 in MYSQL as expected but 0 in MariaDB.

Solution

  • This seems a bug for MariaDB, since the data returns is different from the data in table.

    mysql> SELECT *, (SELECT MAX(m2.update_time) FROM maria_bug m2 WHERE m1.id <> 0 ) max_update FROM maria_bug m1;
+----+----------------------------+----------------------------+
| id | update_time                | max_update                 |
+----+----------------------------+----------------------------+
|  1 | 2001-01-01 00:00:00.200000 | 2001-01-01 00:00:00.200000 |
|  1 | 2001-01-01 00:00:00.100000 | 2001-01-01 00:00:00.000000 |
+----+----------------------------+----------------------------+
2 rows in set (0.00 sec)

    Bug is filed to MariaDB: https://jira.mariadb.org/browse/MDEV-9707

    Update: The bug is confirmed by MariaDB. enter image description here