matrixorigin / matrixorigin/matrixone

[Compatibility]: DATE_ADD and DATE_SUB reject prepared value parameters

Open
#28,119 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

## Summary

`DATE_ADD` and `DATE_SUB` cannot be executed through a prepared statement when either the date operand or the `INTERVAL` value is a parameter marker. The same SQL succeeds with literals, and the related `TIMESTAMPADD` function accepts a parameter marker normally.

This prevents applications from binding ordinary runtime values to `DATE_ADD`/`DATE_SUB` even though the interval unit remains a SQL keyword.

## Environment

- MatrixOne official `main`: `99ed717b769e261b842c2f17345bd65865fc1470`
- Clean local single-CN build and isolated data directory
- Repeated three times through both MySQL text prepared statements and the MySQL binary prepared-statement protocol

## Reproduction

```sql
SELECT DATE_ADD('2024-02-28 23:59:59.123456', INTERVAL 1 DAY);
-- 2024-02-29 23:59:59.123456

PREPARE p_date FROM 'SELECT DATE_ADD(?, INTERVAL 1 DAY)';
SET @d = '2024-02-28 23:59:59.123456';
EXECUTE p_date USING @d;
-- ERROR 20203 (HY000): invalid argument date_add/date_sub function need two args, bad value 3

PREPARE p_interval FROM 'SELECT DATE_ADD(''2024-02-28 23:59:59.123456'', INTERVAL ? DAY)';
SET @n = 1;
EXECUTE p_interval USING @n;
-- ERROR 20203 (HY000): invalid argument date_add/date_sub function need two args, bad value 3
```

The same failure occurs for `DATE_SUB`. Parameterizing both operands fails as well.

## Control

```sql
PREPARE p_timestampadd FROM
'SELECT TIMESTAMPADD(DAY, ?, ''2024-02-28 23:59:59.123456'')';
SET @n = 1;
EXECUTE p_timestampadd USING @n;
-- 2024-02-29 23:59:59
```

The binary protocol has the same split: `DATE_ADD(?, INTERVAL 1 DAY)` and `DATE_ADD(literal, INTERVAL ? DAY)` both fail, while the binary prepared `TIMESTAMPADD` control succeeds. All outcomes were identical in 3/3 runs.

## Expected behavior

Prepared execution should accept parameter markers in value positions, including the first `DATE_ADD`/`DATE_SUB` operand and the numeric expression after `INTERVAL`; the `DAY` unit remains static SQL syntax. MySQL documents `INTERVAL expr unit` as expression syntax and permits prepared-statement parameter markers where data values occur.

## Preliminary code observation

The SQL is first rewritten from its two surface arguments into the internal three-argument form `(date, interval-value, interval-unit)`. At prepared execution, the binder appears to re-enter the public `date_add/date_sub` validation and rejects that internal form with the observed “need two args, bad value 3” error. This is a diagnosis hypothesis, not a proposed fix.

## Suggested regression coverage

Cover text and binary prepared statements for:

1. `DATE_ADD(?, INTERVAL 1 DAY)`.
2. `DATE_ADD(literal, INTERVAL ? DAY)`.
3. `DATE_SUB` equivalents and repeated executions with different values.
4. A `TIMESTAMPADD` prepared-statement control to preserve the distinct supported path.

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.