You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by jincheng sun <su...@gmail.com> on 2017/07/17 07:36:02 UTC

Do we need support % in select clause?

Hi guys,

  Currently the following sql is not supported.

  SELECT a%3 FROM T

* We get the exception:*

     Caused by: org.apache.calcite.sql.parser.SqlParseException: Lexical
error at line 1, column 9.  Encountered: "%" (37), after : ""
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:396)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:129)


*The main reason is we do not add % as a operator token:*

/* OPERATORS */

<DEFAULT, DQID, BTID> TOKEN :
{
    < EQ: "=" >
|   < GT: ">" >
|   < LT: "<" >
|   < HOOK: "?" >
|   < COLON: ":" >
|   < LE: "<=" >
|   < GE: ">=" >
|   < NE: "<>" >
|   < NE2: "!=" >
|   < PLUS: "+" >
|   < MINUS: "-" >
|   < STAR: "*" >
|   < SLASH: "/" >
|   < CONCAT: "||" >
|   < NAMED_ARGUMENT_ASSIGNMENT: "=>" >
|   < DOUBLE_PERIOD: ".." >
|   < QUOTE: "'" >
|   < DOUBLE_QUOTE: "\"" >
|   < VERTICAL_BAR: "|" >
|   < CARET: "^" >
|   < DOLLAR: "$" >
}

In MySQL, SQL Server are supported. For Oracle, you have to use the
MOD function.

So I'm not sure if calcite needs support, What do you think?

Welcome anybody feedback. -:)

Hope your opine. @Julian

Best,

Jincheng

Re: Do we need support % in select clause?

Posted by jincheng sun <su...@gmail.com>.

Thanks Julian. I'll file a JIRA. -:)

Thanks, Jincheng

2017-07-18 3:16 GMT+08:00 Julian Hyde <jh...@apache.org>:

> It’s a similar situation to the “!=“ operator. The standard says “<>”, but
> several databases allow “!=“ as an alternative.
>
> (There seems to be a common goal to make SQL look more like C. Whereas its
> original designers, IBM, apparently wanted to make it look like COBOL or
> PL/1. Hmmm.)
>
> Calcite allows “!=“ if SqlConformance.isBangEqualAllowed() returns true
> (which is true in the LENIENT, ORACLE_10 and ORACLE_12 conformance
> settings)[1]. We could do something similar for “%”. Please log a JIRA
> case. Contributions welcome.
>
> Julian
>
> [1] https://issues.apache.org/jira/browse/CALCITE-1374 <
> https://issues.apache.org/jira/browse/CALCITE-1374>
>
>
> > On Jul 17, 2017, at 12:36 AM, jincheng sun <su...@gmail.com>
> wrote:
> >
> > Hi guys,
> >
> >  Currently the following sql is not supported.
> >
> >  SELECT a%3 FROM T
> >
> > * We get the exception:*
> >
> >     Caused by: org.apache.calcite.sql.parser.SqlParseException: Lexical
> > error at line 1, column 9.  Encountered: "%" (37), after : ""
> > at
> > org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(
> SqlParserImpl.java:396)
> > at
> > org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(
> SqlParserImpl.java:129)
> >
> >
> > *The main reason is we do not add % as a operator token:*
> >
> > /* OPERATORS */
> >
> > <DEFAULT, DQID, BTID> TOKEN :
> > {
> >    < EQ: "=" >
> > |   < GT: ">" >
> > |   < LT: "<" >
> > |   < HOOK: "?" >
> > |   < COLON: ":" >
> > |   < LE: "<=" >
> > |   < GE: ">=" >
> > |   < NE: "<>" >
> > |   < NE2: "!=" >
> > |   < PLUS: "+" >
> > |   < MINUS: "-" >
> > |   < STAR: "*" >
> > |   < SLASH: "/" >
> > |   < CONCAT: "||" >
> > |   < NAMED_ARGUMENT_ASSIGNMENT: "=>" >
> > |   < DOUBLE_PERIOD: ".." >
> > |   < QUOTE: "'" >
> > |   < DOUBLE_QUOTE: "\"" >
> > |   < VERTICAL_BAR: "|" >
> > |   < CARET: "^" >
> > |   < DOLLAR: "$" >
> > }
> >
> > In MySQL, SQL Server are supported. For Oracle, you have to use the
> > MOD function.
> >
> > So I'm not sure if calcite needs support, What do you think?
> >
> > Welcome anybody feedback. -:)
> >
> > Hope your opine. @Julian
> >
> > Best,
> >
> > Jincheng
>
>

Re: Do we need support % in select clause?

Posted by Julian Hyde <jh...@apache.org>.

It’s a similar situation to the “!=“ operator. The standard says “<>”, but several databases allow “!=“ as an alternative.

(There seems to be a common goal to make SQL look more like C. Whereas its original designers, IBM, apparently wanted to make it look like COBOL or PL/1. Hmmm.)

Calcite allows “!=“ if SqlConformance.isBangEqualAllowed() returns true (which is true in the LENIENT, ORACLE_10 and ORACLE_12 conformance settings)[1]. We could do something similar for “%”. Please log a JIRA case. Contributions welcome.

Julian

[1] https://issues.apache.org/jira/browse/CALCITE-1374 <https://issues.apache.org/jira/browse/CALCITE-1374> 

 
> On Jul 17, 2017, at 12:36 AM, jincheng sun <su...@gmail.com> wrote:
> 
> Hi guys,
> 
>  Currently the following sql is not supported.
> 
>  SELECT a%3 FROM T
> 
> * We get the exception:*
> 
>     Caused by: org.apache.calcite.sql.parser.SqlParseException: Lexical
> error at line 1, column 9.  Encountered: "%" (37), after : ""
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:396)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:129)
> 
> 
> *The main reason is we do not add % as a operator token:*
> 
> /* OPERATORS */
> 
> <DEFAULT, DQID, BTID> TOKEN :
> {
>    < EQ: "=" >
> |   < GT: ">" >
> |   < LT: "<" >
> |   < HOOK: "?" >
> |   < COLON: ":" >
> |   < LE: "<=" >
> |   < GE: ">=" >
> |   < NE: "<>" >
> |   < NE2: "!=" >
> |   < PLUS: "+" >
> |   < MINUS: "-" >
> |   < STAR: "*" >
> |   < SLASH: "/" >
> |   < CONCAT: "||" >
> |   < NAMED_ARGUMENT_ASSIGNMENT: "=>" >
> |   < DOUBLE_PERIOD: ".." >
> |   < QUOTE: "'" >
> |   < DOUBLE_QUOTE: "\"" >
> |   < VERTICAL_BAR: "|" >
> |   < CARET: "^" >
> |   < DOLLAR: "$" >
> }
> 
> In MySQL, SQL Server are supported. For Oracle, you have to use the
> MOD function.
> 
> So I'm not sure if calcite needs support, What do you think?
> 
> Welcome anybody feedback. -:)
> 
> Hope your opine. @Julian
> 
> Best,
> 
> Jincheng