is cqengine very strict about additional open and close parentheses ?

[Ramesh M]

hi All,

I have cqengine sql "SELECT * FROM object WHERE (((column1 IN ('4020'))) AND ((column2 IN ('AF','DZ'))) AND (((column3 IN ('N')) AND (column4 IN ('N')))) AND (((column5 IN ('500')))))" I have extra open and close parentheses for "(((column3 IN ('N')) AND (column4 IN ('N'))))", in my point view just added one open and closing  parentheses , should be any problem, but seems cqengine very strict about extra parentheses, because I am getting error 

com.googlecode.cqengine.query.parser.common.InvalidQueryException: Failed to parse query at line 1:170: no viable alternative at input '(((intercompanyFlag IN ('N')) AND (relIntercompanyFlag IN ('N'))))'

at com.googlecode.cqengine.query.parser.common.QueryParser$1.syntaxError(QueryParser.java:54) ~[cqengine-3.0.0.jar:na]

at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) ~[antlr4-runtime-4.7.1.jar:4.7.1]

at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) ~[antlr4-runtime-4.7.1.jar:4.7.1]

at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) ~[antlr4-runtime-4.7.1.jar:4.7.1]

at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) ~[antlr4-runtime-4.7.1.jar:4.7.1]

.............

[Niall]

Yes, unfortunately CQEngine is pretty strict about parentheses.

The reason is that the SQL parser really only supports enough of SQL to translate SQL queries to native CQEngine queries.

The thing missing in the parser is an understanding of SQL's order of precedence of evaluation.

For example:

SELECT * FROM a WHERE b = 1 OR c = 2 AND d = 3

In SQL, by default that AND statement should be evaluated before the OR statement, like:

SELECT * FROM a WHERE (b = 1 OR (c = 2 AND d = 3))

However, SQL does allow to use parentheses to request a specific or different order of evaluation:

SELECT * FROM a WHERE ((b = 1 OR c = 2) AND d = 3)

The SQL parser is currently quite strict about parentheses because I didn't want to spend time implementing all of the rules about how to infer the correct order of evaluation when parentheses are not supplied.

In CQEngine native queries, there is no order of precedence to worry about because queries are always nested appropriately. So my goal was to translate queries to the native format with minimal additional processing.

I might consider extending the SQL support to be a bit less strict about this in future, but I'd probably look for someone to help with that rather than tackle it myself. Hint, hint :)