Correlated scalar subqueries Column Resolution about sqllineage HOT 3 OPEN

This is yet another moment when I feel amazed that it's allowed to write SQL like this.

To summarize, c2, c3 and c4 can come from src_tbl1 and src_tbl2

select
    c1,
    (select c2 || c3 || c4 from src_tbl1) as c2
------------^^^
from
    src_tbl2
;

Again, tried a few open source SQL database:

When src_tbl1 returns multiple rows, this SQL won't be executable. Hive/Spark/Trino disallow this syntax at parsing phase. MySQL/Postgres postpone throwing exception until execution and would allow if the subquery returns 1 row at execution time.

Generally speaking, I think it's a very narrow use case. And it requires a lot of change to sqllineage (we might need to introduce the concept of lexical scope, like Python doing variable lookup from local -> enclosed -> global -> built-in). That's error-prune and not adding too much value.

from sqllineage.

Our SQL does not have this kind of syntax. I just found this syntax when looking at the underlying sqlfluff, so I brought it up. Can be processed or not.

from sqllineage.

OK. We'll keep it open and take it as low priority then.

from sqllineage.