Commented by: Sean Leyne (seanleyne)

JOINs to derived/abstract tables is supported using the following either of the following approaches:

1 - a variation on your original query

SELECT ...
FROM T1
JOIN T2 ON ...
LEFT JOIN (SELECT FIRST 1 A.col1 FROM A WHERE A.col2 = T2.colX ORDER BY A.col2) T3 ON 1 = 1
WHERE
T3.col1 IS NOT NULL

OR

2 - the use of Common Table Expressions (CTE)

WITH T3 AS (
SELECT A.col1
FROM A
ORDER BY A.col2
ROWS 1
)
SELECT ...
FROM T1
JOIN T2 ON ...
JOIN T3 ON T3.col2 = T2.colX

Commented by: mauro russo (mauro)

Dear Sean,

I tried solution1 (with "on 1 = 1", exactly like you wrote) before to post the request,
but it did not work. I have got a message reporting T2.colX as an unknown column.

I am using Firebird 2.1

As a detail of your solution,
what the difference between using "left join ... where T3.col1 IS NOT NULL"
and "join" whitout a where clause?

Finally, I were using a solution similar to your solution2, but adapting the query to my case.
In fact I beleve your solution2 is not the same of solution1, because the only one row
obtained in T3 as preliminar computed derived table is not tied to theT1_join_T2 rows.

Commented by: @dyemanov

This request explicitly violates the SQL standard. Derived tables have to be independent from other contexts.

Commented by: @hvlad

What about LATERAL joins ?

Commented by: @dyemanov

Vlad is correct, the recent standard versions contain a description (although quite brief) of the requested feature. It declares the LATERAL keyword in front of the derived table definition and allows that derived table to reference priorly defined contexts of the same level. It can be used with INNER and LEFT joins only.

Commented by: @dyemanov

Reopened because of the discovered support in the SQL standard.

Commented by: mauro russo (mauro)

Dears,

really my beginning query used LEFT JOIN.

I did not report the keyword LEFT because I thought it was not a basic for derived tables in my case.
Neverthless, it did not work.

Well, I'll try again my query and I will post the full description of my tables and the full query text.

Thanks again for support.
Mauro.

Commented by: @livius2

All LATERAL joins can be changed to WITH RECURSIVE
But will be good to see LATERAL JOIN supported by engine which is more natural/simpler to use

Commented by: @hvlad

I also consider LATERAL DERIVED TABLE as valuable feature and good to have it in Firebird

Commented by: @dyemanov

Committed into v4 Beta 2. Error reporting is still pending some improvements, but the feature is basically ready for public testing.