[ https://issues.apache.org/jira/browse/CALCITE-4906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17459660#comment-17459660 ]
Aleksey Plekhanov commented on CALCITE-4906: -------------------------------------------- [~nobigo], thanks for the review! > 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 > Assignee: Aleksey Plekhanov > Priority: Major > Labels: pull-request-available > Fix For: 1.29.0 > > Time Spent: 50m > Remaining Estimate: 0h > > 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)