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] Updated: (DERBY-2459) Ordering on a CASE-expression casues a NullPointerException when using a UNION
Date Sat, 02 Jun 2007 22:43:15 GMT

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

Bryan Pendleton updated DERBY-2459:
-----------------------------------

    Attachment: rejectquery_v3.diff

Attached is rejectQuery_v3.diff:

1) I updated the message to try to make it more clear that
the error involves an implementation restriction, not necessarily
a syntax error on the part of the user. 

2) I added a number of additional test cases, to try to explore
more areas of what is and isn't supported, including several
test cases suggested by Army in previous comments, and
several more related ones that occurred to me as I was thinking.

My current feeling is that there is a definite implementation
restriction here: there are syntactically valid expressions that
which Derby does not currently support, due to the way that
the result set of the UNION node is managed.

But I also feel that issuing a clear error message is preferable
to failing with an NPE, so I think that this patch is an improvement
over the current behavior.


> 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
>            Assignee: Bryan Pendleton
>         Attachments: rejectquery.diff, rejectquery_v2.diff, rejectquery_v3.diff
>
>
> 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