Support Lateral derived tables

[shenli]

Feature Request

Is your feature request related to a problem? Please describe:

Lateral join is a useful feature introduced in MySQL 8. PostgreSQL also supports it.

Describe the feature you'd like:

See details: https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html
It is also mentioned in the MySQL 8 compatibility issue: #7968

Describe alternatives you've considered:

There are some inefficient ways to simulate: https://stackoverflow.com/questions/50563043/how-can-you-simulate-a-lateral-join-in-mysql-v8-to-execute-a-subquery-or-join-fo

Teachability, Documentation, Adoption, Migration Strategy:

[shawn0915]

add memo, request from asktug, https://asktug.com/t/topic/1018996

[kissmydb]

https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL

[synerp]

Without this feature you are no longer compatible with Prisma see https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview

[Mini256]

feature request from drizzle orm: drizzle-team/drizzle-orm#3623

[wau002]

Without this feature you are no longer compatible with Prisma see https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview

This maybe fixed in https://github.com/tidbcloud/prisma-adapter/releases/tag/v6.12.0 as it now supports relation Joins