Interesting. I just tried in Studio and got the wrong result again: http://i.imgur.com/M76IeWP.png
But looking closer, I noticed that your screenshot indicates that you inserted Item B *after* you inserted Items C and D. (the @rid of Item B is #59:3 whereas the others are #59:1 and #59:2) I didn't see why this should matter, but I tried inserting them in a different order just to see what would happen. And now I'm seeing the same result you saw -- Bag 2 is returned. This seems odd, but if you consider what's probably happening behind the scenes, it makes sense: The second record encountered (offset 1) in the pre-distinct result set is Item B, because the cluster is being scanned in order. On Fri, Oct 10, 2014 at 2:52 AM, Hung Tran <tdhun...@gmail.com> wrote: > Hi, > > I have tried your case, the result is correct as expected > > See the result here: http://prntscr.com/4uqfmm > > <http://i.imgur.com/uLrR8r5.png> > > > On Thursday, October 9, 2014 9:28:27 PM UTC+7, Chris Wilper wrote: >> >> Hi all, >> >> I was doing some testing with the distinct function and paging and had a >> surprising result. It appears that the OFFSET is evaluated before the >> distinct-limited selection, which make paging distinct results a challenge. >> >> To be concrete, given the following: >> >> -- >> create database memory:test >> >> create class Bag >> create class Item >> >> insert into Bag (name) values ('Bag 1') >> insert into Item (name, bag) values ('Item A', #11:0) >> insert into Item (name, bag) values ('Item B', #11:0) >> >> insert into Bag (name) values ('Bag 2') >> insert into Item (name, bag) values ('Item C', #11:1) >> insert into Item (name, bag) values ('Item D', #11:1) >> -- >> >> ...if I do a query like this: >> >> -- >> select distinct(bag.name) from Item limit 1 offset 1 >> -- >> >> ...I get "Bag 1" instead of "Bag 2". I tried this in both Orient 1.7.7 >> and 1.7.9 with the same result. This is different than the behavior that I >> would expect from a traditional RDBMS[1], and presents a problem for paging >> through distinct results, so I wanted to find out: >> >> 1) Is this by design, or a bug? >> >> 2) Regardless, are there any workarounds for this? One idea I thought of >> was to have my code do the skipping itself for each page. This is >> inefficient, but maybe workable. The only problem I see with this approach >> is that the only query method I know of in the public Java API >> (ODatabaseComplex.query) forces me to get the entire result of the query >> into a List. This is fine when I can depend on Orient to do the paging for >> me, as I can request a reasonable chunk at a time, but if I'm doing it >> myself I need either an iterator or a callback mechanism so I don't have to >> force the sum of all pages to be read into memory. >> >> Thanks, >> Chris >> >> [1] For example, if you're running MySQL, an analogous set of DDL+DMS is: >> >> -- >> create table Bag ( >> id int auto_increment primary key, >> name varchar(20) >> ); >> create table Item ( >> id int auto_increment primary key, >> name varchar(20), >> bag int, >> foreign key (bag) references Bag(id) >> ); >> >> insert into Bag (name) values ('Bag 1'); >> insert into Item (name, bag) values ('Item A', 1); >> insert into Item (name, bag) values ('Item B', 1); >> >> insert into Bag (name) values ('Bag 2'); >> insert into Item (name, bag) values ('Item C', 2); >> insert into Item (name, bag) values ('Item D', 2); >> -- >> >> ..and an analogous query is: >> >> -- >> select distinct *Bag.name* >> from Item, Bag >> where Item.bag = *Bag.id* >> limit 1 offset 1 >> -- >> >> Note: you can copy + paste the above at sqlfiddle.com to try it >> yourself. You should get 'Bag 2'. >> > -- > > --- > You received this message because you are subscribed to the Google Groups > "OrientDB" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to orient-database+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
