next »

[Huy H.]

Want to get your feed back on how FGL handles NULL value when comparing variables. 

I've had developers come to me asking why their code doesn't work and they cannot figure out why:

Code

DEFINE cfg_val CHAR(1)
LET cfg_val = get_cfg_val() -- can return null
IF cfg_val != 'Y' THEN
  { code here }
END IF
 

I've known for a while now that when I work with variable that can possibly be NULL, I have to handle it appropriately with IS NULL check. Something to the line of:

Code

DEFINE cfg_val CHAR(1)
LET cfg_val = get_cfg_val() -- can return null
IF cfg_val IS NULL OR cfg_val != 'Y' THEN
  { code here }
END IF
 

I understand this goes back to the way how NULL are handled in databases, but for a programming language, this is a very odd behavior.  I get blank stares and head scratches when I try to explain to other developers.  For cfg_val != 'Y' to return FALSE because cfg_val is NULL just does not make any sense.
 

[Sebastien F.]

Hello,

In such case, I would recommend to make sure that the get_cfg_val() function returns either "Y" or "N" ... but never NULL.

Note also that Genero has the NVL() operator ...

Seb

[David H.]

I feel your pain and had similar issues here with developers. In the end we ended up with two functions that they can wrap around any potentially NULL expressions and decide how they want the NULL case to be treated.

Code

 
FUNCTION null_false(argument)
 
    DEFINE argument SMALLINT
 
    IF (argument IS NULL) THEN
        RETURN FALSE
    END IF
    RETURN argument
 
END FUNCTION
 
 
 
FUNCTION null_true(argument)
 
    DEFINE argument SMALLINT
 
    IF (argument IS NULL) THEN
        RETURN TRUE
    END IF
    RETURN argument
 
END FUNCTION
 
IF null_true(cfg_val != "Y") THEN
   -- do something
END IF
 
 

This makes it very clear how the NULL case is treated!

David

[Leo S.]

Huy, I recently use String.equals() to minimize the NULL surprise whenever possible.
If your return value is a CHAR[40] this isn't a String for sure, but you can use the String constants equals() method and compare the other way round.
HTH, Leo

Code

GLOBALS
  CONSTANT g_yes="y"
END GLOBALS
...
MAIN
  IF g_yes.equals(get_cfg_val()) THEN
    DISPLAY "yes"
  END IF
  IF NOT g_yes.equals(get_cfg_val()) THEN
    DISPLAY "not yes"
  END IF
END MAIN
 
 
FUNCTION get_cfg_val()
  RETURN NULL
END FUNCTION
 

[Huy H.]

Interesting approach Leo. 

Most of the time, the values we are dealing with are database field values, so they are char(n). If you can only get "y".equals(cfg_val) to compile instead of having to use a constant g_yes.equals(), that would be golden! 

[Reuben B.]

Huy,

You aren't the first, and won't be the last.  I did a search and found this old Informix-4gl forum post form from 2005 that diverges into discussing this topic https://comp.databases.informix.narkive.com/VEQoSucd/4gl-compares-null-differently.

The 4gl behaviour is consistent with database logic.  Note the output from this little test

Code

CREATE TABLE foo(key INTEGER, data CHAR(10))
INSERT INTO foo VALUES(1,"Y")
INSERT INTO foo VALUES(2,"N")
INSERT INTO foo VALUES(3,NULL)
 
DISPLAY "Not Equal Y"
DECLARE neqy CURSOR  FROM "SELECT key FROM foo WHERE data != 'Y'"
 
FOREACH neqy INTO key
    DISPLAY key
END FOREACH
 
DISPLAY "Equal Y"
DECLARE eqy CURSOR  FROM "SELECT key FROM foo WHERE data = 'Y'"
 
FOREACH eqy INTO key
    DISPLAY key
END FOREACH
 
DISPLAY "is null"
DECLARE in CURSOR  FROM "SELECT key FROM foo WHERE data IS NULL"
 
FOREACH in INTO key
    DISPLAY key
END FOREACH
 
DISPLAY "is not null"
DECLARE inn CURSOR  FROM "SELECT key FROM foo WHERE data IS NOT NULL"
 
FOREACH inn INTO key
    DISPLAY key
END FOREACH

Code:

Not Equal Y
          2
Equal Y
          1
is null
          3
is not null
          1

How does the database solve this?  You define the column as NOT NULL and then you never have to worry about the fact that !="Y" won't include NULL values.

Seb's solution of ensuring that your function get_cfg_val() never returns NULL you could say is the equivalent.  

Could we ever do something on the 4gl as an equivalent e.g.

Code

FUNCTION get_cfg_val() RETURNS CHAR(1) NOT NULL

... I don't know.  For now you can code at the end of the function ...

 

Code

RETURN nvl(cfg_val, "N")

... and ensure that NULL is never returned.

OR now that we have initialisers could we then code

Code

DEFINE cfg_val CHAR(1) NOT NULL = "Y"

and have a runtime error if the variable value is ever NULL just like a database.  I don't think this would be practical as initialisers aren't aways simple values.


I am a pessimist in this area and think that try as you might, a NULL might sneak in somewhere, and so I prefer a technique that was in that old Informix-4gl link.  We called it "test for success" so rather than coding

Code

IF get_cfg_val() != "Y" THEN
ERROR "CFG must be Y"
...
END IF

you code

Code

IF get_cfgl_val() = "Y" THEN
   # Value OK
ELSE
   ERROR "CFG must be Y"
   ...
END IF

that way if get_cfg_val() does return NULL, it will be caught as you intend

Reuben

[Mark C.]

Sorry to be so late to the party on this one, but perhaps you need to have a discussion with your developers about what NULL really means.  I have found that when they truly understand NULL, this makes a bit more sense.  From your description, I know you understand what's going on, but the following may help you explain things to your developers.

In the old days where everything was done with flat files and COBOL, programmers would typically initialize values with "" (empty string) for character fields and 0 (zero) for numeric fields.  The unfortunate side effect of this is that it implies something that may or may not be true.  The value of the field in question may or may not really be an empty string or a zero, but there is no way to tell whether the value in the field is the correct value or simply the default that was chosen as an default.

Relational databases introduced the NULL to address that. 

What I tell developers is the NULL equates to "I don't know".  How can you compare a known value to "I don't know"?  Comparisons require that both values being compared are known, otherwise you can't have a valid comparison.

Using your example (not sure how to get the pretty box you had around your code):

DEFINE cfg_val CHAR(1)
LET cfg_val = get_cfg_val() -- can return null
IF cfg_val != 'Y' THEN
 { code here }
END IF

if cfg_val is "I don't know", you can't say for certain whether it is or is not equal to any particular value.  That is the reason that the rules of relational databases requires any comparison with one or more NULLs to always return false.  That's why you can't say:

if cfg_val = NULL THEN ...

The hardcoded NULL on the right side of the equal sign would cause the comparison to be false even if cfg_val was also NULL.  That's why the only way to compare something to NULL is with the IS NULL or IS NOT NULL expression.  Or, when available, you can use the nvl() function as an alternate approach.

I hope this helps your developers.

[Reuben B.]

(not sure how to get the pretty box you had around your code):

Above the textedit where you type in your comment, you will see some buttons and two ComboBoxes Change Color and Code Syntax.

Simply highlight the text and either click the

"Insert Quote" button to mark text as a quote like this

or

Code

# the "Code Syntax" button and select the style of code (in most cases, Genero, per. or XML) so that your code is in a fixed-font, line numbered, keywords highlighted like this