Entity Selection Query Null UUID

In my application I routinely set the UUID of a foreign key for a new record to an inter-process variable <>vsNullUUID where

<>vsNullUUID:= “00000000000000000000000000000000”

If I run this query on an entity selection it does not work if I include the search for the ID

$sel:=comp.DogsToCompany.query(“Gender = :1 and LitterID = :2”;“Male”;<>vsNullUUID)

If I omit LitterID search works and also work if I choose a different field.

In the debugger you can see the entities where the litterID is 00000000000000000000000000000000

Is this a valid query?
Paul

depends whom you ask.

I say no, but I know that some others have a different view.

In ORDA, a not assigned field (attribute) has the value NULL. Null means, no value.
For a number, this is not 0, this is NULL.

Using classic language, you can setup the structure to automatically assign an “empty” value to a NULL value. So a numeric NULL, even stored as NULL, is displayed as 0.

For an UUID, which is in fact a 128 bit longint, classic language displayes a string with 32 * “0”.

Still, internally it is stored as NULL.

So with ORDA, you need to search for NULL, not for the classic representation of NULL.

Ok but just to be clear I am not using classic to assign an empty value I am assigning the string to the ID so the query should work ?

$dog:=ds.Dogs.new()
$dog.LitterID:=<>vsNullUUID
$dog.save()

comp.reload()

$sel:=comp.DogsToCompany.query(“Gender = :1 and LitterID = :2”;“Male”;<>vsNullUUID)

Looks like this is the issue

$sel:=comp.DogsToCompany.query(“Gender = :1 and LitterID = :2”;“Male”;Null)

“When you look for null values, you cannot use the placeholder syntax because the query engine considers null as an unexpected comparison value.”

However this is not working.
$sel:=comp.DogsToCompany.query(“LitterID = null”)

This is working
$sel:=comp.DogsToCompany.query(“Gender = Male”)

an UUID is supposed to be an unique number.
So assigning to many records the same ID is possible, but this is against the concept of an unique ID.

If you create a new record with ORDA and don’t assign a field, it stays NULL.

what is “comp”?
Is that a record with a relation named “DogsToCompany” and you want to search through the relation?

To understand your structure, when you do a query:
$ent:=ds.Dogs.query(“LitterID=null”)
is $ent.length 0 or # 0?

If 0, in structure, when you double click the field LitterID of table Dogs, is the checkbox “assign empty value to NULL values” enabled or disabled?

$nError:=$sel.length

Yes the query on Dogs returns 0 in selection and length of 0

In the structure assign empty to null is enabled. This is the foreign key not the primary key so non unique just UUID format.

At moment I am not assigning a value to LitterID.

Yes, comp is the parent entity from record in [Company] with relation to Dogs via DogsToCompany

$sel:=ds.Dogs.query(“LitterID=null”) // nothing found

$nError:=$sel.length // lenght is zero

$sel:=comp.DogsToCompany // finds 9
$sel:=$sel.query(“LitterID=null”) finds zero

I have turned off the Map Null values for the field in structure and it now works but does not find previous. In the debugger LitterID is now shown as null. Which makes sense. Previously in the debugger 32*0 was displayed. Just confirming I did not assign a value to LitterID.

Therefore it looks like if you have map null values set in the structure then you cannot query by null. Is that a bug or a feature ?

Hi Thomas,

I agree on UUID is unique and mandatory for primary keys.

Different story for foreign keys. Foreign keys are not always mandatory and searching for foreign key with Null values (or equivalent) is not simple (I have met many ways of having what is is essence a null foreign key. It needs to be considered careful to have a consistent code (should it be enforced in triggers ?-.

For this reason (and also it is easier to manage for humans for things like human “copy” when debugging or in the search editor, etc…), I stick with longint as primary keys. Also longint sequence is ordered (which is a bonus sometimes). When I will need a distributed system uuid as PK will be an obvious choice.

that’s the way it is supposed to work. Mapping to “empty” values was done when SQL Server was added with 4D v11 to make usage of NULL values easier for classic language.

I once talked with a customer which used a kind of “strange looking” solution to work around that. He did not wanted to use NULL, but he wanted to have a “no foreign key assigned” identifier.

So he used his own UUID identifier, I think it was something like 0101010… as value.
This value was assigned in the trigger if the field was empty.
To search not assigned values, he searched for 0101…
If a record was 00000 it was a bug in his code (missing assignment).

I respect your choice, but myself started to prefer UUIDs as identifier. For me benefits are bigger than disadvantages.

Searching for NULL works for me, but I agree that coding is somethings uncomfortable and code becomes difficult to read. In internal tests I started to rewrite that now as extended data class functions (see LR’s session for Summit 2020), which allows to hide the code in a simple to use function (let’s say car inventory on stock not sold yet, so owner ID is NULL, then simply write cars.unsold()
Today I would need to know how to search that, knowing the internal structure and used values.

Regarding humans: I learned never to display my ID’s to humans. I learned that “what they see is what they want to change”. It does not matter if that is logical or not, they want to change.
PKs are never to be changed, no way, so that means for me, never display them, always hide, use additional “unique identifiers” for humans, such as invoice ID. But again, that’s just my personal thinking, not company guidelines, not related to 4D.

1 Like

Hi

In order to save my sanity with UUID formatted foreign keys, a long time ago we decided to always populate all FK UUIDs with “” rather than leaving them NULL (we do this generically in single method called from all table triggers). 4D represents these in the language as “2020202020…” (I think)

We also have the “Map NULL to blank values” set ON for every field (this was the default on conversion I think, don’t know about the default for new tables)

It’s probably less optimal to have blank UUIDs rather than NULL ones, but if we need to query for blank UUIDs it’s a simple case of querying for “”.

Hope that helps.

Keith

Hello Ketih,

That is what we have always done except we use 32*0 instead of empty string. However we definitely have an anomaly and I have tested this morning with an empty string as well. If you set the UUID field with traditional and query for “” you get the correct results. See below with map null enabled and LitterID:="" set in traditional

$sel:=comp.DogsToCompany.query(“Gender = :1 and LitterID = :2”;“Male”;"") finds 9

QUERY([Dogs];[Dogs]LitterID="") finds 9

$sel:=ds.Dogs.query(“LitterID=null”) finds 0 thats correct

$sel:=ds.Dogs.query(“LitterID=’’”) finds 9

However instead of using

[Dogs]LitterID:=""
SAVE RECORD([Dogs])

we use ORDA to set the ID to “” None of the queries work.

$dog:=ds.Dogs.new()
$dog.LitterID:=""
$dog.save()

Therefore if you have map null values enabled you cannot query for an empty UUID because null doesn’t work as it’s been mapped and you can’t query for a string If you have assigned the variable using ORDA.

Regards
Paul

Perhaps a better way to ask this question is given 4D examples of employees with a recursive relation to the manager. How do you find all employees that don’t have a manager ie ManagerID is null. If you have map to blank enabled on the field
Paul

and you can’t query for a string If you have assigned the variable using ORDA.

Interestng. So if you’ve set LitterID to “” via ORDA, how is that UUID then represented in the traditional language? (if done from traditional it’s 20202020…)

If you put the blank assignment in the table trigger using traditional code, then surely that’s inescapable regardless of using traditional or ORDA to create records. In our generic trigger code, we check to see if UUIDs have a “null” value and if they do we assign “”

Hi Thomas,
I think I’ll never understand this: since the dawn of times, an “empty” field (freshly created record) is always “zero” value (empty string, 0, !00-00-00!, ?00:00:00?…). Then the uuid came with a “zero” that can be multiple (32*“0”, 16*“20” or “”). It’s 4d, not SQL. To me, the only valuable empty uuid value should be 32*“0”, as a uuid is a number. As you say, managing uuid foreign keys is uncomfortable…

Hi Bruno,
I feared that too, but for debugging purpose in the real life, most of the time using “uuid part+@” does the trick. I often use the 4 last chars, for example (FFFF=65535…)

An UUID is not a simple value. And as the name defines “unique”, an empty, repeated value, is not compatible with the name and so with the type.

In any case, I think using “” is another mistake.
“” is not a valid UUID, however you want to read that.

You could consider “” to be (" “16).
And as " " is a space which is in hexadecimal (UUID can be represented as base64) a 0x20, you could write it as 16
"20”.

But that’s assuming that something/somebody is converting something invalid to something more valid.

That’s why I consider it as mistake.

If you do not see it as unique, you could define your own “empty” value and assign that.
As already written, I would avoid “00000…”.
I also avoid “202020…” for the same reason.
Pick any valid UUID and define that inside your application as placeholder for “empty”.

But - as written - just my personal recommendation.

1 Like

Yes Keith, with ORDA an empty string becomes "00000… not 202020…

So the question remains if Map to blank is enabled how do you find null if assigned via ORDA ? either that or disable map to blank.

Thomas, as far as uniqueness goes, why is NULL considered more unique than “”?

NULL is not a value.

Imagine you have longints.
1,2,3,NULL,NULL,NULL
Average = 2 (6/3)

If it would be 0, it would be:
1,2,3,0,0,0
Average = 1 (6/6)

NULL is not existing…

Thomas, I get the Null concept thats not the issue. With traditional default was the option map null values to blank. Therefore how do you search for null using ORDA on an existing database if you have this setting enabled and you have records with “” or 202020 or 00000 ?

If not possible what’s the best way forward ?
Thanks