Vladimir Sitnikov created CALCITE-7880:
------------------------------------------
Summary: Unqualified common column of JOIN USING has the left
column's type, so arithmetic on it can overflow
Key: CALCITE-7880
URL: https://issues.apache.org/jira/browse/CALCITE-7880
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vladimir Sitnikov
An unqualified reference to the common column of an inner {{JOIN ... USING}}
has the type of the left column, while {{SELECT *}} over the same join gives
that column the common type of both sides. When the left column has the
narrower type, arithmetic on the column overflows.
On main (6ff13eaccd):
{code:sql}
SELECT k + 1 AS x
FROM (VALUES (CAST(2147483647 AS INTEGER))) AS d(k)
JOIN (VALUES (CAST(2147483647 AS BIGINT))) AS f(k) USING (k)
{code}
returns
{noformat}
X=-2147483648
{noformat}
On 1.42.0 the same query returns {{X=2147483648}}.
Type of column {{K}} for the same join (FROM clause as above):
||Query||main (6ff13eaccd)||1.42.0||
|{{SELECT *}}|{{K BIGINT NOT NULL}}|{{K BIGINT NOT NULL}}|
|{{SELECT k}}|{{K INTEGER NOT NULL}}|{{K BIGINT NOT NULL}}|
Expected: {{SELECT k}} has the type {{SELECT *}} gives the column, {{BIGINT NOT
NULL}} in the example, and the query returns {{X=2147483648}}, as it does on
1.42.0. The SQL standard defines the common column of a USING join as
{{COALESCE(TA.C, TB.C)}}, whose type is the common type of the two columns.
h3. Possible cause
This is a guess from reading the code; the report above stands without it.
[CALCITE-2659] (commit aa90b89709, not in any release) changed
{{SqlValidatorImpl#expandExprFromJoin}} to expand the column into the left
column for INNER and LEFT joins and into the right column for RIGHT joins,
instead of {{COALESCE(left, right)}}. The reference then keeps that column's
own type. Casting the reference to the common type when the two column types
differ would keep the plan improvement of CALCITE-2659. RIGHT and NATURAL joins
take the same path; I did not run them.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
