Incorrect query result when using named window [CORE6460]

[firebird-automations]

Submitted by: @pavel-zotov

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.

Commits: 9644385

[firebird-automations]

Modified by: @asfernandes

reporter: Adriano dos Santos Fernandes [ asfernandes ] => Pavel Zotov [ tabloid ]

assignee: Vlad Khorsun [ hvlad ]

[firebird-automations]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

[firebird-automations]

Modified by: @pavel-zotov

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