[
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)