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 e74ff3eaa0 [CALCITE-6156] Add ENDSWITH, STARTSWITH functions (enabled
in Postgres, Snowflake libraries)
e74ff3eaa0 is described below
commit e74ff3eaa0a339ad3d099739780ce05a40efbe87
Author: Tanner Clary <tannercl...@google.com>
AuthorDate: Mon Dec 4 17:00:41 2023 -0800
[CALCITE-6156] Add ENDSWITH, STARTSWITH functions (enabled in Postgres,
Snowflake libraries)
---
.../main/java/org/apache/calcite/sql/SqlKind.java | 6 +
.../calcite/sql/dialect/SnowflakeSqlDialect.java | 22 ++++
.../org/apache/calcite/sql/fun/SqlLibrary.java | 4 +-
.../calcite/sql/fun/SqlLibraryOperators.java | 29 +++--
.../calcite/sql2rel/StandardConvertletTable.java | 2 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 34 +++++
.../java/org/apache/calcite/util/UtilTest.java | 8 +-
site/_docs/reference.md | 7 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 139 ++++++++++++++-------
9 files changed, 192 insertions(+), 59 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 35034ca910..3b11ac5cec 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,12 @@ public enum SqlKind {
/** {@code SUBSTR} function (PostgreSQL semantics). */
SUBSTR_POSTGRESQL,
+ /** {@code ENDS_WITH} function. */
+ ENDS_WITH,
+
+ /** {@code STARTS_WITH} function. */
+ STARTS_WITH,
+
/** Call to a function using JDBC function syntax. */
JDBC_FN,
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 b12e647488..32c2874298 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
@@ -17,7 +17,11 @@
package org.apache.calcite.sql.dialect;
import org.apache.calcite.avatica.util.Casing;
+import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
+import org.apache.calcite.sql.parser.SqlParserPos;
/**
* A <code>SqlDialect</code> implementation for the Snowflake database.
@@ -36,6 +40,24 @@ public class SnowflakeSqlDialect extends SqlDialect {
super(context);
}
+ @Override public void unparseCall(final SqlWriter writer, final SqlCall
call, final int leftPrec,
+ final int rightPrec) {
+ switch (call.getKind()) {
+ case ENDS_WITH:
+ SqlCall endsWithCall = SqlLibraryOperators.ENDSWITH
+ .createCall(SqlParserPos.ZERO, call.getOperandList());
+ super.unparseCall(writer, endsWithCall, leftPrec, rightPrec);
+ break;
+ case STARTS_WITH:
+ SqlCall startsWithCall = SqlLibraryOperators.STARTSWITH
+ .createCall(SqlParserPos.ZERO, call.getOperandList());
+ super.unparseCall(writer, startsWithCall, leftPrec, rightPrec);
+ break;
+ default:
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ }
+ }
+
@Override public boolean supportsApproxCountDistinct() {
return true;
}
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
index 3b75d1a763..3f95150608 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
@@ -71,6 +71,8 @@ public enum SqlLibrary {
/** A collection of operators that are in PostgreSQL but not in standard
* SQL. */
POSTGRESQL("p", "postgresql"),
+ /** A collection of operators that are in Snowflake but not in standard SQL.
*/
+ SNOWFLAKE("f", "snowflake"),
/** A collection of operators that are in Apache Spark but not in standard
* SQL. */
SPARK("s", "spark");
@@ -97,7 +99,7 @@ public enum SqlLibrary {
switch (this) {
case ALL:
return ImmutableList.of(BIG_QUERY, CALCITE, HIVE, MSSQL, MYSQL, ORACLE,
- POSTGRESQL, SPARK);
+ POSTGRESQL, SNOWFLAKE, SPARK);
default:
return ImmutableList.of();
}
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 0abce560e5..7f113cc96d 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
@@ -67,6 +67,7 @@ import static org.apache.calcite.sql.fun.SqlLibrary.MSSQL;
import static org.apache.calcite.sql.fun.SqlLibrary.MYSQL;
import static org.apache.calcite.sql.fun.SqlLibrary.ORACLE;
import static org.apache.calcite.sql.fun.SqlLibrary.POSTGRESQL;
+import static org.apache.calcite.sql.fun.SqlLibrary.SNOWFLAKE;
import static org.apache.calcite.sql.fun.SqlLibrary.SPARK;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -360,17 +361,25 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER,
SqlFunctionCategory.STRING);
- /** The "ENDS_WITH(value1, value2)" function (BigQuery). */
- @LibraryOperator(libraries = {BIG_QUERY})
- public static final SqlFunction ENDS_WITH =
- SqlBasicFunction.create("ENDS_WITH", ReturnTypes.BOOLEAN_NULLABLE,
- OperandTypes.STRING_SAME_SAME, SqlFunctionCategory.STRING);
+ /** The "ENDS_WITH(value1, value2)" function (BigQuery, PostgreSQL). */
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ public static final SqlBasicFunction ENDS_WITH =
+ SqlBasicFunction.create(SqlKind.ENDS_WITH, ReturnTypes.BOOLEAN_NULLABLE,
+ OperandTypes.STRING_SAME_SAME);
- /** The "STARTS_WITH(value1, value2)" function (BigQuery). */
- @LibraryOperator(libraries = {BIG_QUERY})
- public static final SqlFunction STARTS_WITH =
- SqlBasicFunction.create("STARTS_WITH", ReturnTypes.BOOLEAN_NULLABLE,
- OperandTypes.STRING_SAME_SAME, SqlFunctionCategory.STRING);
+ /** The "ENDSWITH(value1, value2)" function (Snowflake). */
+ @LibraryOperator(libraries = {SNOWFLAKE})
+ public static final SqlFunction ENDSWITH = ENDS_WITH.withName("ENDSWITH");
+
+ /** The "STARTS_WITH(value1, value2)" function (BigQuery, PostgreSQL). */
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ public static final SqlBasicFunction STARTS_WITH =
+ SqlBasicFunction.create(SqlKind.STARTS_WITH,
ReturnTypes.BOOLEAN_NULLABLE,
+ OperandTypes.STRING_SAME_SAME);
+
+ /** The "STARTSWITH(value1, value2)" function (Snowflake). */
+ @LibraryOperator(libraries = {SNOWFLAKE})
+ public static final SqlFunction STARTSWITH =
STARTS_WITH.withName("STARTSWITH");
/** BigQuery's "SUBSTR(string, position [, substringLength ])" function. */
@LibraryOperator(libraries = {BIG_QUERY})
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 0656b35c39..69666ffd34 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -133,6 +133,8 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
addAlias(SqlStdOperatorTable.PERCENT_REMAINDER, SqlStdOperatorTable.MOD);
addAlias(SqlLibraryOperators.IFNULL, SqlLibraryOperators.NVL);
addAlias(SqlLibraryOperators.REGEXP_SUBSTR,
SqlLibraryOperators.REGEXP_EXTRACT);
+ addAlias(SqlLibraryOperators.ENDSWITH, SqlLibraryOperators.ENDS_WITH);
+ addAlias(SqlLibraryOperators.STARTSWITH, SqlLibraryOperators.STARTS_WITH);
// Register convertlets for specific objects.
registerOp(SqlStdOperatorTable.CAST, this::convertCast);
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 ba235f3d6f..79c340e2da 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
@@ -6662,6 +6662,40 @@ class RelToSqlConverterTest {
sql(sql).withMysql().ok(expectedMysql);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6156";>[CALCITE-6156]
+ * Add ENDSWITH, STARTSWITH functions (enabled in Postgres, Snowflake
libraries)</a>. */
+ @Test void testSnowflakeStartsWith() {
+ final String query = "select startswith(\"brand_name\", 'a')\n"
+ + "from \"product\"";
+ final String expectedBigQuery = "SELECT STARTS_WITH(brand_name, 'a')\n"
+ + "FROM foodmart.product";
+ final String expectedPostgres = "SELECT STARTS_WITH(\"brand_name\", 'a')\n"
+ + "FROM \"foodmart\".\"product\"";
+ final String expectedSnowflake = "SELECT STARTSWITH(\"brand_name\", 'a')\n"
+ + "FROM \"foodmart\".\"product\"";
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withBigQuery().ok(expectedBigQuery);
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withPostgresql().ok(expectedPostgres);
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withSnowflake().ok(expectedSnowflake);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6156";>[CALCITE-6156]
+ * Add ENDSWITH, STARTSWITH functions (enabled in Postgres, Snowflake
libraries)</a>. */
+ @Test void testSnowflakeEndsWith() {
+ final String query = "select endswith(\"brand_name\", 'a')\n"
+ + "from \"product\"";
+ final String expectedBigQuery = "SELECT ENDS_WITH(brand_name, 'a')\n"
+ + "FROM foodmart.product";
+ final String expectedPostgres = "SELECT ENDS_WITH(\"brand_name\", 'a')\n"
+ + "FROM \"foodmart\".\"product\"";
+ final String expectedSnowflake = "SELECT ENDSWITH(\"brand_name\", 'a')\n"
+ + "FROM \"foodmart\".\"product\"";
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withBigQuery().ok(expectedBigQuery);
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withPostgresql().ok(expectedPostgres);
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withSnowflake().ok(expectedSnowflake);
+ }
+
@Test void testSubstringInSpark() {
final String query = "select substring(\"brand_name\" from 2) "
+ "from \"product\"\n";
diff --git a/core/src/test/java/org/apache/calcite/util/UtilTest.java
b/core/src/test/java/org/apache/calcite/util/UtilTest.java
index 78deae0229..2802e561c8 100644
--- a/core/src/test/java/org/apache/calcite/util/UtilTest.java
+++ b/core/src/test/java/org/apache/calcite/util/UtilTest.java
@@ -951,16 +951,16 @@ class UtilTest {
assertThat(SqlLibrary.expand(ImmutableList.of(a)),
hasToString("[ALL, BIG_QUERY, CALCITE, HIVE, MSSQL, MYSQL, ORACLE, "
- + "POSTGRESQL, SPARK]"));
+ + "POSTGRESQL, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(a, c)),
hasToString("[ALL, BIG_QUERY, CALCITE, HIVE, MSSQL, MYSQL, ORACLE, "
- + "POSTGRESQL, SPARK]"));
+ + "POSTGRESQL, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(c, a)),
hasToString("[CALCITE, ALL, BIG_QUERY, HIVE, MSSQL, MYSQL, ORACLE, "
- + "POSTGRESQL, SPARK]"));
+ + "POSTGRESQL, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(c, o, a)),
hasToString("[CALCITE, ORACLE, ALL, BIG_QUERY, HIVE, MSSQL, MYSQL, "
- + "POSTGRESQL, SPARK]"));
+ + "POSTGRESQL, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(o, c, o)),
hasToString("[ORACLE, CALCITE]"));
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 80ca0e3a92..1bcb076bf1 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2629,6 +2629,7 @@ The 'C' (compatibility) column contains value:
* '*' for all libraries,
* 'b' for Google BigQuery ('fun=bigquery' in the connect string),
* 'c' for Apache Calcite ('fun=calcite' in the connect string),
+* 'f' for Snowflake ('fun=snowflake' in the connect string),
* 'h' for Apache Hive ('fun=hive' in the connect string),
* 'm' for MySQL ('fun=mysql' in the connect string),
* 'q' for Microsoft SQL Server ('fun=mssql' in the connect string),
@@ -2728,7 +2729,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b | DATE_TRUNC(date, timeUnit) | Truncates *date* to the
granularity of *timeUnit*, rounding to the beginning of the unit
| o | DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) |
Compares *value* to each *valueN* value one by one; if *value* is equal to a
*valueN*, returns the corresponding *resultN*, else returns *default*, or NULL
if *default* is not specified
| p | DIFFERENCE(string, string) | Returns a measure of
the similarity of two strings, namely the number of character positions that
their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0
if the `SOUNDEX` values are totally different
-| b | ENDS_WITH(string1, string2) | Returns whether
*string2* is a suffix of *string1*
+| f | ENDSWITH(string1, string2) | Returns whether
*string2* is a suffix of *string1*
+| b p | ENDS_WITH(string1, string2) | Equivalent to
`ENDSWITH(string1, string2)`
| o | EXTRACT(xml, xpath, [, namespaces ]) | Returns the XML
fragment of the element or elements matched by the XPath expression. The
optional namespace value that specifies a default mapping or namespace mapping
for prefixes, which is used when evaluating the XPath expression
| o | EXISTSNODE(xml, xpath, [, namespaces ]) | Determines whether
traversal of a XML document using a specified xpath results in any nodes.
Returns 0 if no nodes remain after applying the XPath traversal on the document
fragment of the element or elements matched by the XPath expression. Returns 1
if any nodes remain. The optional namespace value that specifies a default
mapping or namespace mapping for prefixes, which is used when evaluating the
XPath expression.
| m | EXTRACTVALUE(xml, xpathExpr)) | Returns the text of the
first text node which is a child of the element or elements matched by the
XPath expression.
@@ -2825,7 +2827,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| s | SOUNDEX(string) | Returns the phonetic
representation of *string*; return original *string* if *string* is encoded
with multi-byte encoding such as UTF-8
| m | SPACE(integer) | Returns a string of
*integer* spaces; returns an empty string if *integer* is less than 1
| b | SPLIT(string [, delimiter ]) | Returns the string
array of *string* split at *delimiter* (if omitted, default is comma). If the
*string* is empty it returns an empty array, otherwise, if the *delimiter* is
empty, it returns an array containing the original *string*.
-| b | STARTS_WITH(string1, string2) | Returns whether
*string2* is a prefix of *string1*
+| f | STARTSWITH(string1, string2) | Returns whether
*string2* is a prefix of *string1*
+| b p | STARTS_WITH(string1, string2) | Equivalent to
`STARTSWITH(string1, string2)`
| m | STRCMP(string, string) | Returns 0 if both of
the strings are same and returns -1 when the first argument is smaller than the
second and 1 when the second one is smaller than the first one
| b p | STRPOS(string, substring) | Equivalent to
`POSITION(substring IN string)`
| b m o p | SUBSTR(string, position [, substringLength ]) | Returns a portion
of *string*, beginning at character *position*, *substringLength* characters
long. SUBSTR calculates lengths using characters as defined by the input
character set
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 2f21b83dbd..51b19b26cb 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4982,7 +4982,7 @@ public class SqlOperatorTest {
f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3)");
f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3, 'i')");
};
- f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.ORACLE,
SqlLibrary.BIG_QUERY), consumer);
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
SqlLibrary.ORACLE), consumer);
// Tests for double-backslash indexed capturing groups for regexp_replace
in BQ
final SqlOperatorFixture f1 =
@@ -8898,51 +8898,106 @@ public class SqlOperatorTest {
}
@Test void testStartsWithFunction() {
- final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
- f.setFor(SqlLibraryOperators.STARTS_WITH);
- f.checkBoolean("starts_with('12345', '123')", true);
- f.checkBoolean("starts_with('12345', '1243')", false);
- f.checkBoolean("starts_with(x'11', x'11')", true);
- f.checkBoolean("starts_with(x'112211', x'33')", false);
- f.checkFails("^starts_with('aabbcc', x'aa')^",
- "Cannot apply 'STARTS_WITH' to arguments of type "
- + "'STARTS_WITH\\(<CHAR\\(6\\)>, <BINARY\\(1\\)>\\)'\\. Supported "
- + "form\\(s\\): 'STARTS_WITH\\(<STRING>, <STRING>\\)'",
- false);
- f.checkNull("starts_with(null, null)");
- f.checkNull("starts_with('12345', null)");
- f.checkNull("starts_with(null, '123')");
- f.checkBoolean("starts_with('', '123')", false);
- f.checkBoolean("starts_with('', '')", true);
- f.checkNull("starts_with(x'aa', null)");
- f.checkNull("starts_with(null, x'aa')");
- f.checkBoolean("starts_with(x'1234', x'')", true);
- f.checkBoolean("starts_with(x'', x'123456')", false);
- f.checkBoolean("starts_with(x'', x'')", true);
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.STARTS_WITH);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkBoolean("starts_with('12345', '123')", true);
+ f.checkBoolean("starts_with('12345', '1243')", false);
+ f.checkBoolean("starts_with(x'11', x'11')", true);
+ f.checkBoolean("starts_with(x'112211', x'33')", false);
+ f.checkFails("^starts_with('aabbcc', x'aa')^",
+ "Cannot apply 'STARTS_WITH' to arguments of type "
+ + "'STARTS_WITH\\(<CHAR\\(6\\)>, <BINARY\\(1\\)>\\)'\\.
Supported "
+ + "form\\(s\\): 'STARTS_WITH\\(<STRING>, <STRING>\\)'",
+ false);
+ f.checkNull("starts_with(null, null)");
+ f.checkNull("starts_with('12345', null)");
+ f.checkNull("starts_with(null, '123')");
+ f.checkBoolean("starts_with('', '123')", false);
+ f.checkBoolean("starts_with('', '')", true);
+ f.checkNull("starts_with(x'aa', null)");
+ f.checkNull("starts_with(null, x'aa')");
+ f.checkBoolean("starts_with(x'1234', x'')", true);
+ f.checkBoolean("starts_with(x'', x'123456')", false);
+ f.checkBoolean("starts_with(x'', x'')", true);
+ };
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.POSTGRESQL),
consumer);
+ }
+
+ /** Tests for Snowflake's {@code STARTSWITH} function. */
+ @Test void testSnowflakeStartsWithFunction() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.SNOWFLAKE);
+ f.setFor(SqlLibraryOperators.STARTSWITH);
+ f.checkBoolean("startswith('12345', '123')", true);
+ f.checkBoolean("startswith('12345', '1243')", false);
+ f.checkBoolean("startswith(x'11', x'11')", true);
+ f.checkBoolean("startswith(x'112211', x'33')", false);
+ f.checkFails("^startswith('aabbcc', x'aa')^",
+ "Cannot apply 'STARTSWITH' to arguments of type "
+ + "'STARTSWITH\\(<CHAR\\(6\\)>, <BINARY\\(1\\)>\\)'\\. Supported "
+ + "form\\(s\\): 'STARTSWITH\\(<STRING>, <STRING>\\)'",
+ false);
+ f.checkNull("startswith(null, null)");
+ f.checkNull("startswith('12345', null)");
+ f.checkNull("startswith(null, '123')");
+ f.checkBoolean("startswith('', '123')", false);
+ f.checkBoolean("startswith('', '')", true);
+ f.checkNull("startswith(x'aa', null)");
+ f.checkNull("startswith(null, x'aa')");
+ f.checkBoolean("startswith(x'1234', x'')", true);
+ f.checkBoolean("startswith(x'', x'123456')", false);
+ f.checkBoolean("startswith(x'', x'')", true);
}
@Test void testEndsWithFunction() {
- final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.ENDS_WITH);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkBoolean("ends_with('12345', '345')", true);
+ f.checkBoolean("ends_with('12345', '123')", false);
+ f.checkBoolean("ends_with(x'11', x'11')", true);
+ f.checkBoolean("ends_with(x'112211', x'33')", false);
+ f.checkFails("^ends_with('aabbcc', x'aa')^",
+ "Cannot apply 'ENDS_WITH' to arguments of type "
+ + "'ENDS_WITH\\(<CHAR\\(6\\)>, <BINARY\\(1\\)>\\)'\\. Supported "
+ + "form\\(s\\): 'ENDS_WITH\\(<STRING>, <STRING>\\)'",
+ false);
+ f.checkNull("ends_with(null, null)");
+ f.checkNull("ends_with('12345', null)");
+ f.checkNull("ends_with(null, '123')");
+ f.checkBoolean("ends_with('', '123')", false);
+ f.checkBoolean("ends_with('', '')", true);
+ f.checkNull("ends_with(x'aa', null)");
+ f.checkNull("ends_with(null, x'aa')");
+ f.checkBoolean("ends_with(x'1234', x'')", true);
+ f.checkBoolean("ends_with(x'', x'123456')", false);
+ f.checkBoolean("ends_with(x'', x'')", true);
+ };
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.POSTGRESQL),
consumer);
+ }
+
+ @Test void testSnowflakeEndsWithFunction() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.SNOWFLAKE);
f.setFor(SqlLibraryOperators.ENDS_WITH);
- f.checkBoolean("ends_with('12345', '345')", true);
- f.checkBoolean("ends_with('12345', '123')", false);
- f.checkBoolean("ends_with(x'11', x'11')", true);
- f.checkBoolean("ends_with(x'112211', x'33')", false);
- f.checkFails("^ends_with('aabbcc', x'aa')^",
- "Cannot apply 'ENDS_WITH' to arguments of type "
- + "'ENDS_WITH\\(<CHAR\\(6\\)>, <BINARY\\(1\\)>\\)'\\. Supported "
- + "form\\(s\\): 'ENDS_WITH\\(<STRING>, <STRING>\\)'",
- false);
- f.checkNull("ends_with(null, null)");
- f.checkNull("ends_with('12345', null)");
- f.checkNull("ends_with(null, '123')");
- f.checkBoolean("ends_with('', '123')", false);
- f.checkBoolean("ends_with('', '')", true);
- f.checkNull("ends_with(x'aa', null)");
- f.checkNull("ends_with(null, x'aa')");
- f.checkBoolean("ends_with(x'1234', x'')", true);
- f.checkBoolean("ends_with(x'', x'123456')", false);
- f.checkBoolean("ends_with(x'', x'')", true);
+ f.checkBoolean("endswith('12345', '345')", true);
+ f.checkBoolean("endswith('12345', '123')", false);
+ f.checkBoolean("endswith(x'11', x'11')", true);
+ f.checkBoolean("endswith(x'112211', x'33')", false);
+ f.checkFails("^endswith('aabbcc', x'aa')^",
+ "Cannot apply 'ENDSWITH' to arguments of type "
+ + "'ENDSWITH\\(<CHAR\\(6\\)>, <BINARY\\(1\\)>\\)'\\. Supported "
+ + "form\\(s\\): 'ENDSWITH\\(<STRING>, <STRING>\\)'",
+ false);
+ f.checkNull("endswith(null, null)");
+ f.checkNull("endswith('12345', null)");
+ f.checkNull("endswith(null, '123')");
+ f.checkBoolean("endswith('', '123')", false);
+ f.checkBoolean("endswith('', '')", true);
+ f.checkNull("endswith(x'aa', null)");
+ f.checkNull("endswith(null, x'aa')");
+ f.checkBoolean("endswith(x'1234', x'')", true);
+ f.checkBoolean("endswith(x'', x'123456')", false);
+ f.checkBoolean("endswith(x'', x'')", true);
}
/** Tests the {@code SPLIT} operator. */
