You are viewing a plain text version of this content. The canonical link for it is here.

Posted to reviews@iotdb.apache.org by "JackieTien97 (via GitHub)" <gi...@apache.org> on 2026/05/30 11:03:53 UTC

[I] [Feature] Support PIVOT and UNPIVOT statements (DuckDB-style syntax) [iotdb]

JackieTien97 opened a new issue, #17799:
URL: https://github.com/apache/iotdb/issues/17799

   ### Motivation
   
   Reshaping data between **wide** and **long** form is a very common need in time-series analytics and reporting:
   
   - **PIVOT (long → wide):** turn distinct values of a column (e.g. `region`, `device_id`, a status code) into separate columns, aggregating a measurement per cell. Great for building cross-tab/report-style result sets.
   - **UNPIVOT (wide → long):** stack multiple measurement columns (e.g. `temperature`, `humidity`, `pressure`) into a `(name, value)` pair. This is especially natural for IoTDB, where the table model stores each measurement as its own column and users frequently want a normalized long format for downstream analytics or export.
   
   Today the table model has no `PIVOT` / `UNPIVOT`, so users must hand-write verbose `CASE WHEN ... END` aggregations (for pivoting) or long `UNION ALL` chains (for unpivoting). This proposes first-class `PIVOT` / `UNPIVOT` support, **using DuckDB's syntax as the reference**, since DuckDB offers both the SQL-standard form and a friendly simplified form, and is the most ergonomic of the mainstream engines.
   
   > Examples below assume a table model table like `device_metrics(time, device_id, region, temperature, humidity)`.
   
   ---
   
   ### Proposed Syntax (reference: DuckDB)
   
   DuckDB implements **two** syntaxes for each statement: a simplified (DuckDB-specific) form and the SQL-standard form. We can adopt one or both.
   
   #### PIVOT
   
   **Simplified form**
   
   ```sql
   PIVOT ⟨table⟩
   ON ⟨columns⟩                 -- distinct values become new columns
   USING ⟨aggregate(s)⟩         -- value of each cell
   GROUP BY ⟨rows⟩              -- remaining row keys
   [ORDER BY ...] [LIMIT ...];
   ```
   
   IoTDB example — turn `region` values into columns of average temperature, one row per device:
   
   ```sql
   PIVOT device_metrics
   ON region
   USING avg(temperature)
   GROUP BY device_id;
   ```
   
   Restrict to specific values with `IN`, and use multiple/aliased aggregates:
   
   ```sql
   PIVOT device_metrics
   ON region IN ('north', 'south')
   USING avg(temperature) AS avg_temp, max(temperature) AS max_temp
   GROUP BY device_id;
   -- => columns: device_id, north_avg_temp, north_max_temp, south_avg_temp, south_max_temp
   ```
   
   To reshape **without** aggregating, DuckDB uses `first()` (e.g. `USING first(temperature)`).
   
   **SQL-standard form**
   
   ```sql
   SELECT * FROM device_metrics
   PIVOT (
     avg(temperature) AS avg_temp
     FOR region IN ('north', 'south')
     GROUP BY device_id
   );
   ```
   
   #### UNPIVOT
   
   **Simplified form**
   
   ```sql
   UNPIVOT ⟨table⟩
   ON ⟨value-columns⟩
   INTO NAME ⟨name-col⟩ VALUE ⟨value-col⟩;
   ```
   
   IoTDB example — stack measurement columns into long `(measurement, value)` rows:
   
   ```sql
   UNPIVOT device_metrics
   ON temperature, humidity
   INTO NAME measurement VALUE value;
   -- => columns: time, device_id, region, measurement, value
   ```
   
   Dynamic column selection with `COLUMNS(* EXCLUDE (...))` (keeps working when new measurements are added):
   
   ```sql
   UNPIVOT device_metrics
   ON COLUMNS(* EXCLUDE (time, device_id, region))
   INTO NAME measurement VALUE value;
   ```
   
   **SQL-standard form** (with optional `INCLUDE NULLS`; default drops rows whose value is NULL):
   
   ```sql
   FROM device_metrics
   UNPIVOT [INCLUDE NULLS] (
     value FOR measurement IN (temperature, humidity)
   );
   ```
   
   ---
   
   ### Capabilities to cover (from DuckDB)
   
   - **PIVOT**: `ON` (one or more columns), `USING` (one or more aggregates, optional `AS` alias), `GROUP BY` rows; optional `IN (...)` list to fix the pivoted values; generated column naming like `⟨value⟩_⟨agg-alias⟩`.
   - **UNPIVOT**: `ON` explicit columns or `COLUMNS(* EXCLUDE (...))`, `INTO NAME ... VALUE ...`; `INCLUDE NULLS`; (advanced) multiple value columns in one statement; expressions/casts inside `ON` to reconcile differing column types.
   - Usable as a top-level statement and inside subqueries / CTEs.
   - (DuckDB also exposes `PIVOT_WIDER` / `PIVOT_LONGER` as aliases — optional.)
   
   ### Design decisions to discuss before implementing
   
   1. **Which syntax first** — simplified, SQL-standard, or both. (Recommend landing one end-to-end first, then the other.)
   2. **Static vs. dynamic columns (biggest one).** `ON region` without an `IN` list requires discovering distinct values at runtime, so the **output schema isn't known at plan time**. Proposal: require an explicit `IN (...)` list in v1 (static schema), and treat auto-detection as a follow-up (it needs a pre-execution scan of the source).
   3. **Default grouping.** DuckDB defaults to `GROUP BY ALL`. In IoTDB, defaulting to include the `time` column would explode cardinality — so we should likely **require an explicit `GROUP BY`** (or define a sensible default that excludes `time`).
   4. **NULL handling for UNPIVOT.** Default drops NULL values; support `INCLUDE NULLS`.
   5. **Type unification for the UNPIVOT `value` column.** When source columns differ in type, do we implicit-cast or require explicit casts (DuckDB requires explicit casts)?
   6. **Generated column naming** rules, and how non-identifier pivot values (e.g. values with spaces) are quoted.
   7. **Scope/phasing** of multi-column `ON`, multiple aggregates, and multiple value columns.
   
   ### Prior Art
   
   | Engine | PIVOT | UNPIVOT | Auto-detect (dynamic) columns | Friendly/simplified syntax |
   |---|:---:|:---:|:---:|:---:|
   | DuckDB (reference) | ✅ | ✅ | ✅ | ✅ (`ON`/`USING`/`INTO`) |
   | SQL Server | ✅ | ✅ | ❌ (must list values) | ❌ |
   | Oracle | ✅ | ✅ | ❌ | ❌ |
   | Snowflake | ✅ | ✅ | ✅ (`ANY` / subquery) | ❌ |
   | Spark SQL | ✅ | ✅ | ❌ (must list values) | ❌ |
   
   ### Acceptance Criteria
   
   - [ ] `PIVOT` with `ON` / `USING` / `GROUP BY` and an explicit `IN (...)` list (static output schema).
   - [ ] `UNPIVOT` with `INTO NAME ... VALUE ...` (simplified) and/or `FOR ... IN (...)` (SQL-standard).
   - [ ] `INCLUDE NULLS` option for `UNPIVOT` (default = drop NULLs).
   - [ ] `PIVOT` / `UNPIVOT` usable inside subqueries and CTEs.
   - [ ] Documented column-naming and value-column type rules.
   - [ ] Tests + user documentation.
   - [ ] (Phase 2) dynamic column auto-detection, multiple aggregates, `COLUMNS(* EXCLUDE ...)`, multiple value columns.
   
   ### References
   
   - DuckDB — PIVOT statement: https://duckdb.org/docs/stable/sql/statements/pivot
   - DuckDB — UNPIVOT statement: https://duckdb.org/docs/stable/sql/statements/unpivot
   - DuckDB — Friendly SQL (background): https://duckdb.org/2023/08/23/even-friendlier-sql
   
   ---
   
   > Note: this can be split into two independently claimable tasks — **PIVOT** and **UNPIVOT** — if preferred.
   >
   > _Suggested labels: `enhancement`, table model / SQL._


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Feature] Support PIVOT and UNPIVOT statements (DuckDB-style syntax) [iotdb]

Posted by "Cool6689 (via GitHub)" <gi...@apache.org>.

Cool6689 commented on issue #17799:
URL: https://github.com/apache/iotdb/issues/17799#issuecomment-4704189982

   I'm working on this issue 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Feature] Support PIVOT and UNPIVOT statements (DuckDB-style syntax) [iotdb]

Posted by "DaZuiZui (via GitHub)" <gi...@apache.org>.

DaZuiZui commented on issue #17799:
URL: https://github.com/apache/iotdb/issues/17799#issuecomment-4620232088

I would like to propose implementing #17799 in phases.

This first PR will address only the **UNPIVOT** part of #17799. **PIVOT** and advanced UNPIVOT features will be handled in follow-up PRs **after the Phase 1 PR has been reviewed and merged**.

The first PR should be marked as `Part of #17799`.

It should not use `Fixes #17799` or `Closes #17799`, because this PR will not complete the whole issue.

## Phase 1: UNPIVOT v1

## Feature Definition

`UNPIVOT` converts multiple measurement columns in the IoTDB table model from a **wide-table** format into a **long-table** format.

Before UNPIVOT:

```text
time, device_id, region, temperature, humidity
```

After UNPIVOT:

```text
time, device_id, region, measurement, value
```

Here:

- `measurement` stores the original source column name, such as `temperature` or `humidity`.
- `value` stores the corresponding source column value.

This is useful for IoT scenarios where measurements are stored as separate columns, but downstream analytics, export, or visualization prefers a normalized `(measurement, value)` format.

## Syntax

`UNPIVOT v1` will support a subset of DuckDB simplified UNPIVOT syntax, with `INCLUDE NULLS` added to satisfy the NULL handling requirement in #17799.

```sql
UNPIVOT [INCLUDE NULLS] source_relation
ON value_column [, value_column ...]
INTO NAME name_column VALUE value_column_alias;
```

Example:

```sql
UNPIVOT device_metrics
ON temperature, humidity
INTO NAME measurement VALUE value;
```

It can also be used in subqueries:

```sql
SELECT *
FROM (
UNPIVOT device_metrics
ON temperature, humidity
INTO NAME measurement VALUE value
);
```

And with CTEs:

```sql
WITH long_metrics AS (
UNPIVOT device_metrics
ON temperature, humidity
INTO NAME measurement VALUE value
)
SELECT * FROM long_metrics;
```

## Syntax Components

### `UNPIVOT`

Performs the wide-to-long transformation.

### `INCLUDE NULLS`

Optional.

By default, rows whose unpivoted value is `NULL` are dropped. If `INCLUDE NULLS` is specified, those rows are kept.

### `source_relation`

The input relation.

In v1, I propose to support:

- normal table names
- CTE names
- parenthesized subqueries

For more complex inputs such as joins, users can wrap them in a subquery or CTE first.

### `ON value_column [, ...]`

Specifies the source columns to unpivot.

In v1, only explicit column names are supported. The following are not included in this phase:

- expressions
- `CAST`
- wildcards
- dynamic column selection
- `COLUMNS(* EXCLUDE (...))`

### `INTO NAME name_column`

Specifies the generated column that stores the original source column name.

The generated name column should use the existing text type in the IoTDB table model, either `STRING` or `TEXT` depending on the current internal type system.

### `VALUE value_column_alias`

Specifies the generated column that stores the original source column value.

Its type is the same as the strictly identical type of all columns listed in the `ON` clause.

## Output Columns

The output columns are:

1. visible columns from `source_relation` that are not listed in `ON`
2. `name_column`
3. `value_column_alias`

For example, given source columns:

```text
time, device_id, region, temperature, humidity
```

The query:

```sql
UNPIVOT device_metrics
ON temperature, humidity
INTO NAME measurement VALUE value;
```

produces:

```text
time, device_id, region, measurement, value
```

`temperature` and `humidity` are no longer output as separate columns.

## NULL Semantics

By default, `UNPIVOT` drops `NULL` values.

```sql
UNPIVOT device_metrics
ON temperature, humidity
INTO NAME measurement VALUE value;
```

is semantically equivalent to:

```sql
SELECT time, device_id, region,
'temperature' AS measurement,
temperature AS value
FROM device_metrics
WHERE temperature IS NOT NULL

UNION ALL

SELECT time, device_id, region,
'humidity' AS measurement,
humidity AS value
FROM device_metrics
WHERE humidity IS NOT NULL;
```

With `INCLUDE NULLS`, the `IS NOT NULL` filters are not applied.

`UNPIVOT` should not guarantee output row order. Users should use `ORDER BY` if they need stable ordering. Tests should also use explicit ordering.

## Type Rules

In v1, all columns listed in the `ON` clause must have exactly the same type.

Supported types should follow the existing IoTDB table model scalar types, such as:

```text
BOOLEAN
INT32
INT64
FLOAT
DOUBLE
TEXT / STRING
TIMESTAMP
DATE
BLOB
```

There should be no automatic type coercion in v1. For example:

- `INT32 + INT64` should not be automatically coerced.
- `FLOAT + DOUBLE` should not be automatically coerced.
- `TEXT + STRING` should follow the existing IoTDB type system.

This strict type check should be performed in the `UNPIVOT` analyzer before planning. We should not rely only on existing `UNION ALL` analysis, because set operations may find a common super type, which would conflict with the strict v1 semantics.

## Validation Rules

`UNPIVOT v1` should validate that:

- `ON` columns exist
- `ON` columns are not duplicated
- `ON` columns are visible columns
- `name_column` does not conflict with retained output columns
- `value_column_alias` does not conflict with retained output columns
- `name_column` and `value_column_alias` do not conflict with each other
- quoted identifier and case-insensitive conflicts follow existing IoTDB identifier rules

## Implementation Plan

I propose not to rewrite `UNPIVOT` into `UNION ALL` in the pure parser or pre-analyzer rewrite phase, because source visible columns, hidden columns, types, and CTE/subquery schemas are only known after analysis.

A safer implementation flow is:

1. Add `UNPIVOT` as a `queryPrimary` / query body alternative in the relational SQL grammar.
2. Parser builds an `Unpivot` AST node.
3. Analyzer analyzes `source_relation` first, then validates `ON` columns, visibility, duplicates, output-name conflicts, and strict same-type rules.
4. Analyzer determines the static output schema for the `Unpivot` node.
5. Planner expands `Unpivot` into an equivalent `UNION ALL` plan, or reuses existing set operation planning where possible.

Placing `UNPIVOT` at the `queryPrimary` / query body level makes it naturally support top-level usage, subqueries, and CTEs, which matches the issue requirement and DuckDB simplified syntax.

## Test Plan

The first phase should cover at least:

- top-level `UNPIVOT`
- CTE input
- subquery input
- default NULL dropping
- `INCLUDE NULLS`
- missing `ON` column
- duplicate `ON` column
- `name` / `value` output column conflict
- mismatched value column types
- `INT32 + INT64` not automatically coerced
- quoted identifier / case-insensitive conflicts

## Phased Plan

I plan to implement this issue in phases.

Phase 1 will focus only on `UNPIVOT v1`. It will support:

- explicit value columns
- static output schema
- default NULL dropping
- optional `INCLUDE NULLS`
- strict same-type validation
- usage as a top-level query, subquery, or CTE

After the Phase 1 PR is reviewed and merged, I will start Phase 2 in follow-up PRs.

Phase 2 will cover `PIVOT` and advanced `UNPIVOT` features, such as:

- dynamic column selection
- `COLUMNS(* EXCLUDE (...))`
- expressions
- `CAST`
- automatic type coercion
- multiple `VALUE` columns

The reason for waiting until Phase 1 is merged before starting Phase 2 is that `UNPIVOT v1` introduces the basic grammar, AST, analyzer, planner, and test structure for this family of syntax. It is better to first get agreement from reviewers on these foundations before adding more complex features on top of them.

This split also keeps the first PR small and reviewable. `UNPIVOT v1` has a static output schema, so it can be analyzed and planned safely. `PIVOT`, especially dynamic `PIVOT` without an explicit `IN` list, may require runtime discovery of distinct values, which makes output schema determination more complex.

Doing `PIVOT` before the `UNPIVOT` foundation is reviewed could increase implementation risk and make the PR harder to review.

--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@iotdb.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org