Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add rule unfold derived table #50

Closed
jiangzhe opened this issue Feb 16, 2022 · 1 comment · Fixed by #56
Closed

Add rule unfold derived table #50

jiangzhe opened this issue Feb 16, 2022 · 1 comment · Fixed by #56
Labels
feature New feature or request

Comments

@jiangzhe
Copy link
Owner

This rule is to unfold flat derived table containing only simple projections.
For example:

SELECT * FROM (SELECT c0, c1 + 1 as cc FROM t1) x1 WHERE f(cc+1) > 0 

could be unfolded and translate to equivalent SQL like below:

SELECT c0, c1 + 1 as cc FROM t1 WHERE f(c1 + 1 + 1) > 0

The benefits are not only further optimizations like expression simplify, but also more tables involved in join reorder.
In some scenarios, users specify bushy join via derived table:

SELECT 1 FROM t1 JOIN t2 JOIN (SELECT * FROM t3 JOIN t4)

With this rule applied, we could reorder 4 tables instead of 3. Althrough SQL does not allow such syntax:

SELECT 1 FROM t1 JOIN t2 JOIN (t3 JOIN t4)

More advanced unfolding is left for future improvements, e.g. certain patterns of aggr+join involved in derived table could also be unfolded.

@jiangzhe jiangzhe added the feature New feature or request label Feb 16, 2022
@jiangzhe
Copy link
Owner Author

jiangzhe commented Feb 17, 2022

There is limitation on outer query to apply this rule. Suppose we have SQL:

SELECT tt.c1, tt.c2 FROM t1 LEFT JOIN (SELECT 1 as c1, c2 FROM t2 JOIN t3) tt

Replacing topmost tt.c1 expression with 1 as c1 is absolutely wrong.
This is because the semantics of LEFT JOIN can change output values in right table to null.

That means, if any expression in derived table output can not ensure such behavior, we have to double check the outer query semantics, to ensure derived table is not at either sides of FULL JOIN or right side of LEFT JOIN.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
feature New feature or request
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant