macroguo-ghy commented on code in PR #4854:
URL: https://github.com/apache/calcite/pull/4854#discussion_r3004917573
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -3692,11 +3692,30 @@ private SqlDialect nonOrdinalDialect() {
sql(query1).withClickHouse().ok(expected1);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7316";>[CALCITE-7316]
+ * The POSITION function in SQLite is missing the FROM clause</a>. */
@Test void testPositionFunctionForSqlite() {
final String query = "select position('A' IN 'ABC') from \"product\"";
final String expected = "SELECT INSTR('ABC', 'A')\n"
+ "FROM \"foodmart\".\"product\"";
sql(query).withSQLite().ok(expected);
+
+ final String query1 = "select position('C' IN 'ABCABC' FROM 4) from
\"product\"";
+ final String expected1 =
Review Comment:
I think implementing a complex SQL is better than rejecting translations.
And if we consider the case when statement as a whole, we can also assume that
it is not a complex query
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -3692,11 +3692,30 @@ private SqlDialect nonOrdinalDialect() {
sql(query1).withClickHouse().ok(expected1);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7316";>[CALCITE-7316]
+ * The POSITION function in SQLite is missing the FROM clause</a>. */
@Test void testPositionFunctionForSqlite() {
final String query = "select position('A' IN 'ABC') from \"product\"";
final String expected = "SELECT INSTR('ABC', 'A')\n"
+ "FROM \"foodmart\".\"product\"";
sql(query).withSQLite().ok(expected);
+
+ final String query1 = "select position('C' IN 'ABCABC' FROM 4) from
\"product\"";
+ final String expected1 =
Review Comment:
I add a new case to test the `position` in the `where` statement, and the
output SQL is valid. I don't think it needs parentheses
##########
core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java:
##########
@@ -32,6 +41,17 @@
* A <code>SqliteSqlDialect</code> implementation for the SQLite database.
*/
public class SqliteSqlDialect extends SqlDialect {
+ // Use plain function nodes here so the SQLite POSITION rewrite does not
+ // re-enter dialect-specific operator handling during unparsing.
+ private static final SqlFunction INSTR =
Review Comment:
It is a helper variable used for unparsed SQL. I originally intended to use
`ReloSqlConverterUtil.specificalOperatorByName`, but due to its low precedence,
it would produce extra parentheses. So, I have chosen the current solution.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
