Community Manager
Community Manager

Background

When programming in Lua, it is important to understand the different meanings of nil and NULL. Nil is the "unknown type" in the context of Lua whereas null and NULL represent the SQL NULL. The NULL constant is not part of standard Lua and was added by us so the user can do NULL comparisons of result data.

Explanation

The following example shows the different meaning of nil and NULL in the context of Lua when reading a resultset:

  • NULL means that the Value read from the database is NULL
  • nil means that the referenced column doesn't exist
create table null_nil_test (COL1 INT);
insert into null_nil_test values NULL;

--/
create or replace lua script null_nil_explanation as

res = query ([[ select * from null_nil_test]])
if res[1].COL1 == null then --column COL1 exists and contains a NULL value
	output('res[1].COL1 is null')
end

if res[1].COL1 == nil then --this condition is false, so no output
	output('res[1].COL1 is nil')
end

if res[1].X == nil then --column X doesn't exist so it equals to nil
	output('res[1].X is nil')
end

if null ~= nil then --null is not equal to nil
	output('null is not equal to nil')
end
/

execute script null_nil_explanation with output;

Please note that comparisons in Lua always yield true or false, so

  • both (nil==nil) and (null==null) are true
  • while (nil==null) is false.

Neither nil nor null can be concatenated using '..'. While Lua knows about nil and tells you so in the error message, it can not distinguish between null and other types. Errors related to null values will usually contain a reference to a user-defined type.

Please also note that implicit boolean conversions in Lua are not always intuitive:
if( X ) then ... end will not execute the code block only when X

  • contains the boolean value false
  • is nil (does not exist)

It will execute in any other case, including

  • an empty string
  • the (Lua) value null

Additional References