[
https://issues.apache.org/jira/browse/CALCITE-6523?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
suibianwanwan updated CALCITE-6523:
-----------------------------------
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
{code:java}
SELECT brand_name a ,substring(brand_name, 15, 1) b sub_brand FROM
foodmart.product LIMIT 1{code}
Calcite expected result:
{code:java}
a | b
-------------+--------
Washington |{code}
Oracle Result:
{code:java}
a | b
-------------+--------
Washington | NULL{code}
was:
Oracle's `SUBSTR` has very different semantics. For example, if the length of
the character is less than the second argument, null is returned.
{code:java}
select substr('a', 2, 1);{code}
I tested Spark sql, presto, mysql all return empty string, same in calcite. I
noticed that in CALCITE-4427, the substr is handled in sqlToRel, should we also
do some conversion in reltosqlConverter?
Summary: SUBSTRING via JDBC Adapter on Oracle Returns Unexpected
Results (was: Oracle SUBSTR semantics different from Calcite)
> SUBSTRING via JDBC Adapter on Oracle Returns Unexpected Results
> ---------------------------------------------------------------
>
> Key: CALCITE-6523
> URL: https://issues.apache.org/jira/browse/CALCITE-6523
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: suibianwanwan
> Priority: Minor
>
> 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
>
> {code:java}
> SELECT brand_name a ,substring(brand_name, 15, 1) b sub_brand FROM
> foodmart.product LIMIT 1{code}
> Calcite expected result:
>
>
> {code:java}
> a | b
> -------------+--------
> Washington |{code}
> Oracle Result:
> {code:java}
> a | b
> -------------+--------
> Washington | NULL{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
