Is my database corrupted? SQL queries on same data returning different/odd results.

Jean-Sebastien Lemay

unread,

Sep 22, 2015, 11:33:45 PM9/22/15

to OrientDB

Using OrientDB 2.1.1 Community Edition

Situation:

I have "Channel" and "Account" vertices.
Users can join channels, and when they do, a "ChannelActiveUserAccount" edge is created between the two (from Channel to Account)
I'm trying to run queries to determine how many users are sitting in each channel, but I'm getting weird results

In this instance, I know for a fact that I only have 1 user (#15:0) sitting in one of my 5 channels. See query below.

SELECT
@rid,
out("ChannelActiveUserAccount")
FROM Channel

rid out

#31:0 []

#31:1 []

#31:2 #15:0

#31:3 []

#31:4 []

{

"result": [

{

"@type": "d",

"@rid": "#-2:0",

"@version": 0,

"rid": "#31:0",

"out": [

],

"@fieldTypes": "rid=x"

},

{

"@type": "d",

"@rid": "#-2:1",

"@version": 0,

"rid": "#31:1",

"out": [

],

"@fieldTypes": "rid=x"

},

{

"@type": "d",

"@rid": "#-2:2",

"@version": 0,

"rid": "#31:2",

"out": [

"#15:0"

],

"@fieldTypes": "rid=x,out=z"

},

{

"@type": "d",

"@rid": "#-2:3",

"@version": 0,

"rid": "#31:3",

"out": [

],

"@fieldTypes": "rid=x"

},

{

"@type": "d",

"@rid": "#-2:4",

"@version": 0,

"rid": "#31:4",

"out": [

],

"@fieldTypes": "rid=x"

}

],

"notification": "Query executed in 0.02 sec. Returned 5 record(s)"

}

----------

However, if I decide to return the size of each array, to get a total instead, this happens.

SELECT

@rid,

out("ChannelActiveUserAccount").size()

FROM Channel

rid out

#31:0 0

#31:1 0

#31:2 3

#31:3 0

#31:4 2

{

"result": [

{

"@type": "d",

"@rid": "#-2:0",

"@version": 0,

"rid": "#31:0",

"out": 0,

"@fieldTypes": "rid=x"

},

{

"@type": "d",

"@rid": "#-2:1",

"@version": 0,

"rid": "#31:1",

"out": 0,

"@fieldTypes": "rid=x"

},

{

"@type": "d",

"@rid": "#-2:2",

"@version": 0,

"rid": "#31:2",

"out": 3,

"@fieldTypes": "rid=x"

},

{

"@type": "d",

"@rid": "#-2:3",

"@version": 0,

"rid": "#31:3",

"out": 0,

"@fieldTypes": "rid=x"

},

{

"@type": "d",

"@rid": "#-2:4",

"@version": 0,

"rid": "#31:4",

"out": 2,

"@fieldTypes": "rid=x"

}

],

"notification": "Query executed in 0.02 sec. Returned 5 record(s)"

}

I'm not sure where the 3 and 2 are coming from. So I run another query below to see all the data.

----------

SELECT FROM Channel

@rid @class ChannelActiveUserAccount

#31:0 Channel []

#31:1 Channel []

#31:2 Channel #70:100 #70:103 #70:119

#31:3 Channel []

#31:4 Channel #70:101 #70:102

{

"result": [

{

"@type": "d",

"@rid": "#31:0",

"@class": "Channel",

"out_ChannelActiveUserAccount": [

],

"@fieldTypes": "out_ChannelActiveUserAccount=g"

},

{

"@type": "d",

"@rid": "#31:1",

"@class": "Channel",

"out_ChannelActiveUserAccount": [

],

"@fieldTypes": "out_ChannelActiveUserAccount=g"

},

{

"@type": "d",

"@rid": "#31:2",

"@class": "Channel",

"out_ChannelActiveUserAccount": [

"#70:100",

"#70:103",

"#70:119"

],

"@fieldTypes": "out_ChannelActiveUserAccount=g"

},

{

"@type": "d",

"@rid": "#31:3",

"@class": "Channel",

"out_ChannelActiveUserAccount": [

],

"@fieldTypes": "out_ChannelActiveUserAccount=g"

},

{

"@type": "d",

"@rid": "#31:4",

"@class": "Channel",

"out_ChannelActiveUserAccount": [

"#70:101",

"#70:102"

],

"@fieldTypes": "out_ChannelActiveUserAccount=g"

}

],

"notification": "Query executed in 0.026 sec. Returned 5 record(s)"

}

Now this is where I get confused, because some of the edges listed above (such as #70:101 for example) refer to edges that no longer exist. How come they are still in the array somehow? How did they not disappear?

----------

If it helps, here's the database creation script:

CREATE CLASS Channel EXTENDS V;

CREATE CLASS Account EXTENDS V;

CREATE CLASS ChannelActiveUserAccount EXTENDS E;

CREATE PROPERTY ChannelActiveUserAccount.out LINK Channel;

CREATE PROPERTY ChannelActiveUserAccount.in LINK Account;

Enrico Risa

unread,

Sep 23, 2015, 1:24:52 AM9/23/15

to orient-...@googlegroups.com

Hi Jean

how do you remove edges between Channel and Account?

--

---
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-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Jean-Sebastien Lemay

unread,

Sep 23, 2015, 1:40:34 AM9/23/15

to OrientDB, enric...@gmail.com

Through the Java API. Code simplified below.

// Get reference to GraphDB module
OrientGraph db = [...];

try {            
 // Retrieve the channel vertex
 OrientVertex channelVtx = this.getVertex(db,
 KEY_CHANNEL_ID,
 channelId);
 
 // Retrieve the account vertex
 OrientVertex accountVtx = this.getVertex(db, 
 KEY_ACCOUNT_ID,
 accountId);
 
 try {
 // Retrieve edge                
 Edge edge = channelVtx.getEdges(
 accountVtx,
 Direction.OUT, 
 EDGE_CHANNEL_ACTIVE_USER_ACCOUNT)
 .iterator().next();                

 // Remove edge
 db.removeEdge(edge);
 }
 catch (NoSuchElementException ex) {
 throw new DataNotFoundException();
 }
}
catch (OException | DataNotFoundException ex) {
 [...]
}

Andrey Lomakin

unread,

Sep 23, 2015, 6:33:56 AM9/23/15

to OrientDB, enric...@gmail.com

Hi,

This problem probably raised because of "cache optimization" issue.

We fixed it in 2.2 version.

Also I will port it in 2.1 branch.

Jean-Sebastien Lemay

unread,

Sep 23, 2015, 6:35:21 AM9/23/15

to OrientDB, enric...@gmail.com

Hi Andrey,

Thanks a lot for your answer. I will make sure to upgrade!

Reply all

Reply to author

Forward