You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by izual <iz...@163.com> on 2020/03/25 10:07:53 UTC

Question of SQL join on nested field of right table

Hi, Community:
I defined a dim table(tblDim) with schema :

root

 |-- dim_nested_fields: ROW<`id` INT, `product_name` STRING>




and the part of SQL is : JOIN ... ON leftTable.`nested_field`.id = tblDim.`dim_nested_fields`.id.




which will throw an exception like:

Exception in thread "main" org.apache.flink.table.api.TableException: Temporal table join requires an equality condition on fields of table [MockTableSource(dim_nested_fields)].




Then I found these code snippets in UnitTest: FOR SYSTEM_TIME AS OF T.proctime AS D ON T.a + 1 = D.id + 2
Which will throw the same exception too.


In my opinion, these two are not the same case.
Why ` join ... on ... tblDim.`dim_nested_fields`.id ` failed, is it by design or a bug?
And what's the recommended way to solve this?


Thanks.

Re:Re: Question of SQL join on nested field of right table

Posted by izual <iz...@163.com>.
Yes, I am trying to do this too, just as your advice. It shall work.


Thank u.






At 2020-03-25 19:16:21, "Jark Wu" <im...@gmail.com> wrote:

Hi,


This is because temporal table join doesn't support join on a nested join. 
In blink planner, a temporal table join will be translated into lookup join which will use the equality condition fields as the lookup keys. 
However, nested fields can't be lookup keys for now. 


Is that possible to have a top-level fields as the join key in your case?


Best,
Jark


On Wed, 25 Mar 2020 at 18:08, izual <iz...@163.com> wrote:

Hi, Community:
I defined a dim table(tblDim) with schema :

root

 |-- dim_nested_fields: ROW<`id` INT, `product_name` STRING>




and the part of SQL is : JOIN ... ON leftTable.`nested_field`.id = tblDim.`dim_nested_fields`.id.




which will throw an exception like:

Exception in thread "main" org.apache.flink.table.api.TableException: Temporal table join requires an equality condition on fields of table [MockTableSource(dim_nested_fields)].




Then I found these code snippets in UnitTest: FOR SYSTEM_TIME AS OF T.proctime AS D ON T.a + 1 = D.id + 2
Which will throw the same exception too.


In my opinion, these two are not the same case.
Why ` join ... on ... tblDim.`dim_nested_fields`.id ` failed, is it by design or a bug?
And what's the recommended way to solve this?


Thanks.

Re: Question of SQL join on nested field of right table

Posted by Jark Wu <im...@gmail.com>.
Hi,

This is because temporal table join doesn't support join on a nested join.
In blink planner, a temporal table join will be translated into lookup join
which will use the equality condition fields as the lookup keys.
However, nested fields can't be lookup keys for now.

Is that possible to have a top-level fields as the join key in your case?

Best,
Jark

On Wed, 25 Mar 2020 at 18:08, izual <iz...@163.com> wrote:

> Hi, Community:
> I defined a dim table(tblDim) with schema :
>
> root
>
>  |-- dim_nested_fields: ROW<`id` INT, `product_name` STRING>
>
>
> and the part of SQL is : JOIN ... ON leftTable.`nested_field`.id =
> tblDim.`dim_nested_fields`.id.
>
>
> which will throw an exception like:
>
> Exception in thread "main" org.apache.flink.table.api.TableException:
> Temporal table join requires an equality condition on fields of table
> [MockTableSource(dim_nested_fields)].
>
>
> Then I found these code snippets in UnitTest: FOR SYSTEM_TIME AS OF
> T.proctime AS D ON T.a + 1 = D.id + 2
> Which will throw the same exception too.
>
> In my opinion, these two are not the same case.
> Why ` join ... on ... tblDim.`dim_nested_fields`.id ` failed, is it by
> design or a bug?
> And what's the recommended way to solve this?
>
> Thanks.
>
>
>
>