[jira] [Commented] (CALCITE-2233) IN clause does not follow type semantics like JOIN

["Piotr Bojko (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2233?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16421815#comment-16421815 ] 

Piotr Bojko commented on CALCITE-2233:
--------------------------------------

Plan produced by IN
{code}
EnumerableSemiJoin(condition=[=($0, $8)], joinType=[inner]) 
	JdbcToEnumerableConverter JdbcProject(id=[$0], pname=[$1], url=[$2], description=[$4], pkey=[$5], pcounter=[$6], assigneetype=[$7], projecttype=[$10]) 
		JdbcTableScan(table=[[INTERNAL, project]]) 
	EnumerableTableScan(table=[[JIRA, ALL_ALLOWED_PROJECTS]])
{code}
Plan produced by JOIN
{code}
EnumerableCalc(expr#0..9=[{inputs}], proj#0..7=[{exprs}]) 
	EnumerableJoin(condition=[=($8, $9)], joinType=[inner]) 
		JdbcToEnumerableConverter JdbcProject(id=[$0], pname=[$1], url=[$2], description=[$4], pkey=[$5], pcounter=[$6], assigneetype=[$7], projecttype=[$10], id0=[CAST($0):DECIMAL(19, 0) NOT NULL]) 
			JdbcTableScan(table=[[INTERNAL, project]]) 
		EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(19, 0) NOT NULL], ID0=[$t1]) 
			EnumerableTableScan(table=[[JIRA, ALL_ALLOWED_PROJECTS]])
{code}

> IN clause does not follow type semantics like JOIN
> --------------------------------------------------
>
>                 Key: CALCITE-2233
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2233
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.16.0, 1.17.0
>            Reporter: Piotr Bojko
>            Assignee: Julian Hyde
>            Priority: Major
>
> Consider following queries
> SELECT * FROM A WHERE A.ID IN (SELECT B.ID FROM B)
> SELECT A.* FROM A JOIN B ON A.ID = B.ID
> When A.ID is NUMERIC(18,0) and B.ID is BIGINT - first query produces empty results while the  latter produces proper results.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)