calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: JOIN query that uses USING clause, please review results
Date Wed, 07 Feb 2018 00:20:57 GMT
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 <khfaraaz82@gmail.com> 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


Mime
View raw message