db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3880) NPE on a query with having clause involving a join
Date Thu, 23 Oct 2008 17:06:44 GMT

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


Mime
View raw message