This is an automated email from the ASF dual-hosted git repository.
gianm pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new 25de11864a2 feat: PostgreSQL-style :: cast operator. (#20702)
25de11864a2 is described below
commit 25de11864a25d0cba7b12224a939a997a0f3289d
Author: Gian Merlino <[email protected]>
AuthorDate: Tue Oct 27 14:07:36 2026 -0700
feat: PostgreSQL-style :: cast operator. (#20702)
This patch adds cast syntax like x::BIGINT as shorthand for
CAST(x AS BIGINT).
---
docs/querying/sql-data-types.md | 4 +-
docs/querying/sql-functions.md | 2 +-
docs/querying/sql-scalar.md | 2 +-
sql/src/main/codegen/config.fmpp | 12 +
sql/src/main/codegen/includes/common.ftl | 30 ++
.../sql/calcite/planner/DruidSqlValidator.java | 71 +++-
.../druid/sql/calcite/CalciteInsertDmlTest.java | 23 ++
.../apache/druid/sql/calcite/CalciteQueryTest.java | 455 +++++++++++++++++++++
8 files changed, 592 insertions(+), 7 deletions(-)
diff --git a/docs/querying/sql-data-types.md b/docs/querying/sql-data-types.md
index 8d95ae1d0ca..1ca4103492a 100644
--- a/docs/querying/sql-data-types.md
+++ b/docs/querying/sql-data-types.md
@@ -60,7 +60,7 @@ The following table describes how Druid maps SQL types onto
native types when ru
|TINYINT|LONG|`0`||
|SMALLINT|LONG|`0`||
|INTEGER|LONG|`0`||
-|BIGINT|LONG|`0`|Druid LONG columns (except `__time`) are reported as BIGINT.
`LONG` is supported only as a `CAST` alias for `BIGINT`, for example `CAST(x AS
LONG)`, matching the name Druid's native engine uses for this type.|
+|BIGINT|LONG|`0`|Druid LONG columns (except `__time`) are reported as BIGINT.
`LONG` is supported only as a cast alias for `BIGINT`, for example `CAST(x AS
LONG)` or `x::LONG`, matching the name Druid's native engine uses for this
type.|
|TIMESTAMP|LONG|`0`, meaning 1970-01-01 00:00:00 UTC|Druid's `__time` column
is reported as TIMESTAMP. Casts between string and timestamp types assume
standard SQL formatting, such as `2000-01-02 03:04:05`, not ISO 8601
formatting. For handling other formats, use one of the [time
functions](sql-scalar.md#date-and-time-functions).|
|DATE|LONG|`0`, meaning 1970-01-01|Casting TIMESTAMP to DATE rounds down the
timestamp to the nearest day. Casts between string and date types assume
standard SQL formatting—for example, `2000-01-02`. For handling other
formats, use one of the [time
functions](sql-scalar.md#date-and-time-functions).|
|ARRAY|ARRAY|`NULL`|Druid native array types work as SQL arrays, and
multi-value strings can be converted to arrays. See [Arrays](#arrays) for more
information.|
@@ -77,6 +77,8 @@ For casts between two SQL types, the behavior depends on the
runtime type:
If a value cannot be cast to the target type, as in `CAST('foo' AS BIGINT)`,
Druid a substitutes [NULL](#null-values).
+You can also write `CAST(value AS TYPE)` as `value::TYPE`. The `::` operator
binds more tightly than unary and binary operators, so `-x::BIGINT` means
`-(x::BIGINT)`. When a select item is a column cast with `::`, such as
`x::BIGINT`, the output column is named after the input column (`x`), unless
the select list contains `*` or if the name would duplicate another output
column's name.
+
## Arrays
Druid supports [`ARRAY` types](arrays.md), which behave as standard SQL
arrays, where results are grouped by matching entire arrays. The [`UNNEST`
operator](./sql.md#unnest) can be used to perform operations on individual
array elements, translating each element into a separate row.
diff --git a/docs/querying/sql-functions.md b/docs/querying/sql-functions.md
index 5f60619aded..e94bb1f1116 100644
--- a/docs/querying/sql-functions.md
+++ b/docs/querying/sql-functions.md
@@ -1495,7 +1495,7 @@ Returns the following:
Converts a value into the specified data type.
-* **Syntax:** `CAST(value AS TYPE)`
+* **Syntax:** `CAST(value AS TYPE)` or `value::TYPE`
* **Function type:** Scalar, other
<details>
diff --git a/docs/querying/sql-scalar.md b/docs/querying/sql-scalar.md
index d88a7a5312b..c3aa3b0ed7a 100644
--- a/docs/querying/sql-scalar.md
+++ b/docs/querying/sql-scalar.md
@@ -279,7 +279,7 @@ The [DataSketches
extension](../development/extensions-core/datasketches-extensi
|`BLOOM_FILTER_TEST(expr, serialized-filter)`|Returns true if the value of
`expr` is contained in the base64-serialized Bloom filter. See the [Bloom
filter extension](../development/extensions-core/bloom-filter.md) documentation
for additional details. See the [`BLOOM_FILTER` function](sql-aggregations.md)
for computing Bloom filters.|
|`CASE expr WHEN value1 THEN result1 \[ WHEN value2 THEN result2 ... \] \[
ELSE resultN \] END`|Simple CASE.|
|`CASE WHEN boolean_expr1 THEN result1 \[ WHEN boolean_expr2 THEN result2 ...
\] \[ ELSE resultN \] END`|Searched CASE.|
-|`CAST(value AS TYPE)`|Cast value to another type. See [Data
types](sql-data-types.md) for details about how Druid SQL handles CAST.|
+|`CAST(value AS TYPE)`|Cast value to another type. Can also be written as
`value::TYPE`. See [Data types](sql-data-types.md) for details about how Druid
SQL handles CAST.|
|`COALESCE(value1, value2, ...)`|Returns the first non-null value.|
|`DECODE_BASE64_COMPLEX(dataType, expr)`| Decodes a Base64-encoded string into
a complex data type, where `dataType` is the complex data type and `expr` is
the Base64-encoded string to decode. The `hyperUnique` and
`serializablePairLongString` data types are supported by default. You can
enable support for the following complex data types by loading their
extensions:<br/><ul><li>`druid-bloom-filter`:
`bloom`</li><li>`druid-datasketches`: `arrayOfDoublesSketch`, `HLLSketch`,
`KllDoublesSk [...]
|`NULLIF(value1, value2)`|Returns NULL if `value1` and `value2` match, else
returns `value1`.|
diff --git a/sql/src/main/codegen/config.fmpp b/sql/src/main/codegen/config.fmpp
index dd6533ad201..07f74712d52 100644
--- a/sql/src/main/codegen/config.fmpp
+++ b/sql/src/main/codegen/config.fmpp
@@ -122,6 +122,18 @@ data: {
"DruidType()"
]
+ # Binary operators tokens.
+ # Example: "< INFIX_CAST: \"::\" >".
+ binaryOperatorsTokens: [
+ "< INFIX_CAST: \"::\" >"
+ ]
+
+ # Binary operators initialization.
+ # Example: "InfixCast".
+ extraBinaryExpressions: [
+ "InfixCast"
+ ]
+
# List of files in @includes directory that have parser method
# implementations for parsing custom SQL statements, literals or types
# given as part of "statementParserMethods", "literalParserMethods" or
diff --git a/sql/src/main/codegen/includes/common.ftl
b/sql/src/main/codegen/includes/common.ftl
index 0180f671c1d..71d7c038b9c 100644
--- a/sql/src/main/codegen/includes/common.ftl
+++ b/sql/src/main/codegen/includes/common.ftl
@@ -106,6 +106,36 @@ SqlTypeNameSpec DruidType() :
}
}
+// Parses the PostgreSQL-style infix cast operator (e.g. x::BIGINT).
+void InfixCast(List<Object> list, ExprContext exprContext, Span s) :
+{
+ final SqlNode dt;
+}
+{
+ <INFIX_CAST>
+ {
+ checkNonQueryExpression(exprContext);
+ list.add(new SqlParserUtil.ToTreeListItem(SqlLibraryOperators.INFIX_CAST,
getPos()));
+ }
+ (
+ // Accept LONG as an alias for BIGINT, matching CAST(x AS LONG).
+ LOOKAHEAD(2)
+ <LONG>
+ {
+ dt = new SqlDataTypeSpec(new SqlBasicTypeNameSpec(SqlTypeName.BIGINT,
getPos()), getPos());
+ }
+ |
+ dt = DataType()
+ |
+ // Accept interval types, matching CAST(x AS INTERVAL DAY).
+ LOOKAHEAD(2)
+ <INTERVAL> dt = IntervalQualifier()
+ )
+ {
+ list.add(dt);
+ }
+}
+
// Parses the supported file formats for export.
SqlIdentifier FileFormat() :
{
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidSqlValidator.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidSqlValidator.java
index e5b75349058..01548f5388b 100644
---
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidSqlValidator.java
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidSqlValidator.java
@@ -42,6 +42,7 @@ import org.apache.calcite.sql.SqlUpdate;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.SqlWith;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
@@ -53,6 +54,7 @@ import org.apache.calcite.sql.validate.SqlValidatorException;
import org.apache.calcite.sql.validate.SqlValidatorNamespace;
import org.apache.calcite.sql.validate.SqlValidatorScope;
import org.apache.calcite.sql.validate.SqlValidatorTable;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Static;
import org.apache.calcite.util.Util;
@@ -824,7 +826,48 @@ public class DruidSqlValidator extends
BaseDruidSqlValidator
}
}
- return super.performUnconditionalRewrites(node, underFrom);
+ if (node instanceof SqlSelect) {
+ aliasSelectItems((SqlSelect) node);
+ }
+
+ final SqlNode rewritten = super.performUnconditionalRewrites(node,
underFrom);
+
+ // Rewrite "x::TYPE" to standard CAST.
+ if (rewritten instanceof SqlCall cast
+ && SqlLibraryOperators.INFIX_CAST.equals(((SqlCall)
rewritten).getOperator())) {
+ return SqlStdOperatorTable.CAST.createCall(cast.getParserPosition(),
cast.getOperandList());
+ }
+
+ return rewritten;
+ }
+
+ /**
+ * Wraps select items in "AS" when {@link #deriveSelectItemAlias} has a
better name for them. This must run
+ * before other rewrites, since it looks at the syntax the user wrote.
+ */
+ private static void aliasSelectItems(final SqlSelect select)
+ {
+ final SqlNodeList selectList = select.getSelectList();
+ final List<String> aliases = new ArrayList<>(selectList.size());
+ final List<String> names = new ArrayList<>(selectList.size());
+ for (int i = 0; i < selectList.size(); i++) {
+ final SqlNode item = selectList.get(i);
+ if (item instanceof SqlIdentifier && ((SqlIdentifier) item).isStar()) {
+ // Keep all defaults, since we can't tell which names the star expands
to.
+ return;
+ }
+ final String alias = deriveSelectItemAlias(item);
+ aliases.add(alias);
+ // Use the ordinal, so unnamed items reserve the "EXPR$i" names that
Calcite will generate for them.
+ names.add(alias != null ? alias : SqlValidatorUtil.alias(item, i));
+ }
+ for (int i = 0; i < selectList.size(); i++) {
+ final String alias = aliases.get(i);
+ // Skip duplicate names, since we can't have two output columns with the
same name and different types.
+ if (alias != null && Collections.frequency(names, alias) == 1) {
+ selectList.set(i, SqlValidatorUtil.addAlias(selectList.get(i), alias));
+ }
+ }
}
/**
@@ -884,6 +927,29 @@ public class DruidSqlValidator extends
BaseDruidSqlValidator
);
}
+ /**
+ * Returns a preferred name for a select item. We use this so "x::TYPE" can
be automatically named "x".
+ */
+ @Nullable
+ private static String deriveSelectItemAlias(final SqlNode item)
+ {
+ if (item.getKind() == SqlKind.CAST) {
+ final SqlCall cast = (SqlCall) item;
+
+ // This rule is only for infix cast (x::TYPE), not regular CAST.
+ if (SqlLibraryOperators.INFIX_CAST.equals(cast.getOperator())) {
+ final SqlNode operand = cast.operand(0);
+ if (operand instanceof SqlIdentifier identifier &&
!identifier.isStar()) {
+ return Util.last(identifier.names);
+ } else {
+ return deriveSelectItemAlias(operand);
+ }
+ }
+ }
+
+ return null;
+ }
+
private SqlNode getSqlNodeFor(SqlInsert insert, int idx)
{
SqlNode src = insert.getSource();
@@ -903,7 +969,4 @@ public class DruidSqlValidator extends BaseDruidSqlValidator
&& call.getFunctionQuantifier() != null
&& call.getFunctionQuantifier().getValue() ==
SqlSelectKeyword.DISTINCT;
}
-
-
-
}
diff --git
a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteInsertDmlTest.java
b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteInsertDmlTest.java
index 31b5aa183c8..fdaca646947 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteInsertDmlTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteInsertDmlTest.java
@@ -137,6 +137,29 @@ public class CalciteInsertDmlTest extends
CalciteIngestionDmlTest
.verify();
}
+ @Test
+ public void testInsertInfixCastColumnName()
+ {
+ testIngestionQuery()
+ .sql("INSERT INTO dst SELECT __time, dim1::BIGINT FROM foo PARTITIONED
BY ALL TIME")
+ .expectTarget(
+ "dst",
+ RowSignature.builder().add("__time", ColumnType.LONG).add("dim1",
ColumnType.LONG).build()
+ )
+ .expectResources(dataSourceRead("foo"), dataSourceRead("dst"),
dataSourceWrite("dst"))
+ .expectQuery(
+ newScanQueryBuilder()
+ .dataSource("foo")
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "CAST(\"dim1\",
'LONG')", ColumnType.LONG))
+ .columns("__time", "v0")
+ .columnTypes(ColumnType.LONG, ColumnType.LONG)
+ .context(PARTITIONED_BY_ALL_TIME_QUERY_CONTEXT)
+ .build()
+ )
+ .verify();
+ }
+
@Test
public void testInsertFromViewC()
{
diff --git
a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
index 29f6d136515..499df1dbddd 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
@@ -4695,6 +4695,461 @@ public class CalciteQueryTest extends
BaseCalciteQueryTest
);
}
+ @Test
+ public void testFilterOnStringAsNumberInfixCast()
+ {
+ testQuery(
+ "SELECT distinct dim1 FROM druid.foo WHERE dim1::float > 9 and
dim1::float <= 10.5",
+ ImmutableList.of(
+ GroupByQuery.builder()
+ .setDataSource(CalciteTests.DATASOURCE1)
+ .setInterval(querySegmentSpec(Filtration.eternity()))
+ .setGranularity(Granularities.ALL)
+ .setDimensions(dimensions(new
DefaultDimensionSpec("dim1", "d0")))
+ .setDimFilter(
+ range("dim1", ColumnType.FLOAT, 9.0, 10.5, true,
false)
+ )
+ .setContext(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"10.1"}
+ )
+ );
+ }
+
+ @Test
+ public void testInfixCastPrecedence()
+ {
+ // "::" binds tighter than unary minus and binary operators.
+ testQuery(
+ "SELECT -m1::BIGINT, 1 + dim1::BIGINT, 'x' || cnt::VARCHAR FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "(- CAST(\"m1\", 'LONG'))",
ColumnType.LONG),
+ expressionVirtualColumn("v1", "(1 + CAST(\"dim1\",
'LONG'))", ColumnType.LONG),
+ expressionVirtualColumn("v2", "concat('x',CAST(\"cnt\",
'STRING'))", ColumnType.STRING)
+ )
+ .columns("v0", "v1", "v2")
+ .columnTypes(ColumnType.LONG, ColumnType.LONG,
ColumnType.STRING)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{-1L, null, "x1"},
+ new Object[]{-2L, 11L, "x1"},
+ new Object[]{-3L, 3L, "x1"},
+ new Object[]{-4L, 2L, "x1"},
+ new Object[]{-5L, null, "x1"},
+ new Object[]{-6L, null, "x1"}
+ )
+ );
+ }
+
+ @Test
+ public void testInfixCastToMultiWordType()
+ {
+ // Type names containing spaces are parsed in full after "::".
+ testQuery(
+ "SELECT ARRAY[cnt]::VARCHAR ARRAY, STRING_TO_ARRAY(dim1, ',')::DOUBLE
ARRAY, m1::DOUBLE PRECISION FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "CAST(array(\"cnt\"),
'ARRAY<STRING>')", ColumnType.STRING_ARRAY),
+ expressionVirtualColumn(
+ "v1",
+ "CAST(string_to_array(\"dim1\",','), 'ARRAY<DOUBLE>')",
+ ColumnType.DOUBLE_ARRAY
+ ),
+ expressionVirtualColumn("v2", "\"m1\"", ColumnType.DOUBLE)
+ )
+ .columns("v0", "v1", "v2")
+ .columnTypes(ColumnType.STRING_ARRAY, ColumnType.DOUBLE_ARRAY,
ColumnType.DOUBLE)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"[\"1\"]", "[null]", 1.0},
+ new Object[]{"[\"1\"]", "[10.1]", 2.0},
+ new Object[]{"[\"1\"]", "[2.0]", 3.0},
+ new Object[]{"[\"1\"]", "[1.0]", 4.0},
+ new Object[]{"[\"1\"]", "[null]", 5.0},
+ new Object[]{"[\"1\"]", "[null]", 6.0}
+ )
+ );
+ }
+
+ @Test
+ public void testInfixCastToComplexType()
+ {
+ // Fails the same way as CAST(... AS TYPE('COMPLEX<...>')), since complex
types are not resolvable in queries.
+ testQueryThrows(
+ "SELECT unique_dim1::TYPE('COMPLEX<hyperUnique>') FROM foo",
+ invalidSqlContains("Unknown identifier 'COMPLEX<hyperUnique>'")
+ );
+ }
+
+ @Test
+ public void testCastToComplexType()
+ {
+ testQueryThrows(
+ "SELECT CAST(unique_dim1 AS TYPE('COMPLEX<hyperUnique>')) FROM foo",
+ invalidSqlContains("Unknown identifier 'COMPLEX<hyperUnique>'")
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemName()
+ {
+ testQuery(
+ "SELECT dim1::BIGINT FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "CAST(\"dim1\",
'LONG')", ColumnType.LONG))
+ .columns("v0")
+ .columnTypes(ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{null},
+ new Object[]{10L},
+ new Object[]{2L},
+ new Object[]{1L},
+ new Object[]{null},
+ new Object[]{null}
+ ),
+ RowSignature.builder().add("dim1", ColumnType.LONG).build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameQualified()
+ {
+ testQuery(
+ "SELECT foo.dim1::BIGINT FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "CAST(\"dim1\",
'LONG')", ColumnType.LONG))
+ .columns("v0")
+ .columnTypes(ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{null},
+ new Object[]{10L},
+ new Object[]{2L},
+ new Object[]{1L},
+ new Object[]{null},
+ new Object[]{null}
+ ),
+ RowSignature.builder().add("dim1", ColumnType.LONG).build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameChained()
+ {
+ testQuery(
+ "SELECT dim1::VARCHAR::BIGINT FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "CAST(\"dim1\",
'LONG')", ColumnType.LONG))
+ .columns("v0")
+ .columnTypes(ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{null},
+ new Object[]{10L},
+ new Object[]{2L},
+ new Object[]{1L},
+ new Object[]{null},
+ new Object[]{null}
+ ),
+ RowSignature.builder().add("dim1", ColumnType.LONG).build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameDefault()
+ {
+ // CAST(x AS T) keeps its default name, and so does "::" applied to an
expression that has no name.
+ testQuery(
+ "SELECT CAST(dim1 AS BIGINT), (dim1 || 'x')::VARCHAR FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "CAST(\"dim1\", 'LONG')",
ColumnType.LONG),
+ expressionVirtualColumn("v1", "concat(\"dim1\",'x')",
ColumnType.STRING)
+ )
+ .columns("v0", "v1")
+ .columnTypes(ColumnType.LONG, ColumnType.STRING)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{null, "x"},
+ new Object[]{10L, "10.1x"},
+ new Object[]{2L, "2x"},
+ new Object[]{1L, "1x"},
+ new Object[]{null, "defx"},
+ new Object[]{null, "abcx"}
+ ),
+ RowSignature.builder()
+ .add("EXPR$0", ColumnType.LONG)
+ .add("EXPR$1", ColumnType.STRING)
+ .build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameExplicitAlias()
+ {
+ testQuery(
+ "SELECT dim1::BIGINT AS dim1_long FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "CAST(\"dim1\",
'LONG')", ColumnType.LONG))
+ .columns("v0")
+ .columnTypes(ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{null},
+ new Object[]{10L},
+ new Object[]{2L},
+ new Object[]{1L},
+ new Object[]{null},
+ new Object[]{null}
+ ),
+ RowSignature.builder().add("dim1_long", ColumnType.LONG).build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameDuplicate()
+ {
+ // Names that would duplicate another select item's name keep the default.
+ testQuery(
+ "SELECT dim1, dim1::BIGINT, m1::BIGINT, m1::DOUBLE, cnt::VARCHAR FROM
foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "CAST(\"dim1\", 'LONG')",
ColumnType.LONG),
+ expressionVirtualColumn("v1", "CAST(\"m1\", 'LONG')",
ColumnType.LONG),
+ expressionVirtualColumn("v2", "\"m1\"", ColumnType.DOUBLE),
+ expressionVirtualColumn("v3", "CAST(\"cnt\", 'STRING')",
ColumnType.STRING)
+ )
+ .columns("dim1", "v0", "v1", "v2", "v3")
+ .columnTypes(ColumnType.STRING, ColumnType.LONG,
ColumnType.LONG, ColumnType.DOUBLE, ColumnType.STRING)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"", null, 1L, 1.0, "1"},
+ new Object[]{"10.1", 10L, 2L, 2.0, "1"},
+ new Object[]{"2", 2L, 3L, 3.0, "1"},
+ new Object[]{"1", 1L, 4L, 4.0, "1"},
+ new Object[]{"def", null, 5L, 5.0, "1"},
+ new Object[]{"abc", null, 6L, 6.0, "1"}
+ ),
+ RowSignature.builder()
+ .add("dim1", ColumnType.STRING)
+ .add("EXPR$1", ColumnType.LONG)
+ .add("EXPR$2", ColumnType.LONG)
+ .add("EXPR$3", ColumnType.DOUBLE)
+ .add("cnt", ColumnType.STRING)
+ .build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameDuplicateGenerated()
+ {
+ // Names that would duplicate a name generated for an unnamed select item
keep the default.
+ testQuery(
+ "SELECT 'x', EXPR$0::BIGINT FROM (SELECT dim1 AS EXPR$0 FROM foo)",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "'x'", ColumnType.STRING),
+ expressionVirtualColumn("v1", "CAST(\"dim1\", 'LONG')",
ColumnType.LONG)
+ )
+ .columns("v0", "v1")
+ .columnTypes(ColumnType.STRING, ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"x", null},
+ new Object[]{"x", 10L},
+ new Object[]{"x", 2L},
+ new Object[]{"x", 1L},
+ new Object[]{"x", null},
+ new Object[]{"x", null}
+ ),
+ RowSignature.builder()
+ .add("EXPR$0", ColumnType.STRING)
+ .add("EXPR$1", ColumnType.LONG)
+ .build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameStar()
+ {
+ // A star may expand to any name, so nothing in the select list is named.
+ testQuery(
+ "SELECT *, dim1::BIGINT FROM (SELECT dim1 FROM foo)",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "CAST(\"dim1\",
'LONG')", ColumnType.LONG))
+ .columns("dim1", "v0")
+ .columnTypes(ColumnType.STRING, ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"", null},
+ new Object[]{"10.1", 10L},
+ new Object[]{"2", 2L},
+ new Object[]{"1", 1L},
+ new Object[]{"def", null},
+ new Object[]{"abc", null}
+ ),
+ RowSignature.builder()
+ .add("dim1", ColumnType.STRING)
+ .add("EXPR$1", ColumnType.LONG)
+ .build()
+ );
+ }
+
+ @Test
+ public void testInfixCastSelectItemNameOrderBy()
+ {
+ // ORDER BY dim1 refers to the select item named dim1, not the column, so
it sorts numerically.
+ testQuery(
+ "SELECT dim1::BIGINT FROM foo GROUP BY dim1 ORDER BY dim1",
+ ImmutableList.of(
+ GroupByQuery.builder()
+ .setDataSource(CalciteTests.DATASOURCE1)
+ .setInterval(querySegmentSpec(Filtration.eternity()))
+ .setGranularity(Granularities.ALL)
+ .setDimensions(dimensions(new
DefaultDimensionSpec("dim1", "d0")))
+ .setPostAggregatorSpecs(expressionPostAgg("p0",
"CAST(\"d0\", 'LONG')", ColumnType.LONG))
+ .setLimitSpec(
+ new DefaultLimitSpec(
+ ImmutableList.of(
+ new OrderByColumnSpec("p0",
Direction.ASCENDING, StringComparators.NUMERIC)
+ ),
+ Integer.MAX_VALUE
+ )
+ )
+ .setContext(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{null},
+ new Object[]{null},
+ new Object[]{null},
+ new Object[]{1L},
+ new Object[]{2L},
+ new Object[]{10L}
+ ),
+ RowSignature.builder().add("dim1", ColumnType.LONG).build()
+ );
+ }
+
+ @Test
+ public void testInfixCastToLong()
+ {
+ testQuery(
+ "SELECT dim1::LONG FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "CAST(\"dim1\",
'LONG')", ColumnType.LONG))
+ .columns("v0")
+ .columnTypes(ColumnType.LONG)
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{null},
+ new Object[]{10L},
+ new Object[]{2L},
+ new Object[]{1L},
+ new Object[]{null},
+ new Object[]{null}
+ ),
+ RowSignature.builder().add("dim1", ColumnType.LONG).build()
+ );
+ }
+
+ @Test
+ public void testInfixCastMixedWithCastInGroupBy()
+ {
+ // "x::TYPE" and CAST(x AS TYPE) are the same expression for the purposes
of GROUP BY.
+ testQuery(
+ "SELECT CAST(m1 AS BIGINT), COUNT(*) FROM foo GROUP BY m1::BIGINT",
+ ImmutableList.of(
+ GroupByQuery.builder()
+ .setDataSource(CalciteTests.DATASOURCE1)
+ .setInterval(querySegmentSpec(Filtration.eternity()))
+ .setGranularity(Granularities.ALL)
+ .setDimensions(dimensions(new
DefaultDimensionSpec("m1", "d0", ColumnType.LONG)))
+ .setAggregatorSpecs(aggregators(new
CountAggregatorFactory("a0")))
+ .setContext(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{1L, 1L},
+ new Object[]{2L, 1L},
+ new Object[]{3L, 1L},
+ new Object[]{4L, 1L},
+ new Object[]{5L, 1L},
+ new Object[]{6L, 1L}
+ )
+ );
+ }
+
@Test
public void testSimpleLongAggregations()
{
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
