Question of SQL join on nested field of right table

[Izual]

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.

 

[Jark Wu-3]

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 <[hidden email]> 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.

 

[Izual]

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" <[hidden email]> 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 <[hidden email]> 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.