Null values

By João Campos on 28 May 2010

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"

Nuno Leong28 May 2010
this is a no-brainer ... null values exist, a lot of business logic depends on them, they can't be ignored or faked.
João Carvalho31 May 2010

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

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.
João Campos31 May 2010

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.
Ken Evans10 Jun 2010
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.
João Carvalho19 Jul 2010
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!" ...
Ricardo Ardito22 Oct 2010
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.

enigma14 May 2012
best solution for date-time values is a timestamp; 0 is known by any unix system
Eduardo Luís3 Oct 2012
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...
Bert Koot25 Mar 2013
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!
Ravi Vakkalanka27 Feb 2014
I want to be able to use simple query and check the condition that database date time field is is like writing like this : TABLE.DATECOLUMN IS NULL..but I am not able to acheive this using simple query
Curt Raddatz30 Oct 2014
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.
Keith Matthews15 Apr 2015
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.
Tiago Neves21 Apr 2015
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!
João Santos30 Apr 2015
Hi Jan-Hendrik,

Have you considered the "Default Value behavior" setting, described in the Integration Studio online help ( ?

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.

João Santos5 May 2015
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 ( 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.

Cody Ahlquist13 May 2015
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!
Daniel de Witte6 Jul 2015
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.

J.10 Jul 2015
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.

Hans Dollen17 Nov 2015
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:

 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:

 What do you guys think?

I think it's a MUST HAVE
eric Slikker2 Oct (3 weeks ago)

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 (

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.