[jira] [Comment Edited] (CALCITE-4667) INNER join returns empty set

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

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

Stamatis Zampetakis edited comment on CALCITE-4667 at 6/25/21, 2:11 PM:
------------------------------------------------------------------------

It certainly looks like a bug and I suspect that it has to with the fact that the DBMS may sort rows differently ({{JdbcSort}}) than the in-memory Java-based comparator ({{EnumerableMergeJoin}}) is expecting them.

What does the following query return and what's the plan:

{code:sql}
select * from "postgres"."employee" order by "AccountOwner_Name" asc
{code}

If the result is different from that below then my guess is correct.

{noformat}
[Marco Jorge, bernardes, jorge, marco]
{noformat}



was (Author: zabetak):
It certainly looks like a bug and I suspect that it has to with the fact that the DBMS may sort rows differently ({{JdbcSort}}) than the in-memory Java-based comparator ({{EnumerableMergeJoin}}) is expecting them.

What does the following query return and what's the plan:

{code:sql}
select * from "postgres"."employee" order by "AccountOwner_Name" asc
{code}


> 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)