[jira] [Assigned] (CALCITE-7308) Incorrect Result (Missing Row) When Joining a CTE (Temporary Table) with a Persistent Table

["Mihai Budiu (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-7308?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mihai Budiu reassigned CALCITE-7308:
------------------------------------

    Assignee: Mihai Budiu

> 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
>            Assignee: Mihai Budiu
>            Priority: Major
>
> 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)