This is an automated email from the ASF dual-hosted git repository.
yashmayya pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/pinot.git
The following commit(s) were added to refs/heads/master by this push:
new 19002ab9613 Add support for unary +/- operators in SQL queries (#18444)
19002ab9613 is described below
commit 19002ab9613bb3fc41c61eff0e607c9dff6363ae
Author: Jinesh Parakh <[email protected]>
AuthorDate: Fri May 22 00:09:32 2026 +0530
Add support for unary +/- operators in SQL queries (#18444)
---
.../scalar/arithmetic/NegateScalarFunction.java | 4 +-
.../apache/pinot/sql/parsers/CalciteSqlParser.java | 9 +
.../pinot/sql/parsers/CalciteSqlCompilerTest.java | 231 ++++++++++++
.../pinot/calcite/sql/fun/PinotOperatorTable.java | 74 +++-
.../query/planner/logical/RexExpressionUtils.java | 9 +
.../apache/pinot/query/QueryCompilationTest.java | 77 ++++
.../src/test/resources/queries/MathFuncs.json | 387 +++++++++++++++++++++
7 files changed, 771 insertions(+), 20 deletions(-)
diff --git
a/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/arithmetic/NegateScalarFunction.java
b/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/arithmetic/NegateScalarFunction.java
index 8dca5edbb99..dd74b895a25 100644
---
a/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/arithmetic/NegateScalarFunction.java
+++
b/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/arithmetic/NegateScalarFunction.java
@@ -35,6 +35,8 @@ import org.apache.pinot.spi.annotations.ScalarFunction;
@ScalarFunction
public class NegateScalarFunction extends BaseUnaryArithmeticScalarFunction {
+ public static final String FUNCTION_NAME = "negate";
+
private static final Map<ColumnDataType, FunctionInfo>
TYPE_FUNCTION_INFO_MAP = new EnumMap<>(ColumnDataType.class);
static {
@@ -62,7 +64,7 @@ public class NegateScalarFunction extends
BaseUnaryArithmeticScalarFunction {
@Override
public String getName() {
- return "negate";
+ return FUNCTION_NAME;
}
@Override
diff --git
a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
index 576906fd457..fe252e29bc6 100644
---
a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
+++
b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
@@ -52,6 +52,7 @@ import org.apache.calcite.sql.fun.SqlLikeOperator;
import org.apache.calcite.sql.parser.SqlAbstractParserImpl;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.commons.collections4.CollectionUtils;
+import org.apache.pinot.common.function.scalar.arithmetic.NegateScalarFunction;
import org.apache.pinot.common.request.DataSource;
import org.apache.pinot.common.request.Expression;
import org.apache.pinot.common.request.ExpressionType;
@@ -826,6 +827,14 @@ public class CalciteSqlParser {
negated = ((SqlLikeOperator) functionNode.getOperator()).isNegated();
canonicalName = SqlKind.LIKE.name();
break;
+ case MINUS_PREFIX:
+ // SqlKind.MINUS_PREFIX.name() would canonicalize to "minusprefix",
which has no matching entry in
+ // FunctionRegistry. Map directly to the registered
NegateScalarFunction name instead.
+ canonicalName = NegateScalarFunction.FUNCTION_NAME;
+ break;
+ case PLUS_PREFIX:
+ // Unary plus is identity -- unwrap to the operand directly (no
function node needed).
+ return toExpression(functionNode.getOperandList().get(0));
case OTHER:
case OTHER_FUNCTION:
case DOT:
diff --git
a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
index 46bb7b09408..532b28ab0bd 100644
---
a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
+++
b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlCompilerTest.java
@@ -1101,6 +1101,237 @@ public class CalciteSqlCompilerTest {
Assert.assertEquals(selectList.get(4).getFunctionCall().getOperator(),
"moduloorzero");
}
+ @Test
+ public void testUnaryMinusSyntaxCompilesAsNegate() {
+ // -col should produce negate(col), not a binary subtraction
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT -col1 FROM myTable");
+ Expression expr = pinotQuery.getSelectList().get(0);
+ Assert.assertEquals(expr.getFunctionCall().getOperator(), "negate");
+ Assert.assertEquals(expr.getFunctionCall().getOperandsSize(), 1);
+
Assert.assertEquals(expr.getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"col1");
+
+ // unary minus in WHERE
+ pinotQuery = compileToPinotQuery("SELECT col1 FROM myTable WHERE -col1 >
0");
+ Expression filter = pinotQuery.getFilterExpression();
+ Assert.assertEquals(filter.getFunctionCall().getOperator(),
FilterKind.GREATER_THAN.name());
+ Expression negateExpr = filter.getFunctionCall().getOperands().get(0);
+ Assert.assertEquals(negateExpr.getFunctionCall().getOperator(), "negate");
+
Assert.assertEquals(negateExpr.getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"col1");
+
+ // double negation
+ pinotQuery = compileToPinotQuery("SELECT -(-col1) FROM myTable");
+ Expression outer = pinotQuery.getSelectList().get(0);
+ Assert.assertEquals(outer.getFunctionCall().getOperator(), "negate");
+ Expression inner = outer.getFunctionCall().getOperands().get(0);
+ Assert.assertEquals(inner.getFunctionCall().getOperator(), "negate");
+
Assert.assertEquals(inner.getFunctionCall().getOperands().get(0).getIdentifier().getName(),
"col1");
+
+ // compound: -(col1 + col2)
+ pinotQuery = compileToPinotQuery("SELECT -(col1 + col2) FROM myTable");
+ Expression compound = pinotQuery.getSelectList().get(0);
+ Assert.assertEquals(compound.getFunctionCall().getOperator(), "negate");
+
Assert.assertEquals(compound.getFunctionCall().getOperands().get(0).getFunctionCall().getOperator(),
"plus");
+ }
+
+ @Test
+ public void testUnaryPlusSyntaxStrippedAsIdentity() {
+ // +col should be unwrapped to the bare identifier (no function wrapper)
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT +col1 FROM myTable");
+ Expression expr = pinotQuery.getSelectList().get(0);
+ Assert.assertNull(expr.getFunctionCall());
+ Assert.assertEquals(expr.getIdentifier().getName(), "col1");
+
+ // +literal should resolve to the literal itself
+ pinotQuery = compileToPinotQuery("SELECT +5 FROM myTable");
+ Expression literal = pinotQuery.getSelectList().get(0);
+ Assert.assertNull(literal.getFunctionCall());
+ Assert.assertNotNull(literal.getLiteral());
+
+ // +col in arithmetic context: +col + col still strips the unary plus
+ pinotQuery = compileToPinotQuery("SELECT +col1 + col2 FROM myTable");
+ Function plus = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(plus.getOperator(), "plus");
+ Assert.assertEquals(plus.getOperands().get(0).getIdentifier().getName(),
"col1");
+ Assert.assertEquals(plus.getOperands().get(1).getIdentifier().getName(),
"col2");
+ }
+
+ @Test
+ public void testUnaryMinusInsideAggregations() {
+ // SUM(-col) -> sum(negate(col))
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT SUM(-col1) FROM
myTable");
+ Function sum = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(sum.getOperator(), "sum");
+ Function negate = sum.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(negate.getOperator(), "negate");
+ Assert.assertEquals(negate.getOperands().get(0).getIdentifier().getName(),
"col1");
+
+ // MAX(-col), MIN(-col), AVG(-col)
+ pinotQuery = compileToPinotQuery("SELECT MAX(-col1), MIN(-col1),
AVG(-col1) FROM myTable");
+ for (int i = 0; i < 3; i++) {
+ Function agg = pinotQuery.getSelectList().get(i).getFunctionCall();
+
Assert.assertEquals(agg.getOperands().get(0).getFunctionCall().getOperator(),
"negate");
+ }
+
Assert.assertEquals(pinotQuery.getSelectList().get(0).getFunctionCall().getOperator(),
"max");
+
Assert.assertEquals(pinotQuery.getSelectList().get(1).getFunctionCall().getOperator(),
"min");
+
Assert.assertEquals(pinotQuery.getSelectList().get(2).getFunctionCall().getOperator(),
"avg");
+ }
+
+ @Test
+ public void testUnaryMinusOutsideAggregations() {
+ // -SUM(col) -> negate(sum(col))
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT -SUM(col1) FROM
myTable");
+ Function negate = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(negate.getOperator(), "negate");
+ Function sum = negate.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(sum.getOperator(), "sum");
+ Assert.assertEquals(sum.getOperands().get(0).getIdentifier().getName(),
"col1");
+
+ // -COUNT(*) -> negate(count(*))
+ pinotQuery = compileToPinotQuery("SELECT -COUNT(*) FROM myTable");
+ Function negCount = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(negCount.getOperator(), "negate");
+
Assert.assertEquals(negCount.getOperands().get(0).getFunctionCall().getOperator(),
"count");
+ }
+
+ @Test
+ public void testUnaryMinusInGroupBy() {
+ PinotQuery pinotQuery =
+ compileToPinotQuery("SELECT -col1, COUNT(*) FROM myTable GROUP BY
-col1");
+ Assert.assertTrue(pinotQuery.isSetGroupByList());
+ Assert.assertEquals(pinotQuery.getGroupByList().size(), 1);
+ Function groupByNegate =
pinotQuery.getGroupByList().get(0).getFunctionCall();
+ Assert.assertEquals(groupByNegate.getOperator(), "negate");
+
Assert.assertEquals(groupByNegate.getOperands().get(0).getIdentifier().getName(),
"col1");
+ }
+
+ @Test
+ public void testUnaryMinusInHaving() {
+ PinotQuery pinotQuery =
+ compileToPinotQuery("SELECT col1, SUM(col2) FROM myTable GROUP BY col1
HAVING -SUM(col2) < 0");
+ Assert.assertTrue(pinotQuery.isSetHavingExpression());
+ Function lt = pinotQuery.getHavingExpression().getFunctionCall();
+ Assert.assertEquals(lt.getOperator(), FilterKind.LESS_THAN.name());
+ Function lhs = lt.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(lhs.getOperator(), "negate");
+ Function inner = lhs.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(inner.getOperator(), "sum");
+ Assert.assertEquals(inner.getOperands().get(0).getIdentifier().getName(),
"col2");
+ }
+
+ @Test
+ public void testUnaryMinusWithAlias() {
+ // -col AS neg yields as(negate(col), neg)
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT -col1 AS neg FROM
myTable");
+ Function asFunc = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(asFunc.getOperator(), "as");
+ Function negate = asFunc.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(negate.getOperator(), "negate");
+ Assert.assertEquals(negate.getOperands().get(0).getIdentifier().getName(),
"col1");
+ // Alias side is the literal identifier
+ Assert.assertEquals(asFunc.getOperands().get(1).getIdentifier().getName(),
"neg");
+ }
+
+ @Test
+ public void testUnaryMinusInsideCast() {
+ // CAST(-col AS DOUBLE) -> cast(negate(col), 'DOUBLE')
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT CAST(-col1 AS DOUBLE)
FROM myTable");
+ Function cast = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(cast.getOperator(), "cast");
+ Function negate = cast.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(negate.getOperator(), "negate");
+ Assert.assertEquals(negate.getOperands().get(0).getIdentifier().getName(),
"col1");
+ }
+
+ @Test
+ public void testUnaryMinusInsideCase() {
+ // CASE WHEN col1 > 0 THEN -col1 ELSE col1 END
+ PinotQuery pinotQuery = compileToPinotQuery(
+ "SELECT CASE WHEN col1 > 0 THEN -col1 ELSE col1 END FROM myTable");
+ Function caseFunc = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(caseFunc.getOperator(), "case");
+ // Operands: [predicate, then-value, else-value]
+ Function thenNegate = caseFunc.getOperands().get(1).getFunctionCall();
+ Assert.assertEquals(thenNegate.getOperator(), "negate");
+
Assert.assertEquals(thenNegate.getOperands().get(0).getIdentifier().getName(),
"col1");
+
Assert.assertEquals(caseFunc.getOperands().get(2).getIdentifier().getName(),
"col1");
+ }
+
+ @Test
+ public void testUnaryMinusInDistinct() {
+ // SELECT DISTINCT -col1 -> distinct(negate(col1))
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT DISTINCT -col1 FROM
myTable");
+ Function distinct = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(distinct.getOperator(), "distinct");
+ Function negate = distinct.getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(negate.getOperator(), "negate");
+ Assert.assertEquals(negate.getOperands().get(0).getIdentifier().getName(),
"col1");
+ }
+
+ @Test
+ public void testUnaryMinusInJoinCondition() {
+ // JOIN ON T1.key = -T2.key (no aliases -- matches the style used by the
existing testJoin())
+ PinotQuery pinotQuery = compileToPinotQuery(
+ "SELECT T1.col1, T2.col1 FROM T1 INNER JOIN T2 ON T1.col1 = -T2.col1");
+ Assert.assertNotNull(pinotQuery.getDataSource().getJoin());
+ Expression cond = pinotQuery.getDataSource().getJoin().getCondition();
+ Function eq = cond.getFunctionCall();
+ Assert.assertEquals(eq.getOperator(), FilterKind.EQUALS.name());
+ Function negate = eq.getOperands().get(1).getFunctionCall();
+ Assert.assertEquals(negate.getOperator(), "negate");
+ }
+
+ @Test
+ public void testUnaryMinusInOrderBy() {
+ // ORDER BY -col is wrapped as asc(negate(col))
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT col1 FROM myTable
ORDER BY -col1");
+ Assert.assertTrue(pinotQuery.isSetOrderByList());
+ Expression orderExpr = pinotQuery.getOrderByList().get(0);
+ // Outer wrapper is asc/desc; inner is negate(col1)
+ Function inner =
orderExpr.getFunctionCall().getOperands().get(0).getFunctionCall();
+ Assert.assertEquals(inner.getOperator(), "negate");
+ Assert.assertEquals(inner.getOperands().get(0).getIdentifier().getName(),
"col1");
+ }
+
+ @Test
+ public void testUnaryMinusBinaryArithmeticMix() {
+ // 1 - -col -> minus(1, negate(col)) (binary minus then unary minus)
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT 1 - -col1 FROM
myTable");
+ Function minus = pinotQuery.getSelectList().get(0).getFunctionCall();
+ Assert.assertEquals(minus.getOperator(), "minus");
+ Assert.assertEquals(minus.getOperands().get(0).getLiteral().getIntValue(),
1);
+ Function negate = minus.getOperands().get(1).getFunctionCall();
+ Assert.assertEquals(negate.getOperator(), "negate");
+ Assert.assertEquals(negate.getOperands().get(0).getIdentifier().getName(),
"col1");
+ }
+
+ @Test
+ public void testNegativeLiteralCompiles() {
+ // Calcite typically folds "-5" to a negative literal at parse time.
Either form is acceptable;
+ // we just verify the compiled form makes sense (literal -5, or negate(5)).
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT -5 FROM myTable");
+ Expression expr = pinotQuery.getSelectList().get(0);
+ if (expr.isSetLiteral()) {
+ Assert.assertEquals(expr.getLiteral().getIntValue(), -5);
+ } else {
+ Assert.assertEquals(expr.getFunctionCall().getOperator(), "negate");
+
Assert.assertEquals(expr.getFunctionCall().getOperands().get(0).getLiteral().getIntValue(),
5);
+ }
+ }
+
+ @Test
+ public void testUnaryMinusInInList() {
+ PinotQuery pinotQuery = compileToPinotQuery("SELECT col1 FROM myTable
WHERE col1 IN (-1, -2, -3)");
+ Assert.assertTrue(pinotQuery.isSetFilterExpression());
+ Function inFunc = pinotQuery.getFilterExpression().getFunctionCall();
+ Assert.assertEquals(inFunc.getOperator(), FilterKind.IN.name());
+ // First operand is the column, remaining operands are the IN-list values.
+ Assert.assertEquals(inFunc.getOperands().get(0).getIdentifier().getName(),
"col1");
+ // Calcite folds negative literals directly, so each IN-list entry should
be a negative int literal.
+
Assert.assertEquals(inFunc.getOperands().get(1).getLiteral().getIntValue(), -1);
+
Assert.assertEquals(inFunc.getOperands().get(2).getLiteral().getIntValue(), -2);
+
Assert.assertEquals(inFunc.getOperands().get(3).getLiteral().getIntValue(), -3);
+ }
+
@Test
public void testSelectionTransformFunction() {
PinotQuery pinotQuery =
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/sql/fun/PinotOperatorTable.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/sql/fun/PinotOperatorTable.java
index 7206eb87412..6d073d2949e 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/sql/fun/PinotOperatorTable.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/sql/fun/PinotOperatorTable.java
@@ -20,6 +20,7 @@ package org.apache.pinot.calcite.sql.fun;
import com.google.common.base.Preconditions;
import com.google.common.base.Suppliers;
+import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@@ -186,6 +187,8 @@ public class PinotOperatorTable implements SqlOperatorTable
{
// PREFIX OPERATORS
SqlStdOperatorTable.EXISTS,
SqlStdOperatorTable.NOT,
+ SqlStdOperatorTable.UNARY_MINUS,
+ SqlStdOperatorTable.UNARY_PLUS,
// AGGREGATE OPERATORS
SqlStdOperatorTable.COUNT,
@@ -332,12 +335,12 @@ public class PinotOperatorTable implements
SqlOperatorTable {
);
//@formatter:on
- // Key is canonical name
- private final Map<String, SqlOperator> _operatorMap;
+ // Key is canonical name. Multiple operators can share the same name (e.g.
binary "-" and unary "-").
+ private final Map<String, List<SqlOperator>> _operatorMap;
private final List<SqlOperator> _operatorList;
private PinotOperatorTable(boolean nullHandlingEnabled) {
- Map<String, SqlOperator> operatorMap = new HashMap<>();
+ Map<String, List<SqlOperator>> operatorMap = new HashMap<>();
// Register standard operators
for (SqlOperator operator : STANDARD_OPERATORS) {
@@ -354,8 +357,8 @@ public class PinotOperatorTable implements SqlOperatorTable
{
// `col IS NOT NULL AND col <> 0` is simplified to `col <> 0` because `col
IS NOT NULL` is always true if
// `col <> 0` is true. However, in Pinot, `IS NOT NULL` has a special
meaning when using basic null handling
if (!nullHandlingEnabled) {
- operatorMap.put(FunctionRegistry.canonicalize(PINOT_IS_NULL.getName()),
PINOT_IS_NULL);
-
operatorMap.put(FunctionRegistry.canonicalize(PINOT_IS_NOT_NULL.getName()),
PINOT_IS_NOT_NULL);
+ registerOverride(FunctionRegistry.canonicalize(PINOT_IS_NULL.getName()),
PINOT_IS_NULL, operatorMap);
+
registerOverride(FunctionRegistry.canonicalize(PINOT_IS_NOT_NULL.getName()),
PINOT_IS_NOT_NULL, operatorMap);
}
// Register Pinot operators
@@ -373,16 +376,45 @@ public class PinotOperatorTable implements
SqlOperatorTable {
registerTransformFunctions(operatorMap);
registerScalarFunctions(operatorMap);
- _operatorMap = Map.copyOf(operatorMap);
- _operatorList = List.copyOf(operatorMap.values());
+ Map<String, List<SqlOperator>> immutableMap = new
HashMap<>(operatorMap.size());
+ List<SqlOperator> allOperators = new ArrayList<>();
+ for (Map.Entry<String, List<SqlOperator>> entry : operatorMap.entrySet()) {
+ List<SqlOperator> immutableList = List.copyOf(entry.getValue());
+ immutableMap.put(entry.getKey(), immutableList);
+ allOperators.addAll(immutableList);
+ }
+ _operatorMap = Map.copyOf(immutableMap);
+ _operatorList = List.copyOf(allOperators);
}
- private void register(String name, SqlOperator sqlOperator, Map<String,
SqlOperator> operatorMap) {
-
Preconditions.checkState(operatorMap.put(FunctionRegistry.canonicalize(name),
sqlOperator) == null,
- "SqlOperator: %s is already registered", name);
+ /**
+ * Registers an operator under the given name. Multiple operators with
different syntax (e.g. binary "-" and
+ * prefix unary "-") are allowed to share the same canonical name; all other
duplicates are rejected.
+ */
+ private void register(String name, SqlOperator sqlOperator, Map<String,
List<SqlOperator>> operatorMap) {
+ String canonicalName = FunctionRegistry.canonicalize(name);
+ List<SqlOperator> existing = operatorMap.get(canonicalName);
+ if (existing != null) {
+ for (SqlOperator op : existing) {
+ Preconditions.checkState(op.getSyntax() != sqlOperator.getSyntax(),
+ "SqlOperator: %s with syntax %s is already registered", name,
sqlOperator.getSyntax());
+ }
+ existing.add(sqlOperator);
+ } else {
+ operatorMap.put(canonicalName, new ArrayList<>(List.of(sqlOperator)));
+ }
+ }
+
+ /**
+ * Forcibly registers an operator under the given canonical name, replacing
any existing entry.
+ * Used to override standard Calcite operators (e.g. PINOT_IS_NULL /
PINOT_IS_NOT_NULL).
+ */
+ private void registerOverride(String canonicalName, SqlOperator sqlOperator,
+ Map<String, List<SqlOperator>> operatorMap) {
+ operatorMap.put(canonicalName, new ArrayList<>(List.of(sqlOperator)));
}
- private void registerAggregateFunctions(Map<String, SqlOperator>
operatorMap) {
+ private void registerAggregateFunctions(Map<String, List<SqlOperator>>
operatorMap) {
for (AggregationFunctionType functionType :
AggregationFunctionType.values()) {
if (functionType.getReturnTypeInference() != null) {
String functionName = functionType.getName();
@@ -396,26 +428,30 @@ public class PinotOperatorTable implements
SqlOperatorTable {
functionType.getOperandTypeChecker());
}
-
Preconditions.checkState(operatorMap.put(FunctionRegistry.canonicalize(functionName),
function) == null,
+ String canonicalName = FunctionRegistry.canonicalize(functionName);
+ Preconditions.checkState(!operatorMap.containsKey(canonicalName),
"Aggregate function: %s is already registered", functionName);
+ operatorMap.put(canonicalName, new ArrayList<>(List.of(function)));
}
}
}
- private void registerTransformFunctions(Map<String, SqlOperator>
operatorMap) {
+ private void registerTransformFunctions(Map<String, List<SqlOperator>>
operatorMap) {
for (TransformFunctionType functionType : TransformFunctionType.values()) {
if (functionType.getReturnTypeInference() != null) {
PinotSqlFunction function = new
PinotSqlFunction(functionType.getName(), functionType.getReturnTypeInference(),
functionType.getOperandTypeChecker());
for (String name : functionType.getNames()) {
-
Preconditions.checkState(operatorMap.put(FunctionRegistry.canonicalize(name),
function) == null,
+ String canonicalName = FunctionRegistry.canonicalize(name);
+ Preconditions.checkState(!operatorMap.containsKey(canonicalName),
"Transform function: %s is already registered", name);
+ operatorMap.put(canonicalName, new ArrayList<>(List.of(function)));
}
}
}
}
- private void registerScalarFunctions(Map<String, SqlOperator> operatorMap) {
+ private void registerScalarFunctions(Map<String, List<SqlOperator>>
operatorMap) {
for (Map.Entry<String, PinotScalarFunction> entry :
FunctionRegistry.FUNCTION_MAP.entrySet()) {
String canonicalName = entry.getKey();
PinotScalarFunction scalarFunction = entry.getValue();
@@ -429,7 +465,7 @@ public class PinotOperatorTable implements SqlOperatorTable
{
"Scalar function: %s is already registered", canonicalName);
continue;
}
- operatorMap.put(canonicalName, sqlFunction);
+ operatorMap.put(canonicalName, new ArrayList<>(List.of(sqlFunction)));
}
}
@@ -440,9 +476,9 @@ public class PinotOperatorTable implements SqlOperatorTable
{
return;
}
String canonicalName = FunctionRegistry.canonicalize(opName.getSimple());
- SqlOperator operator = _operatorMap.get(canonicalName);
- if (operator != null) {
- operatorList.add(operator);
+ List<SqlOperator> operators = _operatorMap.get(canonicalName);
+ if (operators != null) {
+ operatorList.addAll(operators);
}
}
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
b/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
index 6e16892a34f..9122cb6a36e 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/logical/RexExpressionUtils.java
@@ -48,6 +48,7 @@ import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.NlsString;
import org.apache.calcite.util.Sarg;
import org.apache.calcite.util.TimestampString;
+import org.apache.pinot.common.function.scalar.arithmetic.NegateScalarFunction;
import org.apache.pinot.common.utils.DataSchema.ColumnDataType;
import org.apache.pinot.spi.utils.BooleanUtils;
import org.apache.pinot.spi.utils.ByteArray;
@@ -276,6 +277,14 @@ public class RexExpressionUtils {
return handleReinterpret(rexCall);
case SEARCH:
return handleSearch(rexCall);
+ case MINUS_PREFIX:
+ // Without this explicit case the default branch calls
getFunctionName(), which returns
+ // SqlKind.MINUS_PREFIX.name() = "MINUS_PREFIX". That canonicalizes to
"minusprefix", which is
+ // not registered in FunctionRegistry. Map directly to
NegateScalarFunction's registered name.
+ // Note: PLUS_PREFIX is intentionally not handled here. Calcite's
StandardConvertletTable strips
+ // UNARY_PLUS during SqlNode -> RexNode conversion, so it never
reaches this switch.
+ return new
RexExpression.FunctionCall(RelToPlanNodeConverter.convertToColumnDataType(rexCall.type),
+ NegateScalarFunction.FUNCTION_NAME,
fromRexNodes(rexCall.operands));
default:
return new
RexExpression.FunctionCall(RelToPlanNodeConverter.convertToColumnDataType(rexCall.type),
getFunctionName(rexCall.op), fromRexNodes(rexCall.operands));
diff --git
a/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
b/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
index 842bbf97c15..2dd2f79335b 100644
---
a/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
+++
b/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
@@ -73,6 +73,83 @@ public class QueryCompilationTest extends
QueryEnvironmentTestBase {
assertNotNull(dispatchableSubPlan);
}
+ @Test(dataProvider = "testUnaryOperatorQueries")
+ public void testUnaryPrefixOperatorsPlanQuery(String query) {
+ DispatchableSubPlan dispatchableSubPlan =
_queryEnvironment.planQuery(query);
+ assertNotNull(dispatchableSubPlan);
+ }
+
+ @DataProvider(name = "testUnaryOperatorQueries")
+ public Object[][] provideUnaryOperatorQueries() {
+ return new Object[][]{
+ // INT (col3, col6)
+ {"SELECT -col3 FROM a"},
+ {"SELECT col3 FROM a ORDER BY -col3"},
+ {"SELECT col1, col3, DENSE_RANK() OVER (PARTITION BY col1 ORDER BY "
+ + "CASE WHEN col5 = true THEN -col3 ELSE col3 END) FROM a"},
+ {"SELECT col1, RANK() OVER (ORDER BY -col3) FROM a"},
+ {"SELECT +col3 FROM a"},
+ {"SELECT col3 FROM a ORDER BY +col3"},
+ {"SELECT -(col3 + col6) FROM a"},
+ {"SELECT col3 FROM a WHERE -col3 < 0"},
+ {"SELECT -col3, -col6 FROM a ORDER BY -col3"},
+ {"SELECT col3 - col6, -col3 FROM a"},
+ // LONG (col7)
+ {"SELECT -col7 FROM a"},
+ {"SELECT +col7 FROM a"},
+ {"SELECT -col7, col7 FROM a ORDER BY -col7"},
+ {"SELECT col7 FROM a WHERE -col7 < 0"},
+ {"SELECT -(col3 + col7) FROM a"},
+ // BIG_DECIMAL (col4)
+ {"SELECT -col4 FROM a"},
+ {"SELECT +col4 FROM a"},
+ {"SELECT col4 FROM a WHERE -col4 < 0"},
+ // double negation and compound
+ {"SELECT -(-col3) FROM a"},
+ {"SELECT -(-col7) FROM a"},
+ {"SELECT col3 - (-col6) FROM a"},
+ // GROUP BY with negation
+ {"SELECT -col3, COUNT(*) FROM a GROUP BY -col3"},
+ {"SELECT col1, COUNT(*) FROM a GROUP BY col1, -col3"},
+ // Aggregations with negation
+ {"SELECT SUM(-col3), MAX(-col3), MIN(-col3), AVG(-col3) FROM a"},
+ {"SELECT SUM(-col7), MAX(-col7), MIN(-col7), AVG(-col7) FROM a"},
+ {"SELECT SUM(-col4) FROM a"},
+ {"SELECT -SUM(col3), -MAX(col3), -MIN(col3), -AVG(col3) FROM a"},
+ {"SELECT -COUNT(*) FROM a"},
+ {"SELECT -COUNT(col3) FROM a"},
+ // HAVING with negation
+ {"SELECT col1, SUM(col3) FROM a GROUP BY col1 HAVING -SUM(col3) < 0"},
+ {"SELECT col1, SUM(col3) FROM a GROUP BY col1 HAVING SUM(-col3) >
-100"},
+ // alias-then-orderby
+ {"SELECT -col3 AS neg FROM a ORDER BY neg"},
+ {"SELECT -col3 AS neg, col1 FROM a ORDER BY neg DESC"},
+ // CAST involving negation
+ {"SELECT CAST(-col3 AS DOUBLE) FROM a"},
+ {"SELECT CAST(-col7 AS BIGINT) FROM a"},
+ {"SELECT -CAST(col3 AS DOUBLE) FROM a"},
+ // JOIN with negation
+ {"SELECT a.col1 FROM a JOIN b ON a.col3 = -b.col3"},
+ {"SELECT a.col1, b.col1 FROM a JOIN b ON -a.col3 = b.col3"},
+ // window: PARTITION BY with negation
+ {"SELECT col1, RANK() OVER (PARTITION BY -col3 ORDER BY col6) FROM a"},
+ {"SELECT col1, ROW_NUMBER() OVER (PARTITION BY -col3 ORDER BY -col6)
FROM a"},
+ // DISTINCT with negation
+ {"SELECT DISTINCT -col3 FROM a"},
+ {"SELECT COUNT(DISTINCT -col3) FROM a"},
+ // IN with negative literals
+ {"SELECT col3 FROM a WHERE col3 IN (-1, -2, -3)"},
+ {"SELECT col3 FROM a WHERE -col3 IN (-1, -2)"},
+ // negation in BETWEEN bounds
+ {"SELECT col3 FROM a WHERE col3 BETWEEN -10 AND 10"},
+ {"SELECT col3 FROM a WHERE -col3 BETWEEN -100 AND 0"},
+ // negation in subquery
+ {"SELECT col1 FROM a WHERE col3 > (SELECT AVG(-col3) FROM b)"},
+ // FLOAT (none in schema, skip) -- verify mixed double/big_decimal
negation
+ {"SELECT CAST(-col3 AS DOUBLE) + col4 FROM a"},
+ };
+ }
+
@Test(dataProvider = "testQueryExceptionDataProvider")
public void testQueryWithException(String query, String exceptionSnippet) {
try {
diff --git a/pinot-query-runtime/src/test/resources/queries/MathFuncs.json
b/pinot-query-runtime/src/test/resources/queries/MathFuncs.json
index 76dcc727ec9..72c032e5db8 100644
--- a/pinot-query-runtime/src/test/resources/queries/MathFuncs.json
+++ b/pinot-query-runtime/src/test/resources/queries/MathFuncs.json
@@ -511,5 +511,392 @@
"sql": "SELECT longCol / 1e20 FROM {numTbl}"
}
]
+ },
+ "unary_minus": {
+ "tables": {
+ "numTbl": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"},
+ {"name": "floatCol", "type": "FLOAT"},
+ {"name": "doubleCol", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [0, 0, 0.0, 0.0],
+ [1, 1, 1.5, 1.5],
+ [-1, -1, -1.5, -1.5],
+ [123, 321, 4.2, 4.2],
+ [-456, -456, -7.7, -7.7]
+ ]
+ },
+ "bigDecTbl": {
+ "schema": [
+ {"name": "bigDecCol", "type": "BIG_DECIMAL"}
+ ],
+ "inputs": [
+ ["0"],
+ ["1"],
+ ["-1"],
+ ["123456789012345678901234567890"],
+ ["-123456789012345678901234567890"]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "unary minus on INT column preserves INT type",
+ "sql": "SELECT -intCol FROM {numTbl}"
+ },
+ {
+ "description": "unary minus on LONG column preserves LONG type",
+ "sql": "SELECT -longCol FROM {numTbl}"
+ },
+ {
+ "description": "unary minus on FLOAT column",
+ "sql": "SELECT -floatCol FROM {numTbl}"
+ },
+ {
+ "description": "unary minus on DOUBLE column",
+ "sql": "SELECT -doubleCol FROM {numTbl}"
+ },
+ {
+ "description": "unary minus on BIG_DECIMAL column",
+ "sql": "SELECT -bigDecCol FROM {bigDecTbl}"
+ },
+ {
+ "description": "unary minus in WHERE clause on INT",
+ "sql": "SELECT intCol FROM {numTbl} WHERE -intCol < 0"
+ },
+ {
+ "description": "unary minus in WHERE clause on DOUBLE",
+ "sql": "SELECT doubleCol FROM {numTbl} WHERE -doubleCol < 0"
+ },
+ {
+ "description": "double negation --col returns original value",
+ "sql": "SELECT -(-intCol) FROM {numTbl}"
+ },
+ {
+ "description": "double negation on LONG",
+ "sql": "SELECT -(-longCol) FROM {numTbl}"
+ },
+ {
+ "description": "unary minus on compound expression",
+ "sql": "SELECT -(intCol + longCol) FROM {numTbl}"
+ },
+ {
+ "description": "unary minus mixed with binary arithmetic",
+ "sql": "SELECT intCol - (-longCol) FROM {numTbl}"
+ },
+ {
+ "description": "unary minus on zero returns zero",
+ "sql": "SELECT -intCol FROM {numTbl} WHERE intCol = 0"
+ }
+ ]
+ },
+ "unary_minus_null_handling": {
+ "extraProps": {
+ "enableColumnBasedNullHandling": true
+ },
+ "tables": {
+ "nullTbl": {
+ "schema": [
+ {"name": "id", "type": "INT"},
+ {"name": "intCol", "type": "INT", "notNull": false},
+ {"name": "longCol", "type": "LONG", "notNull": false},
+ {"name": "doubleCol", "type": "DOUBLE", "notNull": false}
+ ],
+ "inputs": [
+ [1, 10, 100, 1.5],
+ [2, null, null, null],
+ [3, -20, -200, -2.5]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "unary minus on DOUBLE column with null rows produces
NULL output",
+ "sql": "SET enableNullHandling=true; SELECT id, -doubleCol FROM
{nullTbl} ORDER BY id"
+ },
+ {
+ "description": "IsNullTransformFunction calls getNullBitmap() on the
inner negate transform at runtime; BaseTransformFunction.getNullBitmap() ORs
the argument bitmaps without invoking the scalar, so Math.negateExact is never
called and no overflow occurs",
+ "sql": "SET enableNullHandling=true; SELECT id FROM {nullTbl} WHERE
-intCol IS NULL ORDER BY id"
+ },
+ {
+ "description": "IS NOT NULL takes the same bitmap-only path through
BaseTransformFunction.getNullBitmap(); the scalar is never invoked",
+ "sql": "SET enableNullHandling=true; SELECT id FROM {nullTbl} WHERE
-intCol IS NOT NULL ORDER BY id"
+ },
+ {
+ "description": "SELECT projection invokes negate on every row; null
INT rows are stored as Integer.MIN_VALUE, and
Math.negateExact(Integer.MIN_VALUE) overflows",
+ "sql": "SET enableNullHandling=true; SELECT id, -intCol FROM {nullTbl}
ORDER BY id",
+ "expectedException": ".*integer overflow.*"
+ },
+ {
+ "description": "SELECT projection invokes negate on every row; null
LONG rows are stored as Long.MIN_VALUE, and Math.negateExact(Long.MIN_VALUE)
overflows",
+ "sql": "SET enableNullHandling=true; SELECT id, -longCol FROM
{nullTbl} ORDER BY id",
+ "expectedException": ".*long overflow.*|.*integer overflow.*"
+ },
+ {
+ "description": "SumAggregationFunction.aggregateSV() calls
blockValSet.getIntValuesSV() to materialize the negated column; that triggers
Math.negateExact on the null-row sentinel and overflows before foldNotNull gets
a chance to skip null rows",
+ "sql": "SET enableNullHandling=true; SELECT SUM(-intCol) FROM
{nullTbl}",
+ "expectedException": ".*integer overflow.*"
+ }
+ ]
+ },
+ "unary_minus_overflow": {
+ "tables": {
+ "overflowTbl": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"}
+ ],
+ "inputs": [
+ [-2147483648, -9223372036854775808]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "negating INT_MIN throws ArithmeticException via
Math.negateExact",
+ "sql": "SELECT -intCol FROM {overflowTbl}",
+ "expectedException": ".*integer overflow.*"
+ },
+ {
+ "description": "negating LONG_MIN throws ArithmeticException via
Math.negateExact",
+ "sql": "SELECT -longCol FROM {overflowTbl}",
+ "expectedException": ".*long overflow.*|.*integer overflow.*"
+ }
+ ]
+ },
+ "unary_minus_null_handling_disabled": {
+ "tables": {
+ "nullTbl": {
+ "schema": [
+ {"name": "id", "type": "INT"},
+ {"name": "intCol", "type": "INT", "notNull": false},
+ {"name": "longCol", "type": "LONG", "notNull": false},
+ {"name": "doubleCol", "type": "DOUBLE", "notNull": false}
+ ],
+ "inputs": [
+ [1, 10, 100, 1.5],
+ [2, null, null, null],
+ [3, -20, -200, -2.5]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "with null handling disabled there is no null concept;
every row is its raw stored value, and rows storing Integer.MIN_VALUE overflow
Math.negateExact",
+ "sql": "SET enableNullHandling=false; SELECT -intCol FROM {nullTbl}",
+ "expectedException": ".*integer overflow.*"
+ },
+ {
+ "description": "with null handling disabled, rows storing
Long.MIN_VALUE overflow Math.negateExact",
+ "sql": "SET enableNullHandling=false; SELECT -longCol FROM {nullTbl}",
+ "expectedException": ".*long overflow.*|.*integer overflow.*"
+ },
+ {
+ "description": "with null handling disabled, negation of finite
doubles succeeds (no value in the DOUBLE column overflows under negation)",
+ "sql": "SET enableNullHandling=false; SELECT -doubleCol FROM {nullTbl}
WHERE id IN (1, 3) ORDER BY id"
+ }
+ ]
+ },
+ "unary_plus": {
+ "tables": {
+ "numTbl": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"},
+ {"name": "floatCol", "type": "FLOAT"},
+ {"name": "doubleCol", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [0, 0, 0.0, 0.0],
+ [1, 1, 1.5, 1.5],
+ [-1, -1, -1.5, -1.5],
+ [123, 321, 4.2, 4.2]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "unary plus on INT is identity",
+ "sql": "SELECT +intCol FROM {numTbl}"
+ },
+ {
+ "description": "unary plus on LONG is identity",
+ "sql": "SELECT +longCol FROM {numTbl}"
+ },
+ {
+ "description": "unary plus on FLOAT is identity",
+ "sql": "SELECT +floatCol FROM {numTbl}"
+ },
+ {
+ "description": "unary plus on DOUBLE is identity",
+ "sql": "SELECT +doubleCol FROM {numTbl}"
+ },
+ {
+ "description": "unary plus over negated column is still negation",
+ "sql": "SELECT +(-intCol) FROM {numTbl}"
+ },
+ {
+ "description": "unary plus in ORDER BY is identity",
+ "sql": "SELECT intCol FROM {numTbl} ORDER BY +intCol"
+ }
+ ]
+ },
+ "unary_minus_aggregations": {
+ "tables": {
+ "numTbl": {
+ "schema": [
+ {"name": "groupKey", "type": "STRING"},
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"},
+ {"name": "doubleCol", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ ["a", 1, 10, 1.5],
+ ["a", 2, 20, 2.5],
+ ["a", 3, 30, 3.5],
+ ["b", 4, 40, 4.5],
+ ["b", 5, 50, 5.5],
+ ["c", 6, 60, 6.5],
+ ["c", -7, -70, -7.5]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "SUM(-col) equals -SUM(col)",
+ "sql": "SELECT SUM(-intCol), -SUM(intCol) FROM {numTbl}"
+ },
+ {
+ "description": "MAX(-col) equals -MIN(col)",
+ "sql": "SELECT MAX(-intCol), -MIN(intCol) FROM {numTbl}"
+ },
+ {
+ "description": "MIN(-col) equals -MAX(col)",
+ "sql": "SELECT MIN(-intCol), -MAX(intCol) FROM {numTbl}"
+ },
+ {
+ "description": "AVG(-col) equals -AVG(col)",
+ "sql": "SELECT AVG(-doubleCol), -AVG(doubleCol) FROM {numTbl}"
+ },
+ {
+ "description": "COUNT is unaffected by negation of argument",
+ "sql": "SELECT COUNT(-intCol) FROM {numTbl}"
+ },
+ {
+ "description": "negation of aggregate result",
+ "sql": "SELECT -SUM(intCol), -MAX(intCol), -MIN(intCol) FROM {numTbl}"
+ },
+ {
+ "description": "aggregate of compound negation",
+ "sql": "SELECT SUM(-(intCol + longCol)) FROM {numTbl}"
+ },
+ {
+ "description": "long type aggregations with negation",
+ "sql": "SELECT SUM(-longCol), MAX(-longCol), MIN(-longCol) FROM
{numTbl}"
+ },
+ {
+ "description": "double type aggregations with negation",
+ "sql": "SELECT SUM(-doubleCol), MAX(-doubleCol), MIN(-doubleCol) FROM
{numTbl}"
+ }
+ ]
+ },
+ "unary_minus_group_by": {
+ "tables": {
+ "numTbl": {
+ "schema": [
+ {"name": "groupKey", "type": "STRING"},
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"}
+ ],
+ "inputs": [
+ ["a", 1, 10],
+ ["a", 2, 20],
+ ["b", 3, 30],
+ ["b", 4, 40],
+ ["c", 5, 50]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "GROUP BY -col -- negation as grouping key",
+ "sql": "SELECT -intCol, COUNT(*) FROM {numTbl} GROUP BY -intCol ORDER
BY -intCol"
+ },
+ {
+ "description": "GROUP BY normal key, aggregate over negation",
+ "sql": "SELECT groupKey, SUM(-intCol) FROM {numTbl} GROUP BY groupKey
ORDER BY groupKey"
+ },
+ {
+ "description": "GROUP BY normal key, negation of aggregate",
+ "sql": "SELECT groupKey, -SUM(intCol) FROM {numTbl} GROUP BY groupKey
ORDER BY groupKey"
+ },
+ {
+ "description": "HAVING -SUM(col) > threshold",
+ "sql": "SELECT groupKey, SUM(intCol) FROM {numTbl} GROUP BY groupKey
HAVING -SUM(intCol) > -7 ORDER BY groupKey"
+ },
+ {
+ "description": "HAVING SUM(-col) < threshold excludes a group",
+ "sql": "SELECT groupKey, SUM(longCol) FROM {numTbl} GROUP BY groupKey
HAVING SUM(-longCol) < -40 ORDER BY groupKey"
+ },
+ {
+ "description": "DISTINCT -col",
+ "sql": "SELECT DISTINCT -intCol FROM {numTbl} ORDER BY -intCol"
+ },
+ {
+ "description": "COUNT(DISTINCT -col)",
+ "sql": "SELECT COUNT(DISTINCT -intCol) FROM {numTbl}"
+ }
+ ]
+ },
+ "unary_minus_misc": {
+ "tables": {
+ "numTbl": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"},
+ {"name": "doubleCol", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [1, 10, 1.5],
+ [2, 20, 2.5],
+ [3, 30, 3.5],
+ [4, 40, 4.5],
+ [5, 50, 5.5]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "unary minus inside CAST",
+ "sql": "SELECT CAST(-intCol AS DOUBLE) FROM {numTbl}"
+ },
+ {
+ "description": "negation of CAST result",
+ "sql": "SELECT -CAST(intCol AS DOUBLE) FROM {numTbl}"
+ },
+ {
+ "description": "alias on negated column, ORDER BY alias",
+ "sql": "SELECT -intCol AS neg FROM {numTbl} ORDER BY neg"
+ },
+ {
+ "description": "IN with negative literals",
+ "sql": "SELECT -intCol FROM {numTbl} WHERE -intCol IN (-1, -2, -3)
ORDER BY -intCol"
+ },
+ {
+ "description": "BETWEEN with negative bounds on negated column",
+ "sql": "SELECT -intCol FROM {numTbl} WHERE -intCol BETWEEN -4 AND -2
ORDER BY -intCol"
+ },
+ {
+ "description": "negation in CASE expression",
+ "sql": "SELECT CASE WHEN intCol > 2 THEN -intCol ELSE intCol END FROM
{numTbl} ORDER BY intCol"
+ },
+ {
+ "description": "negation of mixed-type expression preserves correct
semantics",
+ "sql": "SELECT -(intCol + longCol), intCol, longCol FROM {numTbl}
ORDER BY intCol"
+ }
+ ]
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
