Hi all, Following the earlier requirements discussion, I have prepared an initial technical design for PIVOT and UNPIVOT in the IoTDB table model. It covers static and dynamic PIVOT, as well as single-VALUE and multi-VALUE UNPIVOT.
Related issue: https://github.com/apache/iotdb/issues/17799 Syntax and analysis Both PIVOT and UNPIVOT will support simplified syntax and SQL-style syntax in top-level queries, CTEs, and subqueries. The two syntax forms enter through different grammar rules but produce the same relation AST nodes. They then follow the existing analysis, planning, and query execution pipeline. The design introduces no external interfaces or third-party dependencies. PIVOT planning and dynamic value discovery For static PIVOT, the IN values determine the output columns during analysis, so the input query does not need to be executed beforehand. For dynamic PIVOT, the output columns depend on the input data. Before the main analysis, a preparation step executes the input query once, computes and materializes the required grouping columns, ON expressions, and aggregate arguments, and discovers the dynamic ON values during the same pass. The discovered values are deduplicated and ordered by their canonical string representations to determine the output schema. During planning, static PIVOT computes its input projections. Dynamic PIVOT reads the same materialized input and maps its columns to plan symbols. Once the input columns are prepared, both paths share the plan generation for masks, aggregation, and final projection. The dynamic path does not query the original input again. pivot_limit bounds the number of output columns for each PIVOT and is checked before value combinations are expanded. Dynamic input materialization has separate byte and row budgets; exceeding either budget fails the query rather than falling back to another scan. The proposed defaults are 64 MiB and 1,000,000 rows, subject to evaluation against representative workloads and concurrency levels. UNPIVOT planning and execution During analysis, UNPIVOT binds its source items, determines a common type for each VALUE position, and establishes the output schema. A Project node evaluates expressions and performs the necessary casts before a new UnpivotNode and UnpivotOperator expand the rows. The operator processes the input block by block and row by row, retaining only the current input and output blocks rather than materializing the entire expanded result. By default, EXCLUDE NULLS skips a source item if any of its VALUE fields is NULL after conversion. SQL-style syntax also supports INCLUDE NULLS, which retains such rows. ORDER BY and LIMIT apply to the expanded result; LIMIT is not pushed below UNPIVOT. The design also distinguishes the table model’s placeholder time column from a real time value carried as an ordinary value column. I would especially appreciate feedback on the following points: Is materializing dynamic PIVOT input once and reusing it for aggregation a good fit for IoTDB? Given that final aggregation runs on the coordinator, should we add large-input performance tests or safeguards against excessive plan size? Are the proposed materialization budgets and the limit of eight nested dynamic PIVOTs along a single nesting path reasonable? Do single-node masked aggregation and the lack of DISTINCT aggregates in USING introduce compatibility or performance concerns that should be addressed in the first version? Are the type conversion, NULL handling, and LIMIT pushdown boundaries of the streaming UNPIVOT operator sufficiently defined? Best regards, Xinqi Zhao
