[CALCITE-5922] The SQL generated for the POSITION function(with 3 input arguments) by the SparkSqlDialect is not recognized by Spark SQL

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.35.0
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: hongyu guo
• Reporter: hongyu guo
• Created: 13/Aug/23 08:04
• Updated: 10/Nov/23 11:34
• Resolved: 25/Aug/23 11:08

Description

In SparkSQL, POSITION(substr, str[, pos]) function only accept comma-separated when there are 3 arguments.

For example:

// SparkSQL accepted SQL
select POSITION('a', 'abc', 1);

Calcite will use the IN and FROM keyword to separate the input arguments when unparsing.

// Calcite accepted and unparsed SQL
select POSITION('a' IN 'abc' FROM 1)

For 2 augument inputs, SparkSQL accept both syntaxes. So I think we should write a rule in SparkSqlDialect to convert keyword-separated syntax to comma-separted syntax for POSITION function.

 

Issue Links

• links to: GitHub Pull Request #3363