Re: [PR] [CALCITE-7009] AssertionError when converting query containing multiple correlated subqueries referencing different tables in FROM [calcite]

["asolimando (via GitHub)" <gi...@apache.org>]

asolimando commented on code in PR #4378:
URL: https://github.com/apache/calcite/pull/4378#discussion_r2099806159


##########
core/src/test/resources/sql/scalar.iq:
##########
@@ -301,4 +301,21 @@ from (values (1), (3)) t1(id);
 
 !ok
 
+# Several scalar sub-queries reference different tables in FROM list
+select
+    (select ename from emp where empno = empnos.empno) as emp_name,
+    (select dname from dept where deptno = deptnos.deptno) as dept_name
+  from (values (7369), (7499)) as empnos(empno), (values (10), (20)) as deptnos(deptno) order by 1, 2;
++----------+------------+
+| EMP_NAME | DEPT_NAME  |
++----------+------------+
+| ALLEN    | ACCOUNTING |
+| ALLEN    | RESEARCH   |
+| SMITH    | ACCOUNTING |
+| SMITH    | RESEARCH   |
++----------+------------+
+(4 rows)

Review Comment:
   ```
   CREATE TABLE emp (
     empno INT,
     ename VARCHAR(100)
   );
   
   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');
   INSERT INTO emp VALUES (7499, 'SMITH');
   
   select
     (select ename from emp where empno = empnos.empno) as emp_name,
     (select dname from dept where deptno = deptnos.deptno) as dept_name
   from (values (7369), (7499)) as empnos(empno), 
        (values (1), (2)) as deptnos(deptno)
   order by 1, 2;
   ```
   
   validated with [sqlfiddle](https://sqlfiddle.com/postgresql/online-compiler?id=406f65d5-6b88-4df5-a531-9d7dedc676f5) with PostgreSQL and it's giving the same result



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org