asolimando commented on code in PR #4378:
URL: https://github.com/apache/calcite/pull/4378#discussion_r2156482854
##########
core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml:
##########
@@ -5007,6 +5007,30 @@ LogicalProject(ENAME=[$cor0.ENAME])
}), 0)])
LogicalProject(DEPTNO=[$7], ENAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase
name="testMultipleCorrelatedSubQueriesInSelectReferencingDifferentTablesInFrom">
Review Comment:
with the following DDL:
```CREATE TABLE emp (
empno INT,
ename VARCHAR(100),
deptno INT
);
CREATE TABLE dept (
deptno INT,
dname VARCHAR(100)
);
INSERT INTO dept VALUES (1, 'ACCOUNTING');
INSERT INTO dept VALUES (2, 'RESEARCH');
INSERT INTO emp VALUES (7369, 'ALLEN', 1);
INSERT INTO emp VALUES (7499, 'SMITH', 2);
```
your query fails with `Query Error: more than one row returned by a subquery
used as an expression` with Postgres 17 (tested on https://www.db-fiddle.com/).
I think the following query expresses what you wanted to do and it's valid
according to Postgres:
```
select
(select ename || ' from dept '
|| (
select dname
from dept
where deptno = emp.deptno)
from emp
where emp.empno = empnos.empno) as ename_from_dept
from (values (7369), (7499)) as empnos(empno)
order by 1;
```
@korlov42 could you please check if that one is working at runtime too?
I understand that we are aiming at accepting more valid queries, but we
might be uncovering some bugs that were hidden before, that's not a problem as
long as we don't cause regressions and we file tickets for the problems we find
along the way.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
