[FRG-191] sql parsing exception for trim function syntax

[dynamobi-build]

[reporter="elin", created="Wed, 30 Aug 2006 19:36:19 -0500 (GMT-05:00)"] Hi John,

Anil is running into Sql parsing error when running the trim function with syntax below:

TRIM (BOTH FROM (COALESCE(cast(null as varchar(2)),'') || ' ' || COALESCE("User"."LastName",'')))

It's complaining about finding the parenthesis in front of the first coalesce, but I believe this syntax is correct for SQL2003: see p.277,273, etc in the 2nd section

I believe both of the following statements should work, but don't:
--------------
values trim (both from (coalesce(cast(null as varchar(2)))||' '||coalesce('junk ','')));
values trim (coalesce(cast(null as varchar(2)))||' '||coalesce('junk ',''));

-- the 1st statement
---------------------------------------
Error: org.eigenbase.sql.parser.SqlParseException: Encountered "(" at line 1, column 24.
Was expecting one of:
    "CASE" ...
    "CURRENT_DATE" ...
    "CURRENT_DEFAULT_TRANSFORM_GROUP" ...
    "CURRENT_PATH" ...
    "CURRENT_ROLE" ...
    "CURRENT_TIME" ...
    "CURRENT_TIMESTAMP" ...
    "CURRENT_USER" ...
    "DATE" ...
    "FALSE" ...
    "INTERVAL" ...
    "LOCALTIME" ...
    "LOCALTIMESTAMP" ...
    "MULTISET" ...
    "NEW" ...
    "NULL" ...
    "SESSION_USER" ...
    "SYSTEM_USER" ...
    "TIME" ...
    "TIMESTAMP" ...
    "TRUE" ...
    "UNKNOWN" ...
    "USER" ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
    "?" ...
    "+" ...
    "-" ...
     ...
     ...
    "CAST" ...
    "EXTRACT" ...
    "POSITION" ...
    "CONVERT" ...
    "TRANSLATE" ...
    "OVERLAY" ...
    "FLOOR" ...
    "CEIL" ...
    "CEILING" ...
    "SUBSTRING" ...
    "TRIM" ...
    "SPECIFIC" ...
    "ABS" ...
    "AVG" ...
    "CARDINALITY" ...
    "CHAR_LENGTH" ...
    "CHARACTER_LENGTH" ...
    "COALESCE" ...
    "COLLECT" ...
    "CUME_DIST" ...
    "COUNT" ...
    "DENSE_RANK" ...
    "ELEMENT" ...
    "EXP" ...
    "FIRST_VALUE" ...
    "FUSION" ...
    "LAST_VALUE" ...
    "LN" ...
    "LOWER" ...
    "MAX" ...
    "MIN" ...
    "MOD" ...
    "NULLIF" ...
    "OCTET_LENGTH" ...
    "PERCENT_RANK" ...
    "POWER" ...
    "RANK" ...
    "ROW_NUMBER" ...
    "SQRT" ...
    "SUM" ...
    "UPPER" ... (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: org.eigenbase.sql.parser.SqlParseException: Encountered "(" at line 1, column 24.
Was expecting one of:
    "CASE" ...
    "CURRENT_DATE" ...
    "CURRENT_DEFAULT_TRANSFORM_GROUP" ...
    "CURRENT_PATH" ...
    "CURRENT_ROLE" ...
    "CURRENT_TIME" ...
    "CURRENT_TIMESTAMP" ...
    "CURRENT_USER" ...
    "DATE" ...
    "FALSE" ...
    "INTERVAL" ...
    "LOCALTIME" ...
    "LOCALTIMESTAMP" ...
    "MULTISET" ...
    "NEW" ...
    "NULL" ...
    "SESSION_USER" ...
    "SYSTEM_USER" ...
    "TIME" ...
    "TIMESTAMP" ...
    "TRUE" ...
    "UNKNOWN" ...
    "USER" ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
     ...
    "?" ...
    "+" ...
    "-" ...
     ...
     ...
    "CAST" ...
    "EXTRACT" ...
    "POSITION" ...
    "CONVERT" ...
    "TRANSLATE" ...
    "OVERLAY" ...
    "FLOOR" ...
    "CEIL" ...
    "CEILING" ...
    "SUBSTRING" ...
    "TRIM" ...
    "SPECIFIC" ...
    "ABS" ...
    "AVG" ...
    "CARDINALITY" ...
    "CHAR_LENGTH" ...
    "CHARACTER_LENGTH" ...
    "COALESCE" ...
    "COLLECT" ...
    "CUME_DIST" ...
    "COUNT" ...
    "DENSE_RANK" ...
    "ELEMENT" ...
    "EXP" ...
    "FIRST_VALUE" ...
    "FUSION" ...
    "LAST_VALUE" ...
    "LN" ...
    "LOWER" ...
    "MAX" ...
    "MIN" ...
    "MOD" ...
    "NULLIF" ...
    "OCTET_LENGTH" ...
    "PERCENT_RANK" ...
    "POWER" ...
    "RANK" ...
    "ROW_NUMBER" ...
    "SQRT" ...
    "SUM" ...
    "UPPER" ...

        at net.sf.farrago.parser.impl.FarragoParserImpl.convertException(FarragoParserImpl.java:6394)
        at net.sf.farrago.parser.impl.FarragoParserImpl.normalizeException(FarragoParserImpl.java:58)
        at net.sf.farrago.parser.FarragoAbstractParser.parseSqlText(FarragoAbstractParser.java:149)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:920)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:862)at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)
        at sqlline.SqlLine$Commands.execute(Ljava.lang.String;Z)Z(Unknown Source)
        at sqlline.SqlLine$Commands.sql(Ljava.lang.String;)Z(Unknown Source)
        at sqlline.SqlLine.dispatch(Ljava.lang.String;)Z(Unknown Source)
        at sqlline.SqlLine.begin([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)
        at sqlline.SqlLine.mainWithInputRedirection([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)
        at sqlline.SqlLine.main([Ljava.lang.String;)V(Unknown Source)
0: jdbc:luciddb:>


--------------------------------------
For the 2nd statement:

Error: org.eigenbase.sql.parser.SqlParseException: Encountered "||" at line 1, column 48.
Was expecting one of:
    "FROM" ...
    "OVER" ... (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: org.eigenbase.sql.parser.SqlParseException: Encountered "||" at line 1, column 48.
Was expecting one of:
    "FROM" ...
    "OVER" ...

        at net.sf.farrago.parser.impl.FarragoParserImpl.convertException(FarragoParserImpl.java:6394)
        at net.sf.farrago.parser.impl.FarragoParserImpl.normalizeException(FarragoParserImpl.java:58)
        at net.sf.farrago.parser.FarragoAbstractParser.parseSqlText(FarragoAbstractParser.java:149)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:920)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:862)at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)
        at sqlline.SqlLine$Commands.execute(Ljava.lang.String;Z)Z(Unknown Source)
        at sqlline.SqlLine$Commands.sql(Ljava.lang.String;)Z(Unknown Source)
        at sqlline.SqlLine.dispatch(Ljava.lang.String;)Z(Unknown Source)
        at sqlline.SqlLine.begin([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)
        at sqlline.SqlLine.mainWithInputRedirection([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)
        at sqlline.SqlLine.main([Ljava.lang.String;)V(Unknown Source)
0: jdbc:luciddb:>

[dynamobi-build]

[author="jvs", created="Wed, 30 Aug 2006 20:24:26 -0500 (GMT-05:00)"] Hmm...for some reason the grammar has an AtomicExpression as the argument to TRIM. I'll fix that.

[dynamobi-build]

[author="jvs", created="Thu, 31 Aug 2006 23:15:21 -0500 (GMT-05:00)"] Fixed in eigenchange 7519.