Details
Major Description
I've noticed an unexpected behavior while applying filters on a TEXT column which contains datetime values.
I have a table for auditing changes on some of the other tables which contains following columns: ID (int), timestamp(TEXT), table(TEXT), field(TEXT)
When I select on the above table with statement I receive a set of records
SELECT ftimestamp FROM `changes` WHERE `changes`.`fTable` = 'ex_table' AND `changes`.`fField` = 'ex_field' AND `changes`.`ftimestamp` >= '2023-07-28 12:00:00';
|
+---------------------+
|
| ftimestamp |
|
+---------------------+
|
| 2023-07-28 12:26:24 |
|
| 2023-07-28 12:29:05 |
|
+---------------------+
|
However when I add second filtering on `ftimestamp
SELECT ftimestamp FROM `changes` WHERE `changes`.`fTable` = 'ex_table' AND `changes`.`fField` = 'ex_field' AND `changes`.`ftimestamp` >= '2023-07-28 12:00:00' AND `changes`.`ftimestamp` < '2023-07-28 15:00:00';
|
Empty set (0.000 sec)
|
When I change `less` to `less or equal` it works properly
SELECT ftimestamp FROM `changes` WHERE `changes`.`fTable` = 'ex_table' AND `changes`.`fField` = 'ex_field' AND `changes`.`ftimestamp` >= '2023-07-28 12:00:00' AND `changes`.`ftimestamp` <= '2023-07-28 15:00:00';
|
+---------------------+
|
| ftimestamp |
|
+---------------------+
|
| 2023-07-28 12:26:24 |
|
| 2023-07-28 12:29:05 |
|
+---------------------+
|
When I use only less comparision (with some limits) it also works
SELECT ftimestamp FROM `changes` WHERE `changes`.`fTable` = 'ex_table' AND `changes`.`fField` = 'ex_field' AND `changes`.`ftimestamp`< '2023-07-28 15:00:00' ORDER BY ID DESC LIMIT 10;
|
+---------------------+
|
| ftimestamp |
|
+---------------------+
|
| 2023-07-28 12:29:05 |
|
| 2023-07-28 12:26:24 |
|
| 2023-07-28 11:41:48 |
|
| 2023-07-28 11:21:54 |
|
| 2023-07-28 11:05:41 |
|
| 2023-07-28 11:00:39 |
|
| 2023-07-28 10:59:46 |
|
| 2023-07-27 21:06:43 |
|
| 2023-07-27 17:08:01 |
|
| 2023-07-27 16:58:59 |
|
+---------------------+
|
Using `BETWEEN` works as expected.
Additional info: there is an index on described table
show indexes from echanges;
|
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| echanges | 0 | PRIMARY | 1 | id | A | 12448424 | NULL | NULL | | BTREE | | |
|
 |
| echanges | 1 | ix_wiki_2 | 1 | fTable | A | 5354 | 10 | NULL | | BTREE | | |
|
| echanges | 1 | ix_wiki_2 | 2 | fField | A | 21877 | 10 | NULL | | BTREE | | |
|
| echanges | 1 | ix_wiki_2 | 3 | fTimestamp | A | 366130 | 10 | NULL | | BTREE | | |
|
Above behavior is not seems to be happening on MariaDB 10.3.34