Wrong output when field with result of windowed function is used in query with
useless WHERE 0=0 and GROUP BY clause
--------------------------------------------------------------------------------------------------------------------
Key: CORE-4269
URL: http://tracker.firebirdsql.org/browse/CORE-4269
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0 Alpha 1
Reporter: Pavel Zotov
DDL:
recreate table tb(q int, v int); commit;
insert into tb
with
x as(
select 1 q, 1 v from rdb$database union all
select 1 q, 7 v from rdb$database
)
select * from x;
commit;
----
recreate table tv(v int, n varchar(10), c varchar(10) ); commit;
insert into tv
with
x as(
select 1 v, 'a1' n, 'r' c from rdb$database union all
select 7 v, 'a7' n, 'b' c from rdb$database
)
select * from x;
commit;
QUERY:
=======
with
tc as(
select
tb.q, tb.v, tv.c,
dense_rank () over (partition by tb.q, tv.c order by tb.v) rk,
--dense_rank () over (partition by tv.c order by tb.v) rk,
case when tv.c = 'r' then tv.n end r
from tb
join tv on tv.v = tb.v
where tb.q=1
)
select q, rk , max(r)
from tc
where 0=0 -- <<<<<<<< NOTE: this is always true and can NOT have any effect on
result of query!
group by q, rk
UNION ALL
select q, rk , max(r)
from tc
group by q, rk;
RESULT:
========
Q RK MAX
============ ===================== ========
1 1 <null>
1 1 a1
(two rows in this output should be equal but they aren`t)
--
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
