Wrong output (no rows) in case of subquery with windowed function + where
<field> IN(select ...)
------------------------------------------------------------------------------------------------
Key: CORE-4270
URL: http://tracker.firebirdsql.org/browse/CORE-4270
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Initial
Reporter: 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;
-- 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;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
Free app hosting. Or install the open source package on any LAMP server.
Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
