[
https://issues.apache.org/jira/browse/CALCITE-4667?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17369464#comment-17369464
]
yanjing.wang commented on CALCITE-4667:
---------------------------------------
Could you specify the affected version and environment? The
A."AccountOwner_Name" and E."name" sql type may be helpful also.
> INNER join returns empty set
> ----------------------------
>
> Key: CALCITE-4667
> URL: https://issues.apache.org/jira/browse/CALCITE-4667
> Project: Calcite
> Issue Type: Bug
> Reporter: Marco Jorge
> Priority: Major
>
> I'm trying to figure out why an INNER join would return an empty set, and I
> couldn't find a justification so maybe I found a bug (or if not, please point
> me in the right direction).
> I have these 2 tables;
> {code:java}
> 0: jdbc:calcite:model=src/test/resources/mode> select "AccountOwner_Name",
> "AccountName" from "zoho"."Accounts";
> +-------------------+---------------------------------+
> | AccountOwner_Name | AccountName |
> +-------------------+---------------------------------+
> | Marco Jorge | King (Sample) |
> | Marco Jorge | Truhlar And Truhlar (Sample) |
> | Marco Jorge | Commercial Press (Sample) |
> | Marco Jorge | Morlong Associates (Sample) |
> | Marco Jorge | Chapman (Sample) |
> | Marco Jorge | Printing Dimensions (Sample) |
> | Marco Jorge | Feltz Printing Service (Sample) |
> | Marco Jorge | Chemel (Sample) |
> | Marco Jorge | Chanay (Sample) |
> | Marco Jorge | Benton (Sample) |
> +-------------------+---------------------------------+
> 10 rows selected (0.213 seconds)
> 0: jdbc:calcite:model=src/test/resources/mode> select * from
> "postgres"."employee";
> +----+-------------+
> | id | name |
> +----+-------------+
> | 1 | marco |
> | 2 | bernardes |
> | 3 | jorge |
> | 4 | Marco Jorge |
> +----+-------------+
> 4 rows selected (0.055 seconds)
> {code}
> When I perform a INNER join no results are returned:
> {code:java}
> 0: jdbc:calcite:model=src/test/resources/mode> select "AccountOwner_Name",
> "AccountName", E.* from "zoho"."Accounts" A, "postgres"."employee" E where
> E."name"=A."AccountOwner_Name";
> +-------------------+-------------+----+------+
> | AccountOwner_Name | AccountName | id | name |
> +-------------------+-------------+----+------+
> No rows selected (3.629 seconds)
> {code}
> The explain plan is:
> {code:java}
> 0: jdbc:calcite:model=src/test/resources/mode> explain plan for select
> "AccountOwner_Name", "AccountName", E.* from "zoho"."Accounts" A,
> "postgres"."employee" E where A."AccountOwner_Name"=E."name";
> PLAN EnumerableCalc(expr#0..4=[{inputs}], proj#0..3=[{exprs}])
> EnumerableMergeJoin(condition=[=($0, $4)], joinType=[inner])
> JdbcToEnumerableConverter
> JdbcSort(sort0=[$0], dir0=[ASC])
> JdbcProject(AccountOwner_Name=[$2], AccountName=[$4])
> JdbcTableScan(table=[[zoho, Accounts]])
> JdbcToEnumerableConverter
> JdbcSort(sort0=[$2], dir0=[ASC])
> JdbcProject(id=[$0], name=[$1], name0=[CAST($1):VARCHAR(2000)])
> JdbcTableScan(table=[[postgres, employee]])
> {code}
> Any hints for me?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
