[ 
https://issues.apache.org/jira/browse/IGNITE-26199?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Konstantin Orlov updated IGNITE-26199:
--------------------------------------
    Description: 
Looks like we can quite easily support LEFT joins when joining by combined equi 
and non-equi predicate. Take a look at the query below:
{code}
CREATE TABLE depts (d_id INT PRIMARY KEY, d_name VARCHAR, d_type VARCHAR);
CREATE TABLE projects (p_id INT PRIMARY KEY, p_dept_id INT, p_name VARCHAR);

EXPLAIN SELECT
  d_name,
  count(p_id)
 FROM depts
 LEFT JOIN projects
   ON d_id = p_dept_id AND POSITION(CASE WHEN d_type LIKE 'HQ' THEN 
'HeadQuarters' ELSE d_name END IN p_name) <> 1
GROUP BY d_name; 
{code}

In this case, we still can take an advantage of HashJoin algorithm: first, we 
lookup bucket by `d_id = p_dept_id` predicate, and then do post filtration with 
`POSITION(CASE WHEN d_type LIKE 'HQ' THEN 'HeadQuarters' ELSE d_name END IN 
p_name) <> 1`.

> Sql. Expand usage of HashJoin with non-equi predicate to LEFT joins
> -------------------------------------------------------------------
>
>                 Key: IGNITE-26199
>                 URL: https://issues.apache.org/jira/browse/IGNITE-26199
>             Project: Ignite
>          Issue Type: Improvement
>          Components: sql ai3
>            Reporter: Konstantin Orlov
>            Assignee: Konstantin Orlov
>            Priority: Major
>              Labels: ignite-3
>
> Looks like we can quite easily support LEFT joins when joining by combined 
> equi and non-equi predicate. Take a look at the query below:
> {code}
> CREATE TABLE depts (d_id INT PRIMARY KEY, d_name VARCHAR, d_type VARCHAR);
> CREATE TABLE projects (p_id INT PRIMARY KEY, p_dept_id INT, p_name VARCHAR);
> EXPLAIN SELECT
>   d_name,
>   count(p_id)
>  FROM depts
>  LEFT JOIN projects
>    ON d_id = p_dept_id AND POSITION(CASE WHEN d_type LIKE 'HQ' THEN 
> 'HeadQuarters' ELSE d_name END IN p_name) <> 1
> GROUP BY d_name; 
> {code}
> In this case, we still can take an advantage of HashJoin algorithm: first, we 
> lookup bucket by `d_id = p_dept_id` predicate, and then do post filtration 
> with `POSITION(CASE WHEN d_type LIKE 'HQ' THEN 'HeadQuarters' ELSE d_name END 
> IN p_name) <> 1`.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

Reply via email to