clarifications for distinct() function [moved] #903

lvca opened this Issue Dec 10, 2012 · 2 comments


None yet
2 participants

lvca commented Dec 10, 2012

This is Issue 903 moved from a Google Code project.
Added by 2012-06-20T22:05:30.000Z by
Please review that bug for more context and additional comments, but update this bug.

Original labels: Type-Defect, Priority-Medium, Release

Original description

Hi, just to report what I think is a misbehaviour in distinct() function. Currently distinct() is defined per projection (maybe intended to be applied to queries with an unique projection). This issue applies to OrientDB 1.1.SNAPSHOT.

My argument is that it should be a function to apply (e.g. as flatten) to the resulting set of records, rather than a single projection or alternatively a distinct(proj1, proj2...) and a distinct(proj). It would be good having a mechanism (similar to functions mechanism for projections) to use other functions applying to the whole of resulting records (as flatten does) rather than to a single collection. At the moment flatten behaviour is hardcoded. Would be possible to generalize it to other functions()?

An example, for ouput: 

    Continent   Country     City
    Europe      Germany     Berlin
    Europe      Italy       Rome

if we do select distinct(Continent) as continent, Country, City from.......... which record should be returned?

At the moment, the result is:

Continent   Country             City
Europe      Germany             Berlin
null        Italy               Rome

which is not correct. This is causing me problems in some of my queries. As a workaround it should be possible to use:

..............where projectionValue IS NOT NULL to remove the unwanted records.

But could not make it work yet.

It has been reported in group in this thread:!topic/orient-database/3d-c50Nmjik

A sample code is:

OGraphDatabase database = new OGraphDatabase("remote:localhost/onto");"admin", "admin");

ODocument playerDoc1 = database.createVertex().field("name", "Fernando").field("size", "23").save();        
ODocument teamDoc1 = database.createVertex().field("team", "Chelsea").field("country", "UK").save();
ODocument teamDoc2 = database.createVertex().field("team", "Liverpool").field("country", "UK").save();
database.createEdge(playerDoc1, teamDoc1).field("role", "player").save();
database.createEdge(playerDoc1, teamDoc2).field("role", "player").save();

//String subquery = "select in[role='player'] as player from V where (team = 'Chelsea' or team = 'Liverpool')";
String subquery = "select distinct(country, team) as country from V where (team = 'Chelsea' or team = 'Liverpool')";
List<ODocument> result = database.query(new OSQLSynchQuery<ODocument>(subquery));
for (int i = 0; i < result.size(); i++)
    System.out.println(&quot;player: &quot; + result.get(i));



Current output is:

    player: #-2:0{distinct:UK,country:Liverpool} v0
    player: #-2:1{country:Chelsea} v0

and expected output should be either:

* for a distinct(proj1, proj2, proj3):
    player: #-2:0{country:UK,country:Liverpool} v0
    player: #-2:1{country:UK,country:Chelsea} v0
(only apply distinct if all values in the projection match).

* for a distinct(proj1), proj2, proj3 query:
    player: #-2:0{distinct:UK,country:Liverpool} v0

In this second case, can you figure out any mechanism/query syntax to allow user to specify which records to keep and which records to remove?


@lvca lvca added the enhancement label Oct 3, 2014

This comment has been minimized.

Show comment Hide comment

sebastiandev Dec 9, 2015

+1 I'm experiencing the same with 2.1.5

+1 I'm experiencing the same with 2.1.5

@lvca lvca closed this Aug 3, 2017

This comment has been minimized.

Show comment Hide comment

lvca Aug 3, 2017


OrientDB 3.0 has a regular DISTINCT keyword. Not relevant anymore. Closing it.


lvca commented Aug 3, 2017

OrientDB 3.0 has a regular DISTINCT keyword. Not relevant anymore. Closing it.

@lvca lvca added the wontfix label Aug 3, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment