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 <sunjincheng...@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
