Thanks for taking the time to look up the SQL standard. But
unfortunately the SQL standard isn't much use here, because it doesn't
say much about implicit conversions - it leaves that to the
implementor, such as Calcite or Postgres.
Calcite has its own rules for implicit conversions of arguments. It
also has lots of functions that take numeric and integer arguments.
Calcite's users expect that the conversion rules are consistent across
all functions. Picking one at random, LEFT(string, length) is enabled
in several libraries including Postgres. You should implement the
conversion for SUBSTRING in such a way that LEFT gets it for free.
On Thu, Sep 5, 2024 at 7:20 AM stanilovsky evgeny
<estanilovs...@gridgain.com> wrote:
>
> Thank all for reply, in section : 6.18 <string value function>
> I see only OVERLAY function that not corresponds here too:
>
> <character overlay function> ::=
> OVERLAY <left paren> <character value expression> PLACING <character value
> expression>
> FROM <start position> [ FOR <string length> ]
> [ USING <char length units> ] <right paren>
>
> <start position> ::= <numeric value expression>
> <string length> ::= <numeric value expression>
>
> or Julian you are talking not only about this kind of functions ?
> In such a case it consumes a lot of time for such a check.
>
> Why we can`t move here sequentially ? Just fix operand types ? What
> generic approach you are talking about ?
> SqlSingleOperandTypeChecker STRING_INTEGER is used only in : SqlFunction
> REPEAT
> SqlSingleOperandTypeChecker STRING_INTEGER_INTEGER only in
> SqlSubstringFunction
> STRING_STRING_INTEGER, STRING_STRING_INTEGER_INTEGER only in
> SqlOverlayFunction
>
>
> > Is the desired behavior specific to the SUBSTRING function? Or should it
> > be generic, for all functions that have an argument of type INTEGER?
> >
> > If it’s generic, can you give some other functions as examples where we
> > would want this behavior.
> >
> > Also, if it’s generic, the code should probably not be part of the
> > SUBSTRING function.
> >
> >
> >> On Sep 4, 2024, at 8:31 AM, Norman Jordan
> >> <norman.jor...@improving.com.INVALID> wrote:
> >>
> >> 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.
