[CALCITE-3369] In LatticeSuggester, recommend lattices based on UNION queries

• Type: Bug
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 24/Sep/19 20:09
• Updated: 13/Mar/24 02:58

Description

In LatticeSuggester, recommend lattices based on UNION, EXCEPT and INTERSECT queries. Currently such queries are ignored.

Given the query

select * from t1 join t2
union
select * from t2 join t3;

suggester should generate the same lattice(s) as if it had been given two separate queries

select * from t1 join t2;
select * from t2 join t3; 

Which may be a single lattice t1 - t2 - t3, or might be two lattices t1 - t2, t2 - t3.

Same for EXCEPT (MINUS), INTERSECT, UNION ALL, etc.

If the set-op is internal, I'm not sure what to do, e.g.

select *
from sales
join (select * from good_product
      union
      select * from bad_product) using (product_id)