db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2459) Ordering on a CASE-expression casues a NullPointerException when using a UNION
Date Sun, 27 May 2007 15:44:16 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12499408
] 

Bryan Pendleton commented on DERBY-2459:
----------------------------------------

I've been staring at the SELECT statement for a while and found that I'm
wholly confused by what it's supposed to mean. Here it is again, from the
original description:

   select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
                from A1 t1 left outer join B1 t2 ON t2.id = t1.ref
union all
   select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
               from A2 t1 left outer join B2 t2 ON t2.id = t1.ref
order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END 

What is the ORDER BY supposed to apply to? I can see two possibilities:
1) It's supposed to order 1 or both of the intermediate SELECT statements. But
    that doesn't make any sense; the order of intermediate results is both
    irrelevant and undefined.
2) It's supposed to order the final results, those that come out of the UNION. But
    this is confusing to me, because by the time we get to the UNION, there aren't
    tables "t1" and "t2" anymore. Those tables have disappeared during the
    underlying SELECT join processing and all that's left at the time of the UNION
   processing is the collecting together of the rows from the child result sets.

For the case expressions in the underlying SELECT statements, it's quite clear
what "t2.value" and "t1.value" are supposed to apply to, as there is a clear
definition of "t2" and "t1" in those clauses. But in the UNION part of the statement,
what do we mean by "t2" and "t1"?

Is this statement supposed to be legal? If so, how does it work on other database
systems? Does anyone have access to another DBMS implementation to test
how this statement behaves there?


> Ordering on a CASE-expression casues a NullPointerException when using a UNION
> ------------------------------------------------------------------------------
>
>                 Key: DERBY-2459
>                 URL: https://issues.apache.org/jira/browse/DERBY-2459
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.3.1, 10.1.3.2, 10.2.1.6, 10.2.2.0, 10.2.2.1, 10.3.0.0
>         Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.2
>            Reporter: Lars Gråmark
>         Assigned To: Bryan Pendleton
>
> When an order by clause involves a CASE-expression as seen below, a NullPointerException
is thrown. The error only occurs when two select statements are combined in a union (or union
all).
> select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
> from A1 t1
> left outer join B1 t2 ON t2.id = t1.ref
> union all
> select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
> from A2 t1
> left outer join B2 t2 ON t2.id = t1.ref
> order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
> --Use the following statement to reproduce the problem:
> create table A1
> (
>    id char(1)
>   ,value int
>   ,ref char(1)
> );
> create table A2
> (
>    id char(1)
>   ,value int
>   ,ref char(1)
> );
> create table B1
> (
>    id char(1)
>   ,value int
> );
> create table B2
> (
>    id char(1)
>   ,value int
> );
> insert into A1 (id, value, ref) values ('a', 12, 'e');
> insert into A1 (id, value, ref) values ('b', 1, null);
> insert into A2 (id, value, ref) values ('c', 3, 'g');
> insert into A2 (id, value, ref) values ('d', 8, null);
> insert into B1 (id, value) values ('e', 4);
> insert into B1 (id, value) values ('f', 2);
> insert into B2 (id, value) values ('g', 5);

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


Mime
View raw message