matrixorigin / matrixorigin/matrixone
[Bug]: CTAS rejects fractional constant APPROX_PERCENTILE arguments
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
`CREATE TABLE ... AS SELECT` 错误拒绝 `APPROX_PERCENTILE` 的小数常量 percentile。相同表达式在普通 `SELECT`、`VIEW`、派生表和 `INSERT ... SELECT` 中可以执行;CTAS 却将 `0.5` 或显式 `CAST(0.5 AS DOUBLE)` 判断为非常量。
## Environment
- Branch: `main`
- Commit: `0c3a04f390adaf6281fd592a49778ea5b1155e67`
- Deployment: local single-CN cluster with isolated Log/TN/CN services and a fresh data directory
## Steps to reproduce
```sql
create database approx_percentile_ctas;
use approx_percentile_ctas;
create table src(x int);
insert into src values (1),(2),(3),(4),(5),(6),(7),(8),(9);
-- Returns 5.
select approx_percentile(x,0.5) from src;
-- Fails.
create table c_fractional as
select approx_percentile(x,0.5) p from src;
-- The explicit-cast form fails in the same way.
create table c_cast as
select approx_percentile(x,cast(0.5 as double)) p from src;
```
## Actual behavior
Both CTAS statements return:
```text
ERROR 20301 (HY000): invalid input: percentile argument of approx_percentile must be a constant
```
Neither target table remains after the rejected statement. The source table remains unchanged.
## Expected behavior
`0.5` and `CAST(0.5 AS DOUBLE)` are constant percentile expressions. CTAS should create the table and persist the same result (`5`) returned by the direct query.
## Stability and controls
- Fractional literal `0.5`: 3/3 reproductions.
- Explicit `CAST(0.5 AS DOUBLE)`: 3/3 reproductions.
- Direct `SELECT`, `VIEW`, derived-table query and pre-created-table `INSERT ... SELECT`: return `5`.
- CTAS with `p=0` returns `1`; CTAS with `p=1` returns `9`.
- CTAS with the semantically equivalent constant expression `p=1/2` returns `5`.
- Grouped and ungrouped CTAS are both affected.
- `AVG`, `PERCENTILE_CONT(0.5)` and `PERCENTILE_DISC(0.5)` CTAS controls succeed.
## Code analysis
CTAS executes a generated internal `INSERT ... SELECT` from a derived query. The service log shows the fractional constant reaches this follow-up SQL unchanged, but `constructAggregateConfig` rejects its plan expression in `validateApproxPercentileExpr`.
The closely related exact-percentile issue #28631 was fixed by #28728. That change added `normalizeAggregateConfigExpr` only to the `PERCENTILE_CONT` / `PERCENTILE_DISC` branch. The adjacent `APPROX_PERCENTILE` branch still validates/evaluates its configuration without the CTAS normalization used by the exact variants. The literal-form boundary (`0`/`1` and `1/2` work, `0.5`/explicit DOUBLE cast fail) is consistent with a missing normalization step in the CTAS recompile path.
## Regression coverage
After the product fix, add CTAS coverage for grouped and ungrouped `APPROX_PERCENTILE` with integer endpoints, fractional numeric literals, explicit numeric casts, constant arithmetic, NULL groups, and rejection cleanup for genuinely nonconstant/out-of-range arguments.
## Related
- #28631 / #28728: exact `PERCENTILE_CONT` and `PERCENTILE_DISC` CTAS constant normalization (fixed)
- #28870: invalid percentile errors expose compile stack traces; this issue is the separate supported-query rejection
Contributor guide
Assessment
This issue has not been assessed yet.