[SOL-129] NULL inside Lua scripts and user-defined functions Created: 13.08.2014  Updated: 08.06.2020  Resolved: 08.06.2020

Status: Obsolete
Project: Solution Center
Component/s: EXASolution
Affects Version/s: None
Fix Version/s: None

Type: Explanation
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: None

Issue Links:
Related
is related to SOL-127 NULL in Exasol Obsolete
Explanation:

Note: This solution is no longer maintained. For the latest information, please refer to our Knowledge Base:

https://community.exasol.com/t5/database-features/null-in-udfs-and-lua-scripts/ta-p/364

-------------------------------------------------------------------------------------------------------------------------------------------------

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
Category 1: Scripting
Generated at Sun Jul 12 14:50:51 CEST 2020 using Jira 7.13.13#713013-sha1:4c0f5f5e3383570393fbbf3d2fd5de1eb4057c36.