This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 215577da69 [CALCITE-6689] Add support for INSTR() in Hive SqlLibrary
215577da69 is described below
commit 215577da693bad1e05102377f1bc008c8fc00aed
Author: Vikram Ahuja <[email protected]>
AuthorDate: Wed Nov 13 11:33:18 2024 +0530
[CALCITE-6689] Add support for INSTR() in Hive SqlLibrary
---
.../apache/calcite/sql/dialect/HiveSqlDialect.java | 9 +++++++-
.../calcite/sql/fun/SqlLibraryOperators.java | 2 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 27 ++++++++++++++++++++++
site/_docs/reference.md | 4 ++--
.../org/apache/calcite/test/SqlOperatorTest.java | 2 +-
5 files changed, 39 insertions(+), 5 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
index bf40da483e..dbc6d81db3 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
@@ -98,7 +98,14 @@ public class HiveSqlDialect extends SqlDialect {
writer.sep(",");
call.operand(0).unparse(writer, leftPrec, rightPrec);
if (3 == call.operandCount()) {
- throw new RuntimeException("3rd operand Not Supported for Function
INSTR in Hive");
+ writer.sep(",");
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+ }
+ if (4 == call.operandCount()) {
+ writer.sep(",");
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ call.operand(3).unparse(writer, leftPrec, rightPrec);
}
writer.endFunCall(frame);
break;
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 f74038718f..f08bd63877 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
@@ -402,7 +402,7 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction STRPOS = new SqlPositionFunction("STRPOS");
/** The "INSTR(string, substring [, position [, occurrence]])" function. */
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, HIVE, MYSQL, ORACLE})
public static final SqlFunction INSTR = new SqlPositionFunction("INSTR");
/** Generic "SUBSTR(string, position [, substringLength ])" function. */
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 2648d30446..98583ba359 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
@@ -2937,24 +2937,51 @@ class RelToSqlConverterTest {
final String query = "SELECT INSTR('ABC', 'A', 1, 1) from \"product\"";
final String expectedBQ = "SELECT INSTR('ABC', 'A', 1, 1)\n"
+ "FROM foodmart.product";
+ final String expectedHive = "SELECT INSTR('ABC', 'A', 1, 1)\n"
+ + "FROM `foodmart`.`product`";
final String expected_oracle = "SELECT INSTR('ABC', 'A', 1, 1)\n"
+ "FROM \"foodmart\".\"product\"";
final Sql sqlOracle =
fixture().withOracle().withLibrary(SqlLibrary.ORACLE);
sqlOracle.withSql(query).withOracle().ok(expected_oracle);
final Sql sqlBQ =
fixture().withBigQuery().withLibrary(SqlLibrary.BIG_QUERY);
sqlBQ.withSql(query).withBigQuery().ok(expectedBQ);
+ final Sql sqlHive = fixture().withHive().withLibrary(SqlLibrary.HIVE);
+ sqlHive.withSql(query).withHive().ok(expectedHive);
}
@Test void testInstrFunction3Operands() {
final String query = "SELECT INSTR('ABC', 'A', 1) from \"product\"";
final String expectedBQ = "SELECT INSTR('ABC', 'A', 1)\n"
+ "FROM foodmart.product";
+ final String expectedHive = "SELECT INSTR('ABC', 'A', 1)\n"
+ + "FROM `foodmart`.`product`";
final String expectedOracle = "SELECT INSTR('ABC', 'A', 1)\n"
+ "FROM \"foodmart\".\"product\"";
final Sql sqlOracle =
fixture().withOracle().withLibrary(SqlLibrary.ORACLE);
sqlOracle.withSql(query).withOracle().ok(expectedOracle);
final Sql sqlBQ =
fixture().withBigQuery().withLibrary(SqlLibrary.BIG_QUERY);
sqlBQ.withSql(query).withBigQuery().ok(expectedBQ);
+ final Sql sqlHive = fixture().withHive().withLibrary(SqlLibrary.HIVE);
+ sqlHive.withSql(query).withHive().ok(expectedHive);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6689";>[CALCITE-6689]
+ * Add support for INSTR() in Hive SqlLibrary</a>. */
+ @Test void testInstrFunction2Operands() {
+ final String query = "SELECT INSTR('ABC', 'A') from \"product\"";
+ final String expectedBQ = "SELECT INSTR('ABC', 'A')\n"
+ + "FROM foodmart.product";
+ final String expectedHive = "SELECT INSTR('ABC', 'A')\n"
+ + "FROM `foodmart`.`product`";
+ final String expectedOracle = "SELECT INSTR('ABC', 'A')\n"
+ + "FROM \"foodmart\".\"product\"";
+ final Sql sqlOracle =
fixture().withOracle().withLibrary(SqlLibrary.ORACLE);
+ sqlOracle.withSql(query).withOracle().ok(expectedOracle);
+ final Sql sqlBQ =
fixture().withBigQuery().withLibrary(SqlLibrary.BIG_QUERY);
+ sqlBQ.withSql(query).withBigQuery().ok(expectedBQ);
+ final Sql sqlHive = fixture().withHive().withLibrary(SqlLibrary.HIVE);
+ sqlHive.withSql(query).withHive().ok(expectedHive);
}
/** Tests that we escape single-quotes in character literals using back-slash
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index c0f2b5a6b7..915353d0ce 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2833,8 +2833,8 @@ In the following:
| b s | IFNULL(value1, value2) | Equivalent to
`NVL(value1, value2)`
| p | string1 ILIKE string2 [ ESCAPE string3 ] | Whether *string1*
matches pattern *string2*, ignoring case (similar to `LIKE`)
| p | string1 NOT ILIKE string2 [ ESCAPE string3 ] | Whether *string1* does
not match pattern *string2*, ignoring case (similar to `NOT LIKE`)
-| b o | INSTR(string, substring [, from [, occurrence ] ]) | Returns the
position of *substring* in *string*, searching starting at *from* (default 1),
and until locating the nth *occurrence* (default 1) of *substring*
-| b m o | INSTR(string, substring) | Equivalent to
`POSITION(substring IN string)`
+| b h o | INSTR(string, substring [, from [, occurrence ] ]) | Returns the
position of *substring* in *string*, searching starting at *from* (default 1),
and until locating the nth *occurrence* (default 1) of *substring*
+| b h m o | INSTR(string, substring) | Equivalent to
`POSITION(substring IN string)`
| b | IS_INF(value) | Returns whether *value*
is infinite
| b | IS_NAN(value) | Returns whether *value*
is NaN
| m | JSON_TYPE(jsonValue) | Returns a string value
indicating the type of *jsonValue*
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 7a56f471fb..827074837b 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -10836,7 +10836,7 @@ public class SqlOperatorTest {
f.checkNull("INSTR(x'', null, 1, 1)");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.ORACLE);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.HIVE, SqlLibrary.MYSQL,
SqlLibrary.ORACLE);
f0.forEachLibrary(libraries, consumer);
}
