summary: Wrong output (no rows) in case of subquery with windowed function + where <field> IN(select ...) => Error in case of subquery with windowed function + where <field> IN(select ...)

description: DDL:

recreate table t0(q int); commit;
create index t0_q on t0(q);
commit;
insert into t0(q) values (1);
commit;

QUERY:

select a.qx
from
(
select qx
from
(
select 1 qx
,count(*)over() as c
from t0
where t0.q in (select 1 from rdb$database)
) r
) a
join t0 b on a.qx = b.q;

-- has no output (no rows) in ISQL. Correct result: exactly one row.

This also has wrong result (no rows in ISQL):

select a.qx
from
(
select qx
from
(
select t0.q as qx
,count(*)over() as c
from t0
where t0.q in (select 1 from rdb$database)
) r
) a
join t0 b on a.qx = b.q;

The result will be OK if any of the following actions will be made:
1) replace inner join of derived table `a` with LEFT join
2) remove "dummy condition" where t0.q in (select 1 from rdb$database)
3) remove "useless" count(*)over() or any other windowed function inside DT
4) remove final join: t0 b on a.qx = b.q; - the problem also disappears;
5) disable choise of using index t0_q in JOIN, i.e.: join t0 b on a.qx = b.q+0;

=>

DDL:

recreate table t0(q int); commit;
create index t0_q on t0(q);
commit;
insert into t0(q) values (1);
commit;

QUERY:

select a.qx
from
(
select qx
from
(
select 1 qx
,count(*)over() as c
from t0
where t0.q in (select 1 from rdb$database)
) r
) a
join t0 b on a.qx = b.q;

-- returns error:
Statement failed, SQLSTATE = 22000
no current record for fetch operation

Expected result: exactly one row.

This also shows error:

select a.qx
from
(
select qx
from
(
select t0.q as qx
,count(*)over() as c
from t0
where t0.q in (select 1 from rdb$database)
) r
) a
join t0 b on a.qx = b.q;

The result will be OK if any of the following actions will be made:
1) replace inner join of derived table `a` with LEFT join
2) remove "dummy condition" where t0.q in (select 1 from rdb$database)
3) remove "useless" count(*)over() or any other windowed function inside DT
4) remove final join: t0 b on a.qx = b.q; - the problem also disappears;
5) disable choise of using index t0_q in JOIN, i.e.: join t0 b on a.qx = b.q+0;

assignee: Dmitry Yemanov [ dimitr ]

Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

Version: 3.0 Initial [ 10301 ] =>

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully