Convert "derived tables" to CTE

[snovik75]

Lets say we have this sql

SELECT a.id, b.id, c.id
FROM a 
left join 
    (b inner join c ON c.id = b.id) ON b.id = a.id 

The expression b inner join c ON c.id = b.id is an implicit derived table (subquery representation of Table with Join) but it can be made explicit via an CTE. eliminate_subqueries does not do it and I can understand why.

Question is whether that would be a relevant feature to add? If yes, I could try to volunteer here with some high level design guidance (after looking at the scope code... :) ).

The reason I am asking is that I have lots of cases with recursive from_item and that's a different AST design pattern from the current sqlglot flat FROM/JOIN one.

[snovik75]

Here is an example

SELECT a.a, b.b, c.c
FROM a
left join 
    (b inner join c ON c.id = b.id)
ON b.id = a.id

would be converted into

WITH bc_join AS (
        SELECT 
            b.id AS b_id, 
            b.b as b_b, 
            c.c AS c_c
        FROM b 
        INNER JOIN c ON c.id = b.id
    )
SELECT 
    a.a, 
    bc_join.b_b as b, 
    bc_Join.c_c as c
FROM a
LEFT JOIN bc_join ON bc_join.b_id = a.a

[tobymao]

no, but you can always make a custom transform for your use case