It probably doesn’t matter very much. Every join algorithm has to be able to generate a Cartesian product. E.g. if you have 2 records in the EMP table with DEPTNO=10 and 3 records in the DEPT table with DEPTNO=10 then the join needs to generate 6 rows.
I see a cross-join (i.e. JOIN … ON TRUE) as a special case of equi-join, where you are joining 0 columns from the left to 0 columns from the right, and any equi-join algorithm should be able to handle it. > On Jun 14, 2019, at 10:42 AM, Haisheng Yuan <[email protected]> wrote: > > Even if there is no bug about EnumerableHashJoin, since there is no equi > condition, why generate HashJoin? Should it be NestedLoopJoin? > > - Haisheng > > ------------------------------------------------------------------ > 发件人：Muhammad Gelbana<[email protected]> > 日 期：2019年06月14日 22:58:28 > 收件人：[email protected] ([email protected])<[email protected]> > 主 题：Re: Joining two tables while each table returns a single row of nulls > > Here it is [1], thanks for the confirmation. > > [1] https://issues.apache.org/jira/browse/CALCITE-3128 > > Thanks, > Gelbana > > > On Fri, Jun 14, 2019 at 8:55 AM Julian Hyde <[email protected]> wrote: > >> It's a bug. Please log it. >> >> I got the following plan, which looks correct. >> >>> explain plan for select * from (select nullif(5,5)), (select >> nullif(5,5)); >> EnumerableHashJoin(condition=[true], joinType=[inner]) >> EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1]) >> EnumerableValues(tuples=[[{ 0 }]]) >> EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1]) >> EnumerableValues(tuples=[[{ 0 }]]) >> >> I suspect that EnumerableHashJoin is doing something wrong during >> execution. >> >> On Thu, Jun 13, 2019 at 10:33 PM Muhammad Gelbana <[email protected]> >> wrote: >>> >>> I run the following query against MySQL, PostgreSQL and Calcite and found >>> that Calcite returns 0 rows, while the other DBMSs return a single row of >>> two NULLs >>> >>> SELECT * FROM (SELECT NULLIF(5, 5)) a, (SELECT NULLIF(5, 5)) b >>> >>> Is this possibly a bug ? Is there a configuration or something to tweak >> to >>> return the same output MySQL and PostgreSQL without modifying the query ? >>> >>> Thanks, >>> Gelbana >> >
