[
https://issues.apache.org/jira/browse/DERBY-2459?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12499408
]
Bryan Pendleton commented on DERBY-2459:
----------------------------------------
I've been staring at the SELECT statement for a while and found that I'm
wholly confused by what it's supposed to mean. Here it is again, from the
original description:
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
What is the ORDER BY supposed to apply to? I can see two possibilities:
1) It's supposed to order 1 or both of the intermediate SELECT statements. But
that doesn't make any sense; the order of intermediate results is both
irrelevant and undefined.
2) It's supposed to order the final results, those that come out of the UNION.
But
this is confusing to me, because by the time we get to the UNION, there
aren't
tables "t1" and "t2" anymore. Those tables have disappeared during the
underlying SELECT join processing and all that's left at the time of the
UNION
processing is the collecting together of the rows from the child result sets.
For the case expressions in the underlying SELECT statements, it's quite clear
what "t2.value" and "t1.value" are supposed to apply to, as there is a clear
definition of "t2" and "t1" in those clauses. But in the UNION part of the
statement,
what do we mean by "t2" and "t1"?
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?
> 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
> Assigned To: Bryan Pendleton
>
> 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.
