[
https://issues.apache.org/jira/browse/CALCITE-7880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-7880.
----------------------------------
Fix Version/s: 1.43.0
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/68f16139b5ca26a1519eae071ab217de3ae8c9df]
Thank you for the fix xgbtxy
> 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
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> 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)
