Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-2277

Rethink about "Empty strings and NULL"



    • Type: Task
    • Status: Closed
    • Severity: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.0.0 GA
    • Fix Version/s: 4.0.0 GA
    • Component/s: None
    • Labels:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.


      The actual behaviour is stated in the FAQ like this:

      PDI follows Oracle in its use of empty string and NULLs: they are considered to be the same (e.g. in the Filter step). If you would find a step that doesn't follow this convention, let us knows since it's probably a bug.
      When you use e.g. the Add constants step or Generate rows step and enter an empty string, then a NULL value is created.

      This behaviour is constantly the root cause of writing null values to tables where the originating string is an empty string or where strings are turned to null and they should not (out of different reasons). See also PDI-1796 . This behaviour changed from 2.5.x to 3..x and also prevents some people from updating. In 2.5.x empty strings and NULL values are considered to be the same but empty strings were not turned into NULL values.

      An example of a discussion can be found here:

      I propose to discuss the following options:

      1) When comparing strings should we still consider empty strings and NULL to be the same?
      2) When entering empty strings in steps (e.g. Generate rows step) should the value be turned to NULL or should we invent another possibility of entering NULLs in the UI?
      3) When we write empty strings to the outside world (e.g. database tables) should the value be turned to NULL?
      4) When we change the behaviour: How do we deal with the backward compatibility?


          Issue Links



              • Assignee:
                mcasters Matt Casters (Inactive)
                jbleuel Jens Bleuel
              • Votes:
                5 Vote for this issue
                4 Start watching this issue


                • Created: