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 Tue, 29 May 2007 18:53:15 GMT

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

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

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


I tried it out against DB2 v8 and it failed:

  SQL0197N  A qualified column name is not allowed in the ORDER BY clause.
  SQLSTATE=42877

When I replaced the ORDER BY clause with "ORDER BY 2", it succeeded:

ID 2
-- -----------
b            1
a            4
c            5
d            8

  4 record(s) selected.

> My opinion at this point is that the only things that should be legal for an ORDER BY
clause on
> a UNION is either simple unqualified column references which refer to columns in the
leftmost
> chlid's result set, or column position numbers. 

Seems at a glance to agree with the above results, for what it's worth.  That said, I wonder
if it might be better to change the patch so that instead of throwing "LANG_ORDER_BY_COLUMN_NOT_FOUND",
we throw "LANG_QUALIFIED_COLUMN_NAME_NOT_ALLOWED"...Or would it be too difficult to catch
that specific scenario?  One thing that confuses me about the new test cases in your patch
is the following:

+ij> -- should fail, because the union's results can't be referenced this way
+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 CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END;
+ERROR 42X78: Column '3' is not in the result of the query expression.

It's not immediately clear to me where "Column '3'" is coming from here?  Is that a pulled-up
column?  If so, is a user going to be confused by this particular error message?

Also, the following query does currently work and continues to work with your patch, which
is good:

  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 

Do you think it would be worth it to add this to the test cases in rejectquery.diff, just
for sanity...?

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