数据库迁移后，发现oceanbase 和mysq按年月范围查询返回数据不一致

AntTech_DDON3G · 2025 年12 月 1 日 16:32

【使用环境】生产环境
【 OB or 其他组件】
【使用版本】
【问题描述】oceanbase 和mysq按年月查询返回数据不一致，导致生产环境数据查询异常。
【复现路径】SELECT NOW()>‘2026-01’; mysql输出结果是0，oceanbase输出结果是1
ccb8724d-6aa0-43a5-b535-4c055a8aad43

烫烫烫 · 2025 年12 月 1 日 16:38

您这个SQL语句保真吗，是不是写错了。。。

AntTech_DDON3G · 2025 年12 月 1 日 16:41

你的执行是带上了年月日了，我们系统写了很多按月份查询统计的的sql
ccb8724d-6aa0-43a5-b535-4c055a8aad43

咖啡哥 · 2025 年12 月 2 日 09:17

看执行计划：

| Query Plan                                                                                    |
+-----------------------------------------------------------------------------------------------+
| ==========================================                                                    |
| |ID|OPERATOR  |NAME|EST.ROWS|EST.TIME(us)|                                                    |
| ------------------------------------------                                                    |
| |0 |EXPRESSION|    |1       |1           |                                                    |
| ==========================================                                                    |
| Outputs & filters:                                                                            |
| -------------------------------------                                                         |
|   0 - output([current_timestamp() > cast('2026-01-01', MYSQL_DATETIME(-1, -1))]), filter(nil) |
|       values({current_timestamp() > cast('2026-01-01', MYSQL_DATETIME(-1, -1))})              |

感觉问题出在cast(‘2026-01-01’, MYSQL_DATETIME(-1, -1))这。

但是这个转换，ob和mysql结果都是一样的。


select cast('2026-01-01' as datetime);
+--------------------------------+
| cast('2026-01-01' as datetime) |
+--------------------------------+
| 2026-01-01 00:00:00            |
+--------------------------------+
1 row in set (0.00 sec)

select cast('2026-01' as datetime);
+-----------------------------+
| cast('2026-01' as datetime) |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set, 1 warning (0.01 sec)

不知道为啥了。。

zhenxing · 2025 年12 月 2 日 09:29

从返回值来说，MySQL本身是不符合数据规范的

-- MySQL5.7.44的结果
select now()>'2026-01';
+-----------------+
| now()>'2026-01' |
+-----------------+
|               0 |
+-----------------+
1 row in set, 1 warning (0.0004 sec)
Warning (code 1292): Truncated incorrect datetime value: '2026-01'

-- MySQL8.0.26的结果
select now()>'2026-01';
ERROR: 1525 (HY000): Incorrect DATETIME value: '2026-01'

-- MySQL8.4.7的结果
select now()>'2026-01';
ERROR: 1525 (HY000): Incorrect DATETIME value: '2026-01'

AntTech_DDON3G · 2025 年12 月 2 日 14:16

问题找到了，以前生产用的mysql数据库版本是5.7的，5.7可以正常执行，8.0之后的规范就不能正常执行了，但oceanbase还是能够正常执行，查询返回了错误的数据，没有提示报错，怎么解决呢？
select * from table_name where task_date > ‘2025-01’ 会返回全表的数据，生产环境上会导致定时任务执行全表更新。

zhenxing · 2025 年12 月 2 日 15:46

task_date字段是什么数据类型和格式

AntTech_DDON3G · 2025 年12 月 2 日 15:47

数据类型：datetime
数据格式：2022-03-31 00:00:00

辞霜 · 2025 年12 月 2 日 16:13

ob版本是多少？

AntTech_DDON3G · 2025 年12 月 2 日 16:14

社区版：4.3.5.0

辞霜 · 2025 年12 月 2 日 16:27

查询结果与mysql5.7不一致。是预期内的，ob这边相当于非法字符串转date，这个解析本来也不是完全兼容的

AntTech_DDON3G · 2025 年12 月 2 日 16:29

非法字符能像mysql8.0一样报异常吗，ob直接将非法字符串转’1970-01-01’来运行了。

辞霜 · 2025 年12 月 2 日 16:43

当前逻辑与mysql还是存在差异的。

zhenxing · 2025 年12 月 2 日 17:48

我没相同版本的OB环境，我的环境OB执行不是返回全部数据，是返回空（没报错），不确定是版本问题，还是你SQL_MODE调整过

我的版本示例

-- 版本信息
obclient [demo]> \s
Server version:		OceanBase 4.2.1.10 (r110040012024122218-76b8444f981549d277143b833c2bed1b64471eef) (Built Dec 22 2024 18:56:19)
	

-- 默认SQL_MODE
obclient [demo]> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

-- 查询返回空
obclient [demo]> select * from t1 where UPDATED_DATE>'2025-01';
Empty set (0.008 sec)

-- 设置会话级SQL_MODE，去除了NO_ZERO_DATE
set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 查询返回了全表数据（我的测试表只有100条数据）
obclient [demo]> select * from t1 where UPDATED_DATE>'2025-01';
+------+--------------+
| id   | UPDATED_DATE |
+------+--------------+
|    1 | 2025-09-22   |
|    2 | 2024-11-27   |
|    3 | 2024-02-23   |
|    4 | 2025-03-26   |
|    5 | 2025-05-24   |
|    6 | 2024-06-10   |
|    7 | 2025-03-19   |
|    8 | 2024-05-26   |
|    9 | 2024-02-14   |
|   10 | 2025-02-22   |
|   11 | 2024-10-07   |
|   12 | 2025-09-17   |
|   13 | 2024-12-23   |
|   14 | 2025-11-19   |
|   15 | 2024-11-11   |
|   16 | 2025-05-01   |
|   17 | 2024-10-19   |
|   18 | 2025-04-05   |
|   19 | 2025-09-21   |
|   20 | 2025-08-22   |
|   21 | 2024-12-22   |
|   22 | 2024-11-28   |
|   23 | 2025-11-20   |
|   24 | 2025-11-08   |
|   25 | 2025-05-01   |
|   26 | 2024-05-08   |
|   27 | 2024-05-16   |
|   28 | 2024-12-25   |
|   29 | 2024-07-01   |
|   30 | 2024-03-03   |
|   31 | 2024-09-17   |
|   32 | 2024-12-09   |
|   33 | 2024-09-23   |
|   34 | 2024-02-21   |
|   35 | 2025-11-16   |
|   36 | 2025-10-11   |
|   37 | 2024-10-14   |
|   38 | 2024-01-02   |
|   39 | 2025-04-14   |
|   40 | 2025-12-08   |
|   41 | 2024-04-30   |
|   42 | 2024-11-09   |
|   43 | 2024-12-25   |
|   44 | 2024-03-08   |
|   45 | 2025-10-20   |
|   46 | 2025-02-07   |
|   47 | 2024-12-02   |
|   48 | 2025-02-12   |
|   49 | 2024-04-20   |
|   50 | 2025-06-14   |
|   51 | 2025-02-23   |
|   52 | 2025-04-15   |
|   53 | 2025-10-12   |
|   54 | 2025-01-30   |
|   55 | 2024-03-03   |
|   56 | 2025-09-20   |
|   57 | 2024-10-27   |
|   58 | 2025-12-31   |
|   59 | 2024-05-14   |
|   60 | 2024-05-30   |
|   61 | 2025-02-22   |
|   62 | 2024-02-08   |
|   63 | 2024-01-29   |
|   64 | 2024-01-17   |
|   65 | 2025-12-09   |
|   66 | 2025-10-01   |
|   67 | 2024-03-30   |
|   68 | 2024-04-22   |
|   69 | 2024-03-04   |
|   70 | 2024-03-08   |
|   71 | 2025-01-22   |
|   72 | 2024-06-11   |
|   73 | 2025-07-02   |
|   74 | 2024-03-10   |
|   75 | 2024-05-28   |
|   76 | 2024-10-01   |
|   77 | 2025-04-29   |
|   78 | 2024-10-15   |
|   79 | 2024-05-03   |
|   80 | 2024-05-10   |
|   81 | 2025-12-09   |
|   82 | 2025-01-05   |
|   83 | 2024-07-23   |
|   84 | 2025-08-15   |
|   85 | 2024-02-08   |
|   86 | 2024-07-22   |
|   87 | 2024-01-16   |
|   88 | 2025-08-28   |
|   89 | 2025-07-19   |
|   90 | 2025-02-24   |
|   91 | 2025-02-14   |
|   92 | 2025-04-08   |
|   93 | 2024-08-04   |
|   94 | 2024-08-11   |
|   95 | 2024-04-18   |
|   96 | 2025-11-04   |
|   97 | 2024-06-17   |
|   98 | 2025-12-13   |
|   99 | 2024-11-20   |
|  100 | 2025-12-29   |
+------+--------------+
100 rows in set (0.004 sec)

zhenxing · 2025 年12 月 2 日 18:05

这个问题我理解主要还是SQL的日期类型格式不符合预期，但OB的行为确实比较奇怪，看了下trace日志，实际不管SQL_MODE如何配置，日志中都认为转换失败了，但输出的结果完全不同

-- 默认SQL_MODE产生的trace
[root@10-186-61-29 log]# grep "YB420ABA3D1D-000642AF57439C61-0-0" /home/admin/oceanbase/log/observer.log*
/home/admin/oceanbase/log/observer.log.20251202175656931:[2025-12-02 17:56:41.720287] WDIAG [LIB.TIME] str_to_digit_with_date (ob_time_convert.cpp:1924) [784978][T1006_L0_G0][T1006][YB420ABA3D1D-000642AF57439C61-0-0] [lt=17][errcode=-4219] datetime format too short(ret=-4219, str=2025-01)
/home/admin/oceanbase/log/observer.log.20251202175656931:[2025-12-02 17:56:41.720313] WDIAG [LIB.TIME] str_to_ob_time_with_date (ob_time_convert.cpp:1986) [784978][T1006_L0_G0][T1006][YB420ABA3D1D-000642AF57439C61-0-0] [lt=25][errcode=-4219] failed to get digits(ret=-4219, str=2025-01)

-- 去除了NO_ZERO_DATE的SQL_MODE产生的trace
[root@10-186-61-29 log]# grep "YB420ABA3D1D-000642AF57439C64-0-0" /home/admin/oceanbase/log/observer.log*
/home/admin/oceanbase/log/observer.log:[2025-12-02 17:58:12.836714] WDIAG [LIB.TIME] str_to_digit_with_date (ob_time_convert.cpp:1924) [784978][T1006_L0_G0][T1006][YB420ABA3D1D-000642AF57439C64-0-0] [lt=10][errcode=-4219] datetime format too short(ret=-4219, str=2025-01)
/home/admin/oceanbase/log/observer.log:[2025-12-02 17:58:12.836737] WDIAG [LIB.TIME] str_to_ob_time_with_date (ob_time_convert.cpp:1986) [784978][T1006_L0_G0][T1006][YB420ABA3D1D-000642AF57439C64-0-0] [lt=22][errcode=-4219] failed to get digits(ret=-4219, str=2025-01)

Sunshining · 2025 年12 月 2 日 19:37

日期时间格式问题吧，日期时间格式是在用时比较难办，不统一的，也容易出问题

AntTech_DDON3G · 2025 年12 月 3 日 08:59

使用默认的sql_mode
STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER