Changeset: d08dec29fb41 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d08dec29fb41
Modified Files:
sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.sql
sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.stable.out
Branch: Jun2020
Log Message:
Fix ordering so that row_number gives a consistent result.
diffs (280 lines):
diff --git a/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.sql
b/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.sql
--- a/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.sql
+++ b/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.sql
@@ -918,7 +918,7 @@ NULL NULL "XS0130574600" NULL "SUNDERLAN
"Supranational" NULL "XS0202407093" "EIB" NULL "N" NULL
NULL "12-Oct-2054 00:00:00" NULL "4.25 36" "AAA" NULL "N"
NULL NULL 4.203 4.357 NULL NULL 891
select *, rank() over (partition by "c2_h_field_2" order by "count_1" desc) as
"RANK", dense_rank() over (partition by "c2_h_field_2" order by "count_1" desc)
as "DRANK",
- row_number() over (partition by "c2_h_field_2" order by "count_1" desc)
as "RNUMBER"
+ row_number() over (partition by "c2_h_field_2" order by "count_1" desc,
"Ticker") as "RNUMBER"
from
(
select
diff --git a/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.stable.out
b/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.stable.out
--- a/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.stable.out
+++ b/sql/test/BugTracker-2019/Tests/rank-nan.Bug-6780.stable.out
@@ -104,136 +104,136 @@ stdout of test 'rank-nan.Bug-6780` in di
[ 5, NULL, "KFW", 2, 2, 3 ]
[ 5, NULL, "RBS", 2, 2, 4 ]
[ 4, NULL, "HSBC", 5, 3, 5 ]
-[ 3, NULL, "AIG", 6, 4, 8 ]
-[ 3, NULL, "BAA", 6, 4, 11 ]
-[ 3, NULL, "BRITLD", 6, 4, 9 ]
-[ 3, NULL, "BRK", 6, 4, 10 ]
-[ 3, NULL, "PRUFIN", 6, 4, 7 ]
-[ 3, NULL, "PUNTAV", 6, 4, 6 ]
-[ 2, NULL, "ABBEY", 12, 5, 30 ]
-[ 2, NULL, "BNG", 12, 5, 17 ]
-[ 2, NULL, "BPLN", 12, 5, 21 ]
-[ 2, NULL, "C", 12, 5, 14 ]
-[ 2, NULL, "CCCE", 12, 5, 26 ]
-[ 2, NULL, "CCE", 12, 5, 18 ]
-[ 2, NULL, "CRDSUI", 12, 5, 31 ]
-[ 2, NULL, "DCX", 12, 5, 24 ]
-[ 2, NULL, "EUROF", 12, 5, 27 ]
-[ 2, NULL, "GKFIN", 12, 5, 19 ]
-[ 2, NULL, "IBRD", 12, 5, 25 ]
-[ 2, NULL, "III", 12, 5, 15 ]
-[ 2, NULL, "LLOYDS", 12, 5, 28 ]
-[ 2, NULL, "PACLIF", 12, 5, 13 ]
-[ 2, NULL, "RWE", 12, 5, 29 ]
-[ 2, NULL, "SBRY", 12, 5, 16 ]
-[ 2, NULL, "TELSEC", 12, 5, 20 ]
-[ 2, NULL, "TSCO", 12, 5, 12 ]
-[ 2, NULL, "UKHOSP", 12, 5, 22 ]
-[ 2, NULL, "WSTP", 12, 5, 23 ]
-[ 1, NULL, "AAB", 32, 6, 131 ]
-[ 1, NULL, "AMPAU", 32, 6, 111 ]
-[ 1, NULL, "ANGELT", 32, 6, 70 ]
-[ 1, NULL, "ANGIRI", 32, 6, 50 ]
-[ 1, NULL, "AUTSTR", 32, 6, 120 ]
-[ 1, NULL, "AVLN", 32, 6, 48 ]
-[ 1, NULL, "AWLN", 32, 6, 35 ]
-[ 1, NULL, "BAC", 32, 6, 114 ]
-[ 1, NULL, "BACR", 32, 6, 46 ]
-[ 1, NULL, "BAPLC", 32, 6, 71 ]
-[ 1, NULL, "BATSLN", 32, 6, 97 ]
-[ 1, NULL, "BAVB", 32, 6, 51 ]
-[ 1, NULL, "BBC", 32, 6, 39 ]
-[ 1, NULL, "BIRMIN", 32, 6, 119 ]
-[ 1, NULL, "BKIR", 32, 6, 49 ]
-[ 1, NULL, "BOC", 32, 6, 104 ]
-[ 1, NULL, "BRADBI", 32, 6, 123 ]
-[ 1, NULL, "BRITEL", 32, 6, 133 ]
-[ 1, NULL, "BRITNA", 32, 6, 124 ]
-[ 1, NULL, "CADES", 32, 6, 63 ]
-[ 1, NULL, "CANWHA", 32, 6, 34 ]
-[ 1, NULL, "CARR", 32, 6, 94 ]
-[ 1, NULL, "CATTLE", 32, 6, 99 ]
-[ 1, NULL, "CBRY", 32, 6, 54 ]
-[ 1, NULL, "CENTRI", 32, 6, 92 ]
-[ 1, NULL, "CFC", 32, 6, 69 ]
-[ 1, NULL, "CHESTR", 32, 6, 64 ]
-[ 1, NULL, "CMZB", 32, 6, 61 ]
-[ 1, NULL, "COUK", 32, 6, 85 ]
-[ 1, NULL, "CSWINV", 32, 6, 53 ]
-[ 1, NULL, "CUMBE", 32, 6, 59 ]
-[ 1, NULL, "DMGOLN", 32, 6, 110 ]
-[ 1, NULL, "DT", 32, 6, 86 ]
-[ 1, NULL, "DXNSLN", 32, 6, 93 ]
-[ 1, NULL, "EBRD", 32, 6, 101 ]
-[ 1, NULL, "ELESM", 32, 6, 90 ]
-[ 1, NULL, "EOAGR", 32, 6, 37 ]
-[ 1, NULL, "FMNLN", 32, 6, 33 ]
-[ 1, NULL, "FRTEL", 32, 6, 118 ]
-[ 1, NULL, "FWFIN", 32, 6, 41 ]
-[ 1, NULL, "GESB", 32, 6, 108 ]
+[ 3, NULL, "AIG", 6, 4, 6 ]
+[ 3, NULL, "BAA", 6, 4, 7 ]
+[ 3, NULL, "BRITLD", 6, 4, 8 ]
+[ 3, NULL, "BRK", 6, 4, 9 ]
+[ 3, NULL, "PRUFIN", 6, 4, 10 ]
+[ 3, NULL, "PUNTAV", 6, 4, 11 ]
+[ 2, NULL, "ABBEY", 12, 5, 12 ]
+[ 2, NULL, "BNG", 12, 5, 13 ]
+[ 2, NULL, "BPLN", 12, 5, 14 ]
+[ 2, NULL, "C", 12, 5, 15 ]
+[ 2, NULL, "CCCE", 12, 5, 16 ]
+[ 2, NULL, "CCE", 12, 5, 17 ]
+[ 2, NULL, "CRDSUI", 12, 5, 18 ]
+[ 2, NULL, "DCX", 12, 5, 19 ]
+[ 2, NULL, "EUROF", 12, 5, 20 ]
+[ 2, NULL, "GKFIN", 12, 5, 21 ]
+[ 2, NULL, "IBRD", 12, 5, 22 ]
+[ 2, NULL, "III", 12, 5, 23 ]
+[ 2, NULL, "LLOYDS", 12, 5, 24 ]
+[ 2, NULL, "PACLIF", 12, 5, 25 ]
+[ 2, NULL, "RWE", 12, 5, 26 ]
+[ 2, NULL, "SBRY", 12, 5, 27 ]
+[ 2, NULL, "TELSEC", 12, 5, 28 ]
+[ 2, NULL, "TSCO", 12, 5, 29 ]
+[ 2, NULL, "UKHOSP", 12, 5, 30 ]
+[ 2, NULL, "WSTP", 12, 5, 31 ]
+[ 1, NULL, "AAB", 32, 6, 32 ]
+[ 1, NULL, "AMPAU", 32, 6, 33 ]
+[ 1, NULL, "ANGELT", 32, 6, 34 ]
+[ 1, NULL, "ANGIRI", 32, 6, 35 ]
+[ 1, NULL, "AUTSTR", 32, 6, 36 ]
+[ 1, NULL, "AVLN", 32, 6, 37 ]
+[ 1, NULL, "AWLN", 32, 6, 38 ]
+[ 1, NULL, "BAC", 32, 6, 39 ]
+[ 1, NULL, "BACR", 32, 6, 40 ]
+[ 1, NULL, "BAPLC", 32, 6, 41 ]
+[ 1, NULL, "BATSLN", 32, 6, 42 ]
+[ 1, NULL, "BAVB", 32, 6, 43 ]
+[ 1, NULL, "BBC", 32, 6, 44 ]
+[ 1, NULL, "BIRMIN", 32, 6, 45 ]
+[ 1, NULL, "BKIR", 32, 6, 46 ]
+[ 1, NULL, "BOC", 32, 6, 47 ]
+[ 1, NULL, "BRADBI", 32, 6, 48 ]
+[ 1, NULL, "BRITEL", 32, 6, 49 ]
+[ 1, NULL, "BRITNA", 32, 6, 50 ]
+[ 1, NULL, "CADES", 32, 6, 51 ]
+[ 1, NULL, "CANWHA", 32, 6, 52 ]
+[ 1, NULL, "CARR", 32, 6, 53 ]
+[ 1, NULL, "CATTLE", 32, 6, 54 ]
+[ 1, NULL, "CBRY", 32, 6, 55 ]
+[ 1, NULL, "CENTRI", 32, 6, 56 ]
+[ 1, NULL, "CFC", 32, 6, 57 ]
+[ 1, NULL, "CHESTR", 32, 6, 58 ]
+[ 1, NULL, "CMZB", 32, 6, 59 ]
+[ 1, NULL, "COUK", 32, 6, 60 ]
+[ 1, NULL, "CSWINV", 32, 6, 61 ]
+[ 1, NULL, "CUMBE", 32, 6, 62 ]
+[ 1, NULL, "DMGOLN", 32, 6, 63 ]
+[ 1, NULL, "DT", 32, 6, 64 ]
+[ 1, NULL, "DXNSLN", 32, 6, 65 ]
+[ 1, NULL, "EBRD", 32, 6, 66 ]
+[ 1, NULL, "ELESM", 32, 6, 67 ]
+[ 1, NULL, "EOAGR", 32, 6, 68 ]
+[ 1, NULL, "FMNLN", 32, 6, 69 ]
+[ 1, NULL, "FRTEL", 32, 6, 70 ]
+[ 1, NULL, "FWFIN", 32, 6, 71 ]
+[ 1, NULL, "GESB", 32, 6, 72 ]
[ 1, NULL, "GLHLN", 32, 6, 73 ]
-[ 1, NULL, "GSK", 32, 6, 68 ]
-[ 1, NULL, "GUITST", 32, 6, 43 ]
-[ 1, NULL, "GUSLN", 32, 6, 74 ]
-[ 1, NULL, "HARB", 32, 6, 40 ]
+[ 1, NULL, "GSK", 32, 6, 74 ]
+[ 1, NULL, "GUITST", 32, 6, 75 ]
+[ 1, NULL, "GUSLN", 32, 6, 76 ]
+[ 1, NULL, "HARB", 32, 6, 77 ]
[ 1, NULL, "HESLAN", 32, 6, 78 ]
-[ 1, NULL, "HGLN", 32, 6, 91 ]
-[ 1, NULL, "HIGHB", 32, 6, 122 ]
-[ 1, NULL, "HMSOLN", 32, 6, 127 ]
-[ 1, NULL, "HNZ", 32, 6, 32 ]
-[ 1, NULL, "HVB", 32, 6, 42 ]
-[ 1, NULL, "IADB", 32, 6, 77 ]
-[ 1, NULL, "ICI", 32, 6, 57 ]
-[ 1, NULL, "IMPTOB", 32, 6, 109 ]
-[ 1, NULL, "INVES", 32, 6, 62 ]
-[ 1, NULL, "JPM", 32, 6, 79 ]
-[ 1, NULL, "KEL", 32, 6, 84 ]
-[ 1, NULL, "KOMBNK", 32, 6, 81 ]
-[ 1, NULL, "KRB", 32, 6, 95 ]
-[ 1, NULL, "LAFCP", 32, 6, 96 ]
-[ 1, NULL, "LBW", 32, 6, 66 ]
-[ 1, NULL, "LEH", 32, 6, 80 ]
-[ 1, NULL, "MABLN", 32, 6, 134 ]
-[ 1, NULL, "MFCCN", 32, 6, 88 ]
-[ 1, NULL, "MGFUND", 32, 6, 83 ]
-[ 1, NULL, "MQB", 32, 6, 47 ]
-[ 1, NULL, "MRWLN", 32, 6, 56 ]
-[ 1, NULL, "NATS", 32, 6, 128 ]
-[ 1, NULL, "NIB", 32, 6, 112 ]
-[ 1, NULL, "NRBS", 32, 6, 113 ]
-[ 1, NULL, "NRWBK", 32, 6, 60 ]
-[ 1, NULL, "NTRS", 32, 6, 103 ]
-[ 1, NULL, "OKB", 32, 6, 132 ]
-[ 1, NULL, "ONT", 32, 6, 75 ]
-[ 1, NULL, "PEMEX", 32, 6, 98 ]
-[ 1, NULL, "PERMA", 32, 6, 87 ]
-[ 1, NULL, "PHFSEC", 32, 6, 125 ]
-[ 1, NULL, "PSON", 32, 6, 65 ]
-[ 1, NULL, "RABOBK", 32, 6, 72 ]
-[ 1, NULL, "RENTEN", 32, 6, 76 ]
-[ 1, NULL, "REPHUN", 32, 6, 100 ]
-[ 1, NULL, "RESFER", 32, 6, 45 ]
-[ 1, NULL, "RIOLN", 32, 6, 55 ]
-[ 1, NULL, "RMPA", 32, 6, 44 ]
-[ 1, NULL, "SCOHYD", 32, 6, 121 ]
-[ 1, NULL, "SCOTNB", 32, 6, 58 ]
-[ 1, NULL, "SEWLTD", 32, 6, 135 ]
-[ 1, NULL, "SLOU", 32, 6, 82 ]
-[ 1, NULL, "SMINLN", 32, 6, 105 ]
-[ 1, NULL, "SNCBPD", 32, 6, 115 ]
-[ 1, NULL, "SPIRIT", 32, 6, 102 ]
-[ 1, NULL, "STANLN", 32, 6, 52 ]
-[ 1, NULL, "STOIL", 32, 6, 130 ]
-[ 1, NULL, "TELENZ", 32, 6, 117 ]
-[ 1, NULL, "TLM", 32, 6, 107 ]
-[ 1, NULL, "TUSSAU", 32, 6, 116 ]
-[ 1, NULL, "TVA", 32, 6, 38 ]
-[ 1, NULL, "TWOD", 32, 6, 89 ]
-[ 1, NULL, "UBS", 32, 6, 126 ]
-[ 1, NULL, "VIEFP", 32, 6, 129 ]
-[ 1, NULL, "VOD", 32, 6, 67 ]
-[ 1, NULL, "WMT", 32, 6, 36 ]
-[ 1, NULL, "WSTFD", 32, 6, 106 ]
+[ 1, NULL, "HGLN", 32, 6, 79 ]
+[ 1, NULL, "HIGHB", 32, 6, 80 ]
+[ 1, NULL, "HMSOLN", 32, 6, 81 ]
+[ 1, NULL, "HNZ", 32, 6, 82 ]
+[ 1, NULL, "HVB", 32, 6, 83 ]
+[ 1, NULL, "IADB", 32, 6, 84 ]
+[ 1, NULL, "ICI", 32, 6, 85 ]
+[ 1, NULL, "IMPTOB", 32, 6, 86 ]
+[ 1, NULL, "INVES", 32, 6, 87 ]
+[ 1, NULL, "JPM", 32, 6, 88 ]
+[ 1, NULL, "KEL", 32, 6, 89 ]
+[ 1, NULL, "KOMBNK", 32, 6, 90 ]
+[ 1, NULL, "KRB", 32, 6, 91 ]
+[ 1, NULL, "LAFCP", 32, 6, 92 ]
+[ 1, NULL, "LBW", 32, 6, 93 ]
+[ 1, NULL, "LEH", 32, 6, 94 ]
+[ 1, NULL, "MABLN", 32, 6, 95 ]
+[ 1, NULL, "MFCCN", 32, 6, 96 ]
+[ 1, NULL, "MGFUND", 32, 6, 97 ]
+[ 1, NULL, "MQB", 32, 6, 98 ]
+[ 1, NULL, "MRWLN", 32, 6, 99 ]
+[ 1, NULL, "NATS", 32, 6, 100 ]
+[ 1, NULL, "NIB", 32, 6, 101 ]
+[ 1, NULL, "NRBS", 32, 6, 102 ]
+[ 1, NULL, "NRWBK", 32, 6, 103 ]
+[ 1, NULL, "NTRS", 32, 6, 104 ]
+[ 1, NULL, "OKB", 32, 6, 105 ]
+[ 1, NULL, "ONT", 32, 6, 106 ]
+[ 1, NULL, "PEMEX", 32, 6, 107 ]
+[ 1, NULL, "PERMA", 32, 6, 108 ]
+[ 1, NULL, "PHFSEC", 32, 6, 109 ]
+[ 1, NULL, "PSON", 32, 6, 110 ]
+[ 1, NULL, "RABOBK", 32, 6, 111 ]
+[ 1, NULL, "RENTEN", 32, 6, 112 ]
+[ 1, NULL, "REPHUN", 32, 6, 113 ]
+[ 1, NULL, "RESFER", 32, 6, 114 ]
+[ 1, NULL, "RIOLN", 32, 6, 115 ]
+[ 1, NULL, "RMPA", 32, 6, 116 ]
+[ 1, NULL, "SCOHYD", 32, 6, 117 ]
+[ 1, NULL, "SCOTNB", 32, 6, 118 ]
+[ 1, NULL, "SEWLTD", 32, 6, 119 ]
+[ 1, NULL, "SLOU", 32, 6, 120 ]
+[ 1, NULL, "SMINLN", 32, 6, 121 ]
+[ 1, NULL, "SNCBPD", 32, 6, 122 ]
+[ 1, NULL, "SPIRIT", 32, 6, 123 ]
+[ 1, NULL, "STANLN", 32, 6, 124 ]
+[ 1, NULL, "STOIL", 32, 6, 125 ]
+[ 1, NULL, "TELENZ", 32, 6, 126 ]
+[ 1, NULL, "TLM", 32, 6, 127 ]
+[ 1, NULL, "TUSSAU", 32, 6, 128 ]
+[ 1, NULL, "TVA", 32, 6, 129 ]
+[ 1, NULL, "TWOD", 32, 6, 130 ]
+[ 1, NULL, "UBS", 32, 6, 131 ]
+[ 1, NULL, "VIEFP", 32, 6, 132 ]
+[ 1, NULL, "VOD", 32, 6, 133 ]
+[ 1, NULL, "WMT", 32, 6, 134 ]
+[ 1, NULL, "WSTFD", 32, 6, 135 ]
#ROLLBACK;
# 18:12:32 >
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
