Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Currently MariaDB does not allow to use lateral derived tables in queries though it
uses builds them internally for when employing split optimization.
Consider the following query
SELECT t.b FROM t1, LATERAL (SELECT * FROM t2 WHERE t1.a=t2.a) AS t
|
Here the reference t1.a in the WHERE condition of derived table t refers to the field a
of the table t1 used in the same FROM list as t.
SQL standard allows lateral derived tables and MySQL 8.0 supports them.
In MySQL the table can depend only on other tables in the same SELECT. If record in dependent table changes it invalidate table.
My idea is use Item_cache to store external references values on which current content of the table was generated.
Also maintain table map of dependence (including OUTER_REF_TABLE_BIT) and put check outer values/invalidate of the tables as we get and approved value executing the step in JOIN_TAB array processing (all dependent tables defined and got values in the records )
In case of only outer dependence do not forget "join->outer_ref_cond->val_int()" in do_select()
Attachments
Issue Links
- is blocked by
-
MDEV-26831 New name resolution
- Stalled
- is duplicated by
-
MDEV-32657 Dependent query cannot recognize expr in more than 1 nesting level
- Closed
- is part of
-
MDEV-28906 MySQL 8.0 desired compatibility
- Open
-
MDEV-35388 Postgres compatible syntax
- Open
- relates to
-
MDEV-19337 CTEs not working with outer references
- Closed
-
MDEV-30744 WITH AS clause doesn't work with an outside column
- Closed
-
MDEV-33018 Support LEFT JOIN LATERAL
- Open
Any example where I need to do a subquery with an "ORDER BY" that needs to return 2 or more columns, used by other JOINs in the main query.
Example (just randomly from top of my mind):
SELECT
users.user_id
, users.user_name
, some_extra_data.whatever
, most_popular_post.post_id
, most_popular_post.title
, most_popular_post.time
, most_popular_post.community_id
, most_popular_post.community_name
FROM users
JOIN some_extra_data USING(user_id)
LATERAL (
SELECT
posts.post_id
, posts.title
, posts.time
, communities.community_id
, communities.community_name
FROM posts
JOIN communities USING(community_id)
WHERE posts.from=user_id
ORDER BY posts.total_replies DESC LIMIT 1
) AS most_popular_post
ORDER BY most_popular_post.time DESC
There are plenty of examples like these, and many times I'm forced to create multiple subqueries in the SELECT, for each field I need, or subqueries in subqueries in a JOIN, etc, just because LATERAL isn't available.
You might suggest the above could be converted by starting the query with posts/communities, instead of users, but please note that in this case, I want ALL users to appear, even if "most_popular_post" is NULL