[CALCITE-4906] Wrong result for scalar subquery (single value aggregation) from empty input

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.29.0
• Labels: pull-request-available
• Assignee: Aleksey Plekhanov
• Reporter: Aleksey Plekhanov
• Created: 28/Nov/21 14:29
• Updated: 04/Jan/22 08:27
• Resolved: 04/Jan/22 08:27

Description

Scalar subqueries from the empty input return non-nullable type and in some cases it leads to wrong results. For example:

SELECT (SELECT 1 FROM (SELECT NULL) WHERE 1 = 0)

Returns 0, but expected NULL according to the SQL standard:

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>.

Issue Links

• links to: GitHub Pull Request #2619
• links to: Pull request