This is an automated email from the ASF dual-hosted git repository.
mrhhsg pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 69d25572db1 [fix](ntile) Accept constant expressions that fold to a
positive integer as NTILE bucket (#68503)
69d25572db1 is described below
commit 69d25572db19d3f91d7bec4f0b83e07e98ccf06f
Author: Jerry Hu <[email protected]>
AuthorDate: Thu Oct 15 15:59:53 2026 +0800
[fix](ntile) Accept constant expressions that fold to a positive integer as
NTILE bucket (#68503)
### What problem does this PR solve?
Issue Number: None
Related PR: #58200
Problem Summary:
`ntile(1 + 1) OVER (ORDER BY k)` was rejected at analysis time, although
the bucket is a constant expression that folds to `2`:
```
mysql> SELECT ntile(1 + 1) OVER (ORDER BY k) AS bucket
-> FROM (SELECT 1 AS k UNION ALL SELECT 2 UNION ALL SELECT 3) t;
ERROR 1105 (HY000): errCode = 2, detailMessage = The bucket parameter of
NTILE
must be a constant positive integer: ntile((1 + 1))
```
LAG/LEAD (#58200) and NTH_VALUE already accept such expressions, because
their literal checks run after constant folding.
Root cause: `Ntile.checkLegalityBeforeTypeCoercion` runs while the
function
is being bound in the analyzer, before `FoldConstant` has turned `1 + 1`
into a literal, and it required the argument to already be a `Literal`.
Every non-literal constant expression therefore failed the check.
Fix: keep the integral-type, LARGEINT and constant checks as they are,
then
fold the bucket for validation with
`ExpressionUtils.foldConstantArgument`
(the shared helper introduced in #68579) and require the folded value to
be
an integer literal greater than zero. Constant expressions such as `1 +
1`,
`cast(5 - 2 as bigint)` or `abs(-3)` are now accepted, while `0`, `1 -
1`,
`1 - 2`, a NULL result such as `cast('abc' as int)`, a non-constant
column
and a LARGEINT bucket are still rejected with the existing error
messages.
The backend registers `ntile` only for a non-nullable integer argument
and
reads the bucket count from the argument column, so the bucket must
still be
a literal when the plan is translated. A `checkLegalityAfterRewrite` is
added
that requires a positive integer literal after rewrite (the same
approach LAG
and LEAD use). When constant folding is skipped with
`debug_skip_fold_constant=true`, an unfolded expression such as
`cast('3' as int)` is now rejected with the NTILE message instead of
failing
in the backend with `Agg Function ntile(int) is not implemented`.
After the fix the query above returns `1, 1, 2`.
### Release note
None
### Check List (For Author)
- Test:
- Unit Test: `NtileBucketTest` covers a literal bucket, foldable
constant expressions, expressions folded to zero/negative/NULL,
non-constant, non-integral and LARGEINT buckets, and the
after-rewrite check for literal and unfolded buckets.
- Regression test: `test_ntile_function` adds `ntile(1 + 1)`,
`ntile(cast(5 - 2 as bigint))`, `ntile(abs(-3))` with generated
output, error cases for `ntile(1 - 1)`, `ntile(1 - 2)` and
`ntile(cast('abc' as int))`, and cases with
`debug_skip_fold_constant=true`.
- Baseline update: merged current master and reused the shared
constant-argument helper. The net PR diff passes `git diff --check`; FE
UT and regression were not rerun after this update because
`thirdparty/installed/bin/protoc` is unavailable in this worktree. CI is
pending.
- Behavior changed: Yes (NTILE now accepts a constant bucket expression
that folds to a positive integer; such queries previously failed)
- Does this need documentation: No
---
.../glue/translator/ExpressionTranslator.java | 9 ++
.../analysis/ReplaceExpressionByChildOutput.java | 8 +-
.../trees/expressions/functions/window/Ntile.java | 29 +++-
.../ReplaceExpressionByChildOutputTest.java | 27 ++++
.../functions/window/NtileBucketTest.java | 178 +++++++++++++++++++++
.../window_functions/test_ntile_function.out | 121 ++++++++++++++
.../window_functions/test_ntile_function.groovy | 73 +++++++++
7 files changed, 438 insertions(+), 7 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
index 948238357a4..7676cfb82c6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
@@ -112,6 +112,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.udf.JavaUdtf;
import org.apache.doris.nereids.trees.expressions.functions.udf.PythonUdaf;
import org.apache.doris.nereids.trees.expressions.functions.udf.PythonUdf;
import org.apache.doris.nereids.trees.expressions.functions.udf.PythonUdtf;
+import org.apache.doris.nereids.trees.expressions.functions.window.Ntile;
import
org.apache.doris.nereids.trees.expressions.functions.window.WindowFunction;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionVisitor;
@@ -484,6 +485,14 @@ public class ExpressionTranslator extends
DefaultExpressionVisitor<Expr, PlanTra
return in;
}
+ @Override
+ public Expr visitNtile(Ntile ntile, PlanTranslatorContext context) {
+ // The rewrite rules that fold the bucket and run
checkLegalityAfterRewrite on window expressions can be
+ // disabled, so check again before the bucket reaches the backend,
which uses it as a divisor.
+ ntile.checkLegalityAfterRewrite();
+ return visitWindowFunction(ntile, context);
+ }
+
@Override
public Expr visitWindowFunction(WindowFunction function,
PlanTranslatorContext context) {
// translate argument types from DataType to Type
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutput.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutput.java
index cd53086f966..1dc703d5584 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutput.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutput.java
@@ -72,7 +72,13 @@ public class ReplaceExpressionByChildOutput implements
AnalysisRuleFactory {
for (NamedExpression expr : output) {
if (expr instanceof Alias) {
Alias alias = (Alias) expr;
- sMap.put(alias.child(), alias.toSlot());
+ // replacing a constant sub-expression anywhere it
structurally recurs, e.g. inside
+ // a function argument that must fold to a literal (NTILE's
bucket, LAG/LEAD's offset),
+ // gives no benefit since a constant is folded once anyway,
and can only break such a
+ // position wherever the constant happens to also be an
unrelated output value.
+ if (!alias.child().isConstant()) {
+ sMap.put(alias.child(), alias.toSlot());
+ }
}
}
return sMap;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/Ntile.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/Ntile.java
index bbb915cdcb8..c02f96bda84 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/Ntile.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/Ntile.java
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.functions.AlwaysNotNullable;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLikeLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.shape.LeafExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
@@ -30,6 +31,7 @@ import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.IntegerType;
import org.apache.doris.nereids.types.SmallIntType;
import org.apache.doris.nereids.types.TinyIntType;
+import org.apache.doris.nereids.util.ExpressionUtils;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
@@ -80,12 +82,27 @@ public class Ntile extends WindowFunction implements
LeafExpression, AlwaysNotNu
throw new AnalysisException(
"The bucket of NTILE must be a constant value: " +
this.toSql());
}
- if (buckets instanceof Literal) {
- if (((Literal) buckets).getDouble() <= 0) {
- throw new AnalysisException(
- "The bucket parameter of NTILE must be a constant positive
integer: " + this.toSql());
- }
- } else {
+ // The bucket may be a constant expression such as `1 + 1`, which is
folded to a literal only by the
+ // rewrite phase after this check runs. Reject early a bucket that FE
already evaluates to a non-positive
+ // or NULL value. A bucket FE can not evaluate, e.g. `3 % 2`, may
still be folded by BE when
+ // enable_fold_constant_by_be is set, so it is left to
checkLegalityAfterRewrite.
+ Expression evaluated = ExpressionUtils.foldConstantArgument(buckets);
+ if (evaluated instanceof Literal) {
+ checkPositiveBucket(evaluated);
+ }
+ }
+
+ @Override
+ public void checkLegalityAfterRewrite() {
+ // The backend reads the bucket from the argument column and registers
ntile only for a non-nullable
+ // integer argument, so the bucket must have been folded to a literal
before the plan is translated.
+ // That does not happen when constant folding is skipped, e.g. with
debug_skip_fold_constant=true, or
+ // when the configured folding can not evaluate the bucket.
+ checkPositiveBucket(getArgument(0));
+ }
+
+ private void checkPositiveBucket(Expression bucket) {
+ if (!(bucket instanceof IntegerLikeLiteral) || ((IntegerLikeLiteral)
bucket).getLongValue() <= 0) {
throw new AnalysisException(
"The bucket parameter of NTILE must be a constant positive
integer: " + this.toSql());
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutputTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutputTest.java
index 32387911455..011cc1d524d 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutputTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/ReplaceExpressionByChildOutputTest.java
@@ -18,10 +18,13 @@
package org.apache.doris.nereids.rules.analysis;
import org.apache.doris.nereids.properties.OrderKey;
+import org.apache.doris.nereids.trees.expressions.Add;
import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalHaving;
@@ -59,6 +62,30 @@ public class ReplaceExpressionByChildOutputTest implements
MemoPatternMatchSuppo
);
}
+ @Test
+ void testSortProjectDoesNotReplaceConstantSubExpression() {
+ // an unrelated constant output column, `2 AS two` or `1 + 1 AS two`,
must not make the rule reuse
+ // its slot for the structurally equal constant nested inside a
different order-by sub-expression,
+ // e.g. a function argument that has to fold to a literal (such as
NTILE's bucket)
+ SlotReference col1 = new SlotReference("col1", IntegerType.INSTANCE);
+ LogicalOlapScan logicalOlapScan =
PlanConstructor.newLogicalOlapScan(0, "t1", 0);
+ for (Expression constant : ImmutableList.of(new IntegerLiteral(2),
+ new Add(new IntegerLiteral(1), new IntegerLiteral(1)))) {
+ Alias alias = new Alias(constant, "two");
+ LogicalProject<Plan> logicalProject = new
LogicalProject<>(ImmutableList.of(alias), logicalOlapScan);
+ Add orderKeyExpr = new Add(constant, col1);
+ List<OrderKey> orderKeys = ImmutableList.of(new
OrderKey(orderKeyExpr, true, true));
+ LogicalSort<LogicalProject<Plan>> logicalSort = new
LogicalSort<>(orderKeys, logicalProject);
+
+ PlanChecker.from(MemoTestUtils.createConnectContext(), logicalSort)
+ .applyBottomUp(new ReplaceExpressionByChildOutput())
+ .matchesFromRoot(
+ logicalSort(logicalProject()).when(sort ->
+
sort.getOrderKeys().get(0).getExpr().equals(orderKeyExpr))
+ );
+ }
+ }
+
@Test
void testSortAggregate() {
SlotReference slotReference = new SlotReference("col1",
IntegerType.INSTANCE);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/window/NtileBucketTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/window/NtileBucketTest.java
new file mode 100644
index 00000000000..622ab8f6734
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/window/NtileBucketTest.java
@@ -0,0 +1,178 @@
+// 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.doris.nereids.trees.expressions.functions.window;
+
+import org.apache.doris.analysis.FunctionCallExpr;
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.glue.translator.ExpressionTranslator;
+import org.apache.doris.nereids.glue.translator.PlanTranslatorContext;
+import org.apache.doris.nereids.trees.expressions.Add;
+import org.apache.doris.nereids.trees.expressions.Cast;
+import org.apache.doris.nereids.trees.expressions.Mod;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.Subtract;
+import org.apache.doris.nereids.trees.expressions.literal.BigIntLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DoubleLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.LargeIntLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.StringLiteral;
+import org.apache.doris.nereids.types.BigIntType;
+import org.apache.doris.nereids.types.IntegerType;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.math.BigInteger;
+
+public class NtileBucketTest {
+
+ private static final String POSITIVE_INTEGER_MESSAGE =
+ "The bucket parameter of NTILE must be a constant positive
integer";
+
+ @Test
+ public void testLiteralBucketIsAccepted() {
+ Ntile ntile = new Ntile(new IntegerLiteral(3));
+
+ Assertions.assertDoesNotThrow(ntile::checkLegalityBeforeTypeCoercion);
+ }
+
+ @Test
+ public void testFoldableConstantExpressionIsAccepted() {
+ Ntile add = new Ntile(new Add(new IntegerLiteral(1), new
IntegerLiteral(1)));
+ Ntile cast = new Ntile(new Cast(new Subtract(new IntegerLiteral(5),
new IntegerLiteral(2)),
+ BigIntType.INSTANCE));
+
+ Assertions.assertDoesNotThrow(add::checkLegalityBeforeTypeCoercion);
+ Assertions.assertDoesNotThrow(cast::checkLegalityBeforeTypeCoercion);
+ }
+
+ @Test
+ public void testConstantExpressionFoldedToNonPositiveIsRejected() {
+ Ntile zero = new Ntile(new Subtract(new IntegerLiteral(1), new
IntegerLiteral(1)));
+ Ntile negative = new Ntile(new Subtract(new IntegerLiteral(1), new
IntegerLiteral(2)));
+
+ AnalysisException zeroException = Assertions.assertThrows(
+ AnalysisException.class,
zero::checkLegalityBeforeTypeCoercion);
+
Assertions.assertTrue(zeroException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ AnalysisException negativeException = Assertions.assertThrows(
+ AnalysisException.class,
negative::checkLegalityBeforeTypeCoercion);
+
Assertions.assertTrue(negativeException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ }
+
+ @Test
+ public void testNonPositiveLiteralIsRejected() {
+ Ntile zero = new Ntile(new IntegerLiteral(0));
+ Ntile negative = new Ntile(new IntegerLiteral(-1));
+
+ AnalysisException zeroException = Assertions.assertThrows(
+ AnalysisException.class,
zero::checkLegalityBeforeTypeCoercion);
+
Assertions.assertTrue(zeroException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ AnalysisException negativeException = Assertions.assertThrows(
+ AnalysisException.class,
negative::checkLegalityBeforeTypeCoercion);
+
Assertions.assertTrue(negativeException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ }
+
+ @Test
+ public void testConstantExpressionFoldedToNullIsRejected() {
+ Ntile nullBucket = new Ntile(new Cast(new StringLiteral("abc"),
IntegerType.INSTANCE));
+ Ntile nullLiteral = new Ntile(new NullLiteral(IntegerType.INSTANCE));
+
+ AnalysisException castException = Assertions.assertThrows(
+ AnalysisException.class,
nullBucket::checkLegalityBeforeTypeCoercion);
+
Assertions.assertTrue(castException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ AnalysisException nullException = Assertions.assertThrows(
+ AnalysisException.class,
nullLiteral::checkLegalityBeforeTypeCoercion);
+
Assertions.assertTrue(nullException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ }
+
+ @Test
+ public void testNonConstantBucketIsRejected() {
+ Ntile ntile = new Ntile(new SlotReference("k1", IntegerType.INSTANCE));
+
+ AnalysisException exception = Assertions.assertThrows(
+ AnalysisException.class,
ntile::checkLegalityBeforeTypeCoercion);
+ Assertions.assertTrue(exception.getMessage().contains("The bucket of
NTILE must be a constant value"));
+ }
+
+ @Test
+ public void testNonIntegralBucketIsRejected() {
+ Ntile ntile = new Ntile(new DoubleLiteral(2.0));
+
+ AnalysisException exception = Assertions.assertThrows(
+ AnalysisException.class,
ntile::checkLegalityBeforeTypeCoercion);
+ Assertions.assertTrue(exception.getMessage().contains("The bucket of
NTILE must be a integer"));
+ }
+
+ @Test
+ public void testLiteralBucketIsAcceptedAfterRewrite() {
+ Ntile ntile = new Ntile(new IntegerLiteral(3));
+
+ Assertions.assertDoesNotThrow(ntile::checkLegalityAfterRewrite);
+ }
+
+ @Test
+ public void testUnfoldedBucketIsRejectedAfterRewrite() {
+ Ntile unfolded = new Ntile(new Add(new IntegerLiteral(1), new
IntegerLiteral(1)));
+ Ntile zero = new Ntile(new IntegerLiteral(0));
+
+ AnalysisException unfoldedException = Assertions.assertThrows(
+ AnalysisException.class, unfolded::checkLegalityAfterRewrite);
+
Assertions.assertTrue(unfoldedException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ AnalysisException zeroException = Assertions.assertThrows(
+ AnalysisException.class, zero::checkLegalityAfterRewrite);
+
Assertions.assertTrue(zeroException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ }
+
+ @Test
+ public void testConstantExpressionNotFoldableOnFeIsLeftToRewrite() {
+ // FE has no executor for `%`, but BE can fold it when
enable_fold_constant_by_be is set,
+ // so it is accepted at binding time and must be a positive literal
after rewrite.
+ Ntile ntile = new Ntile(new Mod(new IntegerLiteral(3), new
IntegerLiteral(2)));
+
+ Assertions.assertDoesNotThrow(ntile::checkLegalityBeforeTypeCoercion);
+ AnalysisException exception = Assertions.assertThrows(
+ AnalysisException.class, ntile::checkLegalityAfterRewrite);
+
Assertions.assertTrue(exception.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ }
+
+ @Test
+ public void testTranslateChecksBucket() {
+ FunctionCallExpr translated = (FunctionCallExpr)
ExpressionTranslator.translate(
+ new Ntile(new BigIntLiteral(3)), new PlanTranslatorContext());
+ Assertions.assertEquals("ntile", translated.getFnName().getFunction());
+
+ AnalysisException unfoldedException =
Assertions.assertThrows(AnalysisException.class,
+ () -> ExpressionTranslator.translate(new Ntile(new Mod(new
BigIntLiteral(3), new BigIntLiteral(3))),
+ new PlanTranslatorContext()));
+
Assertions.assertTrue(unfoldedException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ AnalysisException zeroException =
Assertions.assertThrows(AnalysisException.class,
+ () -> ExpressionTranslator.translate(new Ntile(new
BigIntLiteral(0)), new PlanTranslatorContext()));
+
Assertions.assertTrue(zeroException.getMessage().contains(POSITIVE_INTEGER_MESSAGE));
+ }
+
+ @Test
+ public void testLargeIntBucketIsRejected() {
+ Ntile ntile = new Ntile(new
LargeIntLiteral(BigInteger.valueOf(Long.MAX_VALUE).add(BigInteger.ONE)));
+
+ AnalysisException exception = Assertions.assertThrows(
+ AnalysisException.class,
ntile::checkLegalityBeforeTypeCoercion);
+ Assertions.assertTrue(exception.getMessage().contains(
+ "The bucket of NTILE must be an integer within the range of
BIGINT"));
+ }
+}
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/test_ntile_function.out
b/regression-test/data/query_p0/sql_functions/window_functions/test_ntile_function.out
index 00657bf3ec4..b41b7468dc0 100644
---
a/regression-test/data/query_p0/sql_functions/window_functions/test_ntile_function.out
+++
b/regression-test/data/query_p0/sql_functions/window_functions/test_ntile_function.out
@@ -43,3 +43,124 @@
3 3 1 2
3 4 5 3
+-- !select_const_expr --
+1 1 1 1
+1 1 1 1
+1 1 3 1
+1 2 1 1
+1 3 1 2
+1 3 2 2
+1 3 3 2
+4 3 1 1
+5 4 3 1
+
+-- !select_const_expr --
+1 1 1 1
+1 1 1 1
+1 1 3 1
+1 2 1 2
+1 3 1 2
+1 3 2 3
+1 3 3 3
+4 3 1 1
+5 4 3 1
+
+-- !select_const_expr --
+1 1 1 1
+1 1 1 1
+1 1 3 1
+1 2 1 2
+1 3 1 2
+1 3 2 3
+1 3 3 3
+4 3 1 1
+5 4 3 1
+
+-- !select_const_alias_collision --
+2 1 1 1 1
+2 1 1 1 1
+2 1 1 3 1
+2 1 2 1 1
+2 4 3 1 1
+2 5 4 3 1
+2 1 3 1 2
+2 1 3 2 2
+2 1 3 3 2
+
+-- !select_const_alias_collision --
+2 1 1 1 0
+2 1 1 1 0
+2 1 1 3 0
+2 1 2 1 0
+2 1 3 1 0
+2 1 3 2 0
+2 1 3 3 0
+2 4 3 1 0
+2 5 4 3 0
+
+-- !select_const_alias_collision --
+2 1 1 1 0
+2 1 1 1 0
+2 1 1 3 0
+2 1 2 1 0
+2 1 3 1 0
+2 1 3 2 0
+2 1 3 3 0
+2 4 3 1 0
+2 5 4 3 0
+
+-- !select_const_alias_collision --
+3 1 1 1 0
+3 1 1 1 0
+3 1 1 3 0
+3 1 2 1 0
+3 1 3 1 0
+3 1 3 2 0
+3 1 3 3 0
+3 4 3 1 0
+3 5 4 3 0
+
+-- !select_skip_fold --
+1 1 1 1
+1 1 1 1
+1 1 3 1
+1 2 1 1
+1 3 1 2
+1 3 2 2
+1 3 3 2
+4 3 1 1
+5 4 3 1
+
+-- !select_fold_by_be --
+1 1 1 1
+1 1 1 1
+1 1 3 1
+1 2 1 1
+1 3 1 2
+1 3 2 2
+1 3 3 2
+4 3 1 1
+5 4 3 1
+
+-- !select_fold_by_be --
+1 1 1 1
+1 1 1 1
+1 1 3 1
+1 2 1 2
+1 3 1 2
+1 3 2 3
+1 3 3 3
+4 3 1 1
+5 4 3 1
+
+-- !select_no_window_rewrite --
+1 1 1 1
+1 1 1 1
+1 1 3 1
+1 2 1 1
+1 3 1 2
+1 3 2 2
+1 3 3 2
+4 3 1 1
+5 4 3 1
+
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/test_ntile_function.groovy
b/regression-test/suites/query_p0/sql_functions/window_functions/test_ntile_function.groovy
index 124eebfce64..855cc10a317 100644
---
a/regression-test/suites/query_p0/sql_functions/window_functions/test_ntile_function.groovy
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/test_ntile_function.groovy
@@ -71,6 +71,27 @@ suite("test_ntile_function") {
qt_select "select k2, k1, k3, ntile(3) over (order by k2,k3,k1) as ntile
from ${tableName} order by k2, k3, k1, ntile;"
qt_select "select k3, k2, k1, ntile(3) over (partition by k3 order by
k3,k2,k1) as ntile from ${tableName} order by k3, k2, k1, ntile;"
+ // the bucket may be a constant expression that folds to a positive integer
+ qt_select_const_expr "select k1, k2, k3, ntile(1 + 1) over (partition by
k1 order by k2,k3) as ntile from ${tableName} order by k1, k2, k3, ntile;"
+ qt_select_const_expr "select k1, k2, k3, ntile(cast(5 - 2 as bigint)) over
(partition by k1 order by k2,k3) as ntile from ${tableName} order by k1, k2,
k3, ntile;"
+ qt_select_const_expr "select k1, k2, k3, ntile(abs(-3)) over (partition by
k1 order by k2,k3) as ntile from ${tableName} order by k1, k2, k3, ntile;"
+
+ // the bucket literal must stay a literal even when an unrelated projected
column happens to
+ // have the same constant value, e.g. `2 AS two`; rewriting the order-by
key must not reuse that
+ // column's slot inside ntile's own bucket argument just because the
literal values match
+ qt_select_const_alias_collision "select 2 as two, k1, k2, k3, ntile(2)
over (partition by k1 order by k2,k3) as ntile " +
+ "from ${tableName} order by ntile(2) over (partition by k1 order
by k2,k3), k1, k2, k3;"
+
+ // the same collision with the window only in order by: the selected
constant matches the bucket, and the
+ // repeatedly used volatile column `r` keeps the projection from being
merged back, so a bucket rewritten
+ // to that column's slot would stay a slot; floor(rand()) is always 0,
which keeps the result stable
+ qt_select_const_alias_collision "select 2 as two, k1, k2, k3,
floor(rand()) as r from ${tableName} " +
+ "order by ntile(2) over (order by k1,k2,k3), r + r, k1, k2, k3;"
+ qt_select_const_alias_collision "select 1 + 1 as two, k1, k2, k3,
floor(rand()) as r from ${tableName} " +
+ "order by ntile(1 + 1) over (order by k1,k2,k3), r + r, k1, k2,
k3;"
+ qt_select_const_alias_collision "select cast(5 - 2 as bigint) as three,
k1, k2, k3, floor(rand()) as r from ${tableName} " +
+ "order by ntile(cast(5 - 2 as bigint)) over (order by k1,k2,k3), r
+ r, k1, k2, k3;"
+
test {
sql "select k1, k2, k3, ntile(0) over (partition by k1 order by k2) as
ntile from ${tableName} order by k1, k2, k3 desc;"
exception "positive"
@@ -81,11 +102,63 @@ suite("test_ntile_function") {
exception "The bucket of NTILE must be a constant value"
}
+ test {
+ sql "select k1, k2, k3, ntile(1 - 1) over (partition by k1 order by
k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+
+ test {
+ sql "select k1, k2, k3, ntile(1 - 2) over (partition by k1 order by
k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+
+ test {
+ sql "select k1, k2, k3, ntile(cast('abc' as int)) over (partition by
k1 order by k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+
test {
sql "select k1, k2, k3, ntile(170141183460469231731687303715884105727)
over (partition by k1 order by k2) as ntile from ${tableName} order by k1, k2,
k3 desc;"
exception "The bucket of NTILE must be an integer within the range of
BIGINT, but got LARGEINT"
}
+ // when constant folding is skipped, a literal bucket still works while an
unfolded constant expression
+ // is rejected by the planner instead of reaching the backend
+ sql "set debug_skip_fold_constant=true"
+ qt_select_skip_fold "select k1, k2, k3, ntile(2) over (partition by k1
order by k2,k3) as ntile from ${tableName} order by k1, k2, k3, ntile;"
+ test {
+ sql "select k1, k2, k3, ntile(1 + 1) over (partition by k1 order by
k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+ test {
+ sql "select k1, k2, k3, ntile(cast('3' as int)) over (partition by k1
order by k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+ sql "set debug_skip_fold_constant=false"
+
+ // FE can not evaluate `%` or crc32, but BE folds them when
enable_fold_constant_by_be is set
+ sql "set enable_fold_constant_by_be=true"
+ qt_select_fold_by_be "select k1, k2, k3, ntile(5 % 3) over (partition by
k1 order by k2,k3) as ntile from ${tableName} order by k1, k2, k3, ntile;"
+ qt_select_fold_by_be "select k1, k2, k3, ntile(crc32('a') % 3 + 3) over
(partition by k1 order by k2,k3) as ntile from ${tableName} order by k1, k2,
k3, ntile;"
+ test {
+ sql "select k1, k2, k3, ntile(3 % 3) over (partition by k1 order by
k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+ sql "set enable_fold_constant_by_be=false"
+ test {
+ sql "select k1, k2, k3, ntile(5 % 3) over (partition by k1 order by
k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+
+ // the bucket is still checked when translating the plan if the rewrites
that fold and check it are disabled
+ sql "set
disable_nereids_rules='REWRITE_PROJECT_EXPRESSION,REWRITE_WINDOW_EXPRESSION'"
+ qt_select_no_window_rewrite "select k1, k2, k3, ntile(2) over (partition
by k1 order by k2,k3) as ntile from ${tableName} order by k1, k2, k3, ntile;"
+ test {
+ sql "select k1, k2, k3, ntile(1 + 1) over (partition by k1 order by
k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
+ exception "The bucket parameter of NTILE must be a constant positive
integer"
+ }
+ sql "set disable_nereids_rules=''"
+
test {
sql "select k1, k2, k3, ntile(cast(3 as largeint)) over (partition by
k1 order by k2) as ntile from ${tableName} order by k1, k2, k3 desc;"
exception "The bucket of NTILE must be an integer within the range of
BIGINT, but got LARGEINT"
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
