Incorrect query result when using named window
----------------------------------------------
Key: CORE-6460
URL: http://tracker.firebirdsql.org/browse/CORE-6460
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 4.0 Beta 2
Reporter: Adriano dos Santos Fernandes
Reported by Vlad by e-mail:
Pavel Zotov found a bug with windows functions. I investigated it and seems
found a reason.
Sample:
recreate table emp_test (
emp_no smallint,
dept_no char(3),
salary numeric(10,2)
);
commit;
insert into emp_test (emp_no, dept_no, salary) values ( 85, 'd01', 99999);
insert into emp_test (emp_no, dept_no, salary) values (127, 'd01', 11111);
commit;
select e.emp_no, e.dept_no, e.salary,
last_value(e.salary) over (order by e.salary, e.emp_no) as last_2,
last_value(e.salary) over w2 as last_w2,
last_value(e.salary) over (order by e.salary, e.emp_no RANGE BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_3,
last_value(e.salary) over w3 as last_w3,
last_value(e.salary) over w4 as last_w4
from emp_test e
window
w1 as (),
w2 as (w1 order by e.salary, e.emp_no),
w3 as (w1 order by e.salary, e.emp_no RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING),
w4 as (w2 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
order by e.emp_no
Here values in the columns last_2 and last_w2 should be equal, also equal
should be values at
columns last_3, last_w3 and last_w4. But actually we have wrong results:
EMP_NO DEPT_NO SALARY LAST_2 LAST_W2 LAST_3 LAST_W3 LAST_W4
85 d01 99999 99999 11111 99999 11111 99999
127 d01 11111 11111 11111 99999 11111 11111
You see - values at columns with explicit window definitions is OK, while
columns with named windows
are wrong.
As I found, the bug is at WindowClause::dsqlPass:
WindowClause* node = FB_NEW_POOL(dsqlScratch->getPool())
WindowClause(dsqlScratch->getPool(),
window->name,
doDsqlPass(dsqlScratch, window->partition),
doDsqlPass(dsqlScratch, window->order),
doDsqlPass(dsqlScratch, window->extent),
window->exclusion);
it not uses clauses from "this" when creates new window (from base window
"window" and current
definition "this"). I tried following fix:
WindowClause* node = FB_NEW_POOL(dsqlScratch->getPool())
WindowClause(dsqlScratch->getPool(),
window->name,
doDsqlPass(dsqlScratch, window->partition),
doDsqlPass(dsqlScratch, this->order ? this->order : window->order),
doDsqlPass(dsqlScratch, this->extent ? this->extent : window->extent),
this->exclusion ? this->exclusion : window->exclusion);
and it works for me. Probably you have better idea.
--
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
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
