Bugs NullTextIdentifier() AND NullTextIdentifier() on Filter Aggregate

Bugs NullTextIdentifier() AND NullTextIdentifier() on Filter Aggregate

  

Hi there, 

I found a weird thing in filtering aggregate using NullTextIdentifier():

# when the condition is single: 

SyntaxEditor Code Snippet

(Session.Calendar_ParamKeyword = NullTextIdentifier() )

it resuts some rows as the expression has value TRUE.

# but when i give a more condition using AND that has value TRUE (in this case i add the same condition)..i can change another boolean expression like 1=1, etc:

SyntaxEditor Code Snippet

(Session.Calendar_ParamKeyword = NullTextIdentifier() ) and (Session.Calendar_ParamKeyword = NullTextIdentifier() )

it results NO Row...as the condition evaluates to FALSE.

# I manage this using "", instead, and it works:

SyntaxEditor Code Snippet

(Session.Calendar_ParamKeyword = "" ) and (Session.Calendar_ParamKeyword = "" )

# I know that  

Session.Calendar_ParamKeyword

is of type text.

# My question is how comes that happen: TRUE and TRUE results FALSE - in the case the NullTextIdentifier() above?

regards


Hi Boring,

Why on earth are you using NullTextIdentifier() to compare a Session Variable to, while that Session Variable isn't a Text Identifier??? You should never do that. Just change it to "" like you did and you'll be fine. NullTextIdentifier() should only be used to compare against actual Identifiers.

The reason for this behaviour seems to be the way the Platform optimizes the query parameters. The Platform seems to optimize the WHERE clause in certain conditions, and already evaluates it before sending it to the database. A check on "= NullTextIdentifier()", with a Test Value of "" is not sent to the database as a WHERE clause at all (you can check this in the generated SQL). If however you extend the condition (e.g. with a "and 1 = 1"), the full clause is sent to the database, where the check on NullTextIdentifier() is replaced by "IS NULL" or "IS NOT NULL".

Funilly enough, the above means that whether the Text Variable is "" or not, both a Filter on "= NullTextIdentifier()" and "<> NullTextIdentifier()" will result in the same output.

tl;dr: don't use NullTextIdentifier() to compare to a Text Variable. EVER.

Hi Sir,

Thank you for the explanation. Knowing abnormal behavior of Outsystems concerning with database: no null value, date null, and this magic logical expression, I discourage to use Outsytems for heavy-database project.. 

Regards

Hi Boring,

Like any tool, you must know how to use it. Using a NullTextIdentifier() to compare with a Text Variable is just plain stupid. Given that you are already active for quite some time here, making such mistakes is questionable. OutSystems is very well suited for "heavy-database projects", as 100s of customers around the world have proven. Perhaps it's you that's not very suited...

Kilian Hekhuis wrote:

Hi Boring,

Like any tool, you must know how to use it. Using a NullTextIdentifier() to compare with a Text Variable is just plain stupid. Given that you are already active for quite some time here, making such mistakes is questionable. OutSystems is very well suited for "heavy-database projects", as 100s of customers around the world have proven. Perhaps it's you that's not very suited...

Yes Sir,

I am about 8 years in Oracke form project and every thing go all right until Outsyatem got me crazy as it is in its own track when touching with database. 

Regards


Kilian Hekhuis wrote:

Hi Boring,

Why on earth are you using NullTextIdentifier() to compare a Session Variable to, while that Session Variable isn't a Text Identifier??? You should never do that. Just change it to "" like you did and you'll be fine. NullTextIdentifier() should only be used to compare against actual Identifiers.

The reason for this behaviour seems to be the way the Platform optimizes the query parameters. The Platform seems to optimize the WHERE clause in certain conditions, and already evaluates it before sending it to the database. A check on "= NullTextIdentifier()", with a Test Value of "" is not sent to the database as a WHERE clause at all (you can check this in the generated SQL). If however you extend the condition (e.g. with a "and 1 = 1"), the full clause is sent to the database, where the check on NullTextIdentifier() is replaced by "IS NULL" or "IS NOT NULL".

Funilly enough, the above means that whether the Text Variable is "" or not, both a Filter on "= NullTextIdentifier()" and "<> NullTextIdentifier()" will result in the same output.

tl;dr: don't use NullTextIdentifier() to compare to a Text Variable. EVER.

I think reliability should be put before performance. It is unforgiven to evaluate True and True to False in any programming language.

Regards


Hi "Boring",

Let me try and clarify a bit Kilian's explanation:

  • In the first case, the platform can determine that the value of Session.Calendar_ParamKeyword ("") equals NullTextIdentifier() (in OutSystems also represented as a "") and optimize the database call by not adding the irrelevant filter.
  • In the second case, given there is more than one condition, no optimisation is performed, and the condition is converted into a WHERE clause in the actual SQL executed by the database.

Why is that an issue, you may ask? Because of the NULL conversions happening behind the scenes:

  • In the second case, because you are using NullTextIdentifier(), the SQL the platform generates for the Aggregate is actually something like "" IS NULL which is always false.

It is not a matter of reliability, it's a matter of understanding that at the database level the NULL concept is still there and used for foreign keys... no matter what your opinion on the NULL vs No NULLs in the platform is, if you know how the mapping between NULLs in the database and Null*Identifier() works in OutSystems, these things get simpler to understand. And honestly, it all boils down to what Kilian stated before: you should NEVER compare Null*Identifier() with anything other than a compatible Identifier.

Hi,


Just to chill the conversation a bit.

Regardless of if he should not compare Null*Identifiers with Text, in this case it's not obvious and causes runtime problems. I think we all agree that those conditions should return True regardless of being run on the server or database, the actual implementation details are not relevant.


Anyone seeing any good reason for the "Text Identifier" and "Integer Identifier" to have implicit casts to "Text" when in aggregate filters?

I think that would be an easy fix for this, just wondering how many module upgrades would it break.


Regards,
João Rosado

Hi João,

I don't agree... simply because different data types have different semantics... and even though currently the implementation of NullIdentifier() is to return 0 and the implementation of NullTextIdentifier() is to return "", it doesn't mean it will remain that way in the future (who knows, we might still get Nulls in OutSystems?!). Adding an implicit conversion to Text doesn't solve this in my opinion (why would you convert an Integer/Long Integer Identifier to Text? and how many more weird situations would that create?)

On the other hand... Service Studio should show at least a warning about the comparison between different data types like it does if you compare a variable of Identifier data type with the "raw" data type, Session.AnIdentifier = 0 will show a warning, as can be seen in the screenshot below:

Having Service Studio behave in a consistent way when displaying data type mismatches would have avoided the problem here, I think. "Boring" would have a warning telling him/her that the comparison being made was a potential problem, and that might have lead to compare with "" and not with NullTextIdentifier() which would have eliminated the entire issue.

I've created an Idea for this, feel free to vote if it makes sense to you.

I was not suggesting to "Adding an implicit conversion to Text", I was I suggesting to remove it.

Since almost all types currently have implicit conversions to Text, and that's the only reason he didn't get a warning or error in that case.


Edit: fixed image with the correct example


Edit2: just saw the Idea you posted, yup thats what I was suggesting :) Voted

Are you sure about that implicit conversion to Text?... it's not what I see happening.

Check the example I give on my Idea, the issue has to do with comparing something (of data type Long Integer) with a corresponding Null*Identifier (NullIdentifier() - doesn't show warnings) vs comparing an identifier variable (for instance of data type User Identifier) with the corresponding data type (literal 0, for instance - shows errors).

Null*Identifier() is being silently seen as the corresponding basic data type, when it shouldn't without explicit conversion (applying one of the IdentifierTo*() functions).

Hey guys, interesting topic, and you know I like language design so I'm going to give my 2 cents.

I believe the problem in this reported bug is that the Null*Identifier is occurring in a comparison with a variable that comes from outside of the scope of the aggregate. We know that variables in OutSystems cannot ever be Null, and that's why we generally assume that Null*Identifier is an alias for 0 (int/long) or "" (text). But they are not exactly dummy aliases as we assume them to be...

When Null*Identifiers are used inside aggregates, OutSystems needs to be aware that foreign keys can assume the literal NULL value. So, only in the context of an aggregate, if you write Entity.ColumnId = Null*Identifier(), it should be converted to Entity.ColumnId IS NULL.

However, when the left-hand side of the comparison is not in the aggregate scope, like Variable = Null*Identifier(), then it doesn't make any sense to evaluate it as Variable IS NULL, because of course the Variable will not be NULL. That is the case even if the Variable has a correct Identifier type. For example, GetUserId() = NullIdentifier(), when inside an aggregate, should always be treated as GetUserId() = 0, even though no type conversion took place. Having the warning would not take care of this case, and would keep the language inconsistent because warnings can be ignored and hidden.

leonardo.fernandes wrote:

However, when the left-hand side of the comparison is not in the aggregate scope, like Variable = Null*Identifier(), then it doesn't make any sense to evaluate it as Variable IS NULL, because of course the Variable will not be NULL. That is the case even if the Variable has a correct Identifier type. For example, GetUserId() = NullIdentifier(), when inside an aggregate, should always be treated as GetUserId() = 0, even though no type conversion took place. Having the warning would not take care of this case, and would keep the language inconsistent because warnings can be ignored and hidden.

Hi Leonardo,

I agree with you that the behaviour of Null*Identifier() in Aggregates is the special case, as that's where they need to be mapped to the actual database NULL.

But on the rest of your argument, I don't think I agree with you... given the data type of the output parameter of GetUserId() is a User Identifier, the platform should, when in an Aggregate, be aware of NULL conversions and generate a WHERE clause with <value of GetUserId()> IS (NOT) NULL . This would evaluate correctly if, when the value of an Identifier variable is Null*Identifier() it is also mapped to NULL in an Aggregate.

The warning I suggest doesn't avoid errors, it just points to potential pitfalls in what we are doing... if you follow through, you know there may be problems. You could turn it into an error instead, and forcing comparisons to be only with compatible data types (you would need to use the IdentifierTo*() functions to convert data types).

Following your approach you would have a less consistent translation of filters to SQL:

  • if it's from a outside variable translate NullIdentifier() to 0 (and NullTextIdentifier() to ""),
  • otherwise translate Null*Identifier() to IS (NOT) NULL.

I personally prefer to see a generic translation of Null*Identifier() to IS (NOT) NULL, but I guess both approaches would achieve the same goal?

Jorge Martins wrote:

Hi João,

I don't agree... simply because different data types have different semantics... and even though currently the implementation of NullIdentifier() is to return 0 and the implementation of NullTextIdentifier() is to return "", it doesn't mean it will remain that way in the future (who knows, we might still get Nulls in OutSystems?!). Adding an implicit conversion to Text doesn't solve this in my opinion (why would you convert an Integer/Long Integer Identifier to Text? and how many more weird situations would that create?)

On the other hand... Service Studio should show at least a warning about the comparison between different data types like it does if you compare a variable of Identifier data type with the "raw" data type, Session.AnIdentifier = 0 will show a warning, as can be seen in the screenshot below:

Having Service Studio behave in a consistent way when displaying data type mismatches would have avoided the problem here, I think. "Boring" would have a warning telling him/her that the comparison being made was a potential problem, and that might have lead to compare with "" and not with NullTextIdentifier() which would have eliminated the entire issue.

I've created an Idea for this, feel free to vote if it makes sense to you.

Hi Sir,

Unfortunately, I do not have a warning, where Session.Calendar_Paramkeyword if of type Text:

regards,


Jorge Martins wrote:

Hi "Boring",

Let me try and clarify a bit Kilian's explanation:

  • In the first case, the platform can determine that the value of Session.Calendar_ParamKeyword ("") equals NullTextIdentifier() (in OutSystems also represented as a "") and optimize the database call by not adding the irrelevant filter.
  • In the second case, given there is more than one condition, no optimisation is performed, and the condition is converted into a WHERE clause in the actual SQL executed by the database.

Why is that an issue, you may ask? Because of the NULL conversions happening behind the scenes:

  • In the second case, because you are using NullTextIdentifier(), the SQL the platform generates for the Aggregate is actually something like "" IS NULL which is always false.

It is not a matter of reliability, it's a matter of understanding that at the database level the NULL concept is still there and used for foreign keys... no matter what your opinion on the NULL vs No NULLs in the platform is, if you know how the mapping between NULLs in the database and Null*Identifier() works in OutSystems, these things get simpler to understand. And honestly, it all boils down to what Kilian stated before: you should NEVER compare Null*Identifier() with anything other than a compatible Identifier.

Thank you for your explanation, Sir.

In my thought i always convert the construct NullTextIdentifier() into "", so i think using two conditions or more does not matter.

I agree with Joao whatever behind the scene (the parsing/compiling/interpreting, etc), the expression TRUE AND TRUE should evaluate to TRUE. I - as an end user - only know that Outsystems is a fast platform to build application, it is unnecessarily to know the 'behind scene'  [Isn't it said that Outsystems is easy to learn, even for someone who does not have a programming basics? How come this technical issue can be understood by a non-programmer?].

regards

 


Jorge Martins wrote:

But on the rest of your argument, I don't think I agree with you... given the data type of the output parameter of GetUserId() is a User Identifier, the platform should, when in an Aggregate, be aware of NULL conversions and generate a WHERE clause with <value of GetUserId()> IS (NOT) NULL . This would evaluate correctly if, when the value of an Identifier variable is Null*Identifier() it is also mapped to NULL in an Aggregate.

You're right about this, Jorge. The GetUserId(), if used inside an aggregate, will also get the 0 converted to NULL. So, in the case of an annonymous user, GetUserId() = NullIdentifier() would get compiled to NULL IS NULL, which will be true as we would expect.

In this case, the semantics of the platform are well-defined and consistent: any expression of type Identifier, when used in an aggregate, will get converted to NULL if it equals Null*Identifier. Any expression of other types will never get converted to NULL.

I agree that a warning would be useful in this case - just voted on your idea.

Boring wrote:

In my thought i always convert the construct NullTextIdentifier() into "", so i think using two conditions or more does not matter.

Boring, that is your misconception. NullTextIdentifier() is not the same thing as "". This is something that you need to understand before complaining about the lack of NULL values. If you keep complaining, but also keep showing lack of understanding, then your arguments will weaken.


I agree with Joao whatever behind the scene (the parsing/compiling/interpreting, etc), the expression TRUE AND TRUE should evaluate to TRUE. I - as an end user - only know that Outsystems is a fast platform to build application, it is unnecessarily to know the 'behind scene'  [Isn't it said that Outsystems is easy to learn, even for someone who does not have a programming basics? How come this technical issue can be understood by a non-programmer?].

TRUE AND TRUE does evaluate to TRUE. That was never the problem with your initial scenario, so this is showing a second misunderstanding from you. In your scenario, the comparison with NullTextIdentifier() evaluated to FALSE, so in reality you had TRUE AND FALSE. Quite different, isn't it?


Regarding all your generic arguments that "no null values in OutSystems, then it sucks". That is your subjective opinion only, and I don't think it's going to change. Objectively, we could show you numerous cases of success of projects that scaled well, or projects that had difficult integrations. Take a look at https://www.outsystems.com/case-studies/. These cases are true no matter what your personal opinion on the platform is.

Thank you Sir,

Reading the above thread, you can read it is 'same'. If it is not '', so what does its semantic?:


I try one expression "Session..= NullTextIdentifier" evaluated to TRUE, so if i have two same expressions with operator AND, it should be read TRUE AND TRUE, isn't it? Yes, i know this is because of something 'behind the kitchen' that results in TRUE AND FALSE. 

I read this thread idea https://www.outsystems.com/ideas/213/null-values , it is "on our radar", so it may be changed one time (nothing last forever). I see that Outsystems success in integrating, i only drop this line - where i am now in a big project using Outsystems and find that 'bottleneck' concerning with null in database (if null is like the common database sense, my project i believe will be faster about 70% rather than using this new 'null' and new 'nulldate')-  so Outsystems hopefully can be 'more success'.

best regards 

Solution

Boring wrote:

Reading the above thread, you can read it is 'same'. If it is not '', so what does its semantic?:

"Boring", it is not the same. The data type is different, and the platform knows that: "" is a Text and NullTextIdentifier() returns an Identifier. We shouldn't even know how Null*Identifier() is implemented, it is unfortunate that it is easily visible to us, or we wouldn't even be having this argument (you wouldn't be confusing the two).

If you follow these simple rules you should be out of trouble:

  • If you want to check if a Text variable is the empty string, compare it with "".
  • If you want to check if an Integer/Long Integer is 0, compare it with 0.
  • If you want to check if a Text Identifier variable does not have any meaningful value (corresponding to the NULL concept of the databases and memory allocation), compare it with NullTextIdentifier()
  • If you want to check if an Identifier variable does not have any meaningful value, compare it with NullIdentifier()

Forget you ever knew what is the 'behind the kitchen' implementation of NullIdentifier() and NullTextIdentifier() and you will be fine.

I try one expression "Session..= NullTextIdentifier" evaluated to TRUE, so if i have two same expressions with operator AND, it should be read TRUE AND TRUE, isn't it? Yes, i know this is because of something 'behind the kitchen' that results in TRUE AND FALSE. 

 If your Session variable is not a TextIdentifier, and you are comparing with NullTextIdentifier(), that comparison should at least show a warning. Most likely it should be an error. That's what the idea I just created is about. You are comparing different data types that should not be comparable with each other. Think of it as the following comparison: 1 = True. If you know how a boolean is implemented, True probably is implemented as 1 (and false as 0), but the platform doesn't allow you to do it, it shows an error.


Solution