PDGGK commented on PR #19152:
URL: https://github.com/apache/iotdb/pull/19152#issuecomment-6703091317

   Thanks for this, it's a big feature. I built 632fe25 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.
   


-- 
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]

Reply via email to