Null values

On our radar

Full widespread support for null values, in all layers: database, actions, ...

Not fake zeros disguised as nulls, but real thing, the ones that we can compare to is null. The ones that mean "I don't know the value for this... I don't even know if its zero"

Created on 28 May 2010
Comments (73)
this is a no-brainer ... null values exist, a lot of business logic depends on them, they can't be ignored or faked.

The platform would definitively increase it's value for the customers and developers if the real nulls would be there.

well, I hate null-values in my applications and would like to prevent them wherever I can.
It's such a typical database thing and with Outsystems in principal you don't need to know how data is stored.

I don't have the option to prevent nulls... My applications tipycal integrate with other systems which use and require nulls.
Without nulls you cannot tell wether there is a value for the field or not; you always endup with a pair of attributes for eache piece of data: one with the value and another with a boolean to determine wether the values is really a value or if it is the inability of the platform to represent the absence of the value.

And most other systems that you connect to will support nulls and may require you to use them: databases, DLLs, ...

It isn't about how data is stored, it is about the quality of your data.
Adding null value support would not remove the option of avioding nulls from those who would like to do so.  It would, however, help with migration of legacy applications and databases to the Agile Platform, and would therefore be highly useful.
Definitively i agree on having null values on the platform.

Since i work with outsystems and oracle (ebusiness suite) i've been having lots of problems with null to text and text to null conversions consuming a lot of time sometines analizing complex queries just to find again the same conculsion: "Ups! it''s the null to text problem again!" ...
We have just started developing with Outsystems Platform and having problems on dealing with null values. Our existing database (as everyone´s else, I supose) does have a lot of columns with null values, as part of the business, and it has been very anoying to deal with it in Service Studio. NULL values in Service Studio would be very welcome !
When using a DateTime dataype in a web service, and the value is null a date and time is returned.  1900-01-01T00:00:00.0000000+11:00. 

To remove this date we had to change the datatype into a string, which isn't very nice at all.

It doesn't matter too much that null is 1900 within outsystems platform and applications but its important to have a true null value at least for 3rd party using our pubic web service.

best solution for date-time values is a timestamp; 0 is known by any unix system
The way that the platform handles nulls it's, in my opinion, one of its biggests drawbacks. 
Having a 0 or a null are two completly diferent things! One thing is a student without a note, another thing is a student with a note of 0! Now they are handled the same way.

So, 1000 likes to this idea...
The everlasting story of NULL-value. NULL is the value used to represent an unknown piece of data. Null is not equal to zero.  Good idea!
I want to be able to use simple query and check the condition that database date time field is null...it is like writing like this : TABLE.DATECOLUMN IS NULL..but I am not able to acheive this using simple query
The date issue mentioned here is a particular problem for an application I'm working on.  I have birth dates that range from the late 1800's until now.  With the inability to represent any of these old dates and to treat everyone born on 1/1/1900 as NULL I too have to convert to strings.  We really need support for a true NULL and for timestamp data type to handle wider date ranges.
Not having Null values is a real problem.
OutSystems supports no NULLs...!!! And no dates before 01-01-1900...!!!

I can not imagine that everyone uses 0 for NULL in integer???? And 01-01-1900 for NULL in date???

What if 0 is a perfect valid value, and NULL too, for integer?
What if 01-01-1900 is a valid date? What if you have to save the construction date of a building of 200 years old? What if you want to store genealogy?
Since when do we build systems that deny any history older than a century???? For me this is really unbelievable!!!

And aside of the functional aspect, storing 0 or 01-01-1900 instead of NULL in our database would explode the size of indexes - I can not tell my manager that our database will grow from 4TB to 7TB, just because a tool does not support this !!!
Why in the world does someone behind a desk designate perfectly normal live values (0 or 01-01-1900) to fake values?

It is not just a "real problem"... That's the kind of things we sove daily.
It is much worse. It makes the product unusable for a great deal of data driven applications, and in many cases unusable for real life representation of data.

What I mean: there is quite a difference between not having money in my purse ( = 0 ) and not knowing how much money I have in my purse.
There is quite a difference between a building of which I don't know the construction datet, and a building of which the construction date is 01-01-1900.

And why did OutSystems dictate that I can not save the fact that Abraham Lincoln was president till 15 April 1865? Or that Portugal invaded Spain in 1702?

This severely limits usage of OutSystems for any serious data centric systems!!
Till yesterday we considered OutSystems seriously for our enterprise system.
Since I know this, we have to revisit this decision. We are still evaluting the next few months, but little chance that we choose OutSystems if it does not support NULL or dates before 01-01-1900.
Hi Jan,

The calendar widget will only let you select dates between 1900 and 3000 but you can type the date directly in the input field (instead of selecting from the calendar) and OutSystems platform will accept (save/read) dates between 1753 and 9999.

The discussion of having nulls or not is a different one.
After I cooled down a bit (I was really shocked that OutSystems does not support NULLs)...

Besides being really awkward for developers and admins, I came to the following list of major enterprise problems caused by this problem.
  1. Big risks for bugs & runtime errors when reading data from (existing) databases, XML, webservices if NULL or absent is interpreted as 0 or 01-01-1990 (reading NULLs, interpreting as a non-NULL)
  2. Same for the other way around, if 0 or 01-01-1990 is interpreted as NULL (reading non-NULL, interpreting as NULL)
  3. Industry-standard behaviour of external tools (e.g. reporting, like Jasper or Crystal) etc. is deceived.
    * ORDER BY, GROUP BY, IS NULL will not work as expected anymore.
     * NULL does not influence COUNT(value) or AVG(value) (average); but 0 and 01-01-1990 do influence these.
    Reports that worked perfectly will start to produce errors, because OutSystems fucks up the data.
  4. Because of OutSystem's non industry standard behaviour, a database written to by OutSystems will become incompatible (forever!!) with any other platform / application. It becomes impossible to have an other applciation work with the same database as OutSystems. An maddening technical vendor lock-in, and a major impact on your application landscape.
  5. Lots of wasted extra storage. For MySql and MS SQL Server the required additional storage typically will be something between up to +25% storage needed. For Oracle this will quickly be something like +40%; Oracle does not store NULLs in indexes and optimizes (eliminates) storage of NULLs, hence the additional storage when NULL is stored as a non-NULL value.
  6. OutSystems forces users to awkward :-(( workarounds.
    Examples I found on OutSystems fora:
  • Store dates in textual format instead of natively (losing all handy features of date validation and arithmetic)
  • With every numeric or date column in a database table, include an additional column indicating whether it is actually NULL or not
  • Database triggers transforming 01-01-1900 to NULL in the database
  • Updateable views that transform NULLs to 0 / 01-01-1990 (to please OutSystems) on reading, and transform 0 or 01-01-1990 to NULL on writing (to please OutSystems while at the same time keeping the database compatible with industry standard behaviour)
Like it or not, NULLs are omnipotent in databases, XML, development tools, programming languages, platforms, heads of developers.... Everywhere around you!
Hi Jan-Hendrik,

Have you considered the "Default Value behavior" setting, described in the Integration Studio online help (https://www.outsystems.com/help/integrationstudio/9.0/default.htm#Managing_Extensions/Extension_Properties.htm) ?

This parameter allows you to map NULLs in external databases to unusual values such as 1st Jan of 2893 and seems to solve the problem of storage taken by indexes in external databases.

Let me know if this helps, or if you face any difficulty implementing this suggestion.

There are indeed very good arguments for and against the use of NULLs. You can easily find several discussions and even research papers, with passionate and strong arguments on either side.
As an example, this help section (https://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/Database_Management/B035_1094_109A/ch14.16.01.html#9502720) about "designing for missing information" seems to provide a reasonably balanced view of the issue.

OutSystems decided not to have nulls in the language quite some time ago, as a way to make it simple to develop on OutSystems and avoid creeping errors on business logic, which tend to be expensive to handle.
We understand from day one that this approach also has downsides, especially on the integration with other data sources. Nevertheless, we still believe that the benefits outweigh the disadvantages.

Thank for sharing your feedback.

Hi Joao Santos, it sure would be nice if Outsystems at least gave the option to the developer. This would seem to solve the debate of being for or against using NULLS. If there are senerios like Jan-Hendrik van Heusden described then one would have the "option" of choosing to use real NULL values rather than fake ones. It would be nice if this option was built right into the platform so as to make it nice and easy on the developer to set an option like "Use REAL NULL values" on attributes where REAL NULL values are imperative. To me debating whether or not using NULL is good or bad is one thing, but removing the option from the developer is a completely different thing. Just my thoughts on the matter, thanks!
Null values are a must have.
They are not just some 'database-thing' like some people claim here, they originate in programming languages and are the value of a variable before it is assigned any value.

Thinking about it some more and more and I'm finally getting into the agreement mode.
Why, because the solution Outsystems implemented is a bad implementation in the first place.

what is good:
for PK/FK is 0 a perfect alternative to null, at the moment I don't see any major flaws there.

what is bad:
0 and 1/1/1900 as a null-equivalent  for "normal" integers, dates.
and strings also ofcourse.

why is it bad?
how can you tell if a user entered 0 as a value or skipped the value?
(for example a discount value)
how can you tell if a user entered 1/1/900 as a date or skipped the input.
(birthday comes to mind)

The reason because it makes developers easier is not a very handsome one.
we already check if something is a NullIdentifier() or a NullDate(), we already program in that way.
And we introduce complex logic because we want to know if a user entered nothing or something.
and we need to figure something out how to store that in some way.
so even if the "proper" nulls are implemented, nothing changes much.

there is an inconsistency with the REST webservices.
We can send default-values or not, and that imho is already some sort of controlling the nulls in a proper way.

Dear OutSystems,

I would like to challenge you about this topic. Can R&D write questions / challenges you have about how to handle null-values, so the community can respond and perhaps R&D and the community comes up with 'the' solution?


For a platform that states to be good for integration between systems, it really have to support null dates as: 
<Value xsi:nil="true" />
Hi all,

 just wanted to bring some new thoughts into this discussion. I also miss NULL values in OutSystems, i.e. the possiblity to state or check that a given variable has no value. Nevertheless, I'm not in favour of introducing NULL values. Instead, I'd rather have something similar to the  Optional type  in Java (see: https://docs.oracle.com/javase/8/docs/api/java/util/Optional.html).

 The Optional type is a wrapper for an instance of another type. Basically, it encapsulates an instance that may be absent. You can create an empty Optional (that contains no instance) or an Optional containing a given instance. Once with the Optional in hand you can ask it whether or not it is empty, to return the instance in it, to return either the instance in it or a given alternative value if empty, etc. Many more methods are available.
 The "beauty" of the Optional data type is not that it allows us to express that a given value might be absent, but that it makes the code that deals with potentially absent values easy to read and to write. In addition, it prevents the famous NullPointerExceptions.
 Check out the following link on Null vs Optional: https://www.oracle.com/technetwork/articles/java/java8-optional-2175753.html

 What do you guys think?

I think it's a MUST HAVE

I also think it's a MUST HAVE; see the examples of J. on 10 Jul 2015.
The use of Null shouldn't be a discussion (https://en.wikipedia.org/wiki/Null_(SQL)).

And if there is a discussion, this should be on the technical implementation of Null values.
Null is not implemented a value. Null is implemented as a seperate status(-byte) in front of the field, a Null-indicator. All actions on Null values use this Null-indicator.

This construction can als be used by Outsystems. The code_generator of Outsystems can generate a separate indicator(-byte) for fields that can be NULL.

The Null-functions (e.g. NullIdentifier(), NullDate()) must use the indicator part and should ignore the value of the field.

Another Null challenge is sorting.
The SQL standard does not explicitly define a default sort order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.

The nice one is Null on Boolean. In my opinion, a Boolean can't be Null. A Boolean can only be True or False.

I will give some examples.
In this examples the first character is the Null-indicator(byte), where 'Y' means = Null (True) and 'N' means <> Null (False).
Date1 = "Y2016-10-02"
Date2 = "N2016-10-02"
Date3 = "Y1900-01-01"
Date4 = "N1900-01-01"

IF Date1 = NullDate => True   ("Y" = "Y")
IF Date2 = NullDate => False  ("N" = "Y")
IF Date1 = Date2     => False  ("Y2016-10-02" = "N2016-10-02")
IF Date3 = NullDate => True    ("Y" = "Y")
IF Date4 = NullDate => False   ("N" = "Y")
IF Date3 = Date4    => False    ("Y1900-01-01" = "N1900-01-01")

IF Date1 > Date2    => True    ("Y2016-10-02" > "N2016-10-02")

Example with text:
Text1 = "YJohn Doe"
Text2 = "NJohn Doe"
If Text1     = Text2    => False         ("YJohn Doe" = "NJohn Doe")
IF Text1 Like Text2  => False          ("YJohn Doe" Like "NJohn Doe")
IF Text1 > Text2      => ??? (True)  ("YJohn Doe" > "NJohn Doe")

==> Technically the indicator can be a boolean.

Another reason why nulls should be supported is the following:

At this moment it is impossible to create a unique index on a nullable column. This creates a huge problem since it is quite a hastle to enforce uniqueness in a different way without creating a lot of negative side effects.

In my view: when you allow Outsystems to build applications on top of a specific data base brand you must support at least this level of constraint control. In the end it's the data that RULES !!!

it seems as a nice trick to use 0 as null, 1-1-1900 as nulldate and " " as NullText.
But, 0 is a number, 1-1-1900 is a datavalue and " " is a text-value.
it would be exactly the same as: 

we will not use the number 9, character b and date '01-11-2011' (because that's our value to represent a null).

I totally agree with João (and the others that plead for the addition of null values)!

Please for the love of all that is good and kind NO! Null values account for half of all errors. While the method that OS uses to deal with them atm is pretty terrible itself, we should rather implement the Maybe pattern or a empty collections as they occur in Functional Programming languages.

Let's not encourage the awesome sause that is OS to patch instead of to radically rethink!

Keep missing them more and more....

Yes, I would realy like to have real NULL values.

I just want to share the idea how to deal with Null value.

This may not be the best way but I think it would help someone who have a trouble dealing with it.

The way to deal with Null value is to set the lenght of DB data larger/lower than the lenght of user input.

Which mean that developer needs to re-config DB to make sure that there is a space for Null.

Surely developer also need to set Null value on every Input to implement this method and also filther the display.

It's going to be a long development.

Without using Null, at least I want to set my own Null value for the environment or e-space level to make sure that Null and 0 is not equal.

And if the decided Null value can be automatically be hidden, it will reduce time on development a lot. 

NULL values is a necessity, especially for BI purposes.

So how do you consume this REST response (with a null value) in Outsystems?

"deposit": null,
"deviceId": ""

I understand the problem and the justifications for not including nulls (or unset optional values, or whatever you want to call them), but in our situation where mathematical group aggregations are at the core of what we deliver (GROUP BY + AVG/MIN/MAX/SUM/COUNT) it may be untenable. Which is too bad considering that everything else about the system seems a great fit. 

Background: I have the same issue as found at https://www.outsystems.com/forums/discussion/5927/advice-needed-handling-blank-form-fields-in-a-world-without-nulls/ and have thoroughly read through both viewpoints in this post as well.

But we have a major difference – the size of our data. So here is a real example:

  • We have tables full of data metrics (decimal fields), and queries with as many as 16-20 columns sometimes by the time it’s all joined up. This means whatever “solution” is used to work around the should be done during the SQL query if we don’t want to do 16-20 separate queries for what should be just 1 query
  • The unaggregated results occupy hundreds of thousands (even millions) of rows
  • After GROUP BY the aggregated end results would only occupy about 100-200 rows to be shown on the front-end
  • It is *required* that some of those fields do not have values and are thus excluded from mathematical aggregates (AVG, COUNT, etc.). Imagine the difference between averaging [0, 0, 10, 10] and [null, null, 10, 10] – without nulls they get a 5 instead of a perfect 10!
  • There is no way to factor a separate “isSet” Boolean field into the SQL aggregates during the query that I’m aware of, it would have to be done in application code afterword (which would be an absolute nightmare to maintain)
  • Performing the aggregates in application code would be horribly inefficient! You would be returning a list up to 1,000,000 items long to the application server, just so you can iteratively run additional logic on *each item* and then finally work it down to an additional 100-200 rows of data you actually needed, all while the RDBMS already has this capability. Imagine multiplying this inefficiency times the number of active connections querying data and you’ve got an application server weighed down by 10,000 times the RAM usage per request.
  • Exports can no longer be done directly from the database as they will contain the “filler” default values.
  • Streaming database results (for sets too large for an all-at-once API request) for other integrated systems (think BI) are not possible unless we also program them to handle the “default” or “separate isSet field” values as well, and then somehow keep them all in sync as the systems evolve over time. This would be a very complex, time-consuming, and unreliable effort that would have to be perpetually exercised and would continually worsen.
  • Pre-calculating and caching are not viable options since our data is often realtime, and even then the caching mechanism would still have to face the same maintainability challenges

 So in short, it’s not that I’m advocating for nulls specifically – but I need some feasible solution that allows non-applicable numerical values to be excluded from mathematical aggregates while still at the database query level, and at the same time not require error-prone, messy, hard-to-maintain applicaton-level workarounds that put the burden back on the developer (which is what Outsystems is supposed to facilitate).

I agree, though I imagine that would be a major breaking change for the platform.

My legacy project with Oracle Form has a table with a paymentdate field- null if a customer hasn't paid his contract or not null if he has-. Then we extend the project using Outsystems. When we migrate data from the Outsystems to the legacy one, we must take more effort by checking if the paymenetdate is a dummy date. In the worst case, a customer will be considered has paid his contract..:(..what a pity! - What a dangerous thing not to have a NULL - which means there's no information - in Outsystem data abstraction.