This makes sense. Running a quick test with MySQL, I can see that decimal values do not give an error. It appears that MySQL will round a decimal value to the nearest integer value. ________________________________ From: stanilovsky evgeny <estanilovs...@gridgain.com> Sent: Wednesday, September 4, 2024 5:17 AM To: dev@calcite.apache.org <dev@calcite.apache.org> Subject: QUESTION: SUBSTRING implementation
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 ! Warning: The sender of this message could not be validated and may not be the actual sender.