[jira] Commented: (DERBY-3880) NPE on a query with having clause involving a join

["Kathey Marsden (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639647#action_12639647 ] 

Kathey Marsden commented on DERBY-3880:
---------------------------------------

I have a question about virtualColumId's for ResultColumns versus their underlying Expressions.  I am looking in the debugger in ProjectRestrictNode generateMinion where it maps the source columns. At line 1421 we have
		int[] mapArray = 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 force mapArray[2] to be 3 the query completes successfully and correctly.  The 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
	VirtualColumnNode vcn = (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?





> 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 
>            Reporter: Venkateswaran Iyer
>            Priority: Minor
>
> A simple query involving a join and having clause causes a NPE. Any subsequent executions cause severe errors. It almost looks like the underlying connection was closed out.
> ====
> 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=sa;password=sa
> ';
> 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 = t2.i) group by t1.i
>  having avg(t2.i2) > 0;
> ERROR XJ001: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC: java.lang.
> NullPointerException¶¶XJ001.U

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