matrixorigin / matrixorigin/matrixone

[Bug]: CTAS with TIMESTAMPADD fails when generated INSERT quotes the time unit

Open
#28,562 0 comments 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
kind/bug needs-triage
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.