Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

window functions issues #6722

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

window functions issues #6722

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working major SQL

Comments

@monetdb-team
Copy link

Date: 2019-07-02 16:45:46 +0200
From: daniel.zvinca
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @PedroTadim

Last updated: 2019-11-28 10:00:04 +0100

Comment 27101

Date: 2019-07-02 16:45:46 +0200
From: daniel.zvinca

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36
Build Identifier:

  • in combination with window functions returns unexpected doubled columns (unnamed) depending on * position. Easy to reproduce.

Reproducible: Always

Steps to Reproduce:

  1. create table test_x (part string, qty double);

  2. insert into test_x values ('a', 18), ('a', 13),('a', 16),('b', 15),('b', 16),('c', 17),('c', 18),('c', 12),('d', 12),('d', 12);

  3. select
    dense_rank() over (order by part, qty) as rank_id,
    row_number() over (order by part, qty) as row_id,

from test_x order by part, qty

Actual Results:

+---------+--------+------+--------------------------+------+------+
| rank_id | row_id | part | qty | L3 | L10 |
+=========+========+======+==========================+======+======+
| 1 | 1 | a | 13 | 1 | 1 |
| 2 | 2 | a | 16 | 2 | 2 |
| 3 | 3 | a | 18 | 3 | 3 |
| 4 | 4 | b | 15 | 4 | 4 |
| 5 | 5 | b | 16 | 5 | 5 |
| 6 | 6 | c | 12 | 6 | 6 |
| 7 | 7 | c | 17 | 7 | 7 |
| 8 | 8 | c | 18 | 8 | 8 |
| 9 | 9 | d | 12 | 9 | 9 |
| 9 | 10 | d | 12 | 9 | 10 |
+---------+--------+------+--------------------------+------+------+

Expected Results:

+---------+--------+------+--------------------------+
| rank_id | row_id | part | qty |
+=========+========+======+==========================+
| 1 | 1 | a | 13 |
| 2 | 2 | a | 16 |
| 3 | 3 | a | 18 |
| 4 | 4 | b | 15 |
| 5 | 5 | b | 16 |
| 6 | 6 | c | 12 |
| 7 | 7 | c | 17 |
| 8 | 8 | c | 18 |
| 9 | 9 | d | 12 |
| 9 | 10 | d | 12 |
+---------+--------+------+--------------------------+

The followings return different results:

select
*,
(dense_rank() over (order by part, qty)) as rank_id,
(row_number() over (order by part, qty)) as row_id
from test_x;

select
(dense_rank() over (order by part, qty)) as rank_id,
*,
(row_number() over (order by part, qty)) as row_id
from test_x;

select
(dense_rank() over (order by part, qty)) as rank_id,
(row_number() over (order by part, qty)) as row_id,
*
from test_x;

Comment 27103

Date: 2019-07-03 13:12:59 +0200
From: MonetDB Mercurial Repository <>

Changeset fc59db5e875d made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=fc59db5e875d

Changeset description:

Add test for bug #6722

Comment 27161

Date: 2019-07-23 09:17:33 +0200
From: MonetDB Mercurial Repository <>

Changeset 474fab6a03d8 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=474fab6a03d8

Changeset description:

initial steps to fix the * output bug #6722
@monetdb-team monetdb-team added bug Something isn't working major SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working major SQL
Projects
None yet
Development

No branches or pull requests

2 participants