It looks OK to me. The queries * SELECT … FROM x JOIN y USING (…) * SELECT … FROM x, y * SELECT … FROM x JOIN y ON (…)
should all return the same columns, viz the columns of x followed by the columns of y. Julian > On Feb 6, 2018, at 3:52 PM, Khurram Faraaz <[email protected]> wrote: > > 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
