Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <9738581.1180280656436.JavaMail.jira@brutus>
Date: Sun, 27 May 2007 08:44:16 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2459) Ordering on a CASE-expression casues
 a NullPointerException when using a UNION
In-Reply-To: <540877.1174041669553.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


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

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 =3D 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 =3D t1.ref
order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END=20

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 UNI=
ON. 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 th=
e 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 cl=
ear
what "t2.value" and "t1.value" are supposed to apply to, as there is a clea=
r
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 da=
tabase
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 versio=
n 10.2.2
>            Reporter: Lars Gr=C3=A5mark
>         Assigned To: Bryan Pendleton
>
> When an order by clause involves a CASE-expression as seen below, a NullP=
ointerException 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 =3D 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 =3D 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);

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.