[
https://issues.apache.org/jira/browse/CALCITE-7306?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang resolved CALCITE-7306.
-----------------------------------
Resolution: Duplicate
> 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)
