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

40 views
Skip to first unread message

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
0 new messages