You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Muhammad Gelbana <m....@gmail.com> on 2019/06/14 05:33:04 UTC
Joining two tables while each table returns a single row of nulls
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
Re: Joining two tables while each table returns a single row of nulls
Posted by Julian Hyde <jh...@apache.org>.
I think you’re right. However, let’s move further discussion to the JIRA case.
> On Jun 21, 2019, at 3:01 AM, Zhu Feng <we...@gmail.com> wrote:
>
> 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 <jh...@apache.org> 于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 <h....@alibaba-inc.com>
>> 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<m....@gmail.com>
>>> 日 期：2019年06月14日 22:58:28
>>> 收件人：dev@calcite.apache.org (dev@calcite.apache.org)<
>> dev@calcite.apache.org>
>>> 主 题：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 <jh...@apache.org> 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 <m....@gmail.com>
>>>> 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
>>>>
>>>
>>
>>
Re: Joining two tables while each table returns a single row of nulls
Posted by Zhu Feng <we...@gmail.com>.
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 <jh...@apache.org> 于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 <h....@alibaba-inc.com>
> 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<m....@gmail.com>
> > 日 期：2019年06月14日 22:58:28
> > 收件人：dev@calcite.apache.org (dev@calcite.apache.org)<
> dev@calcite.apache.org>
> > 主 题：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 <jh...@apache.org> 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 <m....@gmail.com>
> >> 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
> >>
> >
>
>
Re: Joining two tables while each table returns a single row of nulls
Posted by Julian Hyde <jh...@apache.org>.
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 <h....@alibaba-inc.com> 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<m....@gmail.com>
> 日 期：2019年06月14日 22:58:28
> 收件人：dev@calcite.apache.org (dev@calcite.apache.org)<de...@calcite.apache.org>
> 主 题：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 <jh...@apache.org> 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 <m....@gmail.com>
>> 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
>>
>
Re: Re: Joining two tables while each table returns a single row of nulls
Posted by Haisheng Yuan <h....@alibaba-inc.com>.
Even if there is no bug about EnumerableHashJoin, since there is no equi condition, why generate HashJoin? Should it be NestedLoopJoin?
- Haisheng
------------------------------------------------------------------
发件人：Muhammad Gelbana<m....@gmail.com>
日 期：2019年06月14日 22:58:28
收件人：dev@calcite.apache.org (dev@calcite.apache.org)<de...@calcite.apache.org>
主 题：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 <jh...@apache.org> 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 <m....@gmail.com>
> 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
>
Re: Joining two tables while each table returns a single row of nulls
Posted by Muhammad Gelbana <m....@gmail.com>.
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 <jh...@apache.org> 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 <m....@gmail.com>
> 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
>
Re: Joining two tables while each table returns a single row of nulls
Posted by Julian Hyde <jh...@apache.org>.
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 <m....@gmail.com> 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