r/mariadb•
enedil

Filtering on datetime field works wrong

I have following code:

CREATE TABLE test_tab (
  create_time datetime(6) NOT NULL,
  PRIMARY KEY (create_time)
);

INSERT INTO test_tab (create_time) VALUES ('2023-04-21 00:17:24.000000000');
COMMIT;

SELECT * FROM test_tab WHERE create_time = '2023-04-21 00:17:24.000000000';
DELETE FROM test_tab WHERE create_time = '2023-04-21 00:17:24.000000000';
COMMIT;
SELECT * FROM test_tab WHERE create_time = '2023-04-21 00:17:24.000000000';

Both selects return one row. Any clues why it might happen?

$ mysql --version mysql Ver 15.1 Distrib 10.3.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Posted Apr 21, 2023, 7:41 PM UTC

22
greenman•

Take a look at the warnings and the output results, for example:

INSERT INTO test_tab (create_time) VALUES ('2023-04-21 00:17:24.000000000');
Query OK, 1 row affected, 1 warning (0.002 sec)

DELETE FROM test_tab WHERE create_time = '2023-04-21 00:17:24.000000000';
Query OK, 0 rows affected (0.000 sec)

If you view the warnings, you'll see why

SHOW WARNINGS;
    +-------+------+---------------------------------------------------------------------+
| Level | Code | Message                                                                 |
+-------+------+---------------------------------------------------------------------+
| Note  | 1292 | Truncated incorrect datetime value: '2023-04-21 00:17:24.000000000' |
+-------+------+---------------------------------------------------------------------+

The DATETIME format is YYYY-MM-DD HH:MM:SS.ffffff, not YYYY-MM-DD HH:MM:SS.fffffffff

2
danielgblack•

To confirm DELETE see fiddle.

DELETE FROM test_tab WHERE create_time = '2023-04-21 00:17:24.000000000';
SELECT ROW_COUNT();
0

So no deletions are matching the criteria.

1