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 Thu, 31 May 2007 02:07:15 GMT

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

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

Hi Army, thank you very much for continuing the discussion. I don't think this is
too picky; I think there is a lot to learn here.

I didn't use 42877 as the error message because it required a column "name" as
an argument, and for
  ORDER BY CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END 
I'm not sure what to use as the "name". At the point that the error is detected,
all we have is an OrderByColumn instance with a pointer to a ConditionalNode
instance which represents the entire CASE expression, and there is nothing like
a column name anywhere close at hand.

I believe that with the current implementation, CASE expressions as a whole are
not allowed in the ORDER BY clause of a *UNION* select. CASE expressions seem
to work correctly for a non-UNION select. I believe that Derby's current behavior
here is too restrictive; I think that there are valid CASE expressions for a UNION
select that *ought* to work (such as the one you propose in your comment, which
uses only unqualified column references to valid columns in the UNION's target
list),  but the current implementation is lacking. I'm not sure
how hard this would be to fix. For the time being, as you suggest, issuing a
comprehensible error message which reflects reality accurately seems far
preferable to crashing with an NPE.

I am somewhat surprised at the results that you found for
   select i from tx1 union select j from tx2 order by i; 
I expected that would work, which is why I worded the message that way.
But I can see the logic of the "column names are present in the UNION only
if they match precisely in all UNION children" behavior.

You've pointed out several excellent additional test cases for this area, so I think
the next step for me is to add more test cases to the patch, and to work on
re-wording the error message to try to more accurately describe the current
behavior and its restrictions. Let me give that a go.


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