GetUserId "really null" inappropriately

GetUserId "really null" inappropriately

  

I have an entity "EditTracking" with a lookup to a UserId:

When I create records here as an Anonymous user using GetUserId(), it puts in zero, like I would expect.

But when I try to filter on GetUserId(), it's using a real null in the generated SQL.

The filter:

EditTracking.OriginalId = GuestId
EditTracking.UserId = GetUserId()
EditTracking.EntityName = "ClientRelation_Guest_DTL"

The generated SQL:

SELECT TOP (1) ENEditTracking.[ID] o0, ENEditTracking.[USERID] o1, ENEditTracking.[ORIGINALID] o2, ENEditTracking.[EDITID] o3, ENEditTracking.[ENTITYNAME] o4, ENEditTracking.[ROOTKEYVALUE] o5FROM [OSDEV2].DBO.[OSUSR_22P_EDITTRACKING] ENEditTrackingWHERE ((ENEditTracking.[ORIGINALID] = @OriginalId) AND (ENEditTracking.[ORIGINALID] IS NOT NULL)) AND (ENEditTracking.[USERID] IS NULL) AND (ENEditTracking.[ENTITYNAME] = N'ClientRelation_Guest_DTL')

That (ENEditTracking.[USERID] IS NULL) is a bit surprising. I could see it for entities from Integration Studio with real nulls, but not here.

If I process the GetUserId() like this, then it works fine:

EditTracking.UserId = If(GetUserId() = NullIdentifier(), NullIdentifier(), GetUserId())

SELECT TOP (1) ENEditTracking.[ID] o0, ENEditTracking.[USERID] o1, ENEditTracking.[ORIGINALID] o2, ENEditTracking.[EDITID] o3, ENEditTracking.[ENTITYNAME] o4, ENEditTracking.[ROOTKEYVALUE] o5FROM [OSDEV2].DBO.[OSUSR_22P_EDITTRACKING] ENEditTrackingWHERE ((ENEditTracking.[ORIGINALID] = @OriginalId) AND (ENEditTracking.[ORIGINALID] IS NOT NULL)) AND (ENEditTracking.[USERID] = (CASE WHEN (@GetUserId = 0) THEN (0) ELSE @GetUserId END)) AND (ENEditTracking.[ENTITYNAME] = N'ClientRelation_Guest_DTL')

This all seems a bug when using it with a non-nullable user ID lookup in an OutSystems entity.

It's converting zero into a null generation here, from the looks of it. Doesn't matter if the GetUserId is put into a local variable or a different function.

If it makes a difference, the properties on the UserId column are Is Mandatory=No and Delete Rule=Ignore:

Note that you can't reverse the check:

If(GetUserId() = NullIdentifier(), NullIdentifier(), GetUserId()) will work

If(GetUserId() <> NullIdentifier(), GetUserId(), NullIdentifier()) will not - it goes back to generating the NULL check

Weird :)

Well... in foreign keys you'll have "real nulls" because of the DB constraints.

Normally your filter should be GetUserId() = NullIdentifier() or EditTracking.UserId = GetUserId().

Wouldn't that just return all rows if your user ID was NullIdentifier()?

That aside, it's just that if you put something in as NullIdentifier() in an OutSystems-managed entity, it's a zero, so if it attempts to match actual null in an aggregate, that makes it asymmetrical.

(If creating a record using GetUserId() put an actual NULL in, it would be symmetrical and therefore fine)

Maybe an actual null would pop up from an outer/With Or Without join with the target lookup table?

I just tripped across it pretty hard because I add the GetUserId() when creating my record and attempted to get back the record I made later, and wasn't expecting it to be empty. This was on pages that we send out with a code so non-registered users of the system can fill in their details.

It might be as-designed, but it can be surprising when you don't test for anonymous users, etc.

The OutSystems platform by design has no NULL values. 

Regarding the OS entities... you’ll have zeroes UNLESS it’s a foreign key. 

Hi Ritchie,

Wouldn't that just return all rows if your user ID was NullIdentifier()?

Yes, and it's a very common scenario when you are filtering on a listings page and want an option "do not filter". Of course it will depend on your particular use case.

That aside, it's just that if you put something in as NullIdentifier() in an OutSystems-managed entity, it's a zero, so if it attempts to match actual null in an aggregate, that makes it asymmetrical.

(If creating a record using GetUserId() put an actual NULL in, it would be symmetrical and therefore fine)

Within the OutSystems platform, there are no NULLs, you have equivalents (NullIdentifier(), NullTextIdentifier(), NullDate() or NullBinary()). When you test variables of the correct data types against these values in OutSystems and it will work (including on Aggregates).

GetUserId() will return NullIdentifier() if no user is logged in, which is in line with the platform's philosophy.

At the Database Engine level, on the other hand, there is no knowledge of NullIdentifier() et all, using NULLs instead to represent the absense of a value. When you are creating a SQL statement (even within the platform) you need to take this into consideration and use IS NULL or IS NOT NULL.

The platform bridges this gap by translating one to the other on the fly, on Aggregates and on entity actions. When it finds a NullIdentifier() in a mandatory reference attribute (foreign key) it knows it's of an Identifier type and converts it to NULL, and vice-versa.

Note that you can't reverse the check:

If(GetUserId() = NullIdentifier(), NullIdentifier(), GetUserId()) will work

If(GetUserId() <> NullIdentifier(), GetUserId(), NullIdentifier()) will not - it goes back to generating the NULL check

Weird :)

This is the part I find weird... in what circumstances is the platform making the conversion (or not) when you use an If that gets translated into a CASE in SQL?

I tried to put together a test, and it looks like you are absolutely right. I couldn't tell the difference with the built-in data view tools in OutSystems, but what I have a hold of there in my app is an actual zero, whereas a small test from scratch has an actual NULL in its place, even though it displays as zero.

In those cases, the hacks I put in will actually break things: If(GetUserId() = NullIdentifier(), NullIdentifier(), GetUserId()) tries to match null to zero and fails.

The zero was getting in there when passing the user ID through to an extension. Long Integer Identifier just translates to long instead of something nullable (for updating old code, anyhow) so it went in as zero in SQL.

My apologies :)

(I wish null IDs would show up as nulls, then I would save myself embarrassment!) :)

-- Ritchie

Hey Ritchie,

The zero was getting in there when passing the user ID through to an extension. Long Integer Identifier just translates to long instead of something nullable (for updating old code, anyhow) so it went in as zero in SQL.

The Entities on extensions have a property that tells the platform to perform this translation or not. I suggest, if your legacy/external identifiers allow it, to let the platform to do the translation, the behaviour would be consistent for all entities.

And rest assured, NULLs (or it's absence) are a pet peeve of many in this community (both ways, pro and against), and these situations where they suddenly pop their head help you be aware of how the platform deals with them, so next time you won't be caught off guard.

These weren't entities in extensions, this was an extension I made to be all clever and use the System tables to build a hierarchy and use the hierarchy to make a full copy of a "branch" of data for editing and then reconciling it. I was passing the user ID as a way to isolate the edit tracking down to a user.

I'm building SQL commands on the OutSystems.RuntimePublic.Db.RequestTransactions. I guess with this knowledge in hand, if I want it to participate "fully" the way OutSystems does, I'll have to swap DBNull in for zero and rewrite equals checks to IS NULL .

My project 25 years ago was a "poor man's OutSystems", so I know some of these challenges OutSystems is facing on SQL translation and generation :)

Thanks for the feedback, Jorge!