Hi Muhammad, It seems to be the problem with Linq4q[1]. When the current value is null in left side (i.e., outers), Linq4q sets (innerEnumerable=null) directly, resulting an emptyEnumerator. You can also try
Q1: result expected (1, NULL, NULL) SELECT *FROM (SELECT 1, NULLIF(5, 5)) a, (SELECT NULLIF(5, 5)) b Q2: 0 rowsSELECT *FROM (SELECT NULLIF(5, 5)) a, (SELECT 1, NULLIF(5, 5)) b [1] https://github.com/apache/calcite/blob/4e89fddab415a1e04b82c7d69960e399f608949f/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L1115 Best, DonnyZone Julian Hyde <[email protected]> 于2019年6月15日周六 上午2:05写道： > 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 > >> > > > >
