dynamic parameters in JOIN

["aka.fe2s" <ak...@gmail.com>]

Hi,

I'm developing an adapter with JOIN push-down.


I'm generating *DataContext* object with

*implementor.getRootExpression()*

in my implementation of

*EnumerableRel.implement(EnumerableRelImplementor implementor, Prefer pref)*


During the query execution phase it contains a map of dynamic variables
with names like '?0', '?1', '?2', ...

The problem is that I cannot distinguish which parameters should go on the
left and which should go on the right hand sides of the join expression, so
I can use them to propagate to my underlying data storage.

Example,

SELECT e.name, e.id
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE d.id = ? AND e.name = ?

In DataContext I'm getting
?0 = '99'
?1 = 'Adams'

I cannot infer that '99' should be bound to Department table and 'Adams' to
Employee table.

Any ideas?

--
Oleksiy

Re: dynamic parameters in JOIN

["aka.fe2s" <ak...@gmail.com>]

Thanks, Julian.

--
Алексей

On Wed, Jul 19, 2017 at 8:47 PM, Julian Hyde <jh...@apache.org> wrote:

> You could (not that you should) send all parameters to both sides of the
> join. That side of the join might ignore a particular parameter, so you’ve
> wasted a little effort, but it would work.
>
> Now, how do we optimize? Given a RelNode sub-tree, you can find which
> parameters are used inside it. You could write a visitor that builds a set
> of RexDynamicParam that occurs within all RexNodes in a given RelNode
> sub-tree. It would be a little like RelOptUtil.VariableUsedVisitor. Then
> only send parameters to a sub-tree if you know it is going to use them.
>
> Julian
>
>
>
> > On Jul 18, 2017, at 2:24 AM, aka.fe2s <ak...@gmail.com> wrote:
> >
> > Hi,
> >
> > I'm developing an adapter with JOIN push-down.
> >
> >
> > I'm generating *DataContext* object with
> >
> > *implementor.getRootExpression()*
> >
> > in my implementation of
> >
> > *EnumerableRel.implement(EnumerableRelImplementor implementor, Prefer
> pref)*
> >
> >
> > During the query execution phase it contains a map of dynamic variables
> > with names like '?0', '?1', '?2', ...
> >
> > The problem is that I cannot distinguish which parameters should go on
> the
> > left and which should go on the right hand sides of the join expression,
> so
> > I can use them to propagate to my underlying data storage.
> >
> > Example,
> >
> > SELECT e.name, e.id
> > FROM Employee e
> > JOIN Department d ON e.departmentId = d.id
> > WHERE d.id = ? AND e.name = ?
> >
> > In DataContext I'm getting
> > ?0 = '99'
> > ?1 = 'Adams'
> >
> > I cannot infer that '99' should be bound to Department table and 'Adams'
> to
> > Employee table.
> >
> > Any ideas?
> >
> > --
> > Oleksiy
>
>

Re: dynamic parameters in JOIN

[Julian Hyde <jh...@apache.org>]

You could (not that you should) send all parameters to both sides of the join. That side of the join might ignore a particular parameter, so you’ve wasted a little effort, but it would work.

Now, how do we optimize? Given a RelNode sub-tree, you can find which parameters are used inside it. You could write a visitor that builds a set of RexDynamicParam that occurs within all RexNodes in a given RelNode sub-tree. It would be a little like RelOptUtil.VariableUsedVisitor. Then only send parameters to a sub-tree if you know it is going to use them.

Julian



> On Jul 18, 2017, at 2:24 AM, aka.fe2s <ak...@gmail.com> wrote:
> 
> Hi,
> 
> I'm developing an adapter with JOIN push-down.
> 
> 
> I'm generating *DataContext* object with
> 
> *implementor.getRootExpression()*
> 
> in my implementation of
> 
> *EnumerableRel.implement(EnumerableRelImplementor implementor, Prefer pref)*
> 
> 
> During the query execution phase it contains a map of dynamic variables
> with names like '?0', '?1', '?2', ...
> 
> The problem is that I cannot distinguish which parameters should go on the
> left and which should go on the right hand sides of the join expression, so
> I can use them to propagate to my underlying data storage.
> 
> Example,
> 
> SELECT e.name, e.id
> FROM Employee e
> JOIN Department d ON e.departmentId = d.id
> WHERE d.id = ? AND e.name = ?
> 
> In DataContext I'm getting
> ?0 = '99'
> ?1 = 'Adams'
> 
> I cannot infer that '99' should be bound to Department table and 'Adams' to
> Employee table.
> 
> Any ideas?
> 
> --
> Oleksiy