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.
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.COL1 == null then --column COL1 exists and contains a NULL value
output('res.COL1 is null')
if res.COL1 == nil then --this condition is false, so no output
output('res.COL1 is nil')
if res.X == nil then --column X doesn't exist so it equals to nil
output('res.X is nil')
if null ~= nil then --null is not equal to nil
output('null is not equal to nil')
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) aretrue
while (nil==null) isfalse.
Neither nil nor null can beconcatenatedusing '..'. 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 auser-defined type.
Please also note thatimplicit boolean conversionsin Lua are not always intuitive: if( X ) then ... endwillnotexecute the code block only whenX