Ordering table scans

[Gopalakrishna Holla <vg...@gmail.com>]

Hello,

The problem I'm working on is to order table scans so that one scan can
insert filters into the another. It seems like it should be a common
problem so I'm sure I'm missing some context. Taking a concrete example,
imagine two tables Users and Movies with the following schema

User {
  id: long
  name: String
}

Movie {
  id : long
  name: String
  director_user_id : long
}

movie.director_user_id is a foreign key to user.id

So for this query,

SELECT m.name as movieName, u.name as directorName
FROM Movie as m
JOIN User as u
ON m.director_user_id = u.id
WHERE m.id = 1;

Ideally, I'd like the table scan of Movie to go first, retrieve the
director_user_id and then inject the value(s) as a filter into the scan for
User (instead of doing a full table scan on User).

My question is
- Does Calcite provide an inbuilt way to do this?
- If not, could you help me by pointing to any implementations that do this
so that I can learn from them and apply it to my use case?

I'v tried to search in stack overflow and the dev archives but haven't been
able to find something that can help. Any guidance is much appreciated !

Cheers,
Gopal

Re: Ordering table scans

[Gopalakrishna Holla <vg...@gmail.com>]

Thank you for the pointers ! Will take a look

On Fri, Feb 22, 2019 at 5:39 PM Walaa Eldin Moustafa <wa...@gmail.com>
wrote:

> Also, since you are particularly interested in applying filters from
> one table on another, that is actually a nested loops join rather than
> a filter. Linq4j implementation has examples of nested loops join in
> the CorrelateJoinTest class.
>
> Thanks,
> Walaa.
>

Re: Ordering table scans

[Walaa Eldin Moustafa <wa...@gmail.com>]

Also, since you are particularly interested in applying filters from
one table on another, that is actually a nested loops join rather than
a filter. Linq4j implementation has examples of nested loops join in
the CorrelateJoinTest class.

Thanks,
Walaa.

Re: Ordering table scans

[Walaa Eldin Moustafa <wa...@gmail.com>]

Hi Gopal,

You may take a look at the FilterableTable API. It allows filtering tables
based on a list of RexNodes. Check
ScannableTableTest.testFilterableTableCoopertive() for an example test.

Thanks,
Walaa.


On Fri, Feb 22, 2019 at 12:37 PM Gopalakrishna Holla <vg...@gmail.com>
wrote:

> Hello,
>
> The problem I'm working on is to order table scans so that one scan can
> insert filters into the another. It seems like it should be a common
> problem so I'm sure I'm missing some context. Taking a concrete example,
> imagine two tables Users and Movies with the following schema
>
> User {
>   id: long
>   name: String
> }
>
> Movie {
>   id : long
>   name: String
>   director_user_id : long
> }
>
> movie.director_user_id is a foreign key to user.id
>
> So for this query,
>
> SELECT m.name as movieName, u.name as directorName
> FROM Movie as m
> JOIN User as u
> ON m.director_user_id = u.id
> WHERE m.id = 1;
>
> Ideally, I'd like the table scan of Movie to go first, retrieve the
> director_user_id and then inject the value(s) as a filter into the scan for
> User (instead of doing a full table scan on User).
>
> My question is
> - Does Calcite provide an inbuilt way to do this?
> - If not, could you help me by pointing to any implementations that do this
> so that I can learn from them and apply it to my use case?
>
> I'v tried to search in stack overflow and the dev archives but haven't been
> able to find something that can help. Any guidance is much appreciated !
>
> Cheers,
> Gopal
>