[
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
