NULL inside Lua scripts and user-defined functions

When programming in Lua it is important to understand the differnet meaning 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 . 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