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
Attachments
Activity
To highlight the issue:
create table t1 (d text, key a (d(6))) ; |
insert into t1 values ('prefix 2' ), ('prefix 0' ); |
|
select d from t1 where d >= 'prefix 1' and d < 'prefix 3'; |
alter table t1 drop index a; |
select d from t1 where d >= 'prefix 1' and d < 'prefix 3'; |
drop table t1; |
with the key(d(6)) only the string "prefix" is indexed, the difference between rows is beyond the indexed part. Apparently the server fails to detect that. With VARCHAR the result is correct though.
Thank you for the report! I repeated on 10.4-11.0:
MariaDB [test]> select d from t1 where d >= '2023-07-28 12:00:00' and d < '2023-07-28 15:00:00';Empty set (0,000 sec)MariaDB [test]> alter table t1 drop index a;Query OK, 0 rows affected (0,017 sec)Records: 0 Duplicates: 0 Warnings: 0MariaDB [test]> select d from t1 where d >= '2023-07-28 12:00:00' and d < '2023-07-28 15:00:00';+---------------------+| d |+---------------------+| 2023-07-28 12:29:05 || 2023-07-28 12:26:24 |+---------------------+2 rows in set (0,001 sec)