[
https://issues.apache.org/jira/browse/CALCITE-7308?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18076090#comment-18076090
]
Mihai Budiu commented on CALCITE-7308:
--------------------------------------
I think the type is a red herring. It does not matter how type inference works
- you can declare types explicitly and create a table with the same contents
and you should see the same bug.
This is really about the semantics of CHAR(N) data types, and, in particular,
how comparisons are implemented when you compare two values with different
CHAR/VARCHAR types. Does comparison ignore trailing spaces? There's an implicit
cast there to bring the two values to the same type. Does the cast remove
trailing spaces?
> 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
> 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
> -------+----------+---------+--------+-------+-------+----------+------+------------+---------+--------+--------+
> WARD | SALESMAN | 1250.00 | 500.00 | 7521 | WARD | SALESMAN | 7698 |
> 1981-02-22 | 1250.00 | 500.00 | 30 |
> ALLEN | SALESMAN | 1600.00 | 300.00 | 7499 | ALLEN | SALESMAN | 7698 |
> 1981-02-20 | 1600.00 | 300.00 | 30 |
> (2 rows)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
