[CALCITE-6037] The function category of ARRAY/EXTRACT_VALUE/XML_TRANSFORM/EXTRACT_XML/EXISTSNODE is incorrect

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.35.0
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Ran Tao
• Reporter: Ran Tao
• Created: 06/Oct/23 07:18
• Updated: 10/Nov/23 11:34
• Resolved: 30/Oct/23 01:48

Description

Currently some functions do not specify a category name.

It will cause the error function category:

 

function name	current category	desired category
ARRAY (Spark)	Numeric	System
EXTRACT_VALUE (MySQL)	Numeric	String
XML_TRANSFORM (Oracle)	Numeric	String
EXTRACT_XML (Oracle)	Numeric	String
EXISTSNODE (Oracle)	Numeric	String

the definition of function category in ANSI-SQL (not very sure this is the sql standard, but the implementation of calcite is similar to it) can be found at:
https://www.oreilly.com/library/view/sql-in-a/9780596155322/ch04s04.html.

Generally speaking, these functions (except ARRAY) process XML text and conform to the definition of String category rather than Numeric.

btw, because they use a simplified constructor of SqlBasicFunction, the function category is always set to Numeric. We need to set the desired function category.

Issue Links

• links to: GitHub Pull Request #3458