Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Lateral derived tables [CORE3435] #3797

Closed
firebird-issue-importer opened this issue Apr 8, 2011 · 21 comments
Closed

Lateral derived tables [CORE3435] #3797

firebird-issue-importer opened this issue Apr 8, 2011 · 21 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Apr 8, 2011

Submitted by: mauro russo (mauro)

Votes: 9

I would like to have the following SQL feature.

I would like to use a derived table, in a nested join, for which the definition
assume a eaning only according to the result of previous join.

For example :

select * from T1
join T2 on ....
join lateral (select A.col1 from A where A.col2 = T2.colX order by A.col2 rows 1)
...

With such a select, I can "attach" to my query result a column [A.col1] to each row according to the column values
of each row generated by the previous joins. Of course, in this case a new derived table has to be computed
for each row generated by the previous joins, but this way to write seems to be more easy and fast
respect using nested queries.

====== Test Details ======

NB: currently test contains only initial (trivial) examples. To be continued later.
Find issue with DT (but perhaps it is my mistake there). Sent sample to dimitr, 07.04.2020 21:18.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2011

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2011

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2011

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2011

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2011

Commented by: @hvlad

What about LATERAL joins ?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2011

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2011

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2011

Modified by: @dyemanov

Component: Engine [ 10000 ]

description: I would like to have the following SQL feature.

I would like to use a derived table, in a nested join, for which the definition
assume a eaning only according to the result of previous join.

For example :

select * from T1
join T2 on ....
join (select A.col1 from A where A.col2 = T2.colX order by A.col2 rows 1)
...

With such a select, I can "attach" to my query result a column [A.col1] to each row according to the column values
of each row generated by the previous joins. Of course, in this case a new derived table has to be computed
for each row generated by the previous joins, but this way to write seems to be more easy and fast
respect using nested queries.

=>

I would like to have the following SQL feature.

I would like to use a derived table, in a nested join, for which the definition
assume a eaning only according to the result of previous join.

For example :

select * from T1
join T2 on ....
join lateral (select A.col1 from A where A.col2 = T2.colX order by A.col2 rows 1)
...

With such a select, I can "attach" to my query result a column [A.col1] to each row according to the column values
of each row generated by the previous joins. Of course, in this case a new derived table has to be computed
for each row generated by the previous joins, but this way to write seems to be more easy and fast
respect using nested queries.

summary: derived dipendent tables => Lateral derived tables

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2011

Commented by: @dyemanov

Reopened because of the discovered support in the SQL standard.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2011

Modified by: @dyemanov

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Won't Fix [ 2 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 11, 2011

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 11, 2015

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 4, 2016

Commented by: @hvlad

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 4, 2016

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 4, 2016

Modified by: @dyemanov

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 22, 2019

Modified by: @dyemanov

Fix Version: 4.0 Beta 1 [ 10750 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 6, 2020

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 6, 2020

Modified by: @dyemanov

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done with caveats

Test Details: NB: currently test contains only initial (trivial) examples. To be continued later.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

Test Details: NB: currently test contains only initial (trivial) examples. To be continued later. => NB: currently test contains only initial (trivial) examples. To be continued later.
Find issue with DT (but perhaps it is my mistake there). Sent sample to dimitr, 07.04.2020 21:18.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants