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