[CALCITE-5831] Add SOUNDEX function(enabled in Spark library)

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Ran Tao
• Reporter: Ran Tao
• Created: 10/Jul/23 08:39
• Updated: 10/Nov/23 11:34
• Resolved: 04/Aug/23 01:03

Description

Hi, devs. I noticed that calcite support soundex for BIG_QUERY, MYSQL, POSTGRESQL, ORACLE dialect except Spark. i think we can support it.

soundex(str) - Returns Soundex code of the string.

Examples:

> SELECT soundex('Miller');
M460

https://spark.apache.org/docs/latest/api/sql/index.html#soundex

 

Note: currently soundex for BIG_QUERY, MYSQL, POSTGRESQL, ORACLE dialect throw IllegalArgumentException when string can't be mapped. However, spark not throw exception but return original string.

https://github.com/apache/spark/blob/c37d7dec8aa4d703b6dac9b9d60ff25d9d5dc665/common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java#L1595

Issue Links

• is related to: CALCITE-2965 Implement string functions: REPEAT, SPACE, SOUNDEX, DIFFERENCE
• relates to: CALCITE-5874 Add withReturnTypeInference copy method for SqlBasicFunction
• links to: GitHub Pull Request #3307