I can think of three possibilities: * You are calling the wrong overload of RelBuilder.or and you are passing it a single argument (therefore a trivial OR). * RelBuilder.or does the right thing, but Calcite has a bug in simplifying SEARCH (see https://issues.apache.org/jira/browse/CALCITE-4173 <https://issues.apache.org/jira/browse/CALCITE-4173> and Sarg). (Are you using 1.28 or higher?) * I have found the semantics of JSON/pattern-based query languages (especially MongoDB, which doesn’t really have ANDs and Ors) can be a bit squishy. I don’t know whether GraphQL is better. Check that the semantics are well-defined.
Steps: * Print out the expression returned by RelBuilder.or and make sure that it looks OK * Try to make your GraphQL simpler by removing terms, and see at what step the bug disappears. (In other words, make a minimal test case.) * Try to write a test case in RexProgramTest Julian > On Dec 27, 2021, at 10:09 AM, Gavin Ray <ray.gavi...@gmail.com> wrote: > > Hi all, > > Sometime earlier I emailed asking about how it might be possible to > auto-generate a GraphQL API for Calcite schemas, and to convert GraphQL > queries > into Calcite queries. > > Stamatis Zampetakis replied pointing me towards the Pig examples (Thank > you! I > wasn't subscribed to the list and didn't know how to reply) and I'm happy to > report I've made what I consider good progress =) > > Currently, I am able to: > > - Consume an "org.calcite.schema.Schema" and generate a "GraphQLSchema" > from it > - Consume a GraphQL query for the generated GraphQLSchema, and convert the > "where" clause into the corresponding RelNode expressions > > However, I've been stuck on a small issue and I've not been able to figure > it > out. > > The RelNode I am generating is incorrectly translating an "OR" expression > into > an "AND" expression. I am using "RelBuilder.or()": > > https://gist.github.com/GavinRay97/b40b3359674e94dfa7dd9c26146f8573#file-main-kt-L197-L262 > > For reference, here is the GraphQL query (for the "HR" schema I am testing > with): > https://gist.github.com/GavinRay97/b40b3359674e94dfa7dd9c26146f8573#file-main-kt-L460-L486 > > And the SQL I expect it to generate roughly: > https://gist.github.com/GavinRay97/b40b3359674e94dfa7dd9c26146f8573#file-main-kt-L447-L458 > > For the expected SQL, the plan that is generated is: > =============================================================== > LogicalProject(inputs=[0], exprs=[[$2, $3, $1, $4]]) > LogicalFilter(condition=[AND(=(CAST($1):INTEGER NOT NULL, 20), >($3, > 8000), > <($3, 10000), OR(=(CAST($2):VARCHAR, 'Eric'), =(CAST($4):INTEGER, 10)))]) > LogicalTableScan(table=[[hr, emps]]) > > But for the GraphQL query, what is generated is the below plan: > =============================================================== > LogicalProject(empid=[$0], name=[$2]) > LogicalFilter(condition=[AND(=($1, 20), SEARCH($3, Sarg[[8000..10000]]), > =($2, > 'Eric'), =($4, 10))]) > LogicalTableScan(table=[[emps]]) > > > Using "RelToSqlConverter", it gives: > =============================================================== > SELECT > "empid", "name" > FROM > "emps" > WHERE > "deptno" = 20 AND ("salary" >= 8000 AND "salary" <= 10000) AND "name" = > 'Eric' AND "commission" = 10 > > The last line should be "AND (name = 'Eric' OR commission = 10)" If anyone > has > any ideas, I would be grateful for them. Probably I am using some part of > the API > wrong I assume. > > Thank you =)
