Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <945543842.1224028724292.JavaMail.jira@brutus>
Date: Tue, 14 Oct 2008 16:58:44 -0700 (PDT)
From: "Kathey Marsden (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Issue Comment Edited: (DERBY-3880) NPE on a query with
 having clause involving a join
In-Reply-To: <388414194.1221612464197.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-3880?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1263=
9647#action_12639647 ]=20

kmarsden edited comment on DERBY-3880 at 10/14/08 4:57 PM:
-----------------------------------------------------------------

I have a question about virtualColumnId's for ResultColumns versus their un=
derlying Expressions.  I am looking in the debugger in ProjectRestrictNode =
generateMinion where it maps the source columns. At line 1421 we have
=09=09int[] mapArray =3D resultColumns.mapSourceColumns();

The second time we hit this line we get a mapArray:[3, -1, 5, -1, -1, 2, -1=
].  It seems that it is mapArray[2] that is off.
The virtualColumnId of the the third entry of resultColumns is 3 and if I f=
orce mapArray[2] to be 3 the query completes successfully and correctly.  T=
he ResultColumn is an ##aggregate expression
ame: ##aggregate expression
tableName: T2
isNameGenerated: false
sourceTableName: T2
type: INTEGER
columnDescriptor: null
isGenerated: true
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER

The expression is a ColumnReference with sourceColumn virtualColumId 5
columnName: I2
tableNumber: 1
columnNumber: 3
replacesAggregate: false
tableName: T2
nestingLevel: 0
sourceLevel: 0
dataTypeServices: null

mapSourceColumns() gets the virtualColumnId using
=09VirtualColumnNode vcn =3D (VirtualColumnNode) resultColumn.getExpression=
();
        ....
        mapArray[index] =3D vcn.getSourceColumn().getVirtualColumnId();
so returns 5 in this case.

So my question is what is the relationship between the ResultColumn and the=
 source column in this case?  Is it likely that the correct thing to do for=
 an aggregate is return the virtualColumnId of the the ResultColumn for the=
 aggregate or was that just a coincidence in this case?


      was (Author: kmarsden):
    I have a question about virtualColumId's for ResultColumns versus their=
 underlying Expressions.  I am looking in the debugger in ProjectRestrictNo=
de generateMinion where it maps the source columns. At line 1421 we have
=09=09int[] mapArray =3D resultColumns.mapSourceColumns();

The second time we hit this line we get a mapArray:[3, -1, 5, -1, -1, 2, -1=
].  It seems that it is mapArray[2] that is off.
The virtualColumnId of the the third entry of resultColumns is 3 and if I f=
orce mapArray[2] to be 3 the query completes successfully and correctly.  T=
he ResultColumn is an ##aggregate expression
ame: ##aggregate expression
tableName: T2
isNameGenerated: false
sourceTableName: T2
type: INTEGER
columnDescriptor: null
isGenerated: true
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER

The expression is a ColumnReference with sourceColumn virtualColumId 5
columnName: I2
tableNumber: 1
columnNumber: 3
replacesAggregate: false
tableName: T2
nestingLevel: 0
sourceLevel: 0
dataTypeServices: null

mapSourceColumns() gets the virtualColumnId using
=09VirtualColumnNode vcn =3D (VirtualColumnNode) resultColumn.getExpression=
();

so returns 5 in this case.

So my question is what is the relationship between the ResultColumn and the=
 source column in this case?  Is it likely that the correct thing to do for=
 an aggregate is return the virtualColumnId of the the ResultColumn for the=
 aggregate or was that just a coincidence in this case?


 =20
> NPE on a query with having clause involving a join
> --------------------------------------------------
>
>                 Key: DERBY-3880
>                 URL: https://issues.apache.org/jira/browse/DERBY-3880
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>         Environment: Windows 2003 Server=20
>            Reporter: Venkateswaran Iyer
>            Priority: Minor
>
> A simple query involving a join and having clause causes a NPE. Any subse=
quent executions cause severe errors. It almost looks like the underlying c=
onnection was closed out.
> =3D=3D=3D=3D
> C:\apps\derby\db-derby-10.4.2.0-bin\db-derby-10.4.2.0-bin\bin>ij
> ij version 10.4
> ij> connect 'jdbc:derby://speed:1527/ClassicModels;user=3Dsa;password=3Ds=
a
> ';
> ij> create table t1(i int, c varchar(20));
> 0 rows inserted/updated/deleted
> ij> create table t2(i int, c2 varchar(20), i2 int);
> 0 rows inserted/updated/deleted
> ij> insert into t1 values(1, 'abc');
> 1 row inserted/updated/deleted
> ij> insert into t1 values(2, 'abc');
> 1 row inserted/updated/deleted
> ij> insert into t2 values(1, 'xyz', 10);
> 1 row inserted/updated/deleted
> ij> insert into t2 values(1, 'aaa', 20);
> 1 row inserted/updated/deleted
> ij> insert into t2 values(2, 'xxx', 30);
> 1 row inserted/updated/deleted
> ij> select t1.i, avg(t2.i2) from t1 inner join t2 on (t1.i =3D t2.i) grou=
p by t1.i
>  having avg(t2.i2) > 0;
> ERROR XJ001: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC: jav=
a.lang.
> NullPointerException=C2=B6=C2=B6XJ001.U

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