db-derby-dev mailing list archives

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

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

A B commented on DERBY-2459:
----------------------------

Thank you for the follow-up patch, Bryan.  Is there any reason you chose to use a new error
message instead of the existing LANG_QUALIFIED_COLUMN_NAME_NOT_ALLOWED (42877) error?  Is
this just for the sake of clarity?

Note that one of the test cases already returns 42877:

+ij> -- should fail, because qualified column references can't refer to UNIONs
+select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END
+from d2459_A1 t1 left outer join d2459_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 d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref
+order by t1.id;
+ERROR 42877: A qualified column name 'T1.ID' is not allowed in the ORDER BY clause.

which seems like an appropriate error message for the 42878 case, as well?  Of course, we'll
also get this error if no qualified names are present:

  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 id IS NOT NULL THEN id ELSE 2 END 

so maybe 42877 isn't entirely appropriate either.  Hmm....

I wonder if what we're trying to say here is that CASE statements as a whole are not allowed
in an ORDER BY clause?  I.e. is there *any* form of CASE statement in an ORDER that would
(and should) actually work?  Or should we be throwing a more generic message saying something
along the lines of "Invalid ORDER BY expression."

On a different note, I wonder if mentioning "the target list of the first SELECT in the UNION"
is too implementation-specific.  This seems to correlate to the fact that Derby uses the RCL
of the left child as the RCL of the UNION node.  But that's an implementation decision; from
a user perspective I think we're just talking about the RCL of the UNION node itself.  As
an example, take the following:

ij> select i from tx1 union select j from tx2 order by i;
ERROR 42X78: Column 'I' is not in the result of the query expression.

In this case "i" is in fact "an unqualified name referring to the target list of the first
SELECT in the UNION"--but the query still throws an error.  The reason is that the second
SELECT has a different name, which means the RCL of the UNION has a generated name "1".  So
if the user wants to do an order by, s/he must specify the target column of the *UNION*'s
RCL, which may or may not match the target list of the first SELECT in the UNION.
Sorry if all of this is being too picky; I'm just sort of dumping my thoughts here.  I hope
I'm not discouraging or otherwise overwhelming you with this feedback.  When it comes down
to it the error message that you have proposed is far better than an NPE, so if you just want
to commit the patch as it is, I wouldn't complain...

> 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