You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@druid.apache.org by GitBox <gi...@apache.org> on 2018/07/20 18:21:06 UTC
[GitHub] fjy closed pull request #6005: Add concat and textcat SQL functions
fjy closed pull request #6005: Add concat and textcat SQL functions
URL: https://github.com/apache/incubator-druid/pull/6005
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git a/docs/content/querying/sql.md b/docs/content/querying/sql.md
index 09778119f2c..26ed4202319 100644
--- a/docs/content/querying/sql.md
+++ b/docs/content/querying/sql.md
@@ -125,6 +125,8 @@ String functions accept strings, and return a type appropriate to the function.
|Function|Notes|
|--------|-----|
|`x \|\| y`|Concat strings x and y.|
+|`CONCAT(expr, expr...)`|Concats a list of expressions.|
+|`TEXTCAT(expr, expr)`|Two argument version of CONCAT.|
|`LENGTH(expr)`|Length of expr in UTF-16 code units.|
|`CHAR_LENGTH(expr)`|Synonym for `LENGTH`.|
|`CHARACTER_LENGTH(expr)`|Synonym for `LENGTH`.|
diff --git a/sql/src/main/java/io/druid/sql/calcite/expression/builtin/ConcatOperatorConversion.java b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/ConcatOperatorConversion.java
new file mode 100644
index 00000000000..21268b98318
--- /dev/null
+++ b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/ConcatOperatorConversion.java
@@ -0,0 +1,72 @@
+/*
+ * 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 io.druid.sql.calcite.expression.builtin;
+
+import io.druid.sql.calcite.expression.DruidExpression;
+import io.druid.sql.calcite.expression.OperatorConversions;
+import io.druid.sql.calcite.expression.SqlOperatorConversion;
+import io.druid.sql.calcite.planner.Calcites;
+import io.druid.sql.calcite.planner.PlannerContext;
+import io.druid.sql.calcite.table.RowSignature;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+public class ConcatOperatorConversion implements SqlOperatorConversion
+{
+ private static final SqlFunction SQL_FUNCTION = new SqlFunction(
+ "CONCAT",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.explicit(
+ factory -> Calcites.createSqlType(factory, SqlTypeName.VARCHAR)
+ ),
+ null,
+ OperandTypes.SAME_VARIADIC,
+ SqlFunctionCategory.STRING
+ );
+
+ @Override
+ public SqlFunction calciteOperator()
+ {
+ return SQL_FUNCTION;
+ }
+
+ @Override
+ public DruidExpression toDruidExpression(
+ final PlannerContext plannerContext,
+ final RowSignature rowSignature,
+ final RexNode rexNode
+ )
+ {
+ return OperatorConversions.convertCall(
+ plannerContext,
+ rowSignature,
+ rexNode,
+ druidExpressions -> DruidExpression.of(
+ null,
+ DruidExpression.functionCall("concat", druidExpressions)
+ )
+ );
+ }
+}
diff --git a/sql/src/main/java/io/druid/sql/calcite/expression/builtin/TextcatOperatorConversion.java b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/TextcatOperatorConversion.java
new file mode 100644
index 00000000000..a34b57fe05b
--- /dev/null
+++ b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/TextcatOperatorConversion.java
@@ -0,0 +1,66 @@
+/*
+ * 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 io.druid.sql.calcite.expression.builtin;
+
+import io.druid.sql.calcite.expression.DruidExpression;
+import io.druid.sql.calcite.expression.OperatorConversions;
+import io.druid.sql.calcite.expression.SqlOperatorConversion;
+import io.druid.sql.calcite.planner.PlannerContext;
+import io.druid.sql.calcite.table.RowSignature;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.type.SqlTypeFamily;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+public class TextcatOperatorConversion implements SqlOperatorConversion
+{
+ private static final SqlFunction SQL_FUNCTION = OperatorConversions
+ .operatorBuilder("textcat")
+ .operandTypes(SqlTypeFamily.CHARACTER, SqlTypeFamily.CHARACTER)
+ .requiredOperands(2)
+ .returnType(SqlTypeName.VARCHAR)
+ .functionCategory(SqlFunctionCategory.STRING)
+ .build();
+
+ @Override
+ public SqlFunction calciteOperator()
+ {
+ return SQL_FUNCTION;
+ }
+
+ @Override
+ public DruidExpression toDruidExpression(
+ final PlannerContext plannerContext,
+ final RowSignature rowSignature,
+ final RexNode rexNode
+ )
+ {
+ return OperatorConversions.convertCall(
+ plannerContext,
+ rowSignature,
+ rexNode,
+ druidExpressions -> DruidExpression.of(
+ null,
+ DruidExpression.functionCall("concat", druidExpressions)
+ )
+ );
+ }
+}
diff --git a/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java b/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java
index 1fd43d1e1e5..9ebc87bf659 100644
--- a/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java
+++ b/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java
@@ -42,6 +42,7 @@
import io.druid.sql.calcite.expression.builtin.BTrimOperatorConversion;
import io.druid.sql.calcite.expression.builtin.CastOperatorConversion;
import io.druid.sql.calcite.expression.builtin.CeilOperatorConversion;
+import io.druid.sql.calcite.expression.builtin.ConcatOperatorConversion;
import io.druid.sql.calcite.expression.builtin.DateTruncOperatorConversion;
import io.druid.sql.calcite.expression.builtin.ExtractOperatorConversion;
import io.druid.sql.calcite.expression.builtin.FloorOperatorConversion;
@@ -52,6 +53,7 @@
import io.druid.sql.calcite.expression.builtin.ReinterpretOperatorConversion;
import io.druid.sql.calcite.expression.builtin.StrposOperatorConversion;
import io.druid.sql.calcite.expression.builtin.SubstringOperatorConversion;
+import io.druid.sql.calcite.expression.builtin.TextcatOperatorConversion;
import io.druid.sql.calcite.expression.builtin.TimeArithmeticOperatorConversion;
import io.druid.sql.calcite.expression.builtin.TimeExtractOperatorConversion;
import io.druid.sql.calcite.expression.builtin.TimeFloorOperatorConversion;
@@ -146,6 +148,8 @@
.add(new RegexpExtractOperatorConversion())
.add(new StrposOperatorConversion())
.add(new SubstringOperatorConversion())
+ .add(new ConcatOperatorConversion())
+ .add(new TextcatOperatorConversion())
.add(new AliasedOperatorConversion(new SubstringOperatorConversion(), "SUBSTR"))
.add(new TimeArithmeticOperatorConversion.TimeMinusIntervalOperatorConversion())
.add(new TimeArithmeticOperatorConversion.TimePlusIntervalOperatorConversion())
diff --git a/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java b/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java
index e9351e03784..9b774131b53 100644
--- a/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java
+++ b/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java
@@ -6785,6 +6785,114 @@ public void testPostAggWithTopN() throws Exception
);
}
+ @Test
+ public void testConcat() throws Exception
+ {
+ testQuery(
+ "SELECT CONCAT(dim1, '-', dim1, '_', dim1) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN(
+ "v0",
+ "concat(\"dim1\",'-',\"dim1\",'_',\"dim1\")",
+ ValueType.STRING
+ ))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"-_"},
+ new Object[]{"10.1-10.1_10.1"},
+ new Object[]{"2-2_2"},
+ new Object[]{"1-1_1"},
+ new Object[]{"def-def_def"},
+ new Object[]{"abc-abc_abc"}
+ )
+ );
+
+ testQuery(
+ "SELECT CONCAT(dim1, CONCAT(dim2,'x'), m2, 9999, dim1) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN(
+ "v0",
+ "concat(\"dim1\",concat(\"dim2\",'x'),\"m2\",9999,\"dim1\")",
+ ValueType.STRING
+ ))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"ax1.09999"},
+ new Object[]{"10.1x2.0999910.1"},
+ new Object[]{"2x3.099992"},
+ new Object[]{"1ax4.099991"},
+ new Object[]{"defabcx5.09999def"},
+ new Object[]{"abcx6.09999abc"}
+ )
+ );
+ }
+
+ @Test
+ public void testTextcat() throws Exception
+ {
+ testQuery(
+ "SELECT textcat(dim1, dim1) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN("v0", "concat(\"dim1\",\"dim1\")", ValueType.STRING))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{""},
+ new Object[]{"10.110.1"},
+ new Object[]{"22"},
+ new Object[]{"11"},
+ new Object[]{"defdef"},
+ new Object[]{"abcabc"}
+ )
+ );
+
+ testQuery(
+ "SELECT textcat(dim1, CAST(m2 as VARCHAR)) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN(
+ "v0",
+ "concat(\"dim1\",CAST(\"m2\", 'STRING'))",
+ ValueType.STRING
+ ))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"1.0"},
+ new Object[]{"10.12.0"},
+ new Object[]{"23.0"},
+ new Object[]{"14.0"},
+ new Object[]{"def5.0"},
+ new Object[]{"abc6.0"}
+ )
+ );
+ }
+
private void testQuery(
final String sql,
final List<Query> expectedQueries,
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@druid.apache.org
For additional commands, e-mail: dev-help@druid.apache.org