Hi All,
Can someone here please take a look at this join query that uses the USING
clause, and confirm the behavior please ?
TheSQL specification for USING clause in JOIN is,
{noformat}
<named columns join> ::=
USING <left paren> <join column list> <right paren>
<join column list> ::=
<column name list>
<column name list> ::=
<column name> [ { <comma> <column name> }... ]
<column name> ::=
<identifier>
{noformat}
I tried a join query that uses the USING clause and I suspect that the
number of columns returned by calcite are not correct, can you please
confirm if that is the case.
If yes, do we have a bug for this ?
I first hit a similar issue when I tried a similar query on parquet data on
Drill 1.12.0, and then tried it on Calcite 1.15.0.
Steps to repro.
$ git clone https://github.com/apache/calcite.git
<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_calcite.git&d=DwMFAg&c=cskdkSMqhcnjZxdQVpwTXg&r=CQVtZzfJDJVXN448C12QjA&m=ywgfSLIV2MPsrxcNYx7U11lfdJUX8gkpk0XVvniIfjE&s=G8VJSHj3V77l1QfhIg4KhCjJJL2FwXKvSvZwrxoHy1I&e=>
$ cd calcite
$ mvn install -DskipTests -Dcheckstyle.skip=true
$ cd example/csv
$ ./sqlline
$ sqlline> !connect jdbc:calcite:model=target/test-classes/model.json admin
admin
sqlline version 1.3.0
{noformat}
0: jdbc:calcite:model=target/test-classes/mod> select * from EMPS;
+------------+------+------------+--------+------+------------+------------+---------+---------+----------+
| EMPNO | NAME | DEPTNO | GENDER | CITY | EMPID | AGE
| SLACKER | MANAGER | JOINEDAT |
+------------+------+------------+--------+------+------------+------------+---------+---------+----------+
| 100 | Fred | 10 | | | 30 | 25
| true | false | 1996-08-03 |
| 110 | Eric | 20 | M | San Francisco | 3 |
80 | | false | 2001-01-01 |
| 110 | John | 40 | M | Vancouver | 2 | null
| false | true | 2002-05-03 |
| 120 | Wilma | 20 | F | | 1 | 5
| | true | 2005-09-07 |
| 130 | Alice | 40 | F | Vancouver | 2 | null
| false | true | 2007-01-01 |
+------------+------+------------+--------+------+------------+------------+---------+---------+----------+
5 rows selected (0.833 seconds)
0: jdbc:calcite:model=target/test-classes/mod> select * from EMPS t1 join
EMPS t2 USING(NAME);
+------------+------+------------+--------+------+------------+------------+---------+---------+----------+------------+-------+------------+---------+-------+
| EMPNO | NAME | DEPTNO | GENDER | CITY | EMPID | AGE
| SLACKER | MANAGER | JOINEDAT | EMPNO0 | NAME0 | DEPTNO0 | GENDER0
| CITY0 |
+------------+------+------------+--------+------+------------+------------+---------+---------+----------+------------+-------+------------+---------+-------+
| 100 | Fred | 10 | | | 30 | 25
| true | false | 1996-08-03 | 100 | Fred | 10 |
| |
| 110 | Eric | 20 | M | San Francisco | 3 |
80 | | false | 2001-01-01 | 110 | Eric | 20
| M |
| 110 | John | 40 | M | Vancouver | 2 | null
| false | true | 2002-05-03 | 110 | John | 40 |
M |
| 120 | Wilma | 20 | F | | 1 | 5
| | true | 2005-09-07 | 120 | Wilma | 20 | F
| |
| 130 | Alice | 40 | F | Vancouver | 2 | null
| false | true | 2007-01-01 | 130 | Alice | 40 |
F |
+------------+------+------------+--------+------+------------+------------+---------+---------+----------+------------+-------+------------+---------+-------+
5 rows selected (0.211 seconds)
0: jdbc:calcite:model=target/test-classes/mod> explain plan for select *
from EMPS t1 join EMPS t2 USING(NAME);
+------+
| PLAN |
+------+
| EnumerableJoin(condition=[=($1, $11)], joinType=[inner])
EnumerableInterpreter
BindableTableScan(table=[[SALES, EMPS]])
EnumerableInterpreter
B |
+------+
1 row selected (0.063 seconds)
{noformat}
Thanks,
Khurram
Thanks,
Khurram
