[CALCITE-6523] SUBSTRING via JDBC Adapter on Oracle Returns Unexpected Results

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Component/s: core
• Assignee: Unassigned
• Reporter: suibianwanwan
• Created: 11/Aug/24 09:03
• Updated: 14/Aug/24 08:33
• Resolved: 14/Aug/24 08:33

Description

I tried to submit the SQL to Calcite as follows, Calcite's JDBC adapter converts the query to Oracle SQL, but the Oracle SQL returns results that do not match the function's expectations

SELECT brand_name a ,substring(brand_name, 15, 1) b sub_brand FROM foodmart.product LIMIT 1

Calcite expected result:

     a       |   b
-------------+-------- 
 Washington  |

Oracle Return Result:

     a       |   b 
-------------+-------- 
 Washington  |  NULL

Issue Links

• is related to: CALCITE-815 Add an option to allow empty strings to represent null values