Netezza Performance Server

 View Only
  • 1.  GROUP_CONCAT() - ERROR: String returned from Lua is larger than state value definition!

    Posted Mon July 11, 2022 03:39 PM
    I am using GROUP_CONCAT to concatenate a field.  

    There can be many instances of the field to be concatenated. 

    I am obtaining the following error when using GROUP_CONCAT() in a case were I have many instances to be grouped:
    ERROR: String returned from Lua is larger than state value definition!

    Any ideas on what is the limitation with GROUP_CONCAT?

    Any way to increase the limit?

    ------------------------------
    David Briars
    ------------------------------

    #NetezzaPerformanceServer


  • 2.  RE: GROUP_CONCAT() - ERROR: String returned from Lua is larger than state value definition!

    Posted Fri July 15, 2022 03:58 PM
    NPS Max size for CHAR, VARCAHR is 64000 bytes,  NCHAR, NVARCHAR is 16,000  these are hard limits.  LUA's limit is greater,  so Lua should not be the limiting factor.  If your not using varchar(ANY) you may want to consider changing your code.

    ------------------------------
    John Skier
    Integration Architect
    IBM
    682 220 6587
    ------------------------------



  • 3.  RE: GROUP_CONCAT() - ERROR: String returned from Lua is larger than state value definition!

    Posted Fri July 15, 2022 04:52 PM
    With a little more testing I have determined that as long as the resultant field is less than 3,975 bytes, my query does not get the 'ERROR: String returned from Lua is larger than state value definition!' message. 

    My code looks like this:
    GROUP_CONCAT(NVL(mypool,'')) AS pool​

    Is there some way to allow the results of the GROUP_CONCAT to return more than 3,975 bytes? 

    'mypool' is VARCHAR(7) in the database, if that is a factor. 



    ------------------------------
    David Briars
    ------------------------------



  • 4.  RE: GROUP_CONCAT() - ERROR: String returned from Lua is larger than state value definition!

    Posted Mon July 18, 2022 10:59 AM

    I was not able to repro the error mentioned for a varchar(3975). But the reason for this could be that the mentioned result and state value is 4096.

    Compiling: /nz/extensions/nz/nzlua/examples/group_concat.nzl
    ####################################################################

    UdxName      =  group_concat

    UdxType      =  UDA

    Arguments    =  VARCHAR(128)

    Result       =  VARCHAR(4096)

    State        =  VARCHAR(4096)

    Dependencies =  INZA.INZA.LIBNZLUA_11_2_25

    Comment      =  MySQL group_concat function

    NZUDXCOMPILE OPTIONS: (--replbyval --unfenced --type ANY --mem 2m)

    CREATE AGGREGATE

    COMMENT

     

    Can you provide more details around the repro?

    • DDL of the database objects involved.
    • The exact SQL.
    • Also are you using the lua source from /nz/extensions/nz/nzlua/examples/group_concat.nzl ?


    ------------------------------
    Nikita Nirbhavane
    ------------------------------



  • 5.  RE: GROUP_CONCAT() - ERROR: String returned from Lua is larger than state value definition!

    Posted Tue July 19, 2022 10:52 AM
    Thanks for your reply Nikita.

    Here is what I see for GROUP_CONCAT via Aginity/Workbench: 


    I see the same result/return value that you see -> 4096.

    Can that value be increased?

    ------------------------------
    David Briars
    ------------------------------



  • 6.  RE: GROUP_CONCAT() - ERROR: String returned from Lua is larger than state value definition!

    Posted Thu July 21, 2022 08:57 AM
    Hi David,

    Yes you can increase the value as follows. However, I do not know how much precise value you need to increase for your case to work.

    • The source file on system is group_concat.nzl. Usually it is under the directory /nz/extensions/nz/nzlua/examples/
    • You can change the varchar sizes in getState() and getResult() optimally to suit your repro scenario.
      • function getState()
        state={}
        state[1] = { "", varchar(10000) }
        return state
        end

        function getResult()
        return varchar(10000)
        end
    • Once you change the source you re-compile using /nz/extensions/nz/nzlua/bin/nzl  /nz/extensions/nz/nzlua/examples/group_concat.nzl -d <dataabsename>
    Hope this helps!

    ------------------------------
    Nikita Nirbhavane
    ------------------------------



  • 7.  RE: GROUP_CONCAT() - ERROR: String returned from Lua is larger than state value definition!

    Posted Thu July 21, 2022 11:56 AM
    Thank you so much for the information, Nikita. 

    I have forwarded to my Netezza DBA/Network team. 

    I'll report back what I hear. 

    Once again, many thanks.

    ------------------------------
    David Briars
    ------------------------------