Fully qualified order by column gives "relational query without result"

[monetdb-team]

Date: 2013-09-24 10:27:26 +0200
From: @swingbit
To: SQL devs <>
Version: 11.15.15 (Feb2013-SP4)
CC: @njnes

Last updated: 2013-10-22 14:42:07 +0200

Comment 19169

Date: 2013-09-24 10:27:26 +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:

This query comes from bug #3366, which might be related to this.
The sorting on a constant column is there only for the sake of the example of course.

The following gives the expected result:

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 ORDER BY r;

The following, gives "relational query without result":

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 ORDER BY y.r;

The only difference is "ORDER BY y.r" instead of "ORDER BY r".

Reproducible: Always

Comment 19186

Date: 2013-09-27 09:20:11 +0200
From: @njnes

could you test this on the sp4 ?

Comment 19192

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

Yep! This seems to be fixed on SP4.

Comment 19206

Date: 2013-09-27 18:23:45 +0200
From: @swingbit

Oops.
I'm sorry, but I need to reopen it. This query still fails (v11.15.16,
changeset 49170:c0bf1946fc19):

create table x as select id, CAST(1 as double) as prob from sys.functions with data;
create table y as select id, CAST(1 as double) as prob from sys.functions with data;

with x1 as ( select id, prob, row_number() OVER(ORDER BY id desc) as r from x)
select y.*, x1.prob
from x1, y
where x1.r <=2
and x1.id = y.id
-- order by r; -- this works
order by x1.r; -- this doesn't work

Comment 19229

Date: 2013-10-02 09:06:03 +0200
From: @njnes

Seems to work just fine in the Feb2013 branch, ie will get be part of a SP5.