Can you please log a jira case, and we can discuss there. I am a bit surprised that this is being implemented using EnumerableSemiJoin. According to https://issues.apache.org/jira/browse/CALCITE-374 we can't implement ANTI-semijoins using the SemiJoin operator, which is what this query is.
On Fri, Apr 3, 2015 at 11:50 AM, Yuri Au Yong <[email protected]> wrote: > Hi, > > > > When using JdbcAdapter to execute a query with a WHERE clause wherein the > predicate using the <> operator contains a subquery, e.g.: > > > > select * from table1 where c1 <> (select c1 from table2 > where rnum =0); > > > > where; > > > > select * from table1; > > RNUM C1 C2 > > 0 10 BB > > 1 15 DD > > 2 null EE > > 3 10 FF > > > > select * from table2; > > RNUM C1 C2 > > 0 10 15 > > 1 20 25 > > 2 null 50 > > > > I observed from the generated code that the query is split to two (outer - > “select from * from table1” and inner -“select col2 from table2 where col1 > =0”) Enumerable objects and then combined with the > org.apache.calcite.runtime.Enumerables semiJoin method. > > > > The returned resultset however, is equivalent to the result of executing > “select * from table1” and the WHERE clause condition ignored. > > > > I noticed that there is an overloaded semiJoin method with > EqualityComparer > <http://www.hydromatic.net/calcite-296/apidocs/org/apache/calcite/linq4j/function/EqualityComparer.html> > parameter but from the generated code, the EqualityComparer > <http://www.hydromatic.net/calcite-296/apidocs/org/apache/calcite/linq4j/function/EqualityComparer.html> > parameter is null. > > > > Please advise on how subqueries in predicates should be handled? > > > > > > > > *Thanks & Regards,* > > > > *Yuri Au Yong* > > Software Engineer - NPM | [email protected] | Desk: +60 3 7663 > 8372 > > Persistent Systems Ltd. | Partners in Innovation | www.persistentsys.com > > [image: sign1] <https://tnpmsupport.persistentsys.com/training> > > > > DISCLAIMER ========== This e-mail may contain privileged and confidential > information which is the property of Persistent Systems Ltd. It is intended > only for the use of the individual or entity to which it is addressed. If > you are not the intended recipient, you are not authorized to read, retain, > copy, print, distribute or use this message. If you have received this > communication in error, please notify the sender and delete all copies of > this message. Persistent Systems Ltd. does not accept any liability for > virus infected mails. >
