db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2459) Ordering on a CASE-expression casues a NullPointerException when using a UNION
Date Wed, 23 May 2007 21:38:16 GMT

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

Bryan Pendleton commented on DERBY-2459:
----------------------------------------

Here's a brief narrative description of how things end up going wrong:

1) We are in CursorNode.bindStatement.
2) We perform "pull up" processing (see the DERBY-1861 writeup for background info)
to pull up the ORDER BY column into the result set, since the ORDER BY clause
neither specifies a simple column reference to a column already in the result set,
nor specifies a result column by column number. So we add a new virtual column
to the UnionNode's resultColumnList, causing it to have 3 columns.
3) Then CursorNode.bindStatement calls resultSet.bindResultColumns, which
ends up calling SetOperatorNode.buildRCL. This method simply discards the
resultColumnList from the UnionNode and replaces it by the resultColumnList of
the UnionNode's left child. This processing is intended to handle the situation
in which we had a SELECT * as one of the UnionNode's children, and the * has
now been replaced by the table's actual column list.

But now the data structures are inconsistent: the OrderByColumn thinks that it
will be ResultColumn 3, but the UnionNode thinks that the ResultColumnList
has only 2 ResultColumns.

A few steps later, CursorNode.bindStatement ends up calling OrderByColumn.
bindOrderByColumn, and it stumbles over the inconsistent data structures,
computes a NULL value for the resultColumn field of the OrderByColumn (since there
is no 3rd column in the ResultColumnList anymore), and gets a NPE.

I'll have to think a bit more about how to fix this, but I wanted to get the
narrative description down to preserve it.


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


Mime
View raw message