MySQL/MariaDB datetime虚拟列使用OFFSET时返回错误值问题排查

问题现象 #

在带OFFSET的分页SQL查询中，表内定义的虚拟列fix_expired_date返回错误值，但在查询语句中手动写入完全相同的计算逻辑时，结果却是正确的。
虚拟列定义：

if(`expired_date` = '1900-01-01 00:00:00',NULL,`expired_date`)

分页查询示例：

select a.*, if(`expired_date` = '1900-01-01 00:00:00',NULL,`expired_date`) 
from `inventory_detail_cached` a 
order by `client` asc, `brand` asc, `item_number` asc, `lot_number` asc 
limit 10 offset 11450

核心结论 #

这个问题并非必然是MariaDB与MySQL的差异导致，更可能和虚拟列的计算机制、查询执行计划的优化逻辑有关，尤其是分页场景下的执行路径差异。

可能原因与解决方法 #

1. 虚拟列类型差异
   MariaDB的生成列分为STORED（写入时计算并存储）和VIRTUAL（查询时动态计算，默认类型）。如果你的fix_expired_date是VIRTUAL类型，分页查询的执行优化可能导致计算时机或上下文出错。

   • 解决：将虚拟列改为STORED类型，示例语句：

     ALTER TABLE inventory_detail_cached 
     MODIFY COLUMN fix_expired_date DATETIME 
     GENERATED ALWAYS AS (if(`expired_date` = '1900-01-01 00:00:00',NULL,`expired_date`)) STORED;
     

2. 执行计划优化差异
   当查询包含ORDER BY和LIMIT/OFFSET时，MariaDB可能会用索引优化排序，这可能导致虚拟列计算逻辑与手动计算的执行路径不一致。

   • 解决：用EXPLAIN对比两个查询（仅查虚拟列 vs 手动计算列）的执行计划，查看索引使用差异。若为索引问题，可尝试强制不使用索引，或重构排序相关的复合索引。

3. 版本BUG
   你使用的MariaDB 10.4.28是较老版本，可能存在虚拟列与分页结合的已知BUG。

   • 解决：升级到MariaDB 10.4系列的最新小版本，或直接升级到更高稳定大版本（如10.6、10.11）。

4. 隐式类型转换问题
   虽手动计算结果正确，但仍需确认expired_date字段类型与字符串'1900-01-01 00:00:00'的隐式转换是否存在潜在问题。

   • 解决：将比较条件改为日期类型写法，示例：

     if(`expired_date` = CAST('1900-01-01 00:00:00' AS DATETIME), NULL, `expired_date`)
     

内容的提问来源于stack exchange，提问作者aldesrahim