Bug 6997 - the result is incorrect when using window function including multiple sort_spec
Summary: the result is incorrect when using window function including multiple sort_spec
Status: RESOLVED NEXTFEATURERELEASE
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.37.11 (Jun2020-SP1)
Hardware: All All
: Normal major
Assignee: SQL devs
URL:
Keywords:
: 6985 6987 6988 6989 6990 6991 6992 6993 6995 6996 (view as bug list)
Depends on:
Blocks:
 
Reported: 2020-10-16 10:02 CEST by william.jing
Modified: 2020-11-09 09:17 CET (History)
2 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description william.jing 2020-10-16 10:02:58 CEST
User-Agent:       Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.75 Safari/537.36
Build Identifier: 

the result(column orderbyab) is incorrect when using window function including multiple sort_spec

Reproducible: Always

Steps to Reproduce:
here is the sql below:
create table "ordertest"(str text, orderA int, orderB int, num int);
insert into "ordertest" values('a',1,1,1);
insert into "ordertest" values('a',2,1,10);
insert into "ordertest" values('a',3,1,20);
insert into "ordertest" values('a',4,1,30);

select *,
sum("num") over(order by orderA) as orderbyA,
sum("num") over(order by orderA,orderB) as orderbyAB,
sum("num") over(order by orderB,orderA) as orderbyBA,
sum("num") over(order by orderB) as orderbyB
from "ordertest";
Actual Results:  
the actual result is below:
+------+--------+--------+------+----------+-----------+-----------+----------+
| str  | ordera | orderb | num  | orderbya | orderbyab | orderbyba | orderbyb |
+======+========+========+======+==========+===========+===========+==========+
| a    |      1 |      1 |    1 |        1 |        61 |         1 |       61 |
| a    |      2 |      1 |   10 |       11 |        61 |        11 |       61 |
| a    |      3 |      1 |   20 |       31 |        61 |        31 |       61 |
| a    |      4 |      1 |   30 |       61 |        61 |        61 |       61 |
+------+--------+--------+------+----------+-----------+-----------+----------+

the result of orderbyab is incorrect

Expected Results:  
the expected result is below:(the result is what run the same sql (also the same raw data) in postgresql)

+------+--------+--------+------+----------+-----------+-----------+----------+
| str  | ordera | orderb | num  | orderbya | orderbyab | orderbyba | orderbyb |
+======+========+========+======+==========+===========+===========+==========+
| a    |      1 |      1 |    1 |        1 |         1 |         1 |       61 |
| a    |      2 |      1 |   10 |       11 |        11 |        11 |       61 |
| a    |      3 |      1 |   20 |       31 |        31 |        31 |       61 |
| a    |      4 |      1 |   30 |       61 |        61 |        61 |       61 |
+------+--------+--------+------+----------+-----------+-----------+----------+
Comment 1 Sjoerd Mullender cwiconfidential 2020-10-16 10:17:19 CEST
*** Bug 6996 has been marked as a duplicate of this bug. ***
Comment 2 Sjoerd Mullender cwiconfidential 2020-10-16 10:17:45 CEST
*** Bug 6995 has been marked as a duplicate of this bug. ***
Comment 3 Sjoerd Mullender cwiconfidential 2020-10-16 10:18:03 CEST
*** Bug 6993 has been marked as a duplicate of this bug. ***
Comment 4 Sjoerd Mullender cwiconfidential 2020-10-16 10:18:18 CEST
*** Bug 6992 has been marked as a duplicate of this bug. ***
Comment 5 Sjoerd Mullender cwiconfidential 2020-10-16 10:18:31 CEST
*** Bug 6991 has been marked as a duplicate of this bug. ***
Comment 6 Sjoerd Mullender cwiconfidential 2020-10-16 10:18:49 CEST
*** Bug 6990 has been marked as a duplicate of this bug. ***
Comment 7 Sjoerd Mullender cwiconfidential 2020-10-16 10:19:03 CEST
*** Bug 6989 has been marked as a duplicate of this bug. ***
Comment 8 Sjoerd Mullender cwiconfidential 2020-10-16 10:19:20 CEST
*** Bug 6988 has been marked as a duplicate of this bug. ***
Comment 9 Sjoerd Mullender cwiconfidential 2020-10-16 10:19:36 CEST
*** Bug 6987 has been marked as a duplicate of this bug. ***
Comment 10 Sjoerd Mullender cwiconfidential 2020-10-16 10:19:50 CEST
*** Bug 6985 has been marked as a duplicate of this bug. ***
Comment 11 Pedro Ferreira 2020-10-16 18:58:54 CEST
I already found the root for this problem. Sometime later this month I will create a new branch to tune window functions to work on this and performance issues such as the one issued on bug 6959.
Comment 12 MonetDB Mercurial Repository cwiconfidential 2020-10-26 09:13:42 CET
Changeset 76805b818767, made by Pedro Ferreira <pedro.ferreira@monetdbsolutions.com> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=76805b818767

Changeset description:

	Bug 6997 has been fixed on this branch