MariaDB含IF语句的持久化生成日期列创建失败原因问询

问题重现 #

以下几个创建持久化生成列的语句均可正常执行：

测试1：直接对日期做运算 #

CREATE TABLE `test1` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      `date` - INTERVAL 1 SECOND
  ) STORED
);

测试2：IF分支返回同类型日期 #

CREATE TABLE `test2` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, `date`)
  ) STORED
);

测试3：直接使用常量字符串作为生成列 #

CREATE TABLE `test3` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      '2000-01-01 00:00:00'
  ) STORED
);

但执行以下语句时会报错：

测试4：IF分支返回日期运算结果与字符串常量 #

CREATE TABLE `test4` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, '2000-01-01 00:00:00')
  ) STORED
);

错误信息：

ERROR 1901 (HY000) at line 16: Function or expression 'if(`date` is not null,`date` - interval 1 second,'2000-01-01 00:00:00')' cannot be used in the GENERATED ALWAYS AS clause of `generated`

根据官方文档说明：

非确定性内置函数不支持用于PERSISTENT或带索引的VIRTUAL生成列的表达式中。

原因分析 #

问题出在IF表达式的隐式类型转换上：

• 测试4中，IF的两个分支返回值类型不一致：一个是datetime(3)类型（date - INTERVAL 1 SECOND的运算结果），另一个是字符串类型（'2000-01-01 00:00:00'）。
• 当MariaDB需要将字符串转换为datetime(3)时，这个转换过程依赖会话级别的时区设置——不同时区下，同一个日期字符串解析后的datetime值可能存在差异（比如涉及夏令时的地区）。这种依赖会话上下文的转换会导致整个表达式被判定为非确定性，因此无法用于持久化（STORED）或带索引的虚拟（VIRTUAL）生成列。

对比其他测试用例：

• 测试1、2的表达式返回值类型完全一致，无需跨类型的隐式转换，因此是确定性的。
• 测试3中直接使用字符串常量作为生成列，表定义时已经明确了目标类型为datetime(3)，转换是在表创建阶段完成的，不依赖后续的会话时区，因此也是确定性的。

解决方案 #

将字符串常量显式转换为datetime(3)类型，消除隐式转换对会话时区的依赖，即可让表达式成为确定性的：

CREATE TABLE `test4` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, CAST('2000-01-01 00:00:00' AS datetime(3)))
  ) STORED
);

或者使用STR_TO_DATE函数显式解析，效果相同：

CREATE TABLE `test4` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, STR_TO_DATE('2000-01-01 00:00:00', '%Y-%m-%d %H:%i:%s'))
  ) STORED
);

这样修改后，表达式的结果不再依赖会话时区，符合确定性要求，即可创建带持久化生成列的表，同时也能为该列创建索引。

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