rownum() (and its alias) not recognised in current scope

[monetdb-team]

Date: 2013-09-24 10:17:07 +0200
From: @swingbit
To: SQL devs <>
Version: 11.15.3 (Feb2013-SP1)
CC: @njnes

Last updated: 2013-09-27 10:27:13 +0200

Comment 19168

Date: 2013-09-24 10:17:07 +0200
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.57 Safari/537.36
Build Identifier:

If this works (it does):

create table x as select name, 1 as a from sys.tables with data;
SELECT name, a, row_number() OVER(ORDER BY a DESC) from x;

then I would expect this to work as well (it doesn't):

create table x as select name, 1 as a from sys.tables with data;
SELECT name, a, row_number() OVER(ORDER BY a DESC) as r from x where r < 5;

That gives however:
SELECT: identifier 'r' unknown

Using the fully qualified name (where x.r < 5) doesn't help either.

The only solution is to use a subquery:

create table x as select name, 1 as a from sys.tables with data;
WITH y AS (
SELECT name, a, row_number() OVER(ORDER BY x.a DESC) as r FROM x
)
SELECT name, a FROM y WHERE r <= 20;

I think the row_number() function and its alias should be in the scope of the original query.

Reproducible: Always

Comment 19185

Date: 2013-09-27 09:17:44 +0200
From: @njnes

thats not how sql is defined. Only the from part is known in the where block.

Comment 19193

Date: 2013-09-27 10:27:13 +0200
From: @swingbit

You are completely right, my bad.
It was not about the row_number() OVER(), at all.