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<http://www.persistentsys.com/>
[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.
