You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Yuri Au Yong <yu...@persistent.com> on 2015/04/03 20:50:33 UTC
Enumerables SemiJoin failed to handle subquery in predicate
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 | yuri.auyong@persistent.my | 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.
Re: Enumerables SemiJoin failed to handle subquery in predicate
Posted by Julian Hyde <ju...@hydromatic.net>.
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 <yu...@persistent.com>
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 | yuri.auyong@persistent.my | 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.
>