Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error in case of subquery with windowed function + where <field> IN(select ...) [CORE4270] #4594

Closed
firebird-issue-importer opened this issue Nov 18, 2013 · 8 comments

Comments

@firebird-issue-importer

Submitted by: @pavel-zotov

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;

Commits: dfbc754 FirebirdSQL/fbt-repository@7ba52f2

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Commented by: @pavel-zotov

ISQL Version: LI-T3.0.0.30737 Firebird 3.0 Alpha 1
Server version:
LI-T3.0.0.30737 Firebird 3.0 Alpha 1
LI-T3.0.0.30737 Firebird 3.0 Alpha 1/tcp (oel64)/P13
LI-T3.0.0.30737 Firebird 3.0 Alpha 1/tcp (oel64)/P13

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Modified by: @dyemanov

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;

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Commented by: @dyemanov

I don't see the error in ISQL but I do see no rows returned.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Modified by: @dyemanov

Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

Version: 3.0 Initial [ 10301 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 30, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants