[
https://issues.apache.org/jira/browse/CALCITE-4906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17450081#comment-17450081
]
Aleksey Plekhanov commented on CALCITE-4906:
--------------------------------------------
[~julianhyde], I found the problem with return type inference of single value
aggregate. If both of these ways finally use this aggregate, then they both
will yield the incorrect result (but I'm not sure about it).
I've attached the pull request with the proposed fix, perhaps the problem is
more clear with this patch.
> Wrong result for scalar subquery (single value aggregation) from empty input
> ----------------------------------------------------------------------------
>
> Key: CALCITE-4906
> URL: https://issues.apache.org/jira/browse/CALCITE-4906
> Project: Calcite
> Issue Type: Bug
> Reporter: Aleksey Plekhanov
> Priority: Major
>
> Scalar subqueries from the empty input return non-nullable type and in some
> cases it leads to wrong results. For example:
> {noformat}
> SELECT (SELECT 1 FROM (SELECT NULL) WHERE 1 = 0)
> {noformat}
> Returns {{0}}, but expected {{NULL}} according to the SQL standard:
> {noformat}
> Let SS be a <scalar subquery>.
> Case:
> a) If the cardinality of SS is greater than 1 (one), then an exception
> condition is raised: cardinality violation.
> b) If the cardinality of SS is 0 (zero), then the value of the <scalar
> subquery> is the null value.
> c) Otherwise, let C be the column of <query expression> simply contained in
> SS. The value of SS is the value of C in the unique row of the result of the
> <scalar subquery>.
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
