This is an automated email from the ASF dual-hosted git repository.
tanner pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 88a415032c [CALCITE-6182] Add LENGTH/LEN functions (enabled in
Snowflake library)
88a415032c is described below
commit 88a415032c0c54bba18d192a4333f56b1da240c1
Author: Tanner Clary <[email protected]>
AuthorDate: Tue Dec 26 08:59:18 2023 -0800
[CALCITE-6182] Add LENGTH/LEN functions (enabled in Snowflake library)
---
.../src/main/java/org/apache/calcite/sql/SqlKind.java | 3 +++
.../calcite/sql/dialect/SnowflakeSqlDialect.java | 5 +++++
.../apache/calcite/sql/fun/SqlLibraryOperators.java | 7 ++++++-
.../apache/calcite/sql/fun/SqlStdOperatorTable.java | 5 ++---
.../calcite/sql2rel/StandardConvertletTable.java | 2 ++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 17 +++++++++++++++++
site/_docs/reference.md | 3 ++-
.../java/org/apache/calcite/test/SqlOperatorTest.java | 19 ++++++++++++++++++-
8 files changed, 55 insertions(+), 6 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 381531a82b..5375ee6748 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -806,6 +806,9 @@ public enum SqlKind {
/** {@code SUBSTR} function (PostgreSQL semantics). */
SUBSTR_POSTGRESQL,
+ /** {@code CHAR_LENGTH} function. */
+ CHAR_LENGTH,
+
/** {@code ENDS_WITH} function. */
ENDS_WITH,
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
index 32c2874298..f7d8741491 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
@@ -43,6 +43,11 @@ public class SnowflakeSqlDialect extends SqlDialect {
@Override public void unparseCall(final SqlWriter writer, final SqlCall
call, final int leftPrec,
final int rightPrec) {
switch (call.getKind()) {
+ case CHAR_LENGTH:
+ SqlCall lengthCall = SqlLibraryOperators.LENGTH
+ .createCall(SqlParserPos.ZERO, call.getOperandList());
+ super.unparseCall(writer, lengthCall, leftPrec, rightPrec);
+ break;
case ENDS_WITH:
SqlCall endsWithCall = SqlLibraryOperators.ENDSWITH
.createCall(SqlParserPos.ZERO, call.getOperandList());
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 878e7e0ab3..d4c8b9d1ea 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -274,8 +274,13 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction IFNULL = NVL.withName("IFNULL");
+ /** The "LEN(string)" function. */
+ @LibraryOperator(libraries = {SNOWFLAKE})
+ public static final SqlFunction LEN =
+ SqlStdOperatorTable.CHAR_LENGTH.withName("LEN");
+
/** The "LENGTH(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SNOWFLAKE})
public static final SqlFunction LENGTH =
SqlStdOperatorTable.CHAR_LENGTH.withName("LENGTH");
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 40ec61d624..4b07e5ad01 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1613,10 +1613,9 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
public static final SqlFunction POSITION = new
SqlPositionFunction("POSITION");
public static final SqlBasicFunction CHAR_LENGTH =
- SqlBasicFunction.create("CHAR_LENGTH",
+ SqlBasicFunction.create(SqlKind.CHAR_LENGTH,
ReturnTypes.INTEGER_NULLABLE,
- OperandTypes.CHARACTER,
- SqlFunctionCategory.NUMERIC);
+ OperandTypes.CHARACTER);
/** Alias for {@link #CHAR_LENGTH}. */
public static final SqlFunction CHARACTER_LENGTH =
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 69666ffd34..d9bc1d27d5 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -120,6 +120,8 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
// Register aliases (operators which have a different name but
// identical behavior to other operators).
+ addAlias(SqlLibraryOperators.LEN,
+ SqlStdOperatorTable.CHAR_LENGTH);
addAlias(SqlLibraryOperators.LENGTH,
SqlStdOperatorTable.CHAR_LENGTH);
addAlias(SqlStdOperatorTable.CHARACTER_LENGTH,
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 3a4c480aa3..7a5bc0c499 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -6696,6 +6696,23 @@ class RelToSqlConverterTest {
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withSnowflake().ok(expectedSnowflake);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6182";>[CALCITE-6182]
+ * Add LENGTH/LEN functions (enabled in Snowflake library)</a>. */
+ @Test void testSnowflakeLength() {
+ final String query = "select CHAR_LENGTH(\"brand_name\")\n"
+ + "from \"product\"";
+ final String expectedBigQuery = "SELECT CHAR_LENGTH(brand_name)\n"
+ + "FROM foodmart.product";
+ // Snowflake would accept either LEN or LENGTH, but we currently unparse
into "LENGTH"
+ // since it seems to be used across more dialects.
+ final String expectedSnowflake = "SELECT LENGTH(\"brand_name\")\n"
+ + "FROM \"foodmart\".\"product\"";
+ Sql sql = sql(query).withLibrary(SqlLibrary.BIG_QUERY);
+ sql.withBigQuery().ok(expectedBigQuery);
+ sql.withSnowflake().ok(expectedSnowflake);
+ }
+
@Test void testSubstringInSpark() {
final String query = "select substring(\"brand_name\" from 2) "
+ "from \"product\"\n";
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 841c42650c..3f97448543 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2769,7 +2769,8 @@ In the following:
| m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of
bytes used to store the binary representation of *jsonValue*
| b o | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
| b m p | LEFT(string, length) | Returns the leftmost
*length* characters from the *string*
-| b | LENGTH(string) | Equivalent to
`CHAR_LENGTH(string)`
+| f | LEN(string) | Equivalent to
`CHAR_LENGTH(string)`
+| b f | LENGTH(string) | Equivalent to
`CHAR_LENGTH(string)`
| h s | LEVENSHTEIN(string1, string2) | Returns the Levenshtein
distance between *string1* and *string2*
| b | LOG(numeric1 [, numeric2 ]) | Returns the logarithm
of *numeric1* to base *numeric2*, or base e if *numeric2* is not present
| b o | LPAD(string, length [, pattern ]) | Returns a string or
bytes value that consists of *string* prepended to *length* with *pattern*
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 583fd06882..0e270b7280 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4111,6 +4111,23 @@ public class SqlOperatorTest {
f.checkNull("CHARACTER_LENGTH(cast(null as varchar(1)))");
}
+ /** Tests {@code LEN} function from Snowflake. {@code LEN} is a
+ * Snowflake-specific alias for {@code CHAR_LENGTH}. */
+ @Test void testLenFunc() {
+ final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.LEN);
+ f0.checkFails("^len('hello')^",
+ "No match found for function signature LEN\\(<CHARACTER>\\)",
+ false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SNOWFLAKE);
+ f.checkScalar("len('hello')", "5", "INTEGER NOT NULL");
+ f.checkScalar("len('')", "0", "INTEGER NOT NULL");
+ f.checkScalar("len(CAST('x' as CHAR(3)))", "3", "INTEGER NOT NULL");
+ f.checkScalar("len(CAST('x' as VARCHAR(4)))", "1", "INTEGER NOT NULL");
+ f.checkNull("len(CAST(NULL as CHAR(5)))");
+ }
+
+ /** Tests {@code LENGTH} function from Big Query/Snowflake. {@code LENGTH}
is a
+ * BQ/Snowflake-specific alias for {@code CHAR_LENGTH}. */
@Test void testLengthFunc() {
final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.LENGTH);
f0.checkFails("^length('hello')^",
@@ -4125,7 +4142,7 @@ public class SqlOperatorTest {
f.checkNull("length(CAST(NULL as CHAR(5)))");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY), consumer);
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.SNOWFLAKE),
consumer);
}
@Test void testOctetLengthFunc() {
