You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by gi...@apache.org on 2019/04/03 21:10:00 UTC
[incubator-druid] branch master updated: SQL: Add STRING_FORMAT
function. (#7327)
This is an automated email from the ASF dual-hosted git repository.
gian pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-druid.git
The following commit(s) were added to refs/heads/master by this push:
new 8c104a1 SQL: Add STRING_FORMAT function. (#7327)
8c104a1 is described below
commit 8c104a115c4e08b2df60d940d6d85a7e79bef752
Author: Gian Merlino <gi...@gmail.com>
AuthorDate: Wed Apr 3 17:09:54 2019 -0400
SQL: Add STRING_FORMAT function. (#7327)
---
.../java/org/apache/druid/math/expr/Function.java | 30 ++++++
docs/content/misc/math-expr.md | 1 +
docs/content/querying/sql.md | 1 +
.../calcite/expression/OperatorConversions.java | 29 +++++-
.../builtin/StringFormatOperatorConversion.java | 105 +++++++++++++++++++++
.../sql/calcite/planner/DruidOperatorTable.java | 2 +
.../sql/calcite/expression/ExpressionsTest.java | 48 ++++++++++
7 files changed, 211 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/druid/math/expr/Function.java b/core/src/main/java/org/apache/druid/math/expr/Function.java
index de50cfb..4378ac1 100644
--- a/core/src/main/java/org/apache/druid/math/expr/Function.java
+++ b/core/src/main/java/org/apache/druid/math/expr/Function.java
@@ -1023,6 +1023,36 @@ interface Function
}
}
+ class StringFormatFunc implements Function
+ {
+ @Override
+ public String name()
+ {
+ return "format";
+ }
+
+ @Override
+ public ExprEval apply(List<Expr> args, Expr.ObjectBinding bindings)
+ {
+ if (args.size() < 1) {
+ throw new IAE("Function[%s] needs 1 or more arguments", name());
+ }
+
+ final String formatString = NullHandling.nullToEmptyIfNeeded(args.get(0).eval(bindings).asString());
+
+ if (formatString == null) {
+ return ExprEval.of(null);
+ }
+
+ final Object[] formatArgs = new Object[args.size() - 1];
+ for (int i = 1; i < args.size(); i++) {
+ formatArgs[i - 1] = args.get(i).eval(bindings).value();
+ }
+
+ return ExprEval.of(StringUtils.nonStrictFormat(formatString, formatArgs));
+ }
+ }
+
class StrposFunc implements Function
{
@Override
diff --git a/docs/content/misc/math-expr.md b/docs/content/misc/math-expr.md
index 124935e..8fa706b 100644
--- a/docs/content/misc/math-expr.md
+++ b/docs/content/misc/math-expr.md
@@ -67,6 +67,7 @@ The following built-in functions are available.
|name|description|
|----|-----------|
|concat|concatenate a list of strings|
+|format|format(pattern[, args...]) returns a string formatted in the manner of Java's [String.format](https://docs.oracle.com/javase/8/docs/api/java/lang/String.html#format-java.lang.String-java.lang.Object...-).|
|like|like(expr, pattern[, escape]) is equivalent to SQL `expr LIKE pattern`|
|lookup|lookup(expr, lookup-name) looks up expr in a registered [query-time lookup](../querying/lookups.html)|
|parse_long|parse_long(string[, radix]) parses a string as a long with the given radix, or 10 (decimal) if a radix is not provided.|
diff --git a/docs/content/querying/sql.md b/docs/content/querying/sql.md
index eaf25ed..f6b3041 100644
--- a/docs/content/querying/sql.md
+++ b/docs/content/querying/sql.md
@@ -174,6 +174,7 @@ String functions accept strings, and return a type appropriate to the function.
|`x \|\| y`|Concat strings x and y.|
|`CONCAT(expr, expr...)`|Concats a list of expressions.|
|`TEXTCAT(expr, expr)`|Two argument version of CONCAT.|
+|`FORMAT(pattern[, args...])`|Returns a string formatted in the manner of Java's [String.format](https://docs.oracle.com/javase/8/docs/api/java/lang/String.html#format-java.lang.String-java.lang.Object...-).|
|`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/org/apache/druid/sql/calcite/expression/OperatorConversions.java b/sql/src/main/java/org/apache/druid/sql/calcite/expression/OperatorConversions.java
index 21e7a65..f982d80 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/expression/OperatorConversions.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/expression/OperatorConversions.java
@@ -27,9 +27,11 @@ 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.SqlOperandTypeChecker;
import org.apache.calcite.sql.type.SqlReturnTypeInference;
import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.druid.java.util.common.ISE;
import org.apache.druid.sql.calcite.planner.Calcites;
import org.apache.druid.sql.calcite.planner.PlannerContext;
import org.apache.druid.sql.calcite.table.RowSignature;
@@ -116,8 +118,9 @@ public class OperatorConversions
private SqlFunctionCategory functionCategory = SqlFunctionCategory.USER_DEFINED_FUNCTION;
// For operand type checking
+ private SqlOperandTypeChecker operandTypeChecker;
private List<SqlTypeFamily> operandTypes;
- private int requiredOperands = Integer.MAX_VALUE;
+ private Integer requiredOperands = null;
private OperatorBuilder(final String name)
{
@@ -158,6 +161,12 @@ public class OperatorConversions
return this;
}
+ public OperatorBuilder operandTypeChecker(final SqlOperandTypeChecker operandTypeChecker)
+ {
+ this.operandTypeChecker = operandTypeChecker;
+ return this;
+ }
+
public OperatorBuilder operandTypes(final SqlTypeFamily... operandTypes)
{
this.operandTypes = Arrays.asList(operandTypes);
@@ -172,15 +181,25 @@ public class OperatorConversions
public SqlFunction build()
{
+ final SqlOperandTypeChecker theOperandTypeChecker;
+
+ if (operandTypeChecker == null) {
+ theOperandTypeChecker = OperandTypes.family(
+ Preconditions.checkNotNull(operandTypes, "operandTypes"),
+ i -> requiredOperands == null || i + 1 > requiredOperands
+ );
+ } else if (operandTypes == null && requiredOperands == null) {
+ theOperandTypeChecker = operandTypeChecker;
+ } else {
+ throw new ISE("Cannot have both 'operandTypeChecker' and 'operandTypes' / 'requiredOperands'");
+ }
+
return new SqlFunction(
name,
kind,
Preconditions.checkNotNull(returnTypeInference, "returnTypeInference"),
null,
- OperandTypes.family(
- Preconditions.checkNotNull(operandTypes, "operandTypes"),
- i -> i + 1 > requiredOperands
- ),
+ theOperandTypeChecker,
functionCategory
);
}
diff --git a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/StringFormatOperatorConversion.java b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/StringFormatOperatorConversion.java
new file mode 100644
index 0000000..d64040e
--- /dev/null
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/StringFormatOperatorConversion.java
@@ -0,0 +1,105 @@
+/*
+ * 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.druid.sql.calcite.expression.builtin;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlCallBinding;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlOperandCountRange;
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.type.SqlOperandCountRanges;
+import org.apache.calcite.sql.type.SqlOperandTypeChecker;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.druid.java.util.common.StringUtils;
+import org.apache.druid.sql.calcite.expression.DruidExpression;
+import org.apache.druid.sql.calcite.expression.OperatorConversions;
+import org.apache.druid.sql.calcite.expression.SqlOperatorConversion;
+import org.apache.druid.sql.calcite.planner.PlannerContext;
+import org.apache.druid.sql.calcite.table.RowSignature;
+
+public class StringFormatOperatorConversion implements SqlOperatorConversion
+{
+ private static final SqlFunction SQL_FUNCTION = OperatorConversions
+ .operatorBuilder("STRING_FORMAT")
+ .operandTypeChecker(new StringFormatOperandTypeChecker())
+ .functionCategory(SqlFunctionCategory.STRING)
+ .returnType(SqlTypeName.VARCHAR)
+ .build();
+
+ @Override
+ public SqlOperator calciteOperator()
+ {
+ return SQL_FUNCTION;
+ }
+
+ @Override
+ public DruidExpression toDruidExpression(
+ final PlannerContext plannerContext,
+ final RowSignature rowSignature,
+ final RexNode rexNode
+ )
+ {
+ return OperatorConversions.convertCall(plannerContext, rowSignature, rexNode, "format");
+ }
+
+ private static class StringFormatOperandTypeChecker implements SqlOperandTypeChecker
+ {
+ @Override
+ public boolean checkOperandTypes(SqlCallBinding callBinding, boolean throwOnFailure)
+ {
+ final RelDataType firstArgType = callBinding.getOperandType(0);
+ if (SqlTypeName.CHAR_TYPES.contains(firstArgType.getSqlTypeName())) {
+ return true;
+ } else {
+ if (throwOnFailure) {
+ throw callBinding.newValidationSignatureError();
+ } else {
+ return false;
+ }
+ }
+ }
+
+ @Override
+ public SqlOperandCountRange getOperandCountRange()
+ {
+ return SqlOperandCountRanges.from(1);
+ }
+
+ @Override
+ public String getAllowedSignatures(SqlOperator op, String opName)
+ {
+ return StringUtils.format("%s(CHARACTER, [ANY, ...])", opName);
+ }
+
+ @Override
+ public Consistency getConsistency()
+ {
+ return Consistency.NONE;
+ }
+
+ @Override
+ public boolean isOptional(int i)
+ {
+ return i > 0;
+ }
+ }
+}
diff --git a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
index a1f6e15..f686841 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
@@ -61,6 +61,7 @@ import org.apache.druid.sql.calcite.expression.builtin.PositionOperatorConversio
import org.apache.druid.sql.calcite.expression.builtin.RTrimOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.RegexpExtractOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.ReinterpretOperatorConversion;
+import org.apache.druid.sql.calcite.expression.builtin.StringFormatOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.StrposOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.SubstringOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.TextcatOperatorConversion;
@@ -176,6 +177,7 @@ public class DruidOperatorTable implements SqlOperatorTable
.add(new RegexpExtractOperatorConversion())
.add(new RTrimOperatorConversion())
.add(new ParseLongOperatorConversion())
+ .add(new StringFormatOperatorConversion())
.add(new StrposOperatorConversion())
.add(new SubstringOperatorConversion())
.add(new AliasedOperatorConversion(new SubstringOperatorConversion(), "SUBSTR"))
diff --git a/sql/src/test/java/org/apache/druid/sql/calcite/expression/ExpressionsTest.java b/sql/src/test/java/org/apache/druid/sql/calcite/expression/ExpressionsTest.java
index 5e68984..c48bcb1 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/expression/ExpressionsTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/expression/ExpressionsTest.java
@@ -43,6 +43,7 @@ import org.apache.druid.segment.column.ValueType;
import org.apache.druid.sql.calcite.expression.builtin.DateTruncOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.ParseLongOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.RegexpExtractOperatorConversion;
+import org.apache.druid.sql.calcite.expression.builtin.StringFormatOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.StrposOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.TimeExtractOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.TimeFloorOperatorConversion;
@@ -170,6 +171,53 @@ public class ExpressionsTest extends CalciteTestBase
}
@Test
+ public void testStringFormat()
+ {
+ testExpression(
+ rexBuilder.makeCall(
+ new StringFormatOperatorConversion().calciteOperator(),
+ rexBuilder.makeLiteral("%x"),
+ inputRef("b")
+ ),
+ DruidExpression.fromExpression("format('%x',\"b\")"),
+ "19"
+ );
+
+ testExpression(
+ rexBuilder.makeCall(
+ new StringFormatOperatorConversion().calciteOperator(),
+ rexBuilder.makeLiteral("%s %,d"),
+ inputRef("s"),
+ integerLiteral(1234)
+ ),
+ DruidExpression.fromExpression("format('%s %,d',\"s\",1234)"),
+ "foo 1,234"
+ );
+
+ testExpression(
+ rexBuilder.makeCall(
+ new StringFormatOperatorConversion().calciteOperator(),
+ rexBuilder.makeLiteral("%s %,d"),
+ inputRef("s")
+ ),
+ DruidExpression.fromExpression("format('%s %,d',\"s\")"),
+ "%s %,d; foo"
+ );
+
+ testExpression(
+ rexBuilder.makeCall(
+ new StringFormatOperatorConversion().calciteOperator(),
+ rexBuilder.makeLiteral("%s %,d"),
+ inputRef("s"),
+ integerLiteral(1234),
+ integerLiteral(6789)
+ ),
+ DruidExpression.fromExpression("format('%s %,d',\"s\",1234,6789)"),
+ "foo 1,234"
+ );
+ }
+
+ @Test
public void testStrpos()
{
testExpression(
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org