Re: FilterableTable query planner question

[Eric Berryman]

Perfect!

Thank you so much!

On Mon, Jan 8, 2024 at 12:41 Alessandro Solimando <
alessandro.solima...@gmail.com> wrote:

> Hey Eric,
> that's correct and it's FilterSetOpTransposeRule
> <
> https://github.com/apache/calcite/blob/73e83f6be78c1d6a021a17385463ea572cf0473c/core/src/main/java/org/apache/calcite/rel/rules/FilterSetOpTransposeRule.java
> >
> that
> performs such transformations for set operators (including UNION ALL).
>
> HTH,
> Alessandro
>
> On Mon, 8 Jan 2024 at 18:28, Eric Berryman 
> wrote:
>
> > Thank you,
> >
> > That makes sense. But it seems I should see a filter pushed below for a
> > where on a union, no?
> > ie.
> > SELECT t2.field
> > FROM (
> > SELECT id, field FROM table1
> > UNION ALL
> > SELECT id, field FROM table2
> > UNION ALL
> > SELECT id, field FROM table3) as t2
> > WHERE t2.field = 'test'
> >
> > Thank you again!
> > Eric
> >
> >
> > On Wed, Jan 3, 2024 at 5:43 AM Stamatis Zampetakis 
> > wrote:
> >
> > > Hey Eric,
> > >
> > > When you have a disjunction in the WHERE clause it may not be safe to
> > > push the condition below a join especially when it comes to outer
> > > joins. I suppose that the FilterJoinRule [1] is the place that you
> > > want to check to see if the filter can be pushed below the join and
> > > into the scan.
> > >
> > > Best,
> > > Stamatis
> > >
> > > [1]
> > >
> >
> https://github.com/apache/calcite/blob/8d9b27f1ace7f975407920cb88806715b1f0ef82/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
> > >
> > > On Tue, Jan 2, 2024 at 11:22 PM Eric Berryman  >
> > > wrote:
> > > >
> > > > Hello,
> > > >
> > > > I'm making a FilterableTable with LDAP as a backend.
> > > >
> > > > I noticed in the FilterableTable method:
> > > > public Enumerable scan(DataContext root, List
> > filters)
> > > >
> > > > The filters list is empty if the sql where clause is checking the
> same
> > > > field in all tables.
> > > > ie.
> > > > select test1.field test2.field
> > > > from test1
> > > > full outer join test2 on test1.id = test2.id
> > > > where test1.field = 'myval' or test2.field = 'myval';
> > > >
> > > > When I do an EXPLAIN PLAN FOR select ...
> > > > I notice the BindableTableScan filters array is empty with both:
> > > > where test1.field = 'myval' or test2.field = 'myval';
> > > > and it has the filter if I remove one:
> > > > where test1.field = 'myval'
> > > >
> > > > Is there an example I could be pointed to help understand the query
> > > planner
> > > > here, and hopefully write my implementation such that the filters
> show
> > up
> > > > as expected for each table?
> > > >
> > > > Thank you!
> > > > Eric
> > >
> >
>

Re: FilterableTable query planner question

[Alessandro Solimando]

Hey Eric,
that's correct and it's FilterSetOpTransposeRule

that
performs such transformations for set operators (including UNION ALL).

HTH,
Alessandro

On Mon, 8 Jan 2024 at 18:28, Eric Berryman  wrote:

> Thank you,
>
> That makes sense. But it seems I should see a filter pushed below for a
> where on a union, no?
> ie.
> SELECT t2.field
> FROM (
> SELECT id, field FROM table1
> UNION ALL
> SELECT id, field FROM table2
> UNION ALL
> SELECT id, field FROM table3) as t2
> WHERE t2.field = 'test'
>
> Thank you again!
> Eric
>
>
> On Wed, Jan 3, 2024 at 5:43 AM Stamatis Zampetakis 
> wrote:
>
> > Hey Eric,
> >
> > When you have a disjunction in the WHERE clause it may not be safe to
> > push the condition below a join especially when it comes to outer
> > joins. I suppose that the FilterJoinRule [1] is the place that you
> > want to check to see if the filter can be pushed below the join and
> > into the scan.
> >
> > Best,
> > Stamatis
> >
> > [1]
> >
> https://github.com/apache/calcite/blob/8d9b27f1ace7f975407920cb88806715b1f0ef82/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
> >
> > On Tue, Jan 2, 2024 at 11:22 PM Eric Berryman 
> > wrote:
> > >
> > > Hello,
> > >
> > > I'm making a FilterableTable with LDAP as a backend.
> > >
> > > I noticed in the FilterableTable method:
> > > public Enumerable scan(DataContext root, List
> filters)
> > >
> > > The filters list is empty if the sql where clause is checking the same
> > > field in all tables.
> > > ie.
> > > select test1.field test2.field
> > > from test1
> > > full outer join test2 on test1.id = test2.id
> > > where test1.field = 'myval' or test2.field = 'myval';
> > >
> > > When I do an EXPLAIN PLAN FOR select ...
> > > I notice the BindableTableScan filters array is empty with both:
> > > where test1.field = 'myval' or test2.field = 'myval';
> > > and it has the filter if I remove one:
> > > where test1.field = 'myval'
> > >
> > > Is there an example I could be pointed to help understand the query
> > planner
> > > here, and hopefully write my implementation such that the filters show
> up
> > > as expected for each table?
> > >
> > > Thank you!
> > > Eric
> >
>

Re: FilterableTable query planner question

[Ruben Q L]

Hi Eric,

If I am not mistaken, in order to achieve that you need to use
FilterSetOpTransposeRule [1], see CoreRules#FILTER_SET_OP_TRANSPOSE.

Best regards,
Ruben

[1]
https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rules/FilterSetOpTransposeRule.java

On Mon, Jan 8, 2024 at 5:28 PM Eric Berryman 
wrote:

> Thank you,
>
> That makes sense. But it seems I should see a filter pushed below for a
> where on a union, no?
> ie.
> SELECT t2.field
> FROM (
> SELECT id, field FROM table1
> UNION ALL
> SELECT id, field FROM table2
> UNION ALL
> SELECT id, field FROM table3) as t2
> WHERE t2.field = 'test'
>
> Thank you again!
> Eric
>
>
> On Wed, Jan 3, 2024 at 5:43 AM Stamatis Zampetakis 
> wrote:
>
> > Hey Eric,
> >
> > When you have a disjunction in the WHERE clause it may not be safe to
> > push the condition below a join especially when it comes to outer
> > joins. I suppose that the FilterJoinRule [1] is the place that you
> > want to check to see if the filter can be pushed below the join and
> > into the scan.
> >
> > Best,
> > Stamatis
> >
> > [1]
> >
> https://github.com/apache/calcite/blob/8d9b27f1ace7f975407920cb88806715b1f0ef82/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
> >
> > On Tue, Jan 2, 2024 at 11:22 PM Eric Berryman 
> > wrote:
> > >
> > > Hello,
> > >
> > > I'm making a FilterableTable with LDAP as a backend.
> > >
> > > I noticed in the FilterableTable method:
> > > public Enumerable scan(DataContext root, List
> filters)
> > >
> > > The filters list is empty if the sql where clause is checking the same
> > > field in all tables.
> > > ie.
> > > select test1.field test2.field
> > > from test1
> > > full outer join test2 on test1.id = test2.id
> > > where test1.field = 'myval' or test2.field = 'myval';
> > >
> > > When I do an EXPLAIN PLAN FOR select ...
> > > I notice the BindableTableScan filters array is empty with both:
> > > where test1.field = 'myval' or test2.field = 'myval';
> > > and it has the filter if I remove one:
> > > where test1.field = 'myval'
> > >
> > > Is there an example I could be pointed to help understand the query
> > planner
> > > here, and hopefully write my implementation such that the filters show
> up
> > > as expected for each table?
> > >
> > > Thank you!
> > > Eric
> >
>

Re: FilterableTable query planner question

[Eric Berryman]

Thank you,

That makes sense. But it seems I should see a filter pushed below for a
where on a union, no?
ie.
SELECT t2.field
FROM (
SELECT id, field FROM table1
UNION ALL
SELECT id, field FROM table2
UNION ALL
SELECT id, field FROM table3) as t2
WHERE t2.field = 'test'

Thank you again!
Eric


On Wed, Jan 3, 2024 at 5:43 AM Stamatis Zampetakis 
wrote:

> Hey Eric,
>
> When you have a disjunction in the WHERE clause it may not be safe to
> push the condition below a join especially when it comes to outer
> joins. I suppose that the FilterJoinRule [1] is the place that you
> want to check to see if the filter can be pushed below the join and
> into the scan.
>
> Best,
> Stamatis
>
> [1]
> https://github.com/apache/calcite/blob/8d9b27f1ace7f975407920cb88806715b1f0ef82/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java
>
> On Tue, Jan 2, 2024 at 11:22 PM Eric Berryman 
> wrote:
> >
> > Hello,
> >
> > I'm making a FilterableTable with LDAP as a backend.
> >
> > I noticed in the FilterableTable method:
> > public Enumerable scan(DataContext root, List filters)
> >
> > The filters list is empty if the sql where clause is checking the same
> > field in all tables.
> > ie.
> > select test1.field test2.field
> > from test1
> > full outer join test2 on test1.id = test2.id
> > where test1.field = 'myval' or test2.field = 'myval';
> >
> > When I do an EXPLAIN PLAN FOR select ...
> > I notice the BindableTableScan filters array is empty with both:
> > where test1.field = 'myval' or test2.field = 'myval';
> > and it has the filter if I remove one:
> > where test1.field = 'myval'
> >
> > Is there an example I could be pointed to help understand the query
> planner
> > here, and hopefully write my implementation such that the filters show up
> > as expected for each table?
> >
> > Thank you!
> > Eric
>

Re: FilterableTable query planner question

[Stamatis Zampetakis]

Hey Eric,

When you have a disjunction in the WHERE clause it may not be safe to
push the condition below a join especially when it comes to outer
joins. I suppose that the FilterJoinRule [1] is the place that you
want to check to see if the filter can be pushed below the join and
into the scan.

Best,
Stamatis

[1] 
https://github.com/apache/calcite/blob/8d9b27f1ace7f975407920cb88806715b1f0ef82/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java

On Tue, Jan 2, 2024 at 11:22 PM Eric Berryman  wrote:
>
> Hello,
>
> I'm making a FilterableTable with LDAP as a backend.
>
> I noticed in the FilterableTable method:
> public Enumerable scan(DataContext root, List filters)
>
> The filters list is empty if the sql where clause is checking the same
> field in all tables.
> ie.
> select test1.field test2.field
> from test1
> full outer join test2 on test1.id = test2.id
> where test1.field = 'myval' or test2.field = 'myval';
>
> When I do an EXPLAIN PLAN FOR select ...
> I notice the BindableTableScan filters array is empty with both:
> where test1.field = 'myval' or test2.field = 'myval';
> and it has the filter if I remove one:
> where test1.field = 'myval'
>
> Is there an example I could be pointed to help understand the query planner
> here, and hopefully write my implementation such that the filters show up
> as expected for each table?
>
> Thank you!
> Eric