[
https://issues.apache.org/jira/browse/CALCITE-4667?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17369502#comment-17369502
]
Marco Jorge commented on CALCITE-4667:
--------------------------------------
[~zabetak] - amazing follow up and spot on!
The sort seams case-insentive:
{code:java}
0: jdbc:calcite:model=src/test/resources/mode> select * from
"postgres"."employee" order by "name";
+----+-------------+
| id | name |
+----+-------------+
| 2 | bernardes |
| 3 | jorge |
| 1 | marco |
| 4 | Marco Jorge |
+----+-------------+
{code}
The explain plan is:
{code:java}
PLAN JdbcToEnumerableConverter
JdbcSort(sort0=[$1], dir0=[ASC])
JdbcTableScan(table=[[postgres, employee]])
{code}
When I dig into the collation I have:
{code:java}
SHOW lc_collate;
-- en_US.utf8
select * from employee order by name COLLATE "en_US.utf8";
--2 bernardes
--3 jorge
--1 marco
--4 Marco Jorge
select * from employee order by name COLLATE "C";
--4 Marco Jorge
--2 bernardes
--3 jorge
--1 marco
{code}
It doesn't seem that the collation can be set at the connection level in
PostgreSQL (couldn't find it), so the database needs to be created in "C"
instead of "en_US.utf8". Is this a caveat we should add somewhere?
> 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)
