[
https://issues.apache.org/jira/browse/CALCITE-7308?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18075952#comment-18075952
]
zzwqqq commented on CALCITE-7308:
---------------------------------
I think this may be related to *how the row type of the `VALUES` clause is
inferred.*
For this part:
{code:java}
VALUES ('ALLEN', 'SALESMAN', 1600.00, 300.00),
('WARD', 'SALESMAN', 1250.00, 500.00)
{code}
Here is the plan I get with the default conformance:
{code:java}
EnumerableCalc(expr#0..7=[{inputs}], proj#0..3=[{exprs}], EMPNO=[$t5],
EMP_ENAME=[$t6], EMP_SAL=[$t7])
EnumerableHashJoin(condition=[=($4, $6)], joinType=[inner])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t0):VARCHAR(10) NOT
NULL], proj#0..4=[{exprs}])
EnumerableValues(tuples=[[{ 'ALLEN', 'SALESMAN', 1600.00, 300.00 }, {
'WARD ', 'SALESMAN', 1250.00, 500.00 }]])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
{code}
With the default conformance, *the common type becomes CHAR(5), so WARD is
padded to 'WARD '*.
I also tried SqlConformanceEnum.MYSQL_5, where
*shouldConvertRaggedUnionTypesToVarying*() returns true. With that, the values
are not padded:
{code:java}
EnumerableCalc(expr#0..7=[{inputs}], proj#0..3=[{exprs}], EMPNO=[$t5],
EMP_ENAME=[$t6], EMP_SAL=[$t7])
EnumerableHashJoin(condition=[=($4, $6)], joinType=[inner])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t0):VARCHAR(10) NOT
NULL], proj#0..4=[{exprs}])
EnumerableValues(tuples=[[{ 'ALLEN', 'SALESMAN', 1600.00, 300.00 }, {
'WARD', 'SALESMAN', 1250.00, 500.00 }]])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
{code}
In that case the query returns both rows. So the missing row seems to come from
default conformance choosing *CHAR(5) instead of VARCHAR(5)* for the ragged
string literals in VALUES.
> 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)
