Varying results when firing same query

GSQL
1

Trying to troubleshoot the following query

CREATE QUERY oc_many_hops(  
	VERTEX<entity> seed,
  INT hops,
  INT effective_date,
  INT confidence,
  INT ownership_percentage
) FOR GRAPH oc { 
	MapAccum<STRING, STRING> @@params;
  OrAccum<BOOL> @visited;
  ListAccum<EDGE> @@relationships;

  # Build WHERE condition
  STRING where_condition = "object != seed";

  IF effective_date IS NOT NULL THEN
    where_condition += " AND relationship.earliest_date < effective_date AND relationship.latest_date > effective_date";
    @@params += ("effective_date" -> datetime_format(epoch_to_datetime(effective_date), "%Y-%m-%d %H:%M:%S"));
  END;

  IF confidence IS NOT NULL THEN
    where_condition += " AND relationship.confidence >= confidence";
    @@params += ("confidence" -> to_string(confidence));
  END;

  IF ownership_percentage IS NOT NULL THEN
    where_condition += " AND relationship.ownership_percentage >= ownership_percentage";
    @@params += ("ownership_percentage" -> to_string(ownership_percentage));
  END;
  # Finished building WHERE condition

  vertices = {seed};


  where_condition += " AND object.@visited == false";

  WHILE vertices.size() > 0 LIMIT hops DO
  vertices =
  SELECT object
  FROM vertices:subject - (:relationship) -> :object
  WHERE EVALUATE(where_condition)
  ACCUM @@relationships += relationship, object.@visited = true;

  PRINT vertices;
  END;
  
  PRINT @@relationships;
  PRINT where_condition;

	
	
}

which would return expected results half of the time while the other half “relationship” accumulator returns empty. Parameters used are

seed: [entity id]
hops: 2
effective_date: NULL
confidence: NULL
ownership_percentage: NULL

Any idea what could be the issue?

Thanks.

2

Does it work if you use static inline WHERE clause (i.e. you write the where clause in for the specific case). I know this is not what you want, I’m just trying to isolate the problem.

3

Made this version of the query but same results

  CREATE QUERY oc_many_hops_troubleshoot(  
      VERTEX<entity> seed,
      INT hops,
      INT effective_date,
      INT confidence,
      INT ownership_percentage,
      SET<STRING> child_edge_types
    ) FOR GRAPH oc { 
    	MapAccum<STRING, STRING> @@params;
      OrAccum<BOOL> @visited;
      ListAccum<EDGE> @@relationships;

      vertices = {seed};

     


      WHILE vertices.size() > 0 LIMIT hops DO
      vertices =
      SELECT object
      FROM vertices:subject - (:relationship) -> :object
      WHERE object != seed AND object.@visited == false
      ACCUM @@relationships += relationship, object.@visited = true;

      PRINT vertices;
      END;
      

      PRINT @@relationships;
     

      # Output params for diagnostics
      @@params += ("seed_class" -> seed.class);
      @@params += ("seed_activerecord_id" -> to_string(seed.activerecord_id));
      @@params += ("hops" -> to_string(hops));

      print @@params;
    	
    	
    }
4

Another clue is, when trying a different starting company, I’m also getting varying results, but this time it’s either returning one hop worth of data, or two hops. Which means the problem is not “sometimes tg returns no data” but tg returns different results with the same query.

5

Do you have a small sample data and test case so that we can reproduce this issue in house?
If so, you can email me at mingxi.wu@tigergraph.com

We need the TigerGraph version as well.

thanks,

Mingxi

6

I think it is because you are setting object.@visited = true in the ACCUM clause, though I can 't decide what topologies might have that characteristic given that we can only have one edge between any two specific vertices. I can imagine two-hop variation (if there is a diamond pattern), but not one-hop.

Can you set it in POST-ACCUM? Let us know the result?

7

Thanks Rik, I tried moving @visited = true to the POST-ACCUM as well as getting rid of object.@visited == false condition all together, with results being the same every time. In the changelog we found this for 2.6 version:

Fix for 2.5.2 bug - Inconsistent query results when running non-distributed query on a cluster

“Inconsistent query results when running non-distributed query on a cluster” is exactly the problem we are seeing here I believe, although the changelog seems to suggest that the bug was introduced in 2.5.2 whereas we are on a 2.2.3 version. We are now looking to upgrade to 2.6.3 as the next step to see if that fixes the issue.

8

Aha. I forgot to ask what version you were running. OK, good luck. Let us know if that fixes it.