db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (Updated) (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-4395) Column nullability handling appears to be incorrect in ROLLUP-style GROUP BY clauses, affects use in subqueries
Date Sat, 25 Feb 2012 16:55:48 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4395?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Bryan Pendleton updated DERBY-4395:
-----------------------------------

         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.

    Issue & fix info: Repro attached
            Assignee:     (was: Bryan Pendleton)

Added description; marked as unassigned, since I'm not actively working on this right now.
                
> Column nullability handling appears to be incorrect in ROLLUP-style GROUP BY clauses,
affects use in subqueries
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4395
>                 URL: https://issues.apache.org/jira/browse/DERBY-4395
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.1.1
>            Reporter: Bryan Pendleton
>              Labels: derby_triage10_9
>
> 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.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message