JackieTien97 commented on code in PR #19227: URL: https://github.com/apache/iotdb/pull/19227#discussion_r4545124535
########## iotdb-core/calc-commons/src/main/java/org/apache/iotdb/calc/execution/operator/source/relational/aggregation/integral/IntegralFunctionIntermediateStateCodec.java: ########## @@ -0,0 +1,179 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.calc.execution.operator.source.relational.aggregation.integral; + +import org.apache.iotdb.calc.execution.operator.source.relational.aggregation.rate.TimeValueBuffer; +import org.apache.iotdb.calc.i18n.CalcMessages; +import org.apache.iotdb.calc.plan.planner.memory.MemoryReservationManager; +import org.apache.iotdb.commons.exception.SemanticException; + +import org.apache.tsfile.block.column.ColumnBuilder; +import org.apache.tsfile.common.conf.TSFileDescriptor; +import org.apache.tsfile.utils.Binary; + +import java.nio.BufferUnderflowException; +import java.nio.ByteBuffer; + +import static org.apache.iotdb.calc.utils.constant.SqlConstant.INTEGRAL; + +/** Serializes and merges the sampled points carried between partial and final integral(). */ +public final class IntegralFunctionIntermediateStateCodec { + + private static final int STATE_VERSION = 1; + private static final int SAMPLE_SIZE = Long.BYTES + Double.BYTES; + private static final int STATE_HEADER_SIZE = Integer.BYTES + Long.BYTES + Integer.BYTES; + + private IntegralFunctionIntermediateStateCodec() {} + + public static void encode( + long timeUnit, + TimeValueBuffer samples, + ColumnBuilder output, + MemoryReservationManager memoryReservationManager) { + if (samples == null || samples.isEmpty()) { + output.appendNull(); + return; + } + + long serializedSize = + Math.addExact(STATE_HEADER_SIZE, Math.multiplyExact((long) samples.size(), SAMPLE_SIZE)); + validateSerializedSize(serializedSize); Review Comment: **Blocking: `integral()` fails once a group has more than ~8,191 points in one DataRegion.** Whenever the aggregation's child has more than one child, `visitAggregation` splits the aggregation into PARTIAL/FINAL. That happens in any database with more than one DataRegion. The PARTIAL state encoded here carries every raw `(time, value)` sample at 16 B each, and its size is capped by `max_tsblock_size_in_bytes` (default 131072). So one group can hold at most (131072 − 16) / 16 = 8,191 points per region. Repro: 1C1D with 4 DataRegions, 8 devices × 10,000 points at 1 Hz, i.e. about 2.8 h of data per device. ```sql SELECT device_id, integral(v) FROM big GROUP BY device_id; -- 701: Intermediate state for aggregate function integral exceeds the maximum TsBlock size of 131072 bytes SELECT device_id, integral(v) FROM big WHERE time < 8000000 GROUP BY device_id; -- OK, 8,000 points per device SELECT device_id, integral(v) FROM big WHERE time < 9000000 GROUP BY device_id; -- the same error, 9,000 points ``` `EXPLAIN` shows four `Aggregation Step: PARTIAL` nodes under `Aggregation Step: FINAL`. `rate()` hits the same limit on this data, so the design is inherited. But for `integral()` it makes the basic per-device query unusable on realistic data volumes, and every raw point crosses the exchange. `integral()` has an O(1) mergeable state, which would avoid this. Each partial keeps its first point, its last point and the area of its own segments. FINAL sorts the partial states by start time, sums their areas and adds the trapezoid between consecutive partials. For `GROUP BY device` the partials cover disjoint time ranges, because different regions hold different time partitions, so the result is exact. Only overlapping ranges, i.e. groups that span devices, need a fallback. The other option is to not split `integral()` into two stages at all. ########## iotdb-core/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/relational/tvf/DerivativeTableFunction.java: ########## @@ -0,0 +1,346 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.commons.udf.builtin.relational.tvf; + +import org.apache.iotdb.commons.exception.SemanticException; +import org.apache.iotdb.commons.i18n.CommonMessages; +import org.apache.iotdb.commons.queryengine.utils.TimestampPrecisionUtils; +import org.apache.iotdb.udf.api.exception.UDFException; +import org.apache.iotdb.udf.api.relational.TableFunction; +import org.apache.iotdb.udf.api.relational.access.Record; +import org.apache.iotdb.udf.api.relational.table.MapTableFunctionHandle; +import org.apache.iotdb.udf.api.relational.table.TableFunctionAnalysis; +import org.apache.iotdb.udf.api.relational.table.TableFunctionHandle; +import org.apache.iotdb.udf.api.relational.table.TableFunctionProcessorProvider; +import org.apache.iotdb.udf.api.relational.table.argument.Argument; +import org.apache.iotdb.udf.api.relational.table.argument.DescribedSchema; +import org.apache.iotdb.udf.api.relational.table.argument.ScalarArgument; +import org.apache.iotdb.udf.api.relational.table.argument.TableArgument; +import org.apache.iotdb.udf.api.relational.table.processor.TableFunctionDataProcessor; +import org.apache.iotdb.udf.api.relational.table.specification.ParameterSpecification; +import org.apache.iotdb.udf.api.relational.table.specification.ScalarParameterSpecification; +import org.apache.iotdb.udf.api.relational.table.specification.TableParameterSpecification; +import org.apache.iotdb.udf.api.type.Type; + +import org.apache.tsfile.block.column.ColumnBuilder; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collections; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.concurrent.TimeUnit; + +public class DerivativeTableFunction implements TableFunction { + + public static final String DATA_PARAMETER_NAME = "DATA"; + public static final String UNIT_PARAMETER_NAME = "UNIT"; + + // duration literals are evaluated in the current timestamp precision, so the 1s default has to + // be expressed in that precision as well + private static final long DEFAULT_UNIT = + TimestampPrecisionUtils.currPrecision.convert(1, TimeUnit.SECONDS); + private static final String OUTPUT_TYPES_PROPERTY = "OUTPUT_TYPES"; + private static final String PARTITION_COLUMN_COUNT_PROPERTY = "PARTITION_COLUMN_COUNT"; + private static final String UNIT_PROPERTY = "UNIT"; + + @Override + public List<ParameterSpecification> getArgumentsSpecifications() { + return Arrays.asList( + TableParameterSpecification.builder() + .name(DATA_PARAMETER_NAME) + .setSemantics() + .ascendingOrder() Review Comment: **This differs from the agreed requirements: `TIMECOL` and `DESC`.** The syntax section and the examples of the derivative requirements use `TIMECOL => 'time'`. They also allow the time column to be strictly ascending or strictly descending, with `DESC` computed as Δv / (Δt / unit). This implementation has neither: ```sql SELECT * FROM DERIVATIVE(DATA => table1 PARTITION BY device_id ORDER BY time, TIMECOL => 'time'); -- 701: Unexpected argument name: TIMECOL SELECT * FROM DERIVATIVE(DATA => table1 PARTITION BY device_id ORDER BY time DESC); -- 701: The ORDER BY clause of the DATA argument must be in ascending order. ``` The PR description matches the code, so either the requirements or the code has to change. Please align them before merging. A minor difference as well: the output time column takes the name of the ORDER BY column (e.g. `ts`), while the requirements call it `time`. ########## iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/ExpressionAnalyzer.java: ########## @@ -1174,6 +1178,51 @@ public Type visitFunctionCall(FunctionCall node, StackableAstVisitorContext<Cont functionName)); } } + break; + case INTEGRAL: + // Syntactic sugar: integral(c1) => integral(c1, '1s', time) + // integral(c1, time_unit) => integral(c1, time_unit, time) + if (argumentsNum == 1) { + node.getArguments().add(new StringLiteral(DEFAULT_INTEGRAL_TIME_UNIT)); + addTimeArgument(node.getArguments(), getActualTimeFieldName(relationType)); + } else if (argumentsNum == 2) { + addTimeArgument(node.getArguments(), getActualTimeFieldName(relationType)); + } else if (argumentsNum == 3) { + if (!checkArgumentIsTimestamp( + node.getArguments().get(2), (List<Field>) relationType.getVisibleFields())) { + throw new SemanticException( + String.format( + DataNodeQueryMessages + .THE_THIRD_ARGUMENT_OF_S_FUNCTION_MUST_BE_ACTUAL_TIME_NAME, + functionName)); + } + } else { + throw new SemanticException( + String.format( + DataNodeQueryMessages + .AGGREGATE_FUNCTIONS_S_SHOULD_ONLY_HAVE_ONE_TWO_OR_THREE_ARGUMENTS_C1ADA261, + functionName)); + } + // Normalize time_unit to ticks of the current timestamp precision once, so that the + // accumulator does not have to parse the duration string at execution time + Expression timeUnitArgument = node.getArguments().get(1); + if (!(timeUnitArgument instanceof LongLiteral)) { Review Comment: **Time unit syntax and validation** - **The two functions take the unit in opposite syntaxes.** - `integral(x, 1s)` does not parse: `700: line 1:32: mismatched input 's'`. This is the duration-literal form that `date_bin` uses, and that this PR's own `DERIVATIVE(..., unit => 1s)` uses. - Only the string form `integral(x, '1s')` works. - `DERIVATIVE` rejects the string form: `701: Invalid scalar argument 'UNIT'. Expected type INT64, got Binary`. - The agreed requirements use `1s` for both. Please pick one form, and align the requirements and the user manual with it. - **Bare numbers are accepted, with precision-dependent meaning and late validation.** - A `LongLiteral` passes through here unchanged, which re-analysis needs. As a side effect, users can write a bare number, which counts as ticks of the current timestamp precision. - `integral(x, 1000)` therefore equals `integral(x, '1s')` with ms precision, but means 1 µs with ns precision. - `integral(x, 0)` and `integral(x, -1000)` are only rejected at execution time, and only when there is data: `SELECT integral(x, 0) FROM table1 WHERE device_id = 'nope'` returns `0.0`. - I suggest rejecting user-written numeric units, or at least non-positive ones, here in the analyzer. For example, mark the normalized literal instead of accepting any `LongLiteral`. - **Two more forms from the requirements don't work.** - `integral(x, '1s', 'time')`, with the time column as a string, fails with `701: the input argument does not exist`. The implementation expects a column reference. - `integral(COLUMNS(*), '1s')` fails because `COLUMNS(*)` also expands to `time` and the tag columns: `701: Aggregate functions [integral] only support numeric data types ...`. The requirements expect one result per numeric column. ########## iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/distribute/TableDistributedPlanGenerator.java: ########## @@ -2270,17 +2293,28 @@ static boolean isInputOrderedByTimeAscending( List<Symbol> groupingKeys, OrderingScheme childOrdering) { String functionName = aggregation.getResolvedFunction().getSignature().getName(); - if (step != SINGLE - || childOrdering == null - || aggregation.getArguments().size() < 2 - || !(RATE.equalsIgnoreCase(functionName) - || INCREASE.equalsIgnoreCase(functionName) - || IRATE.equalsIgnoreCase(functionName) - || DELTA.equalsIgnoreCase(functionName))) { + if (step != SINGLE || childOrdering == null) { + return false; + } + + // integral(value, time_unit, time) takes the time column as the third argument, while the + // rate family takes it as the second one + final int timeArgumentIndex; + if (INTEGRAL.equalsIgnoreCase(functionName)) { Review Comment: **The ordered accumulators are effectively unreachable.** I ran a throwaway `PlanTester` probe over `TestMetadata`. All of these plans have `isInputOrderedByTimeAscending() == false`, and all are planned as PARTIAL/FINAL: ```sql SELECT tag1, tag2, tag3, integral(s1) FROM table1 GROUP BY tag1, tag2, tag3 SELECT integral(s1) FROM table1 SELECT tag1, tag2, tag3, date_bin(1h, time) AS w, integral(s1) FROM table1 GROUP BY tag1, tag2, tag3, date_bin(1h, time) SELECT tag1, tag2, tag3, integral(s1) FROM (SELECT * FROM table1 ORDER BY tag1, tag2, tag3, time) GROUP BY tag1, tag2, tag3 ``` For a streaming aggregation, the expected scan ordering is built from the pre-grouped symbols only, so `time` never appears in `childOrdering`. From the code, the same applies with a single region (SINGLE step): the flag stays false, and the naive accumulator buffers every point of every group. `OrderedIntegralAccumulator` and `GroupedOrderedIntegralAccumulator` (about 310 lines) are therefore not exercised by common queries, and they have no tests. The O(1) memory goal is not reached. Either make a real plan reach them and test that plan, or drop them. ########## iotdb-core/calc-commons/src/main/java/org/apache/iotdb/calc/execution/operator/source/relational/aggregation/integral/IntegralFunctionUtils.java: ########## @@ -0,0 +1,131 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.calc.execution.operator.source.relational.aggregation.integral; + +import org.apache.iotdb.calc.execution.operator.source.relational.aggregation.rate.TimeValueBuffer; +import org.apache.iotdb.calc.i18n.CalcMessages; +import org.apache.iotdb.commons.exception.SemanticException; + +import org.apache.tsfile.block.column.Column; +import org.apache.tsfile.enums.TSDataType; + +import static org.apache.iotdb.calc.utils.constant.SqlConstant.INTEGRAL; + +/** + * Helpers shared by the non-grouped and the grouped implementations of integral(), so that value + * reading, validation messages and the trapezoidal accumulation stay identical in both paths. + */ +public final class IntegralFunctionUtils { + + private IntegralFunctionUtils() {} + + public static double readValue(TSDataType valueDataType, Column column, int position) { + final double value = + switch (valueDataType) { + case INT32 -> column.getInt(position); + case INT64 -> column.getLong(position); + case FLOAT -> column.getFloat(position); + case DOUBLE -> column.getDouble(position); + default -> + throw new SemanticException( + String.format( + CalcMessages + .EXCEPTION_AGGREGATE_FUNCTION_ARG_DOES_NOT_SUPPORT_VALUE_TYPE_ARG_9DD7388D, + INTEGRAL, + valueDataType)); + }; + if (!Double.isFinite(value)) { + throw new SemanticException( + String.format( + CalcMessages + .EXCEPTION_AGGREGATE_FUNCTION_ARG_DOES_NOT_SUPPORT_NON_FINITE_VALUE_COL_ARG_AC2AAC62, + INTEGRAL, + value)); + } + return value; + } + + /** Sorts the samples by time and sums the trapezoidal area of adjacent distinct timestamps. */ + public static double calculateArea(TimeValueBuffer samples) { + // fewer than two effective points have no time span, so the area is 0 + if (samples == null || samples.size() < 2) { + return 0.0; + } + samples.sort(); Review Comment: **Two issues here.** 1. **Duplicate timestamps give non-deterministic results.** - The "later point overwrites" rule assumes that arrival order survives up to this point. But `introSort` is not stable, and FINAL merges the partial states in arbitrary order. - Without GROUP BY, two devices with the same timestamps (`table1` with `d1` and `d2` at 0–3 s) gave `34.5`, `7.5` and `34.5` on three consecutive runs of `SELECT integral(x, '1s') FROM table1`. - If "no semantic guarantee" is accepted, please say in the docs that the result is non-deterministic. Otherwise, define a deterministic tie-break. 2. **The window-function form is quadratic.** - `integral(...) OVER (...)` works; the results I checked are correct, including sliding frames. - However, every frame evaluation re-sorts and rescans the whole buffer, so a running window is O(n²). - On one device, `max(w)` over `integral(v, '1s') OVER (PARTITION BY device_id ORDER BY time)` took 0.9 s for 16k rows, 3.6 s for 32k and 16 s for 64k. `sum()` takes about 70–80 ms on the same rows. - The requirements don't cover window usage. Either reject `integral` as a window function, or make it incremental. Add tests either way. ########## iotdb-api/udf-api/src/main/java/org/apache/iotdb/udf/api/relational/table/specification/OrderByRequirement.java: ########## @@ -0,0 +1,37 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.udf.api.relational.table.specification; + +/** + * The requirement a table function imposes on the ORDER BY clause of a table argument. A table + * parameter specification carrying no requirement accepts any ORDER BY clause, including its + * absence. + */ +public enum OrderByRequirement { Review Comment: **This adds public udf-api surface for UDF authors.** `OrderByRequirement` and `TableParameterSpecification.Builder#ascendingOrder()`, `#descendingOrder()` and `#ordered()` become public API. Nothing in this PR uses `descendingOrder()` or `ordered()`. Please either keep the API to what is used, or describe it in the PR description, including its semantics: every sort item must match the direction, not only the time column. The description should also mention the user-visible message change for M4 and FFT, from "must sort the time column in ascending order" to "must be in ascending order". ########## iotdb-core/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/relational/tvf/SampleTableFunction.java: ########## @@ -0,0 +1,232 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.commons.udf.builtin.relational.tvf; + +import org.apache.iotdb.commons.exception.SemanticException; +import org.apache.iotdb.commons.i18n.CommonMessages; +import org.apache.iotdb.udf.api.exception.UDFException; +import org.apache.iotdb.udf.api.relational.TableFunction; +import org.apache.iotdb.udf.api.relational.access.Record; +import org.apache.iotdb.udf.api.relational.table.MapTableFunctionHandle; +import org.apache.iotdb.udf.api.relational.table.TableFunctionAnalysis; +import org.apache.iotdb.udf.api.relational.table.TableFunctionHandle; +import org.apache.iotdb.udf.api.relational.table.TableFunctionProcessorProvider; +import org.apache.iotdb.udf.api.relational.table.argument.Argument; +import org.apache.iotdb.udf.api.relational.table.argument.DescribedSchema; +import org.apache.iotdb.udf.api.relational.table.argument.ScalarArgument; +import org.apache.iotdb.udf.api.relational.table.argument.TableArgument; +import org.apache.iotdb.udf.api.relational.table.processor.TableFunctionDataProcessor; +import org.apache.iotdb.udf.api.relational.table.specification.ParameterSpecification; +import org.apache.iotdb.udf.api.relational.table.specification.ScalarParameterSpecification; +import org.apache.iotdb.udf.api.relational.table.specification.TableParameterSpecification; +import org.apache.iotdb.udf.api.type.Type; + +import org.apache.tsfile.block.column.ColumnBuilder; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.concurrent.ThreadLocalRandom; + +import static org.apache.iotdb.commons.i18n.CommonMessages.EXCEPTION_SAMPLE_FUNCTION_N_VALIDATION; + +public class SampleTableFunction implements TableFunction { + + public static final String DATA_PARAMETER_NAME = "DATA"; + public static final String SAMPLE_SIZE_PARAMETER_NAME = "N"; + + private static final String OUTPUT_TYPES_PROPERTY = "OUTPUT_TYPES"; + + @Override + public List<ParameterSpecification> getArgumentsSpecifications() { + return Arrays.asList( + TableParameterSpecification.builder().name(DATA_PARAMETER_NAME).setSemantics().build(), + ScalarParameterSpecification.builder() + .name(SAMPLE_SIZE_PARAMETER_NAME) + .type(Type.INT32) + .addChecker( + object -> { + if (object instanceof Number) { + int value = ((Number) object).intValue(); + if (value > 0) { + return null; + } + } + return EXCEPTION_SAMPLE_FUNCTION_N_VALIDATION; + }) + .build()); + } + + @Override + public TableFunctionAnalysis analyze(Map<String, Argument> arguments) throws UDFException { + TableArgument tableArgument = (TableArgument) arguments.get(DATA_PARAMETER_NAME); + + // Sampling is row based: every row is a candidate, regardless of which column it comes from. + // All columns are therefore required from the source and kept in the output, with the + // partition columns moved to the front in PARTITION BY order; the remaining columns keep their + // original relative order. + List<Integer> partitionIndexes = WindowTVFUtils.getPartitionIndexes(tableArgument); + Set<Integer> partitionIndexSet = new HashSet<>(partitionIndexes); + + List<Integer> outputIndexes = new ArrayList<>(tableArgument.size()); + List<Type> outputTypes = new ArrayList<>(tableArgument.size()); + DescribedSchema.Builder schemaBuilder = new DescribedSchema.Builder(); + + for (int partitionIndex : partitionIndexes) { + String columnName = + tableArgument + .getFieldNames() + .get(partitionIndex) + .orElseThrow( + () -> new SemanticException(CommonMessages.EXCEPTION_COLUMN_LACK_OF_NAME)); + Type columnType = tableArgument.getFieldTypes().get(partitionIndex); + outputIndexes.add(partitionIndex); + outputTypes.add(columnType); + schemaBuilder.addField(columnName, columnType); + } + + for (int i = 0; i < tableArgument.size(); i++) { + if (partitionIndexSet.contains(i)) { + continue; + } + Type columnType = tableArgument.getFieldTypes().get(i); + if (columnType == Type.OBJECT) { + throw new SemanticException(CommonMessages.OBJECT_TYPE_COLUMN_NOT_SUPPORTED); + } + outputIndexes.add(i); + outputTypes.add(columnType); + schemaBuilder.addField(tableArgument.getFieldNames().get(i), columnType); + } + + MapTableFunctionHandle handle = + new MapTableFunctionHandle.Builder() + .addProperty( + SAMPLE_SIZE_PARAMETER_NAME, + ((ScalarArgument) arguments.get(SAMPLE_SIZE_PARAMETER_NAME)).getValue()) + .addProperty(OUTPUT_TYPES_PROPERTY, WindowTVFUtils.joinTypes(outputTypes)) + .build(); + + return TableFunctionAnalysis.builder() + .properColumnSchema(schemaBuilder.build()) + .requireRecordSnapshot(false) + .requiredColumns(DATA_PARAMETER_NAME, outputIndexes) + .handle(handle) + .build(); + } + + @Override + public TableFunctionHandle createTableFunctionHandle() { + return new MapTableFunctionHandle(); + } + + @Override + public TableFunctionProcessorProvider getProcessorProvider( + TableFunctionHandle tableFunctionHandle) { + MapTableFunctionHandle handle = (MapTableFunctionHandle) tableFunctionHandle; + int sampleSize = (Integer) handle.getProperty(SAMPLE_SIZE_PARAMETER_NAME); + Type[] outputTypes = + WindowTVFUtils.parseTypes((String) handle.getProperty(OUTPUT_TYPES_PROPERTY)); + return new TableFunctionProcessorProvider() { + @Override + public TableFunctionDataProcessor getDataProcessor() { + return new SampleDataProcessor(sampleSize, outputTypes); + } + }; + } + + /** + * Reservoir sampling over whole rows. All rows of a partition are candidates: the first N rows + * fill the reservoir, and each following row replaces a uniformly chosen slot with probability N + * / (rows seen so far). Rows are buffered until the partition ends because the final sample is + * not known before then. + */ + private static class SampleDataProcessor implements TableFunctionDataProcessor { + + private final int sampleSize; + private final Type[] outputTypes; + private final List<SampledRow> reservoir = new ArrayList<>(); Review Comment: **The reservoir is unbounded and invisible to memory control.** `N` is user-controlled up to `Integer.MAX_VALUE`. Every kept row is captured as boxed objects, outside the query memory pool. I measured it on the DataNode, which has a 1.5 GiB max heap: - `SELECT count(*) FROM sample(DATA => p, N => 4000000)` over 4M rows raised used heap from about 162 MB to about 1,049 MB. - The same query with `N => 10` peaked at about 233 MB. That is roughly 200 B per buffered row. A single partition of about 7M rows with a large N could exhaust the heap, and so could a few concurrent queries. Please bound `N` with a config item, or account the reservoir through memory reservation so the query fails cleanly, or both. Please also document the limit. ########## iotdb-core/node-commons/src/main/i18n/en/org/apache/iotdb/commons/i18n/CommonMessages.java: ########## @@ -322,6 +331,8 @@ private CommonMessages() {} "subscription_cache_memory_usage_percentage must be in [0, 1], but was %s."; public static final String EXCEPTION_FILTER_FUNCTION_WPASS_VALIDATION = "the value of wpass should be in (0, 1)"; + public static final String EXCEPTION_SAMPLE_FUNCTION_N_VALIDATION = Review Comment: nit: per the naming rule in AGENTS.md, this should be `EXCEPTION_N_MUST_BE_A_POSITIVE_INTEGER_47990F38`, i.e. `md5("N must be a positive integer.")` truncated to 8 hex characters. Please rename it in the `zh` file as well. A few other new names don't follow the normalized form either, for example `..._AT_LEAST_ONE_43D975B7` instead of `..._AT_LEAST_1_ARG_43D975B7`. All the hashes are correct, though. ########## iotdb-core/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/relational/tvf/DerivativeTableFunction.java: ########## @@ -0,0 +1,346 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.commons.udf.builtin.relational.tvf; + +import org.apache.iotdb.commons.exception.SemanticException; +import org.apache.iotdb.commons.i18n.CommonMessages; +import org.apache.iotdb.commons.queryengine.utils.TimestampPrecisionUtils; +import org.apache.iotdb.udf.api.exception.UDFException; +import org.apache.iotdb.udf.api.relational.TableFunction; +import org.apache.iotdb.udf.api.relational.access.Record; +import org.apache.iotdb.udf.api.relational.table.MapTableFunctionHandle; +import org.apache.iotdb.udf.api.relational.table.TableFunctionAnalysis; +import org.apache.iotdb.udf.api.relational.table.TableFunctionHandle; +import org.apache.iotdb.udf.api.relational.table.TableFunctionProcessorProvider; +import org.apache.iotdb.udf.api.relational.table.argument.Argument; +import org.apache.iotdb.udf.api.relational.table.argument.DescribedSchema; +import org.apache.iotdb.udf.api.relational.table.argument.ScalarArgument; +import org.apache.iotdb.udf.api.relational.table.argument.TableArgument; +import org.apache.iotdb.udf.api.relational.table.processor.TableFunctionDataProcessor; +import org.apache.iotdb.udf.api.relational.table.specification.ParameterSpecification; +import org.apache.iotdb.udf.api.relational.table.specification.ScalarParameterSpecification; +import org.apache.iotdb.udf.api.relational.table.specification.TableParameterSpecification; +import org.apache.iotdb.udf.api.type.Type; + +import org.apache.tsfile.block.column.ColumnBuilder; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collections; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.concurrent.TimeUnit; + +public class DerivativeTableFunction implements TableFunction { + + public static final String DATA_PARAMETER_NAME = "DATA"; + public static final String UNIT_PARAMETER_NAME = "UNIT"; + + // duration literals are evaluated in the current timestamp precision, so the 1s default has to + // be expressed in that precision as well + private static final long DEFAULT_UNIT = + TimestampPrecisionUtils.currPrecision.convert(1, TimeUnit.SECONDS); + private static final String OUTPUT_TYPES_PROPERTY = "OUTPUT_TYPES"; + private static final String PARTITION_COLUMN_COUNT_PROPERTY = "PARTITION_COLUMN_COUNT"; + private static final String UNIT_PROPERTY = "UNIT"; + + @Override + public List<ParameterSpecification> getArgumentsSpecifications() { + return Arrays.asList( + TableParameterSpecification.builder() + .name(DATA_PARAMETER_NAME) + .setSemantics() + .ascendingOrder() + .build(), + ScalarParameterSpecification.builder() + .name(UNIT_PARAMETER_NAME) + .type(Type.INT64) + .defaultValue(DEFAULT_UNIT) + .addChecker( + object -> { + // a duration smaller than the smallest unit of the current timestamp precision + // has already been truncated to 0 when the argument was evaluated, so explain + // this case explicitly + if (object instanceof Long && (Long) object > 0) { + return null; + } + return String.format( + CommonMessages + .EXCEPTION_DERIVATIVE_FUNCTION_UNIT_SHOULD_BE_A_POSITIVE_DURATION_OF_AT_LEAST_ONE_43D975B7, + TimestampPrecisionUtils.TIMESTAMP_PRECISION); + }) + .build()); + } + + @Override + public TableFunctionAnalysis analyze(Map<String, Argument> arguments) throws UDFException { + TableArgument tableArgument = (TableArgument) arguments.get(DATA_PARAMETER_NAME); + + // ORDER BY is mandatory here and must contain exactly one TIMESTAMP column: the derivative is + // taken with respect to this column, which is also emitted as the time of each output row. + List<String> orderBy = checkAndGetOrderBy(tableArgument); + int timeColumnIndex = + WindowTVFUtils.findColumnIndex( + tableArgument, orderBy.get(0), Collections.singleton(Type.TIMESTAMP)); + + List<Integer> partitionIndexes = WindowTVFUtils.getPartitionIndexes(tableArgument); + Set<Integer> excludedIndexes = new HashSet<>(partitionIndexes); + excludedIndexes.add(timeColumnIndex); + + List<Type> outputTypes = new ArrayList<>(); + DescribedSchema.Builder schemaBuilder = new DescribedSchema.Builder(); + + // keep the partition columns with their original names and types, in PARTITION BY order + for (int partitionIndex : partitionIndexes) { + String columnName = + tableArgument + .getFieldNames() + .get(partitionIndex) + .orElseThrow( + () -> new SemanticException(CommonMessages.EXCEPTION_COLUMN_LACK_OF_NAME)); + Type columnType = tableArgument.getFieldTypes().get(partitionIndex); + outputTypes.add(columnType); + schemaBuilder.addField(columnName, columnType); + } + + // each output row corresponds to a pair of adjacent points and carries the time of the second + // point of the pair, so the time column is emitted as is. + String timeColumnName = + tableArgument + .getFieldNames() + .get(timeColumnIndex) + .orElseThrow(() -> new SemanticException(CommonMessages.EXCEPTION_COLUMN_LACK_OF_NAME)); + outputTypes.add(Type.TIMESTAMP); + schemaBuilder.addField(timeColumnName, Type.TIMESTAMP); + List<Integer> calculationIndexes = + WindowTVFUtils.getCalculationIndexes( + tableArgument, + excludedIndexes, + columnName -> { + outputTypes.add(Type.DOUBLE); + schemaBuilder.addField(convertColumnName(columnName), Type.DOUBLE); + }); + if (calculationIndexes.isEmpty()) { + throw new SemanticException(CommonMessages.EXCEPTION_NO_CALCULATE_COLUMNS); + } + + // the output columns are in the same order as the required input columns: partition columns, + // the time column and one DOUBLE column per calculation column. The processor therefore only + // needs the output types and the number of partition columns to walk both of them. + MapTableFunctionHandle handle = + new MapTableFunctionHandle.Builder() + .addProperty(OUTPUT_TYPES_PROPERTY, WindowTVFUtils.joinTypes(outputTypes)) + .addProperty(PARTITION_COLUMN_COUNT_PROPERTY, partitionIndexes.size()) + .addProperty( + UNIT_PROPERTY, ((ScalarArgument) arguments.get(UNIT_PARAMETER_NAME)).getValue()) + .build(); + + List<Integer> requiredColumns = new ArrayList<>(partitionIndexes); + requiredColumns.add(timeColumnIndex); + requiredColumns.addAll(calculationIndexes); + + return TableFunctionAnalysis.builder() + .properColumnSchema(schemaBuilder.build()) + .requireRecordSnapshot(false) + .requiredColumns(DATA_PARAMETER_NAME, requiredColumns) + .handle(handle) + .build(); + } + + private static List<String> checkAndGetOrderBy(TableArgument tableArgument) { + List<String> orderBy = tableArgument.getOrderBy(); + if (orderBy.isEmpty()) { + throw new SemanticException( + CommonMessages + .EXCEPTION_TABLE_ARGUMENT_WITH_SET_SEMANTICS_REQUIRES_AN_ORDER_BY_CLAUSE_10C986D9); + } + if (orderBy.size() != 1) { + throw new SemanticException( + CommonMessages + .EXCEPTION_THE_ORDER_BY_CLAUSE_OF_THE_DATA_ARGUMENT_MUST_CONTAIN_EXACTLY_ONE_TIMESTAMP_COLUMN_6B40C59B); + } + return orderBy; + } + + private static String convertColumnName(String columnName) { + return String.format("derivative(%s)", columnName); + } + + @Override + public TableFunctionHandle createTableFunctionHandle() { + return new MapTableFunctionHandle(); + } + + @Override + public TableFunctionProcessorProvider getProcessorProvider( + TableFunctionHandle tableFunctionHandle) { + MapTableFunctionHandle handle = (MapTableFunctionHandle) tableFunctionHandle; + Type[] outputTypes = + WindowTVFUtils.parseTypes((String) handle.getProperty(OUTPUT_TYPES_PROPERTY)); + int partitionColumnCount = (Integer) handle.getProperty(PARTITION_COLUMN_COUNT_PROPERTY); + long unit = (Long) handle.getProperty(UNIT_PROPERTY); + return new TableFunctionProcessorProvider() { + @Override + public TableFunctionDataProcessor getDataProcessor() { + return new DerivativeDataProcessor(partitionColumnCount, outputTypes, unit); + } + }; + } + + /** + * Streaming processor over one partition. Each row is paired with the previous one, but the pair + * is not complete until the time of its second point is known to be final, because a later row + * may share that timestamp and overwrite its values. The newest point is therefore buffered and + * the pair ending at it is emitted when a larger timestamp arrives; {@code finish} emits the last + * pair. + * + * <p>The required input columns are the partition columns, the time column (the single ORDER BY + * column of DATA) and the calculation columns, in that order, which is also the order of the + * output columns. + */ + private static class DerivativeDataProcessor implements TableFunctionDataProcessor { + + private final Type[] outputTypes; + private final int partitionColumnCount; + private final int timeColumnIndex; + private final int calculationColumnCount; + private final long unit; + + // all rows of a partition share the same partition values, so they are captured from the first + // row and reused for every output row, including the one emitted in finish where no input row + // is available. + private Object[] partitionValues; + // "previous" is the finalized point right before "current". "current" is the newest point, + // whose values may still be overwritten by a later row with the same timestamp. + private Point previous; + private Point current; + + private DerivativeDataProcessor(int partitionColumnCount, Type[] outputTypes, long unit) { + this.outputTypes = outputTypes; + this.partitionColumnCount = partitionColumnCount; + this.timeColumnIndex = partitionColumnCount; + this.calculationColumnCount = outputTypes.length - partitionColumnCount - 1; + this.unit = unit; + } + + @Override + public void process( + Record input, + List<ColumnBuilder> properColumnBuilders, + ColumnBuilder passThroughIndexBuilder) { + // the ORDER BY column of DATA is not necessarily the implicit time column and may be null; + // such a row cannot be paired with anything and is dropped. + if (input.isNull(timeColumnIndex)) { + return; + } + long timestamp = input.getLong(timeColumnIndex); + + if (current == null) { + capturePartitionValues(input); + current = snapshot(input, timestamp); + return; + } + if (timestamp < current.timestamp) { + throw new SemanticException( + CommonMessages + .EXCEPTION_THE_TIME_COLUMN_OF_THE_DATA_ARGUMENT_MUST_BE_MONOTONICALLY_INCREASING_WITHIN_EACH_PARTITION_68B8C2B7); + } + if (timestamp == current.timestamp) { + // the same timestamp may appear across devices: the later row overwrites the earlier one, + // and the pair ending at this timestamp is not emitted until its value is final + current = snapshot(input, timestamp); + return; + } + + // a larger timestamp proves that no row can overwrite "current" anymore, so the pair ending + // at it is final and can be emitted + if (previous != null) { + emitDerivative(previous, current, properColumnBuilders); + } + previous = current; + current = snapshot(input, timestamp); + } + + @Override + public void finish( + List<ColumnBuilder> properColumnBuilders, ColumnBuilder passThroughIndexBuilder) { + if (previous != null && current != null) { + emitDerivative(previous, current, properColumnBuilders); + // clear the buffered point so that a repeated finish emits nothing + previous = null; + } + } + + private Point snapshot(Record input, long timestamp) { + Object[] values = new Object[calculationColumnCount]; + for (int i = 0; i < calculationColumnCount; i++) { + int columnIndex = timeColumnIndex + 1 + i; + // native boxed values of the calculation columns, null when the value is null + values[i] = input.isNull(columnIndex) ? null : input.getObject(columnIndex); + } + return new Point(timestamp, values); + } + + private void capturePartitionValues(Record input) { + partitionValues = new Object[partitionColumnCount]; + for (int i = 0; i < partitionColumnCount; i++) { + partitionValues[i] = + input.isNull(i) ? null : WindowTVFUtils.readValue(input, i, outputTypes[i]); + } + } + + private void emitDerivative(Point start, Point end, List<ColumnBuilder> properColumnBuilders) { + for (int i = 0; i < partitionColumnCount; i++) { + WindowTVFUtils.writeValue(properColumnBuilders.get(i), partitionValues[i], outputTypes[i]); + } + properColumnBuilders.get(timeColumnIndex).writeLong(end.timestamp); + + long timeDelta = end.timestamp - start.timestamp; + for (int i = 0; i < calculationColumnCount; i++) { + int columnIndex = timeColumnIndex + 1 + i; + ColumnBuilder builder = properColumnBuilders.get(columnIndex); + Object startValue = start.values[i]; + Object endValue = end.values[i]; + // a null on either side only makes this column null for this pair; pairing and time still + // advance normally. + if (startValue == null || endValue == null) { + builder.appendNull(); Review Comment: **The NULL semantics need to be agreed and documented.** Here, a NULL on either side makes the whole pair NULL. For example, values `1.0, null, -4.0, 4.0` at t = 0, 1, 2, 3 s give `null, null, 8.0`. InfluxDB's `DERIVATIVE`, which the requirements use as their reference, computes each field independently and skips missing values. That would give `-2.5` for the (t0, t2) pair, then `8.0`. Neither behavior is wrong, but the requirements don't define this case. Please get it agreed and put it in the user manual. ########## iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/distribute/TableDistributedPlanGenerator.java: ########## @@ -185,6 +187,27 @@ public class TableDistributedPlanGenerator implements PlanVisitor<List<PlanNode>, TableDistributedPlanGenerator.PlanContext> { private static final Logger LOGGER = LoggerFactory.getLogger(TableDistributedPlanGenerator.class); + // Functions that keep per-partition state and therefore require every partition to be handled + // by a single processor. A partition is not guaranteed to be confined to one child of the + // distributed plan: PARTITION BY may use non-device columns, and a device itself may span + // regions. These functions are collected before being evaluated instead of being cloned for + // every child. + private static final Set<String> NON_SPLITTABLE_TABLE_FUNCTIONS = Review Comment: **Blocking: this conflicts with #19155 on master and disables parallelism for shapes that were already split correctly.** 1. **Conflict with master.** I merged this head into master `6e7448d9be`; the merge is textually clean. `TableFunctionTest` then runs 27 tests with 6 failures, all added by #19155: - `testNestedSetSemanticPermutedPartitionsKeepBothGroups` - `testNestedSetSemanticDifferentOrderByKeepsOuterGroup` - `testNestedSetSemanticGlobalOrderByPermutesPartitionColumns` - `testThreeLevelNestedSetSemanticPermutedPartitions` - `testNestedSetSemanticReorderedSubsetFallsBackToSort` - `testNestedSetSemanticGlobalOrderByDeclaredOrder` Master alone passes 26/26, and this PR alone passes 21/21. #19155 deliberately parallelizes nested SESSION, and this list puts SESSION back on a single processor. 2. **Performance.** The most common shape is a direct scan with `PARTITION BY` covering all tags. For that shape the per-child split was already correct: - `ParallelizeGrouping.visitDeviceTableScan` keeps the `GroupNode` only when every tag is a partition key. - `constructDeviceTableScanByTags` routes every cross-region device into a single MergeSort child. So one partition never spans two children. With this list, those plans become a single `TableFunctionProcessor` over a MergeSort of raw scans. The results are identical. Steady-state times on 1C1D with 4 regions, 16 devices × 250k points: | query | base | this PR | |---|---|---| | `M4(... SIZE => 1m)` | ~53 ms | ~92 ms | | `M4(... SIZE => 1s)` | ~65 ms | ~176 ms | | `SESSION(... GAP => 1s)` | ~38 ms | ~65 ms | | `VARIATION(... DELTA => 100.0)` | ~39 ms | ~66 ms | On a multi-node cluster, every raw row additionally has to be shipped to one node. 3. **The bug itself is real, and I reproduced it.** The query is `M4` with `PARTITION BY g` over `SESSION(DATA => pf PARTITION BY (g, dev) ...)`. The 8 devices with `g='A'` are spread over several DataRegions, and all their points fall in one 1h window. Base returns 15 rows, although M4 emits at most 4 per window; this PR returns 3. The root cause is in two places. `ParallelizeGrouping.checkPrefixMatch` returns `ENABLE_PARALLEL` when the outer key `g` is a strict positional prefix of the inner `(g, dev)`. And `canSplitTableFunctionProcessor` trusts `isPartitionedGroup`. A name-based list covers only built-ins, so a user-defined set-semantic TVF in the same shape is still split. Please fix it there instead, keeping the split for the safe shapes. Either don't return `ENABLE_PARALLEL` for a strict prefix after a fan-in (the #19155 javadoc already describes this case), or let the split require that the partition keys cover the child's distribution. `mergeChildrenForSingleProcessor`, which merges by the GroupNode ordering with a SortNode fallback, is a good fix for the single-processor path and should stay. Please also add planner tests for both the split and the non-split shapes. ########## iotdb-core/calc-commons/src/main/java/org/apache/iotdb/calc/execution/operator/source/relational/aggregation/integral/IntegralFunctionUtils.java: ########## @@ -0,0 +1,131 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.calc.execution.operator.source.relational.aggregation.integral; + +import org.apache.iotdb.calc.execution.operator.source.relational.aggregation.rate.TimeValueBuffer; +import org.apache.iotdb.calc.i18n.CalcMessages; +import org.apache.iotdb.commons.exception.SemanticException; + +import org.apache.tsfile.block.column.Column; +import org.apache.tsfile.enums.TSDataType; + +import static org.apache.iotdb.calc.utils.constant.SqlConstant.INTEGRAL; + +/** + * Helpers shared by the non-grouped and the grouped implementations of integral(), so that value + * reading, validation messages and the trapezoidal accumulation stay identical in both paths. + */ +public final class IntegralFunctionUtils { + + private IntegralFunctionUtils() {} + + public static double readValue(TSDataType valueDataType, Column column, int position) { + final double value = + switch (valueDataType) { + case INT32 -> column.getInt(position); + case INT64 -> column.getLong(position); + case FLOAT -> column.getFloat(position); + case DOUBLE -> column.getDouble(position); + default -> + throw new SemanticException( + String.format( + CalcMessages + .EXCEPTION_AGGREGATE_FUNCTION_ARG_DOES_NOT_SUPPORT_VALUE_TYPE_ARG_9DD7388D, + INTEGRAL, + valueDataType)); + }; + if (!Double.isFinite(value)) { Review Comment: **NaN or Infinity anywhere fails the whole query.** One NaN in the series is enough: ``` 701: Aggregate function [integral] does not support non-finite value_col: NaN ``` Sensor data often contains NaN from device faults. The requirements don't say whether NaN should be skipped like NULL, propagated, or rejected. Please get this decided and documented, and add a test for it. -- 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]
