row_number doesn't work in complex query

[monetdb-team]

Date: 2011-04-27 16:11:41 +0200
From: monetdb
To: SQL devs <>
Version: 11.13.3 (Oct2012)
CC: @njnes, @elpetrak

Last updated: 2013-01-22 09:29:20 +0100

Comment 15743

Date: 2011-04-27 16:11:41 +0200
From: monetdb

User-Agent: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)
Build Identifier:

row_number() may fail with the following error:

MALException:algebra.leftfetchjoin:GDK reported error.
ERROR: BATfetchjoin: head column of right input must be dense

See repro for an example.

Reproducible: Always

Steps to Reproduce:

(Table schema available inside additional information)

The following query works:

with Raw as
(
select origin_state, "month", day_of_month, count() as value
from ontime
where "year" = 2010
and cancelled = 0
group by origin_state, "month", day_of_month
)
select Raw., rank() over (partition by origin_state order by value desc) as rn
from Raw;

But replace rank() with row_number() and the query fails.

Actual Results:

MALException:algebra.leftfetchjoin:GDK reported error.
ERROR: BATfetchjoin: head column of right input must be dense

Expected Results:

The query should return a result set similar to the one with rank or dense_rank (but it should break ties).

Table DDL:

CREATE TABLE "sys"."ontime" (
"year" INTEGER,
"quarter" INTEGER,
"month" INTEGER,
"day_of_month" INTEGER,
"day_of_week" INTEGER,
"flight_date" DATE,
"unique_carrier" CHAR(7),
"carrier" CHAR(2),
"origin" CHAR(5),
"origin_state" CHAR(2),
"dest" CHAR(5),
"dest_state" CHAR(5),
"dep_time" INTEGER,
"dep_delay" INTEGER,
"arr_time" INTEGER,
"arr_delay" INTEGER,
"cancelled" INTEGER,
"cancellation_code" CHAR(1)
);

Comment 15811

Date: 2011-05-16 22:13:31 +0200
From: @njnes

without data the query seems to work just fine, ie do you have some data on which it fails?

Comment 18202

Date: 2012-11-28 13:45:39 +0100
From: @elpetrak

Changeset b5e2f5647000 made by Eleni Petraki petraki@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b5e2f5647000

Changeset description:

Bug #2805: row_number doesn't work in complex query.

Comment 18387

Date: 2013-01-22 09:29:20 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.