Window Function: ntile(num_buckets integer) [CORE3618]

[firebird-automations]

Submitted by: Sean Leyne (seanleyne)

Jira_subtask_inward CORE1688

Votes: 1

Commits: 01c131b 0bd8fd6

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

[firebird-automations]

Commented by: @pavel-zotov

Should NTILE() allow to use EXPRESSION or only literal ?

DDL:
recreate table test(id int, x int);
commit;
insert into test values(null, 100);
insert into test values(null, 101);
insert into test values(null, 102);
insert into test values(2000, 200);
insert into test values(2001, 201);
insert into test values(2002, 300);
insert into test values(2003, 301);
commit;

Now try:

select id,x,ntile( 2+3 )over(order by http://t.id) from test t order by http://t.id,t.x;

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 21
-+

select id,x,ntile( (2+5)/3 )over(order by http://t.id) from test t order by http://t.id,t.x;

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 20
-(

PS. MS SQL works fine with such query (i.e. when argument for ntile is expression).

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: Deferred

[firebird-automations]

Commented by: @pavel-zotov

PS.

select x,y,ntile(2.718)over(order by x,y) from test order by x,y;

- should this raise error about positive int/bigint as the only allowed type ? (MS SQL "The function 'ntile' takes only a positive int or bigint expression as its input.")

[firebird-automations]

Commented by: @asfernandes

Pavel, I committed inprovements: 01c131b

Not everything can be in the NTILE parameter. I did now what the sql spec. says.

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: Deferred => Done successfully

[firebird-automations]

Modified by: @pavel-zotov

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