matrixorigin / matrixorigin/matrixone
[Bug]: CTAS with TIMESTAMPADD fails when generated INSERT quotes the time unit
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## 现象
`TIMESTAMPADD()` 可以直接查询、用于 view 和 `INSERT ... SELECT`,但相同表达式放入 CTAS 后,MatrixOne 在内部生成的 `INSERT` 重解析阶段把时间单位格式化为带双引号的字符串,最终报 SQL 语法错误。
```sql
CREATE DATABASE ctas_timestampadd_repro;
USE ctas_timestampadd_repro;
CREATE TABLE src(id INT PRIMARY KEY, d DATETIME(6));
INSERT INTO src VALUES (1, '2024-01-31 01:02:03.123456');
-- 直接查询正常
SELECT TIMESTAMPADD(DAY, 1, d) FROM src;
-- view 正常
CREATE VIEW v AS SELECT TIMESTAMPADD(DAY, 1, d) AS d FROM src;
SELECT * FROM v;
-- INSERT ... SELECT 正常
CREATE TABLE sink(d DATETIME(6));
INSERT INTO sink SELECT TIMESTAMPADD(DAY, 1, d) FROM src;
-- CTAS 报错
CREATE TABLE copied AS
SELECT TIMESTAMPADD(DAY, 1, d) AS d FROM src;
```
CTAS 返回:
```text
ERROR 1105: ... syntax error ... near
""day", 1, `src`.`d`) as `d` from `src`) as __mo_ctas_source"
```
CTAS 保持原子性,错误后 `copied` 不存在。MySQL 8.0.46 对相同 CTAS 正常创建并写入 `2024-02-01 01:02:03.123456`。
## 覆盖范围
以下 9 种标准单位均出现相同问题:
```text
MICROSECOND, SECOND, MINUTE, HOUR, DAY,
WEEK, MONTH, QUARTER, YEAR
```
server-side prepared CTAS 也会在执行时出现相同重解析错误:
```sql
PREPARE p FROM
'CREATE TABLE c_prepared AS
SELECT TIMESTAMPADD(DAY, ?, d) AS d FROM src';
SET @n = 1;
EXECUTE p USING @n;
```
相邻控制均正常:
- `CREATE TABLE ... AS SELECT DATE_ADD(d, INTERVAL 1 DAY)`;
- `CREATE TABLE ... AS SELECT TIMESTAMPDIFF(DAY, d, DATE_ADD(d, INTERVAL 2 DAY))`;
- 直接 SELECT、view 和 `INSERT ... SELECT` 中的 `TIMESTAMPADD()`。
## 代码定位
`pkg/sql/plan/build_ddl.go` 构造 CTAS 的内部 `INSERT ... SELECT` 时:
1. 使用 AST formatter 重新输出 `stmt.AsSource`;
2. `TIMESTAMPADD(DAY, ...)` 被输出为 `timestampadd("day", ...)`;
3. `restoreIntervalSyntaxForCTAS()` 只修复 `interval(expr, unit)` 到 `INTERVAL expr unit` 的格式,没有恢复 `TIMESTAMPADD/TIMESTAMPDIFF` 的单位 token;
4. 生成 SQL 被内部 executor 重新解析时,语法要求位置上出现的是 `time_stamp_unit` token,而不是带引号字符串,因此失败。
## 验证信息
- MatrixOne: latest `main` `ac660ae56ecd37bab3dd8c61193617a323377029`
- MySQL oracle: 8.0.46
- 9 种时间单位、普通 CTAS、prepared CTAS
- 直接查询/view/`INSERT ... SELECT`/`DATE_ADD`/`TIMESTAMPDIFF` 相邻控制
- 3 个全新数据库重复,结果一致
Contributor guide
Assessment
This issue has not been assessed yet.