[jira] [Commented] (CALCITE-2530) Reasonable behavior of TRIM function when the length of trim character is not 1(one)

["Hongze Zhang (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16603860#comment-16603860 ] 

Hongze Zhang commented on CALCITE-2530:
---------------------------------------

And found this in SqlOperatorBaseTest#testTrimFunc:

 
{code:java}
if (Bug.FNL3_FIXED) {
// SQL:2003 6.29.9: trim string must have length=1. Failure occurs
// at runtime.
//
// TODO: Change message to "Invalid argument\(s\) for
// 'TRIM' function".
// The message should come from a resource file, and should still
// have the SQL error code 22027.
tester.checkFails(
"trim('xy' from 'abcde')",
"could not calculate results for the following row:\n"
+ "\\[ 0 \\]\n"
+ "Messages:\n"
+ "\\[0\\]:PC=0 Code=22027 ",
true);
tester.checkFails(
"trim('' from 'abcde')",
"could not calculate results for the following row:\n"
+ "\\[ 0 \\]\n"
+ "Messages:\n"
+ "\\[0\\]:PC=0 Code=22027 ",
true);
}
{code}
Since [FNL-3|http://issues.eigenbase.org/browse/FNL-3] is not accessible (from my side), [~julianhyde] Could you please share some thing about the optimization possibility of this point, thank you.

 

 

 

> Reasonable behavior of TRIM function when the length of trim character is not 1(one)
> ------------------------------------------------------------------------------------
>
>                 Key: CALCITE-2530
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2530
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: Hongze Zhang
>            Assignee: Julian Hyde
>            Priority: Minor
>
> Calcite's TRIM function takes the first character of <trim character> string to trim the <trim source>, whereas SQL standard suggests to report a "trim error" when the length of <trim character> is not 1.
> I am not sure if this could be treated as an issue(since the calcite's way might has better availability), until I found this:
> https://issues.apache.org/jira/browse/BEAM-4704
> It seems user could be sometimes confused with the behavior of TRIM function in Calcite.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)