CoollZzz commented on PR #19152: URL: https://github.com/apache/iotdb/pull/19152#issuecomment-6760506353
> Thanks for this, it's a big feature. I built [632fe25](https://github.com/apache/iotdb/commit/632fe2583a6f866a7621c3051599fc9e24a165e6) and ran it as a standalone server, and tried it against hand-written equivalents. Most of it held up well; two things came up. > > **What I compared** > > The table had 40 devices and 480 rows with NULLs, in a database with 3 data regions. I used a `region` FIELD column and a copy where `region` is a TAG. > > * 20 PIVOT queries were each compared with the same query written as `agg(CASE WHEN … THEN … END)`. These covered static and dynamic values, several aggregates, `first`/`last`, two ON items, an ON expression, the SQL-style form with aliases, implicit grouping, grouping by a TAG, `ORDER BY … LIMIT/OFFSET`, a WHERE on the result, and a JOIN. 18 matched cell for cell. In the other two my equivalent was wrong (implicit grouping), or the PIVOT is rejected on purpose (`GROUP BY date_bin(...)`). > * UNPIVOT output matched the source columns row by row: 819 rows for `temperature, humidity`, and 960 with `INCLUDE NULLS`. With `cnt, big` (INT32 + INT64), the INT64 values came back exact. > * `name`, `include`, `exclude`, `pivot` and `unpivot` worked unquoted as table and column names in `CREATE TABLE`, `INSERT` and `SELECT`, and as an alias. > * With about 2 million rows, the static PIVOT returned the same result as the `CASE WHEN` query: 1.37 s against 1.19 s. > > **1. An empty string becomes an empty column name** > > ```sql > CREATE TABLE cc (device_id STRING TAG, region STRING FIELD, v INT32 FIELD); > INSERT INTO cc(time, device_id, region, v) VALUES (1, 'd1', '', 8), (2, 'd1', 'x-y', 16); > PIVOT cc ON region USING sum(v) GROUP BY device_id; > ``` > > This returns a column whose name is empty, and the static form `ON region IN ('')` does the same. That name cannot be used anywhere else: `SELECT "" FROM (PIVOT …)` fails with `Zero-length delimited identifier not allowed`, as do `SELECT 1 AS ""` and `CREATE TABLE t (… "" INT32 FIELD)`. In `pivotValueName` a string value is used as the name directly. Rejecting an empty name, with a message that asks for `AS`, or giving such columns a fixed name would avoid this. > > **2. Dynamic PIVOT and `pivot_input_max_rows`** > > As described, a dynamic PIVOT fails once its input is larger than `pivot_input_max_rows`. I wanted to see what that means for a table of ordinary size: > > query, about 2 million rows result > `PIVOT big ON region IN ('east','north','south') USING avg(temperature) GROUP BY device_id` 40 rows, 1.37 s > `PIVOT big ON region USING avg(temperature) GROUP BY device_id` `719: Dynamic PIVOT input exceeds pivot_input_max_rows` > the same, with a WHERE that keeps 491,520 rows 40 rows, 1.14 s; the static form takes 0.99 s > `SELECT DISTINCT region FROM big` 4 rows, 0.83 s > With the default limit, a dynamic PIVOT over any table with more than a million rows fails, unless the user filters it first or raises the limit. In IoTDB that is not a large table: 40 devices at one sample per second reach it in about seven hours. Was finding the values with a separate `SELECT DISTINCT` on the ON expressions considered? The values would then come from a slightly earlier snapshot than the aggregation, which I understand is what materializing the input avoids. On the other hand, the size of the input would no longer matter. I'm not sure which matters more here, so I'm just raising it. > > A smaller point: a TIMESTAMP value gives a column named by its number, for example `1704067200000` for `ON date_bin(1d, time)`. That name would be different with another `timestamp_precision`. It works fine with `AS`. > > The unit tests added in this PR pass for me (`AnalyzerTest`, `PivotUnpivotParserTest`, `CoordinatorPivotInputPreparerTest`, `UnpivotOperatorTest` and the others), and so do `spotless:check` and a `with-zh-locale` compile. I did not run the ITs. Thank you for testing this and raising the scalability concern. We considered using a separate SELECT DISTINCT to determine the ON values. However, under IoTDB’s current read model, the discovery and aggregation scans do not share a query-level MVCC snapshot. If concurrent writes occur between the scans, they may read different rows, making the output column set inconsistent with the data used for aggregation. This PR instead materializes the input in a single scan and discovers the ON values from those rows. The aggregation then uses the same materialized input, ensuring that the output columns and aggregate results correspond to the same set of rows. Supporting separate scans that share a query snapshot would require broader changes to the storage engine. I agree that the current input limits affect the use of dynamic PIVOT on large tables, and this limitation needs further evaluation. Possible improvements include spilling the materialized input to disk or providing a query snapshot that can be shared across scans. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
