Bugs item #2839602, was opened at 2009-08-18 13:01
Message generated for change (Settings changed) made by nielsnes
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2839602&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL "stable"
>Status: Closed
>Resolution: Wont Fix
Priority: 5
Private: No
Submitted By: Stefan de Konink (skinkie)
Assigned to: Niels Nes (nielsnes)
Summary: SQL: distinct in rank query causes troubles
Initial Comment:
create table kvk (kvks integer);
I have filled it with about 2.4mil rows.
Executing the following query fails:
select distinct rank() over (order by kvks) as rank_number, kvks from (select
kvks from kvk) as x limit 10;
Trace:
>sql.start();
>>trace select distinct rank() over (order by kvks) as rank_number, kvks from
>(select kvks from kvk) as x limit 10;
[ 9457 usec mdb.setTimer(true); ]
[ 12 usec @0 mdb.setThread(true); ]
[ 31 usec @0 barrier _69 := language.dataflow(); ]
[ 144 usec @0 _2:bat[:oid,:int] <tmp_2652>[2414680] :=
sql.bind("sys","kvk","kvks",0); ]
[ 51 usec @0 _7:bat[:oid,:int] <tmp_5163>[0] :=
sql.bind("sys","kvk","kvks",2); ]
[ 377 usec @0 _9<tmp_5506>[2414680] :=
algebra.kdifference(_2=<tmp_2652>:bat[:oid,:int][2414680],_7=<tmp_5163>:bat[:oid,:int][0]);
]
[ 15 usec @0 _2:bat[:oid,:int] := nil:BAT; ]
[ 23 usec @0 _10<tmp_5507>[2414680] :=
algebra.kunion(_9=<tmp_5506>[2414680],_7=<tmp_5163>:bat[:oid,:int][0]); ]
[ 18 usec @0 _9 := nil:BAT; ]
[ 2 usec @0 _7:bat[:oid,:int] := nil:BAT; ]
[ 91 usec @0 _11:bat[:oid,:int] <tmp_5164>[0] :=
sql.bind("sys","kvk","kvks",1); ]
[ 28 usec @0 _13<tmp_5506>[2414680] :=
algebra.kunion(_10=<tmp_5507>[2414680],_11=<tmp_5164>:bat[:oid,:int][0]); ]
[ 8 usec @0 _10 := nil:BAT; ]
[ 2 usec @0 _11:bat[:oid,:int] := nil:BAT; ]
[ 13 usec @0 _14:bat[:oid,:oid] <tmp_5166>[0] :=
sql.bind_dbat("sys","kvk",1); ]
[ 130 usec @0 _15<tmpr_5166>[0] :=
bat.reverse(_14=<tmp_5166>:bat[:oid,:oid][0]); ]
[ 3 usec @0 _14:bat[:oid,:oid] := nil:BAT; ]
[ 14 usec @0 _16<tmp_5507>[2414680] :=
algebra.kdifference(_13=<tmp_5506>[2414680],_15=<tmpr_5166>[0]); ]
[ 6 usec @0 _13 := nil:BAT; ]
[ 2 usec @0 _15 := nil:BAT; ]
[ 622740 usec @0 _17<tmpr_5506>[2414680] :=
algebra.sortTail(_16=<tmp_5507>[2414680]); ]
[ 2582255 usec @0 return rank_grp<tmp_5510>[2414680] :=
sql.rank(b=<tmpr_5506>[2414680]); ]
[ 2582584 usec @0 _18:bat[:oid,:int] <tmp_5510>[2414680] :=
batcalc.rank_grp(_17=<tmpr_5506>[2414680]); ]
[ 44 usec @0 _17 := nil:BAT; ]
[ 1658133 usec @0 (ext23<tmp_5511>[1916813],grp21<tmp_5512>[2414680]) :=
group.new(_18=<tmp_5510>:bat[:oid,:int][2414680]); ]
Explain:
explain select distinct rank() over (order by kvks) as rank_number, kvks from
(select kvks from kvk) as x limit 10;
+---------------------------------------------------------------------+
| function user.s1_1{autoCommit=true}():void; |
| barrier _69 := language.dataflow(); |
| _2:bat[:oid,:int] := sql.bind("sys","kvk","kvks",0); |
| _7:bat[:oid,:int] := sql.bind("sys","kvk","kvks",2); |
| _9 := algebra.kdifference(_2,_7); |
| _2:bat[:oid,:int] := nil:BAT; |
| _10 := algebra.kunion(_9,_7); |
| _9 := nil:BAT; |
| _7:bat[:oid,:int] := nil:BAT; |
| _11:bat[:oid,:int] := sql.bind("sys","kvk","kvks",1); |
| _13 := algebra.kunion(_10,_11); |
| _10 := nil:BAT; |
| _11:bat[:oid,:int] := nil:BAT; |
| _14:bat[:oid,:oid] := sql.bind_dbat("sys","kvk",1); |
| _15 := bat.reverse(_14); |
| _14:bat[:oid,:oid] := nil:BAT; |
| _16 := algebra.kdifference(_13,_15); |
| _13 := nil:BAT; |
| _15 := nil:BAT; |
| _17 := algebra.sortTail(_16); |
| _18:bat[:oid,:int] := batcalc.rank_grp(_17); |
| _17 := nil:BAT; |
| (ext23,grp21) := group.new(_18); |
| (ext26,grp24) := group.derive(ext23,grp21,_16); |
| grp24 := nil:BAT; |
| ext23 := nil:BAT; |
| grp21 := nil:BAT; |
| _23 := bat.mirror(ext26); |
| ext26 := nil:BAT; |
| _24 := algebra.join(_23,_18); |
| _18:bat[:oid,:int] := nil:BAT; |
| _25 := algebra.slice(_24,0,9); |
| _24 := nil:BAT; |
| _26 := algebra.join(_23,_16); |
| _23 := nil:BAT; |
| _16 := nil:BAT; |
| _27 := algebra.semijoin(_26,_25); |
| _26 := nil:BAT; |
| exit _69; |
| _28 := sql.resultSet(2,1,_25); |
| sql.rsColumn(_28,".","rank_number","int",32,0,_25); |
| _25 := nil:BAT; |
| sql.rsColumn(_28,"sys.x","kvks","int",32,0,_27); |
| _27 := nil:BAT; |
| _36 := io.stdout(); |
| sql.exportResult(_36,_28); |
| end s1_1; |
+---------------------------------------------------------------------+
Plan:
plan select distinct rank() over (order by kvks) as rank_number, kvks from
(select kvks from kvk) as x limit 10;
+------------------------------------------------------------------------------+
| top N ( |
| | distinct project ( |
| | | project ( |
| | | | table(sys.kvk) [ kvk.kvks, kvk.%TID% NOT NULL ] |
| | | ) [ kvk.kvks as x.kvks, kvk.%TID% NOT NULL ] |
| | ) [ rank(x.kvks ASC) as rank_number, x.kvks ] |
| ) [ 10 ] |
+------------------------------------------------------------------------------+
----------------------------------------------------------------------
>Comment By: Niels Nes (nielsnes)
Date: 2009-11-22 21:24
Message:
The generated code is correct. We did fix a problem in grp_mark recently
which probably improved the performance
of the distinct (grp.derive) statement. As the test is large we cannot add
this to the nightly testing.
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2009-09-20 19:14
Message:
This bug can only be reproduced using the data.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2839602&group_id=56967
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
