回复：question about trim() function

["于仲(重籽)" <yu...@alibaba-inc.com>]

Thanks Julian. There are several sources that support the case of multi-char SC, including Sparks, Microsoft, as well as Calcite's own reference page. But I guess we only need to worry about the single-char case. ------------------------------------------------------------------发件人：Julian Hyde <jh...@apache.org>发送时间：2017年12月7日(星期四) 18:26收件人：dev <de...@calcite.apache.org>主　题：Re: question about trim() function
I take it you mean

  trim(‘ab’ from ‘abcba')

(Double-quotes delimit identifiers, not character literals.)

SQL standard says:

  6.30.11 If <trim function> is specified, then:
  (a) Let S be the value of the <trim source>.
  (b) If <trim character> is specified, then let SC be the
    value of <trim character>; otherwise, let SC be <space>.
  (c)  If at least one of S and SC is the null value, then the result of
    the <trim function> is the null value. If the length in characters of
    SC is not 1 (one), then an exception condition is raised:
    data exception — trim error.
 
  <trim function> ::=
    TRIM <left paren> <trim operands> <right paren>

  <trim operands> ::=
    [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source>

  <trim source> ::=
    <character value expression>

  <trim specification> ::=
      LEADING
    | TRAILING
    | BOTH

In your example, Calclte should give an error because ‘ab’ does not have length 1.

You’ll see a comment to that effect in SqlOperatorBaseTest.testTrimFunc (but sadly that part of the test is disabled).

Julian

> On Dec 7, 2017, at 1:59 AM, 于仲(重籽) <yu...@alibaba-inc.com> wrote:
> 
> It seems that     trim("ab" from "abcba")should return "c", according to https://calcite.apache.org/docs/reference.html
> However calcite removes only "a" of "ab", and this trim() returs "bcb". See implementation of SqlFunctions.trim()
> Is it a bug or something? :)
> 
> Zhong Yu

Re: question about trim() function

[Julian Hyde <jh...@apache.org>]

You’re right that http://calcite.apache.org/docs/reference.html#character-string-operators-and-functions <http://calcite.apache.org/docs/reference.html#character-string-operators-and-functions> needs fixing.

I think we’re doing the right thing in implementing the SQL standard TRIM function according to the spec. Maybe those other systems are not trying to implement the SQL standard function.

Note that we also have LTRIM and RTRIM (to be compatible with Oracle; possibly you need fun=oracle in the JDBC URL) and these may do what you want.

Julian


> On Dec 7, 2017, at 2:40 AM, 于仲(重籽) <yu...@alibaba-inc.com> wrote:
> 
> Thanks Julian. There are several sources that support the case of multi-char SC, including Sparks, Microsoft, as well as Calcite's own reference page. But I guess we only need to worry about the single-char case. 
> ------------------------------------------------------------------
> 发件人：Julian Hyde <jh...@apache.org>
> 发送时间：2017年12月7日(星期四) 18:26
> 收件人：dev <de...@calcite.apache.org>
> 主　题：Re: question about trim() function
> 
> I take it you mean
> 
>   trim(‘ab’ from ‘abcba')
> 
> (Double-quotes delimit identifiers, not character literals.)
> 
> SQL standard says:
> 
>   6.30.11 If <trim function> is specified, then:
>   (a) Let S be the value of the <trim source>.
>   (b) If <trim character> is specified, then let SC be the
>     value of <trim character>; otherwise, let SC be <space>.
>   (c)  If at least one of S and SC is the null value, then the result of
>     the <trim function> is the null value. If the length in characters of
>     SC is not 1 (one), then an exception condition is raised:
>     data exception — trim error.
>  
>   <trim function> ::=
>     TRIM <left paren> <trim operands> <right paren>
> 
>   <trim operands> ::=
>     [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source>
> 
>   <trim source> ::=
>     <character value expression>
> 
>   <trim specification> ::=
>       LEADING
>     | TRAILING
>     | BOTH
> 
> In your example, Calclte should give an error because ‘ab’ does not have length 1.
> 
> You’ll see a comment to that effect in SqlOperatorBaseTest.testTrimFunc (but sadly that part of the test is disabled).
> 
> Julian
> 
> > On Dec 7, 2017, at 1:59 AM, 于仲(重籽) <yu...@alibaba-inc.com> wrote:
> > 
> > It seems that     trim("ab" from "abcba")should return "c", according to https://calcite.apache.org/docs/reference.html
> > However calcite removes only "a" of "ab", and this trim() returs "bcb". See implementation of SqlFunctions.trim()
> > Is it a bug or something? :)
> > 
> > Zhong Yu