Why does this two queries produce "inconsistent" results?

[lingvisa]

match (m:Product) return count(m)

And

MATCH p=(m:Product)-[r:competitiveProduct]->(:Product) 
RETURN distinct count(m)

The 1st generates 84749 and the 2nd generates 123039. I thought the two numbers should be the same or the 2nd number should be equal or less than the first. However, the 2nd is much larger. Why is that?

[duane]

I have experienced this in the past. I am not sure of your environment, but one possible suggestion is that the actual Database itself changed. I ran two queries that were identical, yet produced different results. After fretting over my cypher for a few hours, I finally narrowed down the cause to the fact one of my colleagues had updated the DB itself. Not saying that is what happened to you, but you may want to rule it out.

[lingvisa]

@duane That's not happening with my case. I ran the two queries almost the same time.

[dana_canzano]

@lingvisa

the 2 queries are very different

the first simply returns the number of :Product nodes. For example f I run

unwind range (1,1000) as x create (n:Product {id:x});

this creates 1000 :Product nodes and thus a

match (n:Product ) return count(n);

will return 1000.

However

MATCH p=(m:Product)-[r:competitiveProduct]->(:Product) 
RETURN distinct count(m);

requires a :Product node must have a :competitveProduct relationship.
So if all I do is run the unwind as above then the 1st statement will return a value of 1000 and the second will return a value of 0 but this is expected since the queries are very different

[lingvisa]

lingvisa:

The 1st generates 84749 and the 2nd generates 123039. I thought the two numbers should be the same or the 2nd number should be equal or less than the first. However, the 2nd is much larger.

@dana.canzano You are right, and that's why I said the result count for the 2nd should be = or less then the first one, but my queries shows the opposite:

"The 1st generates 84749 and the 2nd generates 123039. I thought the two numbers should be the same or the 2nd number should be equal or less than the first. However, the 2nd is much larger."

[dana_canzano · Accepted solution]

@lingvisa

as you initially stated

I thought the two numbers should be the same or the 2nd number should be equal or less than the first.

well they can not be the same or at least my example above demonstrates why they are not the same.

But also as to why the 2nd is larger than the first and using a new database and under 4.2.3 if I run

// insure the database is empty
match (n) detach delete n;


// populate some data
create (n:Product {id:1});
create (n:Product {id:2});
create (n:Product {id:3});
match (n:Product {id:1}), (n2:Product {id:2}) create (n)-[:competitiveProduct]->(n2);
match (n:Product {id:1}), (n2:Product {id:3}) create (n)-[:competitiveProduct]->(n2);
match (n:Product {id:2}), (n2:Product {id:3}) create (n)-[:competitiveProduct]->(n2);
match (n:Product {id:3}), (n2:Product {id:1}) create (n)-[:competitiveProduct]->(n2);
match (n:Product {id:2}), (n2:Product {id:1}) create (n)-[:competitiveProduct]->(n2);

// now run the 2 queries in question
match (n:Product) return count(n);

MATCH p=(m:Product)-[r:competitiveProduct]->(:Product)
RETURN distinct count(m);

this results in output of

count(n)
3
count(m)
5

maybe the next question is what is the question you are attempting to ask of the database and via a cypher statement.

The first cypher you provided simply provides the number of :Product nodes.

also, your initial 2nd cypher should probably be

return count( distinct m);

[lingvisa]

@dana.canzano Your examples explain the issue well! The relationship could be both ways and that's why it can larger. Thanks.