4D is something different than SQL is. We loved not to be too much drawn into the depths of bits and bytes, when 4D was young and we were, too. Nowadays this seems different. Just check the idea of NULL, which 4D never supported, discussed on 4D Forums.

Keisuke Miyako

I can NOT represent 4D on this issue because I have no internal information on this topic,

still, here is what I „think“ might be an explanation.

NULL was introduced in 4D v11 SQL to be SQL-compliant.
supporting NULL is not an option in SQL, the engine must support it.

„Map NULL to empty“, on the other hand,
is a construct to bridge the gap between SQL and 4D,
which historically never supported NULL.

so 4D expects NULL to be empty,
SQL expects NULL to be NULL.

in practice, there should be no reason to map NULL to empty in SQL.
the feature only makes sense in traditional 4D language which doesn’t consider NULL.

if you need to create tables and fields that are more 4D-friendly,
you might have to consider IMPORT STRUCTURE instead of SQL.

Thomas Maul

But it would be a major change in the way 4D works. It could have extreme effects – with major compatibility breaks.

Please take a moment to consider the meaning of „Map NULL values“.

Map – not replace. Map – not save.

4D internally still stores NULL. It does not stores „0000…00“ or „“. It stores NULL.

When it loads a record, the record get’s analyzed and converted.
This is now a genious – and old concept.

In „the old days“, you could store a record on Mac and open it on Windows – and it worked, surprise.
Why surprise? Because Motorola used another Byte Order as Intel and still it worked, magic.

You can add fields – and open an old record – and it works.
You can modify field types – and it works.

When you ‚convert‘ a 4D v11 data file with v15, it takes 0 seconds (to convert the data, not talking about rebuilding Index required because we changed ICU version).
How can it be so fast? Yes, it does nothing, just modify the header. All records are in v11 format.

The handling happens when a record is read in memory.

And this is the moment, when „Map UUID to blank“ is called. NOT when a record is saved.

Try it…

So whenever a record is fully loaded in memory, it is converted.
When a record is not needed in memory, because content could be retrieved on another way (as using an index), it is not loaded. 4D Server is highly optimized.

So – to fullfil your feature request (or bug, however you see it), two ways:
– we modify the behavior of the command. From „Map value“ to „Modify value“. You enter NULL, we save Blank. NULL is not possible anymore. Then the command would work as you expect. But if you want that, why not using the option „NULL not allowed“, then you never get NULL. The idea here is to allow you to have NULL!
– 4D stops using optimized code for fields with „Map NULL to blank“ and always load (and convert) all records. This will slow down 100 times, but it would work as requested.

A 3rd way – which I would recommend:
it seems you do not really want NULL. You want Blank.
Why not have a trigger for that table checking if the field is NULL. If yes, save it as BLANK. For UUID that could be „0000…000“

Just in case this is unknown:
The index element for NULL is different for the index element of „000..0000“
A NULL record is NOT in the index at all. From point of index, this record does not exist.
A record with a value like „000…000“ is in the index as any other record.

In SQL this is the difference between Count and Count(*). One gives the number of records in table, the other the records in index.

What you want is a trustful way to work with NULL records using 4D syntax, not using NULL commands. Is that right? If I understood it correctly, you should not use NULL, as they are, similar to NaN (not a number, invalid real) outside of the normal world. Don’t use them. Use blank value.