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

Lobanov

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(?)'

Bryan_H

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?

Lobanov

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

Jim_Knicely

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?

Lobanov

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

Lobanov

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

Jim_Knicely

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

Lobanov

According this docs
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/ClientDriverMisc/UsingPreparedStatements.htm
in prepare statment we can use ? for placeholders

Bryan_H

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?

Lobanov

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_H

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?

Lobanov

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)

Lobanov

vertica version
Vertica Analytic Database v9.2.1-0

odbc driver version 9.2.1-0

Bryan_H

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!

Lobanov

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_H

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

Lobanov

What time the fix can take? Approximately

Lobanov

Hello. Any news about this issue?

Bryan_H

Unfortunately, this issue remains open as of 10.0 as there have been few other reports of this issue. If you are able to file a support request, I can add that to the internal ticketing system.