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