You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Venkateswaran Iyer (JIRA)" <ji...@apache.org> on 2008/09/17 02:47:44 UTC
[jira] Created: (DERBY-3880) NPE on a query with having clause
involving a join
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.
Re: [jira] Resolved: (DERBY-3880) NPE on a query with having clause involving a join
Posted by Mamta Satoor <ms...@gmail.com>.
Now, I feel extremely guilty about not extending the same offer of
ownership change when Army came up with the fix for DERBY-3872.
Mamta
On Thu, Nov 6, 2008 at 1:56 PM, Army <qo...@gmail.com> wrote:
> Myrna van Lunteren wrote:
>>>
>>> Giving Army the point since he came up with the fix.
>>
>> I think you should give yourself 1/2 point for the testing and pushing
>> to completion...
>
> +1!
>
> In truth I think you could just assign it to yourself, as all I did was
> suggest a code change that demonstrated the underlying problem--and then I
> stopped there. It was you (Kathey) who did due diligence to verify that the
> tests passed and that the change actually constitutes a valid fix. Many
> thanks for your persistence/follow-through in that regard...
>
> Army
>
>
Re: [jira] Resolved: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by Army <qo...@gmail.com>.
Myrna van Lunteren wrote:
>> Giving Army the point since he came up with the fix.
>
> I think you should give yourself 1/2 point for the testing and pushing
> to completion...
+1!
In truth I think you could just assign it to yourself, as all I did was
suggest a code change that demonstrated the underlying problem--and then
I stopped there. It was you (Kathey) who did due diligence to verify
that the tests passed and that the change actually constitutes a valid
fix. Many thanks for your persistence/follow-through in that regard...
Army
Re: [jira] Resolved: (DERBY-3880) NPE on a query with having clause involving a join
Posted by Myrna van Lunteren <m....@gmail.com>.
> Giving Army the point since he came up with the fix.
I think you should give yourself 1/2 point for the testing and pushing
to completion...
:-)
Myrna
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642202#action_12642202 ]
A B commented on DERBY-3880:
----------------------------
Thanks for your work on this, Kathey.
I spent some time looking at this, and there definitely seems to be something amiss with the virtual column ids for the aggregates, but I wasn't able to figure out what it is that's off.
You may want to try running two queries that are as similar as possible, where one passes and the other fails, and then try to figure out what it is that's different between the two compilation paths. The ones I've been using are:
-- Passes.
select t1.i, avg(t2.i2)
from t1 inner join t2 on (t1.i = t2.i)
group by t1.i having avg(t2.i) > 0;
-- Fails.
select t1.i, avg(t2.i)
from t1 inner join t2 on (t1.i = t2.i)
group by t1.i having avg(t2.i) > 0;
The only difference between the two is the column that is referenced in the avg for the SELECT (t2.i2 vs t2.i). That said, I just now also noticed the following:
-- Passes.
select t1.i, avg(t2.i2)
from t1 inner join t2 on (t1.i = t2.i)
group by t1.i having avg(t2.i) > 0;
-- Passes.
select t1.i, avg(t2.i2)
from t1 inner join t2 on (t1.i = t2.i)
group by t1.i having max(t2.i) > 0;
-- Fails.
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;
-- Fails.
select t1.i, avg(t2.i2)
from t1 inner join t2 on (t1.i = t2.i)
group by t1.i having min(t2.i2) > 0;
>From these tiny examples, it *seems* that if the aggregate in the SELECT list references the same column as the aggregate in the HAVING clause, then the query fails; if they reference *different* columns, the query passes.
That might be something to investigate further? Without having looked at all into it (I only just now realized the apparent correlation), I think the "SubstituteExpressionVisitor" that is used by GroupByNode relies in some way on "equivalent" expressions, and then it plugs VirtualColumnNodes into the tree based on that equivalency. I wonder if the fact that the SELECT clause and the HAVING clause are have "equivalent" column references is somehow messing the virtual column ids up somewhere...? It's pure speculation, but if you're looking for options, that might be one to pursue...
> 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
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644393#action_12644393 ]
Kathey Marsden commented on DERBY-3880:
---------------------------------------
Thanks Army for the great explanation and patch proposal. I'll give it a try.
> 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
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12641062#action_12641062 ]
Kathey Marsden commented on DERBY-3880:
---------------------------------------
Thanks Mamta for looking at this. I think the reason for the difference is that sourceRow has only 3 columns yet projectMapping has values [3, -1, 5, -1, -1, 2, -1], the third entry (5) does not refer to an actual column so when we get to
result.setColumn(index + 1, sourceRow.getColumn(projectMapping[index]));
We call ValueRow.getColumn() which is defined as:
public DataValueDescriptor getColumn (int position) {
if (position <= column.length)
return column[position-1];
else
return (DataValueDescriptor)null;
}
and return null because position is greater than column.length.
I found that projectMapping is originally generated in projectRestrictNode.generateMinion() when we call
int[] mapArray = resultColumns.mapSourceColumns();
ResultColumnList. mapSourceColumns for the column in question executes this code:
else if (resultColumn.getExpression() instanceof ColumnReference)
{
ColumnReference cr = (ColumnReference) resultColumn.getExpression();
.....
// Virtual column #s are 1-based
mapArray[index] = cr.getSource().getVirtualColumnId();
}
Where the resultColumn looks like:
ResultColumn (id=72)
exposedName "##aggregate expression"
virtualColumnId 3
expression ColumnReference (id=74)
columnName "I2"
source ResultColumn (id=76)
exposedName "I2"
virtualColumnId 5
expression VirtualColumnNode (id=94)
sourceColumn ResultColumn (id=100)
exposedName "I2"
virtualColumnId 2
expression VirtualColumnNode (id=102)
sourceColumn ResultColumn (id=214)
expression VirtualColumnNode (id=227)
virtualColumnId 2
sourceColumn ResultColumn (id=239)
exposedName "I2"
expression BaseColumnNode (id=251)
columnName "I2"
virtualColumnId 2
The virtualColumnId of the ColumnReference under the ##aggregate expression is *5*, yet there are more deeply nested VirtualColumnNode's with a virtualColumnId of 2 which I think is correct. I think I need to better understand mapSourceColumns() and the meaning of this deeply nested ResultColumn structure to understand what's right here.
Kathey
> 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
> Attachments: querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642243#action_12642243 ]
Bryan Pendleton commented on DERBY-3880:
----------------------------------------
Army's observation about the SubstituteExpressionVisitor made me remember
the discussion in DERBY-3094. It may not be directly related, but there is some
detailed discussion in the comments to that issue regarding various paths
through the code in question, which might be useful. So I thought I'd mention it.
Plus, although I have no reason to think this would be true, it might be interesting
to try backing out the changes for DERBY-3094 and see if that had any affect
on either the repro script or on the various test cases that Army mentions.
Hopefully this is just a red herring, and we don't have to drag all the complexity
of DERBY-3094 into this issue but hopefully it's also helpful for people working
on this issue to be aware of that history.
> 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
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12640574#action_12640574 ]
Mamta A. Satoor commented on DERBY-3880:
----------------------------------------
Kathey, thanks for providing the working version of the query (ie the one without the explicit inner join clause). It gives something to compare the failing query against.
While trying to trace through the 2 queries, I found following discripency.
Both successful query and the NPE query fo through the following code path
Thread [main] (Suspended)
ProjectRestrictResultSet.doProjection(ExecRow) line: 514
ProjectRestrictResultSet.getNextRowCore() line: 291
GroupedAggregateResultSet.getRowFromResultSet() line: 580
GroupedAggregateResultSet.getNextRowFromRS() line: 566
GroupedAggregateResultSet.loadSorter() line: 306
GroupedAggregateResultSet.openCore() line: 180
ProjectRestrictResultSet.openCore() line: 168
ProjectRestrictResultSet(BasicNoPutResultSetImpl).open() line: 245
GenericPreparedStatement.execute(Activation, boolean, long) line: 384
EmbedStatement40(EmbedStatement).executeStatement(Activation, boolean, boolean) line: 1235
EmbedStatement40(EmbedStatement).execute(String, boolean, boolean, int, int[], String[]) line: 625
EmbedStatement40(EmbedStatement).execute(String) line: 555
ij.executeImmediate(String) line: 329
Main.go(LocalizedInput, LocalizedOutput, Properties) line: 215
utilMain.runScriptGuts() line: 350
Main.main(String[]) line: 73
utilMain.go(LocalizedInput[], LocalizedOutput, Properties) line: 248
Main.mainCore(String[], Main) line: 181
Main.main(String[]) line: 73
ij.main(String[]) line: 59
But if you examin row that results after the projection in ProjectRestrictResultSet, you will notice that the successful query has following row
result ValueRow (id=212)
{ 1, NULL, 10, NULL, NULL, 10, NULL }
whereas NPE query has following row
result ValueRow (id=251)
{ 1, NULL, null, NULL, NULL, 10, NULL }
As you can see, the 3rd column has value of null in NPE query whereas the successful query has value 10. Will debug more to see what causes this but thought will post this piece of information.
> 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
> Attachments: querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12634275#action_12634275 ]
Kathey Marsden commented on DERBY-3880:
---------------------------------------
If I use an implicit join:
select t1.i, avg(t2.i2) from t1, t2 where ( t1.i = t2.i) group by t1.i
having avg(t2.i2) > 0;
The query runs. Can this be used as a workaround?
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644992#action_12644992 ]
Kathey Marsden commented on DERBY-3880:
---------------------------------------
Army's patch passes regression tests. I'll add a test and commit, then backport to 10.4 and 10.3. Thanks Army!
> 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
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Resolved: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden resolved DERBY-3880.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.5.0.0
10.4.2.1
10.3.3.1
Assignee: A B
The fix has been committed to trunk, 10.4 and 10.3. Giving Army the point since he came up with the fix.
> 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
> Assignee: A B
> Priority: Minor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.0.0
>
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Updated: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3880:
----------------------------------
Derby Categories: [High Value Fix]
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12634204#action_12634204 ]
Kathey Marsden commented on DERBY-3880:
---------------------------------------
This is a regression caused by DERBY-681 (svn revision 516454)
> 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.
[jira] Issue Comment Edited: (DERBY-3880) NPE on a query with
having clause involving a join
Posted by "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 ]
kmarsden edited comment on DERBY-3880 at 10/14/08 4:57 PM:
-----------------------------------------------------------------
I have a question about virtualColumnId'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();
....
mapArray[index] = 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 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646605#action_12646605 ]
Bryan Pendleton commented on DERBY-3880:
----------------------------------------
Hi Army, Kathey, sorry it took me a while to read through Army's excellent description of 30-Oct
I have a couple questions that came to mind while I was studying Army's notes:
1) Army says "so the JoinNode's bind-time RCL is: { T1.i, T1.c, T2.i, T2.c2, T2.i2 }" but I don't
see how that is, if the query is
select t1.i, avg(t2.i)
from t1 inner join t2 on (t1.i = t2.i)
group by t1.i having avg(t2.i) > 0;
Is it possible that the query Army was analyzing was actually "on (t1.c = t2.c2)" ? Otherwise,
I'm having trouble seeing why T1.c and T2.c2 are in the join node's RCL.
2) While I was reading Army's writeup, I anticipated that I was going to see a fix which
involved passing the HAVING clause expressions to fromList.flattenFromTables() so that
it could properly maintain the ColumnReference data in the HAVING clause during flattening.
But it seems like the essence of Army's fix is to observe that we don't need to maintain
the ColumnReference instances inside of AggregateNode instances because we can
always go back at the end of optimization and re-construct the ColumnReference info.
This seems very nice, because I've seen numerous problems over the past several
years which involved situations where we lost track of the ColumnReference's table
and column pointers during the compilation and optimization processing. That code
overall seems very tricky and trouble-prone. I could track down the actual JIRA id's
if you wish; they involved GROUP BY and ORDER BY analysis, which is similar, but
certainly not identical, to HAVING clause analysis.
So now I'm wondering: if it's true that we can figure out the proper ColumnReference
information at the *end* of the optimization process, and don't have to maintain
that data during the compilation/optimzation process, then perhaps we shouldn't be
doing it at all, but rather should simply wait til optimization is complete, then fix-up all
the ColumnReferences in the tree at the end.
That is, if it's safe to allow the ColumnReference in the aggregate of a HAVING clause
to "decay" (become in-accurate) during optimization, and then fix it up at the end,
why do we have to re-map the ColumnReference information for the SELECT's RCL?
And similarly could we avoid having to "fix-up" the GROUP BY and ORDER BY clauses?
Or is it only safe to allow HAVING clause ColumnReference instances to diverge
from reality for short periods, because they are fundamentally different from the
aggregate nodes in the select RCL?
3) Army says "the compile time query tree has two AggregateNodes". I've noticed
this myself. In fact, while looking at somewhat similar situations, I've noticed that
(I think) the actual code that is generated to execute the query may contain
multiple computations of the same expressions. That is, when this query runs,
is it true that we are actually going to *compute* AVG(t2.i) twice? We'll only do
one pass over the data, of course, but we'll have two running-total values that
are redundantly accumulating the t2.i sub-totals as we go?
I've been wondering if maybe there is some sort of fundamental "common sub-expression
detection" which the Derby compilation system is not performing, such that it
doesn't recognize when two parts of a query are actually computing the same
result, and so we end up generating a query which performs more computation
than is needed, redundantly computing values multiple times in situations where
they are mentioned multiple times.
I noticed this in studying GROUP BY queries, where if the GROUP BY clause
contained an expression, and that expression matched a similar expression
in the SELECT list, the resulting query seemed to compute the expression twice,
when it would have been adequate to compute it only once.
I'm not intending to "hijack the thread" with any of these questions; I'm extremely
happy that we've solved this problem and that the query now runs correctly.
I'm just trying to improve my understanding of Derby internals by taking this
opportunity to ask a few questions about parts of this issue that puzzled me.
Thanks VERY much to Army and Kathey for all the hard work they did on this problem!
> 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
> Assignee: A B
> Priority: Minor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.0.0
>
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Updated: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3880:
---------------------------------
Issue & fix info: [High Value Fix]
> 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
> Assignee: A B
> Priority: Minor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.1.1
>
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Updated: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3880:
----------------------------------
Derby Info: [Regression]
> 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.
[jira] Updated: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3880:
----------------------------------
Attachment: querytree_fail.txt
querytree_works.txt
I printed out the query trees for the working query versus the failing query. The failing query uses a JoinNode and passes through the code in JoinNode.buildRCL()
tmpRCL.adjustVirtualColumnIds(resultColumns.size());
I noticed that if I remove this adjustment the query works, but it of course causes other problems with other queries, but perhaps there is something wrong with the VirtualColumnId adjustment.
The working query doesn't use a JoinNode and so does not pass through this code and as best I can tell does not do the same adjustment elsewhere. I don't know if any of this is helpful but I thought I would mention it.
> 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
> Attachments: querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643316#action_12643316 ]
Kathey Marsden commented on DERBY-3880:
---------------------------------------
Thanks Army and Brian for looking at this pesky issue. With regard to DERBY-3094, thank you for the write up. It was indeed most informative. As you suggested I backed out the change for DERBY-3094 and found no appreciable difference in the behaviour of the queries in question in this issue.
I looked at the SubstituteExpressionVisitor logic and don't think it is related. The result column in question is the one created in AggregateNode.java with the code
node = (operand == null) ?
this.getNewNullResultExpression() :
operand;
return (ResultColumn) getNodeFactory().getNode(
C_NodeTypes.RESULT_COLUMN,
"##aggregate expression",
node,
getContextManager());
}
where it is the operand being wrapped. I do not think this column is traversed with the SubstituteExpressionVisitor.
> 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
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Updated: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3880:
----------------------------------
Attachment: AggregateExpressionResultColumn.txt
Weill it looks like the indentation for my ResultColumn summary did not show up in the comment so I am attaching a file AggregateExpressionResultColumn.txt
> 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
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12631634#action_12631634 ]
Mamta A. Satoor commented on DERBY-3880:
----------------------------------------
Can you please provide the complete stack trace? I ran it on trunk, but since my codeline is compiled with Sane=true, I am not running into npe, instead I am getting Assert Failures for a null object.
ERROR XJ001: Java exception: 'ASSERT FAILED col[2] is null: org.apache.derby.shared.common.sanity.AssertFailure'.
java.sql.SQLException: Java exception: 'ASSERT FAILED col[2] is null: org.apache.derby.shared.common.sanity.AssertFailure'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException (TransactionResourceImpl.java:403)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2192)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1325)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:508)
at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:3
50)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:248)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
at org.apache.derby.tools.ij.main(ij.java:59)
Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED col[2] is null
at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
at org.apache.derby.impl.store.access.sort.MergeSort.checkColumnTypes(MergeSort.java:458)
at org.apache.derby.impl.store.access.sort.MergeInserter.insert(MergeInserter.java:98)
at org.apache.derby.impl.sql.execute.GroupedAggregateResultSet.loadSorter(GroupedAggregateResultSet.java:308)
at org.apache.derby.impl.sql.execute.GroupedAggregateResultSet.openCore(GroupedAggregateResultSet.java:180)
at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(P
rojectRestrictResultSet.java:168)
at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:245)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:384)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
... 10 more
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646775#action_12646775 ]
A B commented on DERBY-3880:
----------------------------
Hi Bryan,
Thanks for your interest in this issue.
> Army says "so the JoinNode's bind-time RCL is: { T1.i, T1.c, T2.i, T2.c2, T2.i2 }" but I don't
> see how that is [...] I'm having trouble seeing why T1.c and T2.c2 are in the join node's RCL.
I think the key observation here is in the sentence immediately preceding the one that you quoted, namely:
The JoinNode's bind-time result column list is simply a concatenation of the result column
lists from its operands.
Due to the specification of "inner join" the parser will create a parse-time JoinNode whose left operand is a FromBaseTable representing T1 and whose right operand is a FromBaseTable representing T2. Note how neither operand has a ProjectRestrictNode above it, which in turn means we will *not* project out any columns (at this point). Thus the FromBaseTable for T1 has an RCL with *ALL* of T1's columns in it, and the FromBaseTable for T2 has an RCL with *ALL* of T2's columns in it. The fact that some columns are never referenced in the query has no effect on the RCLs for the FromBaseTables at this point in binding.
To verify, you can put a breakpoint in JoinNode.buildRCL() and, when the breakpoint hits, look at the leftOperand and rightOperand. They will both be FromBaseTables and the RCLs will have all columns that exist in the underlying base tables. So the JoinNode's RCL then becomes a concatenation of all columns from T1 and all columns from T2, hence { T1.i, T1.c, T2.i, T2.c2, T2.i2 }--even though T1.c and T2.c2 are never referenced in the query.
> I anticipated that I was going to see a fix which involved passing the HAVING clause
> expressions to fromList.flattenFromTables() so that it could properly maintain the
> ColumnReference data in the HAVING clause during flattening.
To be honest, I think you're absolutely right: that seems like a more robust approach to the problem. See below...
> if it's safe to allow the ColumnReference in the aggregate of a HAVING clause
> to "decay" (become in-accurate) during optimization, and then fix it up at the end,
> why do we have to re-map the ColumnReference information for the SELECT's
> RCL?
Good question. I do not think it is generally safe to assume that a temporarily inaccurate ColumnReference is going to be okay. The vast majority of the ColumnReference remapping that occurs during optimization happens in order to support the pushing of predicates down the tree and/or to allow the insertion of query tree nodes into an existing tree. In both cases the column reference is being remapped from one valid ResultColumn to another valid ResultColumn. In other words, we can't just look at the ColumnReference after optimization has finished and say "pick the only one that's valid", because there could be an arbitrarily long chain of ResultColumns, VirtualColumnNodes, and ColumnReferences, all of which are valid and referenceable by different parts of the query tree. The only way to know which one is the "right" one for a given ColumnReference is to remap (or un-remap) in "real time"--ex. when we push/pull a predicate, we have to remap its column reference as part of the push/pull.
That said, the fact that a post-optimization remapping of the HAVING aggregate's ColumnReference corrected the problem was a pleasant surprise to me, as I too was more inclined to believe that, by then, it would be too late. But I made the change, saw that it made sense and that it fixed the immediate problem, and that's what I posted. Having done so, I think it's worth it to echo the final few sentences from that same comment, namely:
I admit that this change may not be sufficient (or it may be too general), but it does
cause the reported query to run without error, so at the very least it demonstrates
the problem. There could very well be a better way to fix it...
I posted a quick fix to demonstrate the problem and stopped there. Kathey was kind enough to pick it up and take it to completion. But at a higher level, I think you're right: there's probably a better (and safer) way to fix this bug, and your suggestion that such a fix would involve passing the HAVING clause expressions to fromList.flattenFromTables() seems like a good one to me...
> I've noticed that (I think) the actual code that is generated to execute the query may
> contain multiple computations of the same expressions. That is, when this query
> runs, is it true that we are actually going to *compute* AVG(t2.i) twice?
I have not looked at this in any detail, but I believe the answer is "Yes", at least based on the tracing I was doing when I was trying to track down the cause of this issue. But I wasn't specifically investigating that particular behavior at the time, so it's possible I missed something...
> 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
> Assignee: A B
> Priority: Minor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.0.0
>
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646804#action_12646804 ]
Bryan Pendleton commented on DERBY-3880:
----------------------------------------
Thanks Army!
I had overlooked your explanation about the construction of the JoinNode RCL; thanks
for the clarification. I worked through JoinNode.buildRCL and I see exactly what you mean:
/* Now we append the propagated RCL from the right to the one from
* the left and call it our own.
*/
resultColumns.nondestructiveAppend(tmpRCL);
It seems like it would be worth following up some more on the other topics; I'll see about
filing them as separate JIRA issues so we can pursue them down the line.
Thanks again not only for the fix, but even better for helping me understand how it works.
> 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
> Assignee: A B
> Priority: Minor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.0.0
>
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644199#action_12644199 ]
A B commented on DERBY-3880:
----------------------------
I looked a bit more at this issue and I think I've discovered at least part of the problem. A query like:
select t1.i, avg(t2.i)
from t1 inner join t2 on (t1.i = t2.i)
group by t1.i having avg(t2.i) > 0;
has two aggregates in it, which means the compile time query tree has two AggregateNodes, each of which has an "operand" that is a column reference. The result column to which the operand points is determined at bind time, and in this case the result column "t2.i" points to a bind-time JoinNode. The JoinNode's bind-time result column list is simply a concatenation of the result column lists from its operands. In our example, the query specifies T1 followed by T2, so the JoinNode's bind-time RCL is: { T1.i, T1.c, T2.i, T2.c2, T2.i2 }. Notice how, with respect to this list, the position of "t2.i" is "3". So when we bind the AggregateNodes, each of them ends up with an operand that is a ColumnReference which points to the "t2.i" column of the bind-time JoinNode, which in turn has virtual column id of "3".
After binding is complete we start optimization, the first phase of which is preprocessing. As part of SelectNode preprocessing we look at the FromList to see if there are any JoinNodes that can be flattened. In the above query the JoinNode *can* be flattened, which means it effectively disappears. That in turn means that any result columns in the SELECT list have to be updated to reflect the fact that they no longer point to the JoinNode; instead, they point directly to the underlying base tables. The process of flattening the JoinNode and updating the SelectNode's result column list begins with the following call in SelectNode.preprocess():
// Flatten any flattenable FromSubquerys or JoinNodes
fromList.flattenFromTables(resultColumns,
wherePredicates,
whereSubquerys,
groupByList);
Notice how we pass the SelectNode's RCL into the flattenFromTables method, but we do *not* pass the HAVING clause. Long story short, the AggregateNode in the SelectNode's RCL is properly updated to reflect the removal of the JoinNode--but the AggregateNode in the HAVING clause continues to point to a result column from the now-useless JoinNode. The *real* column to which the Aggregate should be pointing is actually buried *beneath* the JoinNode's ResultColumn, so the aggregate's operand should have been remapped to point to the buried column. (And indeed, that's exactly what happens with the aggregate in the SelectNode's RCL.)
When optimization is done and we modify the tree, result columns are updated to reflect the final join order and also to remove any un-referenced columns. But since the HAVING aggregate node is still pointing to the unused JoinNode result column, it doesn't see any changes--it continues to point to a ResultColumn that has a virtual column id of "3" and which points to a JoinNode that is no longer valid.
The final query plan inclues a *new* JoinNode between T1 and T2, and that JoinNode's RCL only contains columns which are referenced in the query. Thus the execution-time JoinNode result column list is, for the above query, { T1.i, T2.i }. Note how it only has TWO columns in it, not five like the bind-time JoinNode. So when it comes time to execute the query, the aggregate in the having clause ends up pointing to column "3" of the new JoinNode's RCL, and since that doesn't exist, the column value is set to null (as Kathey noted in earlier comments). Attempts to reference that column for GROUP BY sorting, then, result in the NPE.
The reason the same query works if the two aggregates reference two _different_ columns in T2 has nothing to do with equivalence--that was a red herring, sorry for the miscue. The reason the query passes appears to have something to do with referenced columns--esp. the SelectNode (which includes the Select RCL and the Having clause) then references TWO columns from T2 plus ONE column from T1, leading to THREE columns in the execution JoinNode's RCL: { T1.i, T2.i, T2.i2 }. So now the RCL has three columns instead of two, which means the result column in the HAVING aggregate, which still has the INCORRECT virtual id of "3", accidentally points to a valid column, and thus the query passes--but that was just chance, the code is still technically wrong.
This also explains why the original query passes if the user does NOT specify "inner join". The inclusion of "inner join" leads to the creation of the bind-time JoinNode between T1 and T2, against which the aggregates are then bound. In preprocessing we realize the JoinNode is useless so we flatten it out of the query, and that causes the aforementioned problem. But if the query does not have "inner join", the bind-time JoinNode is never created, which means both aggregate nodes already point directly to the underlying base tables. Throughout optimization and query generation the base table result columns are properly updated, which means both aggregates end up pointing to the correct place, and all is well.
So all of that said, I think the fix for this problem is to somehow ensure that the HAVING clause's AggregateNode is correctly remapped when/if necessary, esp. if it points to a JoinNode that has been flattened out. I happened to notice that, when the JoinNode is flattened, all of its result columns are marked as "redundant" as part of the flattening process (see JoinNode.flatten()). So the quick (and possibly NOT complete) fix that I found was to add an "if" statement to the getNewExpressionResultColumn() method of AggregateNode.java. That method is called as part of "modification of access paths"--i.e. after optimization has completed--and is used for creating the result column that will ultimately be used for aggregate input at execution time. I found that if I added logic to remap the Aggregate's operand (if it's a column reference), then the posted query starts running without error:
Index: java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java (revision 707244)
+++ java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java (working copy)
@@ -539,6 +539,26 @@
this.getNewNullResultExpression() :
operand;
+ /* The operand for this aggregate node was initialized at bind
+ * time. Between then and now it's possible that certain changes
+ * have been made to the query tree which affect this operand. In
+ * particular, if the operand was pointing to a result column in
+ * a JoinNode and then that JoinNode was flattened during pre-
+ * processing, all of the references to that JoinNode--including
+ * this aggregate's operand--need to be updated to reflect the
+ * fact that the Join Node no longer exists. So check to see if
+ * the operand is a column reference, and if so, make a call to
+ * remap it to its underlying expression. If nothing has happened
+ * then this will be a no-op; but if something has changed to void
+ * out the result column to which the operand points, the result
+ * column will be marked "redundant" and the following call should
+ * remap as appropriate. DERBY-3880.
+ */
+ if (operand instanceof ColumnReference)
+ {
+ ((ColumnReference)operand).remapColumnReferencesToExpressions();
+ }
+
return (ResultColumn) getNodeFactory().getNode(
C_NodeTypes.RESULT_COLUMN,
"##aggregate expression",
I didn't run any other tests, and I admit that this change may not be sufficient (or it may be too general), but it does cause the reported query to run without error, so at the very least it demonstrates the problem. There could very well be a better way to fix it...
> 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
> Attachments: AggregateExpressionResultColumn.txt, querytree_fail.txt, querytree_works.txt
>
>
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Venkateswaran Iyer (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12631857#action_12631857 ]
Venkateswaran Iyer commented on DERBY-3880:
-------------------------------------------
Here's the complete stack:
Apache Derby Network Server - 10.4.2.0 - (689064) started and ready to accept connections on port 1527 at 2008-09-16 20:19:45.296 GMT
----------------------------------------------------------------
2008-09-16 20:26:34.328 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.4.2.0 - (689064): instance a816c00e-011c-6cd1-8f55-00004e6b54c8
on database directory C:\apps\derby\db-derby-10.4.2.0-bin\db-derby-10.4.2.0-bin\bin\ClassicModels
Database Class Loader started - derby.database.classpath=''
2008-09-16 20:27:53.890 GMT Thread[DRDAConnThread_3,5,main] (XID = 525), (SESSIONID = 8), (DATABASE = ClassicModels), (DRDAID = C0A82DCA.GFE0-4183842832230338175{9}), Cleanup action starting
2008-09-16 20:27:53.890 GMT Thread[DRDAConnThread_3,5,main] (XID = 525), (SESSIONID = 8), (DATABASE = ClassicModels), (DRDAID = C0A82DCA.GFE0-4183842832230338175{9}), Failed Statement is: SELECT "APP"."Orders"."orderNumber", AVG( "APP"."OrderDetails"."quantityOrdered" )
FROM ("APP"."Orders" INNER JOIN "APP"."OrderDetails" ON "APP"."Orders"."orderNumber" = "APP"."OrderDetails"."orderNumber" )
GROUP BY "APP"."Orders"."orderNumber"
HAVING AVG( "APP"."OrderDetails"."quantityOrdered" ) > 0
java.lang.NullPointerException
at org.apache.derby.impl.sql.execute.BasicSortObserver.getClone(Unknown Source)
at org.apache.derby.impl.sql.execute.BasicSortObserver.insertNonDuplicateKey(Unknown Source)
at org.apache.derby.impl.sql.execute.AggregateSortObserver.insertNonDuplicateKey(Unknown Source)
at org.apache.derby.impl.store.access.sort.SortBuffer.insert(Unknown Source)
at org.apache.derby.impl.store.access.sort.MergeInserter.insert(Unknown Source)
at org.apache.derby.impl.sql.execute.GroupedAggregateResultSet.loadSorter(Unknown Source)
at org.apache.derby.impl.sql.execute.GroupedAggregateResultSet.openCore(Unknown Source)
at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown Source)
at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(Unknown Source)
at org.apache.derby.impl.drda.DRDAStatement.execute(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
Cleanup action completed
> 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.
[jira] Commented: (DERBY-3880) NPE on a query with having clause
involving a join
Posted by "Venkateswaran Iyer (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12634298#action_12634298 ]
Venkateswaran Iyer commented on DERBY-3880:
-------------------------------------------
That should work. Thanks.
> 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.