【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】ocp:4.3.2，oceanbase-ce：4.3.3.1
【问题描述】使用oceanbase数据库查询时间过滤条件：where date = ‘2020-10-01 上午00:00:00’ 可以获取10月1日数据，如果填写where date = ‘2020-10-01 上午12:00:00’ 就查询不到了；使用相同的数据，在mysql上面可以查询到10月1日的数据；
【复现路径】随时可以复习查询
请按如下步骤提供下日志
1.开启 Trace 功能
SET ob_enable_show_trace=ON;
2.执行SQL
3.获取SQL trace_id
SELECT last_trace_id() FROM DUAL;
4.登录对应 OBServer 节点，进入到日志文件所在目录
cd /home/admin/oceanbase/log
5.获取trace_id对应的日志
grep xxxxxxx observer.log --填写第3步获取的trace_id
grep xxxxxxx rootservice.log --填写第3步获取的trace_id
我们尝试复现下这个问题
根据上面的操作，只获取到了：observer.log 日志，rootservice.log无数据
[2024-11-25 18:00:40.170710] WDIAG [SQL.PC] common_free (ob_lib_cache_object_manager.cpp:141) [3617162][T1_L0_G0][T1][Y22B2AC10CF98-000626FE49E867FA-0-0] [lt=20][errcode=0] set logical del time(cache_obj->get_logical_del_time()=104725124011064, cache_obj->added_lc()=false, cache_obj->get_object_id()=13098, cache_obj->get_tenant_id()=1, lbt()=“0x7bcec65 0xfb1dce8 0x74e3164 0x74d8299 0x74cce31 0x74c555c 0x74c39ef 0x74b3b63 0xe9ade57 0x1f6278be 0x7f9ec5d1eea5 0x7f9ec5a47b0d”)
确认下 你这里的date字段是什么数据类型？varchar， date ，datetime还是timestamp？
或者脱敏后发下表结构
我没有复现出来这个问题，下面是复现过程，您看下有哪些不符合的地方？
/home/admin/oceanbase-ce/bin/observer -V
observer (OceanBase_CE 4.3.3.1)
REVISION: 101000012024102216-2df04a2a7a203b498f23e1904d4b7a000457ce43
BUILD_BRANCH: HEAD
BUILD_TIME: Oct 22 2024 17:42:50
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO:
Copyright (c) 2011-present OceanBase Inc.
obclient [testtime]> create table testt(id int,date datetime);
Query OK, 0 rows affected (0.157 sec)
obclient [testtime]> insert into testt values(1,'2020-10-01 上午00:00:00');
Query OK, 1 row affected (0.034 sec)
obclient [testtime]> insert into testt values(1,'2020-10-01 上午12:00:00');
Query OK, 1 row affected (0.004 sec)
obclient [testtime]> select * from testt;
+------+---------------------+
| id | date |
+------+---------------------+
| 1 | 2020-10-01 00:00:00 |
| 1 | 2020-10-01 12:00:00 |
+------+---------------------+
2 rows in set (0.003 sec)
obclient [testtime]> select * from testt where date = '2020-10-01 上午00:00:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 1 | 2020-10-01 00:00:00 |
+------+---------------------+
1 row in set (0.002 sec)
obclient [testtime]> select * from testt where date = '2020-10-01 上午12:00:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 1 | 2020-10-01 12:00:00 |
+------+---------------------+
1 row in set (0.000 sec)
2个表是一样的，字段数据和索引一致，都是分区表，从mysql迁移到oceanbase库的；
有点出入，我截图给你看看；
这个是mysql数据库查询的：date 内容：2020-10-01 00:00:00
这个是oceanbase数据库查询出来的；
mysql复现了下
$/usr/local/mysql/bin/mysql -V
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.35, for linux-glibc2.12 (x86_64) using EditLine wrapper
mysql> create table testmysql(id int,date datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into testmysql values(2,'2020-10-01 上午00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2020-10-01 上午00:00:00' for column 'date' at row 1
mysql> insert into testmysql values(2,'2020-10-01 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into testmysql values(2,'2020-10-01 12:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> select * from testmysql;
+------+---------------------+
| id | date |
+------+---------------------+
| 2 | 2020-10-01 00:00:00 |
| 2 | 2020-10-01 12:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from testmysql where date = '2020-10-01 上午00:00:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 2 | 2020-10-01 00:00:00 |
+------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2020-10-01 上午00:00:00' for column 'date' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2020-10-01 上午00:00:00' for column 'date' at row 1 |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from testmysql where date = '2020-10-01 上午12:00:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 2 | 2020-10-01 00:00:00 |
+------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2020-10-01 上午12:00:00' for column 'date' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2020-10-01 上午12:00:00' for column 'date' at row 1 |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from testmysql where date = '2020-10-01 00:00:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 2 | 2020-10-01 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from testmysql where date = '2020-10-01 12:00:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 2 | 2020-10-01 12:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
MySQL这里可以查出来，但是有Warning，这个结果其实是不对的
是的，mysql查询有Warning，但是我们这边的开发代码是这样写的，oceanbase能这样吗
OB的表现大概率是符合预期的，我再咨询下SQL老师看看
这里OB和MySQL表现不同，本质上是字符串解析逻辑不同导致的，mysql遇到非法字符就停了，ob会继续解析，
cast(‘2020-10-01 上午12:00:00’ as datetime) 这种写法本来就有问题，OB解析能力更强给解析出来了，
如果不希望要后面的时间，建议直接就写成0，不要利用一个错误把后面的时间截断掉。