[MDEV-16366] Simplify query expressions over UNION/EXCEPT/INTERSECT Created: 2018-06-01  Updated: 2023-08-24

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: Task Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The goal of this task is to simplify/optimize query expressions over UNION/EXCEPT/INTERSECT:

1. convert UNION/EXCEPT/INTERSECT ALL into UNION/EXCEPT/INTERSECT DISTINCT

whenever it's possible.
E.g.

select1 UNION ALL select2 UNION DISTINCT select3 UNION ALL select4
 
=>
 
select1 UNION DISTICT select2 UNION DISTINCT select3 UNION ALL select4

2. Remove unnecessary brackets

E.g.

a)
 
select1 UNION ALL (select2 UNION ALL select3)
 
=>
 
select1 UNION ALL select2 UNION ALL select3
 
 b)
 
select1 UNION  (select2 EXCEPT select3)
 
=>
 
select1 UNION select2 EXCEPT select 3
 
c)
 
select1 EXCEPT  (select2 EXCEPT select3)
 
=>
 
select1 EXCEPT  select2 UNION select3

3. Swap operands in order to omit brackets

a)
 
 select1 UNION [ALL]  (select2 INTERSECT select3)
 
=>
 
select2 INTERSECT select3 UNION [ALL] select1
 
b)
 
select1 UNION ALL (select2 EXCEPT select3)
 
=>
 
select2 EXCEPT select3 UNION ALL select1

In this task it is assumed that query expressions does not contain EXCEPT ALL / INTERSECT ALL (these operations are not implemented yet in MariaDB).
Generated at Thu Feb 08 08:28:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.