Derby
  1. Derby
  2. DERBY-4395

Column nullability handling appears to be incorrect in ROLLUP-style GROUP BY clauses, affects use in subqueries

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      As described by Dag Wanvik here, using a ROLLUP-style GROUP BY query as a sub-query of
      a larger query causes the query execution logic to be confused about the nullability of the columns
      in the subquery:

      https://issues.apache.org/jira/browse/DERBY-3002?focusedCommentId=12749974&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-12749974

      Fundamentally, the ROLLUP feature can cause NULL values to be returned in non-NULL-able
      columns, which is a troubling behavior.

      I haven't cataloged the complete symptoms of this problem, but it is definitely
      true that in queries such as those described by Dag:

      select cast(x as varchar(2)),y,z from (select c,c2,sum from t group by rollup (c,c2)) t(x,y,z)

      the result set metadata for the top-level result set will show that the columns are
      not nullable, yet they contain NULL values.

      Perhaps it would be adequate to have the query compiler detect when a ROLLUP
      query is being performed, and force the nullability of the columns to be set, and
      then ensure that this nullability is propagated to the final result set columns.

        Activity

        Gavin made changes -
        Workflow jira [ 12478442 ] Default workflow, editable Closed status [ 12796942 ]
        Bryan Pendleton made changes -
        Description As described by Dag Wanvik here, using a ROLLUP-style GROUP BY query as a sub-query of
        a larger query causes the query execution logic to be confused about the nullability of the columns
        in the subquery:

        https://issues.apache.org/jira/browse/DERBY-3002?focusedCommentId=12749974&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-12749974

        Fundamentally, the ROLLUP feature can cause NULL values to be returned in non-NULL-able
        columns, which is a troubling behavior.

        I haven't cataloged the complete symptoms of this problem, but it is definitely
        true that in queries such as those described by Dag:


        select cast(x as varchar(2)),y,z from (select c,c2,sum(i) from t group by rollup (c,c2)) t(x,y,z)

        the result set metadata for the top-level result set will show that the columns are
        not nullable, yet they contain NULL values.

        Perhaps it would be adequate to have the query compiler detect when a ROLLUP
        query is being performed, and force the nullability of the columns to be set, and
        then ensure that this nullability is propagated to the final result set columns.
        Assignee Bryan Pendleton [ bryanpendleton ]
        Issue & fix info Repro attached [ 10424 ]
        Hide
        Bryan Pendleton added a comment -

        Added description; marked as unassigned, since I'm not actively working on this right now.

        Show
        Bryan Pendleton added a comment - Added description; marked as unassigned, since I'm not actively working on this right now.
        Mamta A. Satoor made changes -
        Labels derby_triage10_9
        Mamta A. Satoor made changes -
        Urgency Normal [ 10052 ]
        Hide
        Mamta A. Satoor added a comment -

        Bryan, when you get a chance, can you put little description of the issue. Thanks

        Show
        Mamta A. Satoor added a comment - Bryan, when you get a chance, can you put little description of the issue. Thanks
        Kathey Marsden made changes -
        Affects Version/s 10.5.1.1 [ 12313771 ]
        Bryan Pendleton made changes -
        Parent DERBY-3002 [ 12375845 ]
        Issue Type Sub-task [ 7 ] Bug [ 1 ]
        Mike Matrigali made changes -
        Field Original Value New Value
        Component/s SQL [ 11408 ]
        Bryan Pendleton created issue -

          People

          • Assignee:
            Unassigned
            Reporter:
            Bryan Pendleton
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development