[MDEV-31800] Problem with open ranges on prefix blobs keys Created: 2023-07-31  Updated: 2023-08-01  Resolved: 2023-07-31

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5.19, 10.5, 10.6, 10.9, 10.10, 10.11
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2, 11.2.1

Type: Bug Priority: Major
Reporter: Marcin Boniakowski Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: wrong_result
Environment:

Debian 11.6, 2GB RAM, Proxmox VM


 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

 Comments   
Comment by Alice Sherepa [ 2023-07-31 ]

Thank you for the report! I repeated on 10.4-11.0:

create  table t1 (d text, key a (d(10))) ;
 
insert into t1 values ('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' );
 
 
 
select d from t1 where   d >= '2023-07-28 12:00:00'  and d < '2023-07-28 15:00:00';
 
alter table t1 drop index a;
 
select d from t1 where   d >= '2023-07-28 12:00:00'  and d < '2023-07-28 15:00:00';


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: 0
 
 
 
MariaDB [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)

Comment by Sergei Golubchik [ 2023-07-31 ]

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.

Generated at Thu Feb 08 10:26:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.