matrixorigin / matrixorigin/matrixone

[Bug]: CTAS rejects fractional constant APPROX_PERCENTILE arguments

Open
#28,886 1 comment 0 reactions 1 assignee Assigned to @XuPeng-SH View on GitHub
kind/bug needs-triage
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

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.