[
https://issues.apache.org/jira/browse/CALCITE-7306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18040966#comment-18040966
]
Mihai Budiu commented on CALCITE-7306:
--------------------------------------
I Calcite the type inferred for an aggregate is the same as the type of the
data that is being aggregated. So AVG of integers is an integer.
By using 1.0 you force the values to be interpreted as DECIMAL (with some
default scale).
Alternatively, you can cast AVG(CAST(exams.grade AS DECIMAL(10, 4)))
> Avg return wrong result
> -----------------------
>
> Key: CALCITE-7306
> URL: https://issues.apache.org/jira/browse/CALCITE-7306
> Project: Calcite
> Issue Type: Bug
> Reporter: weihua zhang
> Priority: Major
>
> https://github.com/apache/spark/blob/161ed3d18dc346d3ad970b7a5997e42ea05b5206/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-select.sql#L199
> {code:sql}
> with students(id, name, major, year_year) AS (VALUES
> (0, 'A', 'CS', 2022),
> (1, 'B', 'CS', 2022),
> (2, 'C', 'Math', 2022)),
> exams(sid, course, curriculum, grade, date_date) AS (VALUES
> (0, 'C1', 'CS', 4, 2020),
> (0, 'C2', 'CS', 3, 2021),
> (1, 'C1', 'CS', 2, 2020),
> (1, 'C2', 'CS', 1, 2021))
> SELECT students.name, exams.course
> FROM students, exams
> WHERE students.id = exams.sid
> AND (students.major = 'CS' OR students.major = 'Games Eng')
> AND exams.grade >= (
> SELECT avg(exams.grade) + 1
> FROM exams
> WHERE students.id = exams.sid
> OR (exams.curriculum = students.major
> AND students.year_year > exams.date_date));
> {code}
> right result:
> {code:java}
> name | course
> ------+--------
> A | C1
> (1 row)
> {code}
> calcite result:
> {code:java}
> NAME, COURSE
> A, C1
> A, C2
> {code}
> but when I run in Impala with Calcite 1.42.0-snapshot, can get right result
> too. Therefore, I suspect there is a bug in Calcite's executor implementation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
