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

Wrong output when field with result of windowed function is used in query with useless WHERE 0=0 and GROUP BY clause [CORE4269] #4593

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

Comments

@firebird-issue-importer

Submitted by: @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)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2013

Commented by: @pavel-zotov

One more sample, but in this case the 'dummy condition' like WHERE 0=0 does not help.
the problem is in PARTITION BY clause in sum()over():

DDL:

recreate table tv(id int primary key); commit;
recreate table tb(vid int references tv, qid int); commit;

insert into tv values(10);
insert into tv values(20);
insert into tv values(30);
insert into tv values(40);
commit;
insert into tb values(20,100);
insert into tb values(20,200);
insert into tb values(20,300);
insert into tb values(30,400);
commit;

QUERY:

select id,max(qid) qid_max
from(
select v.*, b.*
from tv v
left join(
select b.*
,sum(1)over(partition by qid) dummy -- this lead to WRONG result (no rows)
--,sum(qid)over() dummy -- this is OK
from tb b
) b on v.id=b.vid
)
--where 0=0 -- does NOT help in this case!
group by id
having max(qid) is null;

RESULT: no rows (wrong!).

Correct result:
ID QID_MAX
=== ============
10 <null>
40 <null>

PS. No effect if we remove FOREIGN KEY constraint.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 18, 2013

Commented by: @pavel-zotov

Both effects disappeared, I've checked scripts on LI-T3.0.0.30792.
Perhaps this was side-effect of other bugs.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 18, 2013

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

assignee: Dmitry Yemanov [ dimitr ]

@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