I'm probably jumping the gun on this and maybe it's already known (or simply upcoming on the things to try list), but if you use an aggregate or window function in the lateral portion, and there is more than one row, and you reference an outside column in either the output rows or from clause, you get a segfault.

Works:
SELECT * FROM (VALUES (1)) JOIN LATERAL (select COUNT(*), column1);
SELECT * FROM (VALUES (1), (2)) JOIN LATERAL (select COUNT(*));

Segfaults:
SELECT * FROM (VALUES (1), (2)) JOIN LATERAL (select COUNT(*), column1);

Also as a bonus, I found another bug/oddity:

SELECT * FROM (VALUES (1)) JOIN LATERAL (select 1 LIMIT column1);
Parse error: no such column: column1

I tried to test this bit in Postgres, but couldn't get it to parse at all. It kept complaining of a syntax error at the semi-colon no matter what I tried.

With my whinging complete, thank you for working on this new feature. Looks like it could be quite handy in the future.

That's a good catch. Thanks for reporting it.

The issue is an adverse interaction between LATERAL and the new "VALUES-as-coroutine" optimization that was added in the previous release (item 5a in the release notes.

The current implementation of LATERAL is unable to interoperate with coroutines in subqueries to its left. All FROM clause elements to the left of the LATERAL must be actual tables, either tables in the database or materializations of subqueries or views. In the prototype LATERAL implementation, I work around this limitation by simply disabling the use of coroutines when LATERAL appears anywhere in the FROM clause.

However, in the VALUES-as-coroutine optimization that strategy won't work, because the coroutine that implements the VALUES clause is generated prior to the parser even reaching the LATERAL keyword.

So it appears that I'll need to rework the implementation of LATERAL so that it is able to interoperate with coroutines. That's easier said than done. It might well involve changes to the way coroutines are implemented, not just a change to the new LATERAL implementation.

Summary: I have your report. I will try to fix it. But doing so will take some time.

This is why the version control system has "branches" and why we use those branches for new features. :-)

As of the latest checkin on the branch, LATERAL subqueries are now able to referenced other subqueries to their left that are implemented as co-routines. This resolves the crash reported above. (The fix was easier than I thought it would be.)

I have not yet looked into the LIMIT problem, as that is more of a limitation than a bug. I'll consider that issue separately.

All issues reported above have now been addressed, including both concerns on (1) and the new find at (4).

The Big Remaining Question:

Should this LATERAL query enhancement be merged into trunk and added to the next release? Or is LATERAL just an extra complication that doesn't really have a practical application? Is support for LATERAL just about being buzz-word compliant, or is LATERAL actually useful for something?

My default action will be to leave the LATERAL join optimization to languish on the branch. If you thinking having LATERAL is important, then try to convince me otherwise. Your best approach would be do demonstrate a real-world problem that LATERAL solves that would be difficult to resolve without LATERAL.

Dear SQLiters,

I have no idea what LATERAL does, could grasp it on the first reading, but I am fascinated by Richard! He asks for usefulness of LATERAL upfront (I presume query can be written without it as most can without WITH but not without WITH RECURSIVE ), goes through the trouble of implementing it and then debugging in less than a day and after all that not putting into production !

Richard is curious and wants SQLite to be useful. Mental exercise is never a waste of time even if something does not go into production.

I am fascinated by all SQLite developers ! I am extremely thankful that SQLite exists. It makes my work so much easier.

Roman

In my personal opinion I'd like to see it added (always nice to have an extra tool in you back pocket for when you need it), but after going over my own code it's not as applicable as I would hope (I found one query that generates some statistics which could use LATERAL to be more easily understood).

Looking online it seems people mainly suggest using LATERAL (or OUTER/CROSS APPLY for SQL Server) for a couple common things.

1. Deduplicate calculations without needing to nest subqueries. If you have calculated values being used to calculate other values, you can calculate the intermediate values that are used more than once in a LATERAL join (or if needed, multiple consecutive LATERAL joins referencing values calculated in previous ones) and reference that. You could have the length/width/height of whatever in a table and have to calculate several related things that use volume.

2. Join a table but limit the number of rows produced per row of the left table. Could want to get users' top 3 movies, assuming there's a users table and a movie ratings table, so you use a LATERAL join to select using userid ordered by rating limit 3, to get user A's favourites, user B's, etc.. Certainly doable without LATERAL but uglier, trickier, and potentially significantly slower.

Also it gets used for table valued functions, but SQLite already lets you do stuff like JOIN json_each(table.column). Adding LATERAL would I guess just be extending the existing implicit LATERAL joins SQLite does.

Overall I want it, but I think I'd be lying if I said it was important to have. It's like RIGHT/FULL joins; they're nice but I just rarely (but not never!) find myself writing a query that benefits from having it. Perhaps the reasoning you had for adding RIGHT/FULL joins would apply here as well, assuming it wasn't a consortium member making a request.

If SQLite's LATERAL is compatible with PostgreSQL's, then that's a big win for the subset of people (like me) that use both PostgreSQL and SQLite, to have the same mental model and SQL for queries.

We have two implementations for interacting with data, using a legacy proprietary DB-like API, and a new PostgreSQL-based (thus SQL-based) one.
Long term, I'd like an SQLite-based 3rd impl (to work with local projects, when offline or for performance), and the closer SQLite's SQL is to PostgreSQL's, the better in my book.

That's admittedly perhaps a niche justification, but it matters to me at least. Thanks, --DD

PG accepts "LATERAL" before a table-valued function, though the PG documentation admits that this is just a "noise" keyword and has no effect, since the same table-valued function will do exactly the same thing without the LATERAL prefix. SQLite does not accept "LATERAL" before a table-valued function, since doing so would mean unnecessary complication in the parser and the noise keyword adds no value.

Other than that, I think the SQLite and PG implementations are compatible. Your help if verifying that assertion is appreciated.

After Googling LATERAL, I realized that I could have used this concept in a project I delivered just days ago. I required a complicated elapsed time value to be used in several expressions in one report.

The issue was, mainly, avoidance of writing that long expression several times. My solution was to create a succession of three views, each one supplying at least one complicated expression value needed in the next view. I then joined the views on rowid.

All this did was force Sqlite to create the long, messy query internally. Well, that's what computers are for.

I guess that in this case, I would have chosen LATERAL if it existed.