[MCOL-4513] Investigate the original reasons why ColumnStore is flagging "circular joins" as an error, and provide guidance as to how to stop it from happening. Created: 2021-01-22  Updated: 2021-04-21  Resolved: 2021-04-21

Status: Closed
Project: MariaDB ColumnStore
Component/s: ?
Affects Version/s: None
Fix Version/s: 5.6.1

Type: Task Priority: Major
Reporter: Gregory Dorman (Inactive) Assignee: Denis Khalikov
Resolution: Done Votes: 0
Labels: None

Issue Links:
PartOf
is part of MCOL-1205 support queries with circular INNER j... Closed
Sprint: 2021-6, 2021-7

 Description   

SELECT * FROM a,b,c WHERE a.fk=b.PK and b.FK=c.PK and a.FOO = c.FOO

For historical reasons (nolonger known) ColumnStore is unable to do what every other relational databases do - break the loop in the join graph, and treat one of cross-table clauses as a filter as opposed to join condition (we are talking about b.FK=c.PK vs a.FOO=c.FOO - one of them has to be dropped from the join graph and become a post join filter).

This task is to figure out why it has not been done (if possible), and figure out how to solve it in the safest manner.



 Comments   
Comment by Denis Khalikov [ 2021-04-13 ]

Currently CS use following steps to apply join for tables:
1. Staring from the largest table (largest by rows number);
2. join:
2.1. inner join:
`joinToLargeTable` walk down starting from the root (largest table) using basic DFS and applying join from the bottom up to "parent" node. So this is why CS has check for spanning tree.
2..2 left, right. outer join
`joinTablesInOrder` sorts joins in order `join order` then apply join.

How to add support of circular joins.
1. Walk on graph, find all cycles.
2. Transform edges which makes cycle to filter (need a rule to decide which edge to transform to filter which not), probably we can use something like
` addJoinFilte` to add filter, but not sure currently, need more expertise in this part.

Generated at Thu Feb 08 02:50:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.