My query is not null of course and moderately complex. It's a recursive query though.
The "with recursive" part works very well in a different query.
I'm investigating but lack familiarity with hsqldb (just a few days).
The access is in server mode, the version is 2.3.4.
Same pb in 2.3.1.
DatabaseManagerSwing raises same NPE.
Looks like a subquery is lost although I can see it still under debug in NamedSubQueries
Complete stack trace/script available on demand :)
I reduced the query that generates the NPE to
with recursive offer_tree (lvl, parent, child) as (
select 0, null, itemrelationtarget
from cwpc_itemrelation_v r
join cwpc_item i on i.itemcode = r.itemcode
where itemrelationtarget = 'STUFF'
union
select lvl+1, itemcode as parent, itemrelationtarget as child
from cwpc_itemrelation_v p
join offer_tree c on p.itemcode = c.child
)
select
a.itemattrversionid
from
cwpc_itemattribute_v a
where
a.itemcode in (
select distinct child from offer_tree
)
It seems the issue is with the introduction of an implicit join between the table resulting from the recursive CTE and the select.
So I tried
with recursive offer_tree (lvl, parent, child) as (
select 0, null, itemrelationtarget
from cwpc_itemrelation_v r
join cwpc_item i on i.itemcode = r.itemcode
where itemrelationtarget = 'PO_KD38_PROMOTION'
union
select lvl+1, itemcode as parent, itemrelationtarget as child
from cwpc_itemrelation_v p
join offer_tree c on p.itemcode = c.child
)
select
a.itemattrversionid
from
cwpc_itemattribute_v a,
offer_tree b
where
a.itemcode = b.child ;
And that works perfectly.....
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I should be able to fix this if you send me minimal table definitions with one or two rows of sample data to check the query. You can send direct via email. Your modified query is better as the IN (SELECT ...) part is usually turned into a join by the engine.
Last edit: Fred Toussi 2016-05-23
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
org.hsqldb.Scanner.reset(java.lang.String) line: 227
org.hsqldb.Scanner.reset(org.hsqldb.Session, java.lang.String) line: 214
org.hsqldb.ParserDQL(org.hsqldb.ParserBase).reset(org.hsqldb.Session, java.lang.String) line: 106
org.hsqldb.ParserDQL.reset(org.hsqldb.Session, java.lang.String) line: 99
org.hsqldb.TableDerived.newDerivedTable(org.hsqldb.Session) line: 157
org.hsqldb.ParserCommand(org.hsqldb.ParserDQL).readNamedSubqueryOrNull() line: 1911
org.hsqldb.ParserCommand(org.hsqldb.ParserDQL).readTableOrSubquery() line: 1983
Ran into that one during a
PreparedStatment ps = = jdbcConnection.prepareStatement ( query );
Complete stack trace/script available on demand :)
I reduced the query that generates the NPE to
with recursive offer_tree (lvl, parent, child) as (
select 0, null, itemrelationtarget
from cwpc_itemrelation_v r
join cwpc_item i on i.itemcode = r.itemcode
where itemrelationtarget = 'STUFF'
union
select lvl+1, itemcode as parent, itemrelationtarget as child
from cwpc_itemrelation_v p
join offer_tree c on p.itemcode = c.child
)
select
a.itemattrversionid
from
cwpc_itemattribute_v a
where
a.itemcode in (
select distinct child from offer_tree
)
It seems the issue is with the introduction of an implicit join between the table resulting from the recursive CTE and the select.
So I tried
with recursive offer_tree (lvl, parent, child) as (
select 0, null, itemrelationtarget
from cwpc_itemrelation_v r
join cwpc_item i on i.itemcode = r.itemcode
where itemrelationtarget = 'PO_KD38_PROMOTION'
union
select lvl+1, itemcode as parent, itemrelationtarget as child
from cwpc_itemrelation_v p
join offer_tree c on p.itemcode = c.child
)
select
a.itemattrversionid
from
cwpc_itemattribute_v a,
offer_tree b
where
a.itemcode = b.child ;
And that works perfectly.....
I should be able to fix this if you send me minimal table definitions with one or two rows of sample data to check the query. You can send direct via email. Your modified query is better as the IN (SELECT ...) part is usually turned into a join by the engine.
Last edit: Fred Toussi 2016-05-23
Hi Fred, Merci :)
I'll do that right away.
But it yields twice as many rows because some of the nodes (leaf nodes I guess) appear several times.
Confirmed as fixed starting with r5642.