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

["Aleksey Plekhanov (Jira)" <ji...@apache.org>]

Aleksey Plekhanov created CALCITE-4906:
------------------------------------------

             Summary: 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


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)