Potential problem using GUIDs as Primary IDs

GSQL
#1

It took me awhile to nail this one as I thought my data was bad or the db was corrupt.

I’m using GUIDs as my primary ID data types. I imported the data from csv files and started to test the queries. Occasionally a query would not return data when I thought it should. It turns out that some apps/databases convert the letters in GUIDs to lowercase but I imported them as uppercase. In my testing I grabbed some GUIDs from another DB where I know the proper relationships were in place and it should give me some good results.

So this value works:
85B32758-B347-4D42-B2B1-837831AC3E35

but this does not:
85b32758-b347-4d42-b2b1-837831ac3e35

and returns this error:
Failed to convert user vertex id for parameter inputVoter

My experience is that all apps and databases consider upper case and lower case GUIDs to be the same, but I’m guessing that TG sees this as a string and TG is case sensitive. It would be nice if TG could add a GUID data type to it’s list of types and then consider both upper and lower case GUIDs to be the same. If an application is interacting the data coming going from different sources its likely there will be both upper and lower case GUIDs in the mix.

#2

I’ll add it to our list of feature requests.

In the meantime you’ll note that the loader has an gsql_upper token function so you can load case independent strings if you want. Equivalently there is an upper() function to force query strings to upper case as well.

#3

Good tip. I’ll look into those functions.

#4

OK, looking into these, I’m not sure how it would be implemented and didn’t see any examples online. For example, for this query:

CREATE QUERY GetConceptItem_ByConcept(vertex<Concept> inputConcept) FOR GRAPH wtapp syntax v2 {
	# 479EE849-6DF8-4297-AE6B-62C2D6564055
Start = {inputConcept};
Items = SELECT t FROM Start:s-(Concept_Has_ConceptItem>:e)-ConceptItem:t;
PRINT Items;
}

I get a compile error if I try this:
Start = {upper(inputConcept)};

Looks like it will have to be handled outside of TG. Right?

Thank you.

#5

This will work:

CREATE QUERY GetConceptItem_ByConcept(STRING inputConceptId) FOR GRAPH wtapp syntax v2 {
	# 479EE849-6DF8-4297-AE6B-62C2D6564055
SetAccum <STRING> @@mySet;
@@mySet += upper(inputConceptId);
Start = to_vertex_set(@@mySet, "Concept");

Items = SELECT t FROM Start:s-(Concept_Has_ConceptItem>:e)-ConceptItem:t;
PRINT Items;
}
#6

Thanks rik! That workflow was not intuitive and I wouldn’t have come up with that on my own, but now looking at it, it makes sense.

Have a nice weekend and Happy 4th of July! Brits Welcome! :smiley: