Query gives inconsistent result

[YuanchengJiang]

Version: 2.13.0

For MATCH (s0)-[]-(s0) RETURN count(s0), I would expect the result should be 4. However, it outputs 4116, which is the result of MATCH p=(s0)-[]-(s1) RETURN count(s0)

Correct:

postgres=# MATCH p=(s0)-[]-(s0) RETURN count(s0);
 count 
-------
 4
(1 row)

Correct:

postgres=# MATCH (s0)-[]-(s1) WHERE s0=s1 RETURN count(s0);
 count 
-------
 4
(1 row)

Wrong:

postgres=# MATCH (s0)-[]-(s0) RETURN count(s0);
 count 
-------
 4116
(1 row)

Correct:

postgres=# MATCH (s0)-[]-(s1) RETURN count(s0);
 count 
-------
 4116
(1 row)

Correct Query Plan:

postgres=# EXPLAIN MATCH p=(s0)-[]-(s0) RETURN count(s0);
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Aggregate  (cost=117.97..117.98 rows=1 width=32)
   ->  Hash Join  (cost=85.84..117.69 rows=110 width=63)
         Hash Cond: (s0.id = ag_edge.start)
         ->  Append  (cost=0.00..27.00 rows=1001 width=63)
               ->  Seq Scan on ag_vertex s0_1  (cost=0.00..0.00 rows=1 width=46)
               ->  Seq Scan on person s0_2  (cost=0.00..3.67 rows=167 width=63)
               ->  Seq Scan on director s0_3  (cost=0.00..3.64 rows=164 width=63)
               ->  Seq Scan on movie s0_4  (cost=0.00..3.76 rows=176 width=63)
               ->  Seq Scan on genre s0_5  (cost=0.00..3.73 rows=173 width=63)
               ->  Seq Scan on buyer s0_6  (cost=0.00..3.68 rows=168 width=63)
               ->  Seq Scan on actor s0_7  (cost=0.00..3.52 rows=152 width=63)
         ->  Hash  (cost=85.56..85.56 rows=22 width=16)
               ->  Append  (cost=0.00..85.56 rows=22 width=16)
                     ->  Seq Scan on ag_edge  (cost=0.00..0.00 rows=1 width=16)
                           Filter: (start = "end")
                     ->  Seq Scan on rated ag_edge_1  (cost=0.00..11.84 rows=3 width=16)
                           Filter: (start = "end")
                     ->  Seq Scan on in_genre ag_edge_2  (cost=0.00..10.39 rows=3 width=16)
                           Filter: (start = "end")
                     ->  Seq Scan on directed ag_edge_3  (cost=0.00..10.25 rows=2 width=16)
                           Filter: (start = "end")
                     ->  Seq Scan on acted_in ag_edge_4  (cost=0.00..10.25 rows=2 width=16)
                           Filter: (start = "end")
                     ->  Seq Scan on ag_edge ag_edge_5  (cost=0.00..0.00 rows=1 width=16)
                           Filter: ("end" = start)
                     ->  Seq Scan on rated ag_edge_6  (cost=0.00..11.84 rows=3 width=16)
                           Filter: ("end" = start)
                     ->  Seq Scan on in_genre ag_edge_7  (cost=0.00..10.39 rows=3 width=16)
                           Filter: ("end" = start)
                     ->  Seq Scan on directed ag_edge_8  (cost=0.00..10.25 rows=2 width=16)
                           Filter: ("end" = start)
                     ->  Seq Scan on acted_in ag_edge_9  (cost=0.00..10.25 rows=2 width=16)
                           Filter: ("end" = start)
(33 rows)

Wrong Query Plan:

postgres=# EXPLAIN MATCH (s0)-[]-(s0) RETURN count(s0);
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Aggregate  (cost=106.05..106.06 rows=1 width=32)
   ->  Append  (cost=0.00..95.75 rows=4118 width=8)
         ->  Seq Scan on ag_edge  (cost=0.00..0.00 rows=1 width=8)
         ->  Seq Scan on rated ag_edge_1  (cost=0.00..10.47 rows=547 width=8)
         ->  Seq Scan on in_genre ag_edge_2  (cost=0.00..9.11 rows=511 width=8)
         ->  Seq Scan on directed ag_edge_3  (cost=0.00..9.00 rows=500 width=8)
         ->  Seq Scan on acted_in ag_edge_4  (cost=0.00..9.00 rows=500 width=8)
         ->  Seq Scan on ag_edge ag_edge_5  (cost=0.00..0.00 rows=1 width=8)
         ->  Seq Scan on rated ag_edge_6  (cost=0.00..10.47 rows=547 width=8)
         ->  Seq Scan on in_genre ag_edge_7  (cost=0.00..9.11 rows=511 width=8)
         ->  Seq Scan on directed ag_edge_8  (cost=0.00..9.00 rows=500 width=8)
         ->  Seq Scan on acted_in ag_edge_9  (cost=0.00..9.00 rows=500 width=8)
(12 rows)

[emotionbug]

Thanks for reporting.

When using the same Alias, these bugs seem to occur. We will investigate this issue.

CREATE ({id: 1})-[:e1]->({id: 2})-[:e1]->({id: 3})-[:e1]->({id: 4})
RETURN *;
        ?column?         |      ?column?      |        ?column?         |      ?column?      |        ?column?         |      ?column?      |        ?column?         
-------------------------+--------------------+-------------------------+--------------------+-------------------------+--------------------+-------------------------
 ag_vertex[1.1]{"id": 1} | e1[4.1][1.1,1.2]{} | ag_vertex[1.2]{"id": 2} | e1[4.2][1.2,1.3]{} | ag_vertex[1.3]{"id": 3} | e1[4.3][1.3,1.4]{} | ag_vertex[1.4]{"id": 4}
(1 row)

MATCH (a {id: 1}), (b {id: 1})
CREATE (b)-[:e1]->(a)
RETURN *;
            a            |            b            |      ?column?      
-------------------------+-------------------------+--------------------
 ag_vertex[1.1]{"id": 1} | ag_vertex[1.1]{"id": 1} | e1[4.4][1.1,1.1]{}
(1 row)

MATCH (a)
RETURN *;
            a            
-------------------------
 ag_vertex[1.1]{"id": 1}
 ag_vertex[1.2]{"id": 2}
 ag_vertex[1.3]{"id": 3}
 ag_vertex[1.4]{"id": 4}
(4 rows)

MATCH (a)-[]-(a) RETURN *;
            a            
-------------------------
 ag_vertex[1.1]{"id": 1}
 ag_vertex[1.1]{"id": 1}
 ag_vertex[1.1]{"id": 1}
 ag_vertex[1.2]{"id": 2}
 ag_vertex[1.2]{"id": 2}
 ag_vertex[1.3]{"id": 3}
 ag_vertex[1.3]{"id": 3}
 ag_vertex[1.4]{"id": 4}
(8 rows)

MATCH p=(a)-[]-(a) RETURN *;
            a            |                                  p                                   
-------------------------+----------------------------------------------------------------------
 ag_vertex[1.1]{"id": 1} | [ag_vertex[1.1]{"id": 1},e1[4.4][1.1,1.1]{},ag_vertex[1.1]{"id": 1}]
 ag_vertex[1.1]{"id": 1} | [ag_vertex[1.1]{"id": 1},e1[4.4][1.1,1.1]{},ag_vertex[1.1]{"id": 1}]
(2 rows)

[emotionbug]

This issue was fixed in #599. That patch merge in next release.