MySQL 8.0 implemented this.

Really useful, as many times I need this feature and I don't have it.

SQL Server has OUTER APPLY and CROSS APPLY.

Would be great if MariaDB implemented LATERAL.

More comments and more votes in https://jira.mariadb.org/browse/MDEV-6373

Any update regarding this implementation?

Every other database system has this, except MariaDB...

Thank you very much.

Is there an ETA on this ?

No. At the moment it's not planned for 10.6

I keep wishing for this...
It was requested in 2014 (MDEV-6373), currently marked as Critical.
6 years later, still not planned :/

I find myself lacking this functionality as well. I need it to optimize a currently very expensive query on the front page of our web app. I would absolutely love to come back to this StackOverflow question to announce that this feature is available as soon as it is. I see that implementation has been planned for 10.7, let's hope that promise is fulfilled.

igor serg marko Hoping to see this planned and implemented in 10.7, please!
Thank you very much & have a great day.

igor serg marko Hey guys! Is this still going on 10.7, or out of luck at this point?
Thank you very much!
Kind regards.

Likely out of luck. Depends on when 10.7 will be closed for new features.

serg
Thank you for your reply, and I really appreciate everyone's efforts with MariaDB.
BIG THANKS to your deliveries.

However, please consider that this feature has been 2 of the most voted requests tagged for 10.7 (two different JIRA tickets) for long time, and something where MariaDB stays behind MySQL, PostgreSQL and SQL Server.
And is a huge limitation in many situations which LATERAL JOIN would make very easy.

Have a very great day.

Bad news - postponed since 10.6

MDEV-19078 has 33 votes while MDEV-6373 has only 19 votes.

MDEV-19078 (with more votes) was removed the "Fix version" so it will no longer appear in the results sorted by Votes, for a specific milestone/fix version.

And meanwhile, MDEV-6373 was bumped to fix version 10.9.

Seriously... MariaDB is the only DB I know that still doesn't support LATERAL / OUTER APPLY.

igor serg marko Any updates on the plan for MDEV-19078 and MDEV-6373, please?
Thank you very much!

sorry, there's no immediate plan at the moment

Decided to migrate to MariaDB from mysql and this issue causes errors to most of my queries. Is there any plan on adding support for lateral derived tables this in the very near future or shall I abort migration?

Thank you

It's unlikely to happen in the "very near future". This is work in progress, indeed (or, to be more precise, we're implementing necessary prerequisites), but it'll take some time.

Thank you, serg.

This is really the only feature I miss a lot in MariaDB, and keeps being a daily inconvenience, not having it.

However, I'm thankful that at least work is being done in the pre-requisites.

Thank you very much everyone.

@Nuno - It seems like the initial example can be rewritten, in fewer keystrokes, as

SELECT t2.b FROM t1 JOIN t2 t1.a=t2.a

Do you have an example of LATERAL that cannot be rewritten as INNER JOIN?

If rewriting is always feasible, then might it be "easy" to have the Optimizer reformulate it – similar to what is done between LEFT JOIN, EXISTS, and IN(SELECT)?

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

@Nuno - Thanks. I'll research how LATERAL can be used to solve the "groupwise-max" and "Top-N per group" problems. There are many inefficient algorithms out there and some return extra rows when the max value (eg, total_replies) occurs more than once.

I have also optimized many queries using LATERAL JOIN in postgresql and I really miss it in mariadb.

Just to add some extra context, `LATERAL` is essentially a window function under the hood.

The transformation for the following `LATERAL` query. "Fetch the first 5 Artists, and for each Artist, fetch their first 3 Albums":

select art.*, alb.*

from (select * from artists limit 5) art

       cross join lateral (select *

                           from    albums

                           where  albums.artist_id = art.artist_id

                           limit 3) alb

Is:

SELECT art.*, alb.*

FROM (

  SELECT *, row_number() OVER (PARTITION BY artist_id ORDER BY album_id) as rn

  FROM albums

) alb

LEFT JOIN (

  SELECT *

  FROM artists

  LIMIT 5

) art ON alb.artist_id = art.artist_id

WHERE alb.rn <= 3;

With the results:

artist_id	name	album_id	title	artist_id

1	Artist 1	1	Album 1	1

1	Artist 1	2	Album 2	1

1	Artist 1	3	Album 3	1

2	Artist 2	4	Album 4	2

2	Artist 2	5	Album 5	2

2	Artist 2	6	Album 6	2

3	Artist 3	7	Album 7	3

3	Artist 3	8	Album 8	3

3	Artist 3	9	Album 9	3

In fact, in CockroachDB this is exactly how the transformation/rewriting is implemented:

$> explain select * from abc, lateral (select * from xyz where x = a limit 2);

                                           info

-------------------------------------------------------------------------------------------

  distribution: local

  vectorized: true

  • filter

  │ estimated row count: 1

  │ filter: row_num <= 2

  │

  └── • window

      │ estimated row count: 2

      │

      └── • hash join

          │ estimated row count: 2

          │ equality: (x) = (a)

          │

          ├── • scan

          │     estimated row count: 6 (100% of the table; stats collected 2 minutes ago)

          │     table: xyz@xyz_pkey

          │     spans: FULL SCAN

          │

          └── • scan

                estimated row count: 1 (100% of the table; stats collected 3 minutes ago)

                table: abc@abc_pkey

                spans: FULL SCAN