Marco Jorge created CALCITE-4667:
------------------------------------
Summary: 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
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)
