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

Dag H. Wanvik commented on DERBY-3002:
--------------------------------------

Playing with the latest patch, I notice something that puzzles me (totally contrived example :-) :

> describe t;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
C                   |VARCHAR  |NULL|NULL|2     |NULL      |4         |NO      
C2                  |VARCHAR  |NULL|NULL|2     |NULL      |4         |YES     
I                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     

with a few columns:

> select * from t
C |C2  |I          
-------------------
aa|NULL|NULL       
bb|NULL|NULL

Then I try this:

> select c,c2,sum(i) from t group by rollup (c,c2)

and I see:

C   |C2  |3          
---------------------
aa  |NULL|NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
aa  |NULL|NULL       
NULL|NULL|NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
bb  |NULL|NULL       
bb  |NULL|NULL  

which is I think is as expected. But then I tried:

ij> select cast(c as varchar(2)),c2,sum(i) from t group by rollup (c,c2)

but now I see:

1   |C2  |3          
---------------------
aa  |NULL|NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
aa  |NULL|NULL       
bb  |NULL|NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
bb  |NULL|NULL       
bb  |NULL|NULL       

Notice that the third row is now different (equal to row four). Is this correct?

If I embed this group by into a subquery:

ij> 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)

I see again the row with null, null, null:

1 |Y   |Z          
-------------------
aa|NULL|NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
aa|NULL|NULL       
N&|NULL|NULL       
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
bb|NULL|NULL       
bb|NULL|NULL       

but this time, the nullability of column 1 seems wrong (notice the
"N&" - ij will use two columns here (VARCHAR(2)) if it thinks column can't contain
a NULL which, of course, needs 4 columns to print). This could be
related to the problem Knut is working on under DERBY-4284, though.


> Add support for GROUP BY ROLLUP
> -------------------------------
>
>                 Key: DERBY-3002
>                 URL: https://issues.apache.org/jira/browse/DERBY-3002
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.4.1.3
>            Reporter: Bryan Pendleton
>            Assignee: Bryan Pendleton
>            Priority: Minor
>         Attachments: fixWhiteSpace.diff, IncludesASimpleTest.diff, passesRegressionTests.diff, prototypeChangeNoTests.diff, rewriteGroupByRS.diff, rollupNullability.diff, useLookahead.diff
>
>
> Provide an implementation of the ROLLUP form of multi-dimensional grouping according to the SQL standard.
> See http://wiki.apache.org/db-derby/OLAPRollupLists for some more detailed information about this aspect of the SQL standard.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.