Bugs item #2839602, was opened at 2009-08-18 13:01
Message generated for change (Comment added) 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: Open
Resolution: None
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-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
------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
