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

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant