Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-129

NULL inside Lua scripts and user-defined functions

    XMLWordPrintable

    Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Explanation:
      Hide

      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
      Show
      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
    • Category 1:
      Scripting

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: