The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Length of a string in a return type must be greater than zero

After update vertica version from 8.1 to 9.2. We begin to get an error [42V13]ERROR 3854: Length of a string in a return type must be greater than zero.
This error throws from PHP function odbc_prepare with sql query string like 'UPDATE TableAlisa SET VMapFieldName = MapJSONExtractor(?)'

Comments

  • Bryan_HBryan_H Employee

    That UPDATE might touch every row in table TabeAlisa, so the reported error might be masking something else. Does this only happen in the prepared statement? Can you run the update in vsql with the same input string and get the same error? Is there anything in vertica.log on the node that processes the query?

  • LobanovLobanov
    edited June 20

    Yep. error was throwed only from preparing statement. When I try to update row in vsql directly, everything is ok

  • Jim_KnicelyJim_Knicely Administrator
    edited June 20

    The error indicates that you are passing in an empty string to the MAPTOSTRING function.

    dbadmin=> SELECT MAPTOSTRING('');
    ERROR 3854:  Length of a string in a return type must be greater than zero
    
    dbadmin=> CREATE TABLE TableAlisa (VMapFieldName VARCHAR);
    CREATE TABLE
    
    dbadmin=> UPDATE TableAlisa SET VMapFieldName = MAPTOSTRING('');
    ERROR 3854:  Length of a string in a return type must be greater than zero
    

    Is there a way you can print out the prepared statement prior to exectuing it so that you can figure out when this is happeing?

  • Error appears when I try to prepare statment like that 'UPDATE TableAlisa SET VMapFieldName = MapJSONExtractor(?)'

  • When I make reques like 'UPDATE TableAlisa SET VMapFieldName = MapJSONExtractor({valid json here})' everything ok

  • Jim_KnicelyJim_Knicely Administrator

    Maybe. Is there a a way to show the ? prior to executing 'UPDATE TableAlisa SET VMapFieldName = MapJSONExtractor(?)'

  • Bryan_HBryan_H Employee

    I think Jim_Knicely is asking to test the values passed to odbc_execute(), for example:

    $stmt = odbc_prepare($conn, 'UPDATE TableAlisa SET VMapFieldName = MapJSONExtractor(?)');
    $success = odbc_execute($stmt, array($a));

    In this code, can we check the value of $a to ensure that it is not empty?

  • odbc_prepare($conn, 'UPDATE TableAlisa SET VMapFieldName = MapJSONExtractor(?)');
    In this code vertice throws error.
    And $a is checked for not empty before pass to update request

  • Bryan_HBryan_H Employee

    Does the vertica.log show a more specific error message, ideally with the invalid input or output? We would need to know what value is causing the error so our engineers can determine what the issue is. I've tried a number of values, but I can't reproduce the exception on my cluster. Also, what version of ODBC driver is PHP using to send the statement?

  • Log

    2019-06-27 17:42:45.063 Init Session:0x7f42c0ff9700 [Session] [Query] TX:0(v_tracker_node0001-3314:0x16c) set session autocommit to off
    2019-06-27 17:42:45.065 Init Session:0x7f42c0ff9700 [Session] [PQuery] TX:0(v_tracker_node0001-3314:0x16c) INSERT INTO Orders (trackCode, dateTime, orderId, sid, techSid, uuid, stateId, amount, margin, revenue, comment, contactPerson, callerPhone, email, type, callerPhoneHash, tagsVMap, tags, items, hash, importKey, callerPhoneExtraVMap, callerPhoneExtra, matching_by_phone_hash, importSourceId, recognitionDate, externalCallId, isDemo, transferCounterId, transferId, transferUpdatedDate, createdDate, updatedDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, MapJSONExtractor(?), ?, ?, ?, ?, MapJSONExtractor(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    2019-06-27 17:42:45.066 Init Session:0x7f42c0ff9700-a000000000d0a8 [Txn] Begin Txn: a000000000d0a8 'INSERT INTO Orders (trackCode, dateTime, orderId, sid, techSid, uuid, stateId, amount, margin, revenue, comment, contactPerson, callerPhone, email, type, callerPhoneHash, tagsVMap, tags, items, hash, importKey, callerPhoneExtraVMap, callerPhoneExtra, matching_by_phone_hash, importSourceId, recognitionDate, externalCallId, isDemo, transferCounterId, transferId, transferUpdatedDate, createdDate, updatedDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, MapJSONExtractor(?), ?, ?, ?, ?, MapJSONExtractor(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
    2019-06-27 17:42:45.067 Init Session:0x7f42c0ff9700-a000000000d0a8 @v_tracker_node0001: 42V13/3854: Length of a string in a return type must be greater than zero
    LOCATION: checkReturnTypesLength, /data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_2_1-x_grader/build/vertica/Catalog/LanguageSupport.cpp:2044
    2019-06-27 17:42:45.067 Init Session:0x7f42c0ff9700-a000000000d0a8 [Session] [Query] TX:a000000000d0a8(v_tracker_node0001-3314:0x16c) ROLLBACK
    2019-06-27 17:42:45.069 Init Session:0x7f42c0ff9700-a000000000d0a8 [Txn] Rollback Txn: a000000000d0a8 'INSERT INTO Orders (trackCode, dateTime, orderId, sid, techSid, uuid, stateId, amount, margin, revenue, comment, contactPerson, callerPhone, email, type, callerPhoneHash, tagsVMap, tags, items, hash, importKey, callerPhoneExtraVMap, callerPhoneExtra, matching_by_phone_hash, importSourceId, recognitionDate, externalCallId, isDemo, transferCounterId, transferId, transferUpdatedDate, createdDate, updatedDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, MapJSONExtractor(?), ?, ?, ?, ?, MapJSONExtractor(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
    2019-06-27 17:42:45.069 Init Session:0x7f42c0ff9700 [Session] [Query] TX:0(v_tracker_node0001-3314:0x16c) set session autocommit to on
    2019-06-27 17:42:45.090 Init Session:0x7f42c0ff9700 @v_tracker_node0001: 00000/4719: Session v_tracker_node0001-3314:0x16c ended; closing connection (connCnt 1)

  • vertica version
    Vertica Analytic Database v9.2.1-0

    odbc driver version 9.2.1-0

  • Bryan_HBryan_H Employee

    Are you able to put together a simple reproducer case, ideally a single SELECT MapJSONExtract() statement where the JSON causes the error? I've checked several other support cases and bug reports but I can't reproduce this issue on 9.2.1-0. It may help to open a support ticket if you need to send sensitive data.
    Thanks!

  • define('DB_HOST', '127.0.0.1');
    define('DB_PORT', '5433');
    define('DB_NAME', '');
    define('DB_USER', '');
    define('DB_PASSWORD', '');

    $conn = odbc_connect(
    sprintf('Driver=Vertica;Servername=%s;Port=%s;Database=%s;', DB_HOST, DB_PORT, DB_NAME),
    DB_USER,
    DB_PASSWORD
    );

    odbc_exec($conn,"CREATE SCHEMA IF NOT EXISTS reproducer");
    checkDBError($conn);

    odbc_exec($conn,"DROP TABLE SomeTable");
    checkDBError($conn);

    odbc_exec($conn,'CREATE TABLE SomeTable(someId IDENTITY , someMap long varbinary(32768))');
    checkDBError($conn);

    odbc_prepare ($conn , 'UPDATE SomeTable SET someId = ?, someMap = MapJSONExtractor(?)');
    checkDBError($conn);

    function checkDBError($conn) {
    if ($error = odbc_errormsg($conn)) {
    print_r($error);
    }
    }

  • Bryan_HBryan_H Employee

    Hi, this appears to be a bug in ODBC driver so I will report to the developers. Thanks for bringing this to our attention. Here is a much simpler test case:

    $ isql -vvv vertica DbUser Password
    +---------------------------------------+
    | Connected! |
    | |
    | sql-statement |
    | help [tablename] |
    | quit |
    | |
    +---------------------------------------+
    SQL> UPDATE SomeTable SET someId = ?, someMap = MapJSONExtractor(?);
    [42V13]ERROR 3854: Length of a string in a return type must be greater than zero

    [ISQL]ERROR: Could not SQLPrepare

  • What time the fix can take? Approximately

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.