Hi all, i want to discuss current SUBSTRING func implementation.
Lets take a standard and found:
<character substring function> ::=
SUBSTRING <left paren> <character value expression> FROM <start position>
[ FOR <string length> ] <right paren>
and further : <start position> ::= <numeric value expression>
thus it not restrict <start position> for only integer types
Calcite documentation says:
SUBSTRING(string FROM integer FOR integer) (we see restrictions here)
Lets dig deeper:
Calcite implementation operands checker not restrict operands too :
1. OperandTypes.STRING_NUMERIC - (1 param: substring ('asd', 2)) (not
restricted params)
2. OperandTypes.STRING_INTEGER_INTEGER - (2 params: substring ('asd', 2,
3)) (only integer)
So if i call "SELECT SUBSTRING('asd', 1.2)" runtime exception will occur:
java.lang.RuntimeException: while resolving method 'substring[class
java.lang.String, class java.math.BigDecimal]' in class class
org.apache.calcite.runtime.SqlFunctions
at
org.apache.calcite.adapter.enumerable.EnumUtils.call(EnumUtils.java:770)
at
org.apache.calcite.adapter.enumerable.RexImpTable$MethodImplementor.call(RexImpTable.java:2866)
at
org.apache.calcite.adapter.enumerable.RexImpTable$MethodImplementor.implementSafe(RexImpTable.java:2847)
So i appeal to align (1 and 2 operands checker implementation, so for 2
operands it need: STRING_NUMERIC_NUMERIC) and append appropriate
implementation (with will cut off fractional numeric part) into
SqlFunctions.
wdyt ? if there will be no objections i will fill an issue.
thanks !
