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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.5.1.1
• Component/s: SQL
• Labels: derby_triage10_9
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Bryan Pendleton
• Created: 01/Oct/09 23:22
• Updated: 25/Feb/12 16:54

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.