Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <21463909.1180824195679.JavaMail.jira@brutus>
Date: Sat, 2 Jun 2007 15:43:15 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (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.atlassia=
n.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton updated DERBY-2459:
-----------------------------------

    Attachment: rejectquery_v3.diff

Attached is rejectQuery_v3.diff:

1) I updated the message to try to make it more clear that
the error involves an implementation restriction, not necessarily
a syntax error on the part of the user.=20

2) I added a number of additional test cases, to try to explore
more areas of what is and isn't supported, including several
test cases suggested by Army in previous comments, and
several more related ones that occurred to me as I was thinking.

My current feeling is that there is a definite implementation
restriction here: there are syntactically valid expressions that
which Derby does not currently support, due to the way that
the result set of the UNION node is managed.

But I also feel that issuing a clear error message is preferable
to failing with an NPE, so I think that this patch is an improvement
over the current behavior.


> 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
>            Assignee: Bryan Pendleton
>         Attachments: rejectquery.diff, rejectquery_v2.diff, rejectquery_v=
3.diff
>
>
> 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.