Lateral Derived Tables and Pyrrho v7

This post discusses a new feature in v7 of Pyrrho, namely a simple implementation of lateral derived tables, that does not require the LATERAL keyword. Lateral derived tables are specified in section 7.6 "Table Reference" of the SQL standard, but are well introduced in the MySQL 8.0 manual at https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html .

That page gives an example problem where the concept is useful: "Given a table of people in a sales force (where each row describes a member of the sales force), and a table of all sales (where each row describes a sale: salesperson, customer, amount, date), determine the size and customer of the largest sale for each salesperson. "

Now a naive solution might look like this:

create table SalesPerson(pid int primary key)

create table Sales(sid int primary key, spid int, cust int, amount int)

select * from SalesPerson,  (select cust, amount from Sales where spid = pid  order by amount desc fetch first 1 rows only)

According to the MySQL manual page, this is not permitted because "A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. " The page goes on to discuss a number of solutions which either are very complicated or require the LATERAL keyword.

Pyrrho v7 will automatically detect the need for a lateral derived table (or "lateral join") and will form the required join without using the LATERAL keyword. For example, the above naive solution is accepted and correctly computed by the current alpha build of Pyrrho at  https://github.com/MalcolmCrowe/ShareableDataStructures . The feature is not discussed in the Pyrrho manual, because no special syntax is required.

I'd welcome comments on this feature, and a reference in the SQL standard for the rule that MySQL gives. Write to me at malcolm.crowe@uws.ac.uk .