BUG: Advanced SQL queries do not filter / return correct values for null fields

BUG: Advanced SQL queries do not filter / return correct values for null fields

  

When searching for records where a value is null, the SQL Advanced capabilities are not correctly filtering and displaying the values. When searching for 0 values, the test says, "Query returned no rows".

We are not allowed to search for "NullIdentifier()" values in that field:


When properly specifying valid criteria: [field] is null, then results are returned, but the values are displayed as 0, rather than NULL.

Hello PJ,

This is not a bug.

OutSystems does not have the NULL concept, but database has.

So, if you have a field in an entity, in database, with NULL set, when the platforms brings this value from database, it assumes the default value of the type. In the case of a numeric type as integer, the default value is 0 (zero).

Cheers

Displaying values incorrectly is a bug. If there is no concept of a NULL in OutSystems, then the columns should be marked "NOT NULL" in the database and thus, the value displayed would match the value in the database.

This is by definition a bug: "A software bug is an error, flaw, failure or fault in a computer program or system that causes it to produce an incorrect or unexpected result, or to behave in unintended ways."

https://en.wikipedia.org/wiki/Software_bug


Pj, 

I said that OutSystems (code) does not have the null concept, while the database has.

So, what the platform does is to convert a NULL value (in database) into the default data type value in code.

This is a design choice. Not a bug.

By default, when saving a record into an OutSystems entity, all values not provided by the user will be stored with the data type default values. Not NULL. The only attribute I'm not sure is the Foreign Key.

Typically, only External entities, imported through Integration Studio, store NULL. And this is configurable.

So, if you are having a problem in your code because of this concept, please, tell us what you want to do and how are you doing, and I'm sure we will be able to help you.

Cheers

I want to look at data and then enter that data into a WHERE clause and that data come back. I want OutSystems to behave in an expected way. I want correct data to be displayed in result screens.

That's not what happens. Thus, it's a bug and OutSystems should fix it.

Hi PJ,

You can open a support case asking for OutSystems to fix something if you think there is a bug.

You can also add an idea in the Idea's section of this site and give some arguments on why OutSystems should change something.

Cheers.

Ok, let me weigh in here. First, the OutSystems Platform has indeed no concept of actual "null values". That means that when you work with everything but the SQL Statement, you never work with actual null values. "NullIdentifier()", "NullTextIdentifier()" and "NullDate()" are just representations for 0, "" and #1900-01-01# respectively. When storing things in the database, null dates are actually stored as #1900-01-01#, but Identifiers, regardless of whether they are Integer, Long Integer or Text Identifiers, are stored as NULL values. This makes sense, as non-NULL values must reference an actual foreign record, so storing 0 or "" is not an option.

The SQL (formerly know as "Advanced SQL") Statement is closer to the "bare metal" of the database. Except for Entity names (which get translated to their physical table names), everything you write is sent 1:1 to the database. This also means that if you want to test for NULL values of foreign keys, you need to test on "IS NULL" or "IS NOT NULL", instead of "= 0" or "<> 0". You can throw a fit about that, or call it a bug, but that's the way the Platform works, and once you know it, it's pretty easy to deal with.

That said, why on earth are you using a SQL Statement instead of an Aggregate for something so simple as the query you showed above? In an Aggregate, the right way to deal with null values is indeed "Entity.Attribute = NullIdentifier()", and the Platform will happily convert that to the right SQL. I would strongly advise you to always use Aggregates, unless you run into its limitations (like needing a sub query, or an IN or the like).

Solution

The only correct solution is for OutSystems to show a blank field in foreign key columns when the value is a NULL, not show a ZERO.

An OutSystems developer can fix this and make it right and behave in the expected way, or countless developers in the future can continue to waste even more countless hours "getting used to OutSystems doing things wrong."



Solution

PJ,

You come over as pretty frustrated by now. The fact that you think it's wrong doesn't mean the majority of developers think so, nor that what you propose is "making it right". I think it's time for you to move on concerning this topic. Any further reply I will have to delete.

Finding this IDEA actually gives me hope!

https://www.outsystems.com/ideas/213/null-values

Upvote it if you want to save future developers and yourself a lot of headaches! So glad fixing this is on the radar!! :) :)