weihua zhang created CALCITE-7308:
-------------------------------------
Summary: Incorrect Result (Missing Row) When Joining a CTE
(Temporary Table) with a Persistent Table
Key: CALCITE-7308
URL: https://issues.apache.org/jira/browse/CALCITE-7308
Project: Calcite
Issue Type: Bug
Reporter: weihua zhang
When executing a JOIN operation between a CTE (Common Table Expression, acting
as a temporary table) and a persistent table, Calcite returns incorrect results
with one row missing. The expected result should include all matching rows from
both tables, but the actual output omits one valid matching row.
{code:sql}
!use scott
WITH bonus1(ENAME, JOB, SAL, COMM) AS (
VALUES
('ALLEN', 'SALESMAN', 1600.00, 300.00),
('WARD', 'SALESMAN', 1250.00, 500.00)
)
SELECT * FROM bonus1
INNER JOIN emp -- `emp` is a persistent table (predefined in the database)
ON bonus1.ename = emp.ename;
{code}
{code:java}
ENAME, JOB, SAL, COMM, EMPNO, ENAME0, JOB0, MGR, HIREDATE, SAL0, COMM0, DEPTNO
ALLEN, SALESMAN, 1600.00, 300.00, 7499, ALLEN, SALESMAN, 7698, 1981-02-20,
1600.00, 300.00, 30
!ok
{code}
but right result is:
{code:java}
ename | job | sal | comm | empno | ename | job | mgr |
hiredate | sal | comm | deptno | dname | deptno | loc
-------+----------+---------+--------+-------+-------+----------+------+------------+---------+--------+--------+-------+--------+---------
WARD | SALESMAN | 1250.00 | 500.00 | 7521 | WARD | SALESMAN | 7698 |
1981-02-22 | 1250.00 | 500.00 | 30 | SALES | 30 | CHICAGO
ALLEN | SALESMAN | 1600.00 | 300.00 | 7499 | ALLEN | SALESMAN | 7698 |
1981-02-20 | 1600.00 | 300.00 | 30 | SALES | 30 | CHICAGO
(2 rows)
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
