suibianwanwank commented on PR #4332:
URL: https://github.com/apache/calcite/pull/4332#issuecomment-2848562688
I tried adding your first test in sub-query.iq, but I ran into some
difficulties.
The test was rewritten based on the original table as:
```
SELECT
e1.COMM,
EXISTS (
SELECT 1
FROM EMP e2
WHERE e2.COMM IS NULL OR e2.COMM > e1.COMM * 10
) AS exists_flag
FROM EMP e1;
```
Plan:
```
LogicalProject(COMM=[$6], EXISTS_FLAG=[IS NOT NULL($10)])
LogicalJoin(condition=[IS NOT DISTINCT FROM($8, $9)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[*($6, 10)])
LogicalTableScan(table=[[scott, EMP]])
LogicalProject($f8=[$0], $f1=[true])
LogicalAggregate(group=[{0}])
LogicalProject($f8=[$9])
LogicalJoin(condition=[OR(IS NULL($6), >($8, $9))],
joinType=[inner])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], COMM0=[CAST($6):DECIMAL(17,
2)])
LogicalTableScan(table=[[scott, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject($f8=[*($6, 10)])
LogicalTableScan(table=[[scott, EMP]])
```
In fact, I believe the plan is correct, but it returns incorrect results in
QuidemTest, so I suspect this might be caused by the Quidem test:
```
COMM | EXISTS_FLAG
--------+-------------
0.00 | true
1400.00 | false
300.00 | false
500.00 | false
| true
| true
| true
| true
| true
| true
| true
| true
| true
| true
```
Additionally, I attempted to use the RelToSqlConverter to convert the
RelNode to PGSQL and executed it in PostgreSQL, which returned the expected
results.
```
with emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) as (
values
(7369, 'SMITH', 'CLERK', 7902, date '1980-12-17', 800.00, null,
20),
(7499, 'ALLEN', 'SALESMAN', 7698, date '1981-02-20', 1600.00, 300.00,
30),
(7521, 'WARD', 'SALESMAN', 7698, date '1981-02-22', 1250.00, 500.00,
30),
(7566, 'JONES', 'MANAGER', 7839, date '1981-02-04', 2975.00, null,
20),
(7654, 'MARTIN', 'SALESMAN', 7698, date '1981-09-28', 1250.00, 1400.00,
30),
(7698, 'BLAKE', 'MANAGER', 7839, date '1981-01-05', 2850.00, null,
30),
(7782, 'CLARK', 'MANAGER', 7839, date '1981-06-09', 2450.00, null,
10),
(7788, 'SCOTT', 'ANALYST', 7566, date '1987-04-19', 3000.00, null,
20),
(7839, 'KING', 'PRESIDENT', null, date '1981-11-17', 5000.00, null,
10),
(7844, 'TURNER', 'SALESMAN', 7698, date '1981-09-08', 1500.00, 0.00,
30),
(7876, 'ADAMS', 'CLERK', 7788, date '1987-05-23', 1100.00, null,
20),
(7900, 'JAMES', 'CLERK', 7698, date '1981-12-03', 950.00, null,
30),
(7902, 'FORD', 'ANALYST', 7566, date '1981-12-03', 3000.00, null,
20),
(7934, 'MILLER', 'CLERK', 7782, date '1982-01-23', 1300.00, null,
10)
)
select t.comm, t5.f1 is not null as exists_flag
from (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, comm * 10 as f8
from emp
) as t
left join (
select t2.f8, true as f1
from (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, cast(comm as
decimal(17, 2)) as comm0
from emp
) as t0
inner join (
select comm * 10 as f8
from emp
group by comm * 10
) as t2
on t0.comm is null or t0.comm0 > t2.f8
group by t2.f8
) as t5
on t.f8 is not distinct from t5.f8;
COMM | EXISTS_FLAG
--------+-------------
0.00 | true
1400.00 | true
300.00 | true
500.00 | true
| true
| true
| true
| true
| true
| true
| true
| true
| true
| true
```
--
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]
