Some SQL Query throw error `ResultSet` for Vertica DB

syahmispectrum · November 13, 2024, 4:29am

Goal: I want to perform a query on my Vertica DB. Reference to the example Query
The connection is alright since I can call some other query with success.
Steps:
Using Retool version: 3.75.3 - self hosted
Goto Workflow
Create a Resource Query
Choose my Vertica DB resource
Write my query and run
The block will throw error ResultSet was expected
Details:
The code snippet

CREATE TABLE Umat AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',
exclude_columns='id', key_columns='id') OVER() FROM small_svd;

The error returned:

Error running instance method
java.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query "CREATE TABLE Umat AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',
exclude_columns='id', key_columns='id') OVER() FROM small_svd;". Query not executed.

Screenshots:

Tess · November 21, 2024, 9:37pm

Hi @syahmispectrum,

I am looking into this! Are there any additional container logs around the time of running this query?

Do any queries to this resource work (i.e a simple select statement)?

I assume by the error, but just to double check, the table is not created ?

syahmispectrum · November 22, 2024, 4:10am

Hi @Tess ,

Are there any additional container logs around the time of running this query?

Thank you for looking into this. Not sure which container logs you meant, but I attached some logs at the end of this post.

Do any queries to this resource work (i.e a simple select statement)?

Yes, most queries works, just some of it that does not work. Even basic CREATE TABLE example(id INT); works. Two kind of error identified for now are those that has something to do with doubleValueSync and ResultSet errors.

I assume by the error, but just to double check, the table is not created ?

Yes, regarding the error mentioned, the table was not created.

Below are the full logs taken from running the query on the UI.

{"data":null,"error":{"error":true,"message":"Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n","isRetoolSystemError":false,"queryExecutionMetadata":{"estimatedResponseSizeBytes":1121,"resourceTimeTakenMs":2613,"isPreview":false,"resourceType":"vertica","lastReceivedFromResourceAt":1732246750279}}}

While here is the log taken from Docker container workflows-backend-1:

2024-11-22 11:46:37 workflows-backend-1   | {"level":"info","message":{"http":{"method":"POST","url_base":"http://workflows-backend:3000","url_path":"/api/workflow/runQueryForSandboxed"},"type":"REQUEST_BEGIN"},"organization":{"id":1,"name":"syahmi@email.com"},"pid":84,"requestId":"3edf74e3-4249-438e-bb36-fd986d41a389","timestamp":"2024-11-22T03:46:37.285Z","user":{"email":"syahmi@email.com","sid":"user_22ccf937ebc34c3887bbbb72ff499155"}}
2024-11-22 11:46:37 workflows-backend-1   | {"level":"info","message":{"context":"workflow","dbconnectorType":"","environment":"production","ipAddress":"::ffff:172.20.0.2","isLatestConnectorVersion":true,"isPreview":false,"orgSid":"org_58bbf69b679f4c63a198fe90d7776e82","queryHash":"abd68dbf679fd41c09fefb20f2f2241a619015acf2b616a698d1dc83c10da94e","resourceName":"c40d1972-4457-4d90-85bc-c40f378bdbe1","resourceType":"vertica","retoolHeaders":{"x-retool-dbconnector-hash":"3ae1f150fdc209d6","x-retool-resource-type":"vertica"},"type":"QUERY_REQUEST","userError":false,"userSid":"user_22ccf937ebc34c3887bbbb72ff499155"},"pid":84,"requestId":"3edf74e3-4249-438e-bb36-fd986d41a389","timestamp":"2024-11-22T03:46:37.516Z"}
2024-11-22 11:46:37 workflows-backend-1   | [winston] Attempt to write logs with no transports, which can increase memory usage: {"cause":{},"level":"error"}
2024-11-22 11:46:37 workflows-backend-1   | {"level":"info","message":{"context":"workflow","dbconnectorType":"","endTime":"1,254,537,744,573","environment":"production","ipAddress":"::ffff:172.20.0.2","isLatestConnectorVersion":true,"isPreview":false,"isRetoolSystemError":false,"msg":"Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n","orgSid":"org_58bbf69b679f4c63a198fe90d7776e82","queryHash":"abd68dbf679fd41c09fefb20f2f2241a619015acf2b616a698d1dc83c10da94e","resourceName":"c40d1972-4457-4d90-85bc-c40f378bdbe1","resourceType":"vertica","retoolHeaders":{"x-retool-dbconnector-hash":"3ae1f150fdc209d6","x-retool-resource-type":"vertica"},"stack":"Error: Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n","startTime":"1,254,396,522,891","timeTakenMillis":141,"type":"QUERY_FAILED","userError":false,"userSid":"user_22ccf937ebc34c3887bbbb72ff499155"},"pid":84,"requestId":"3edf74e3-4249-438e-bb36-fd986d41a389","timestamp":"2024-11-22T03:46:37.673Z"}
2024-11-22 11:46:37 workflows-backend-1   | {"environment":"production","isEditorMode":"undefined","isPreview":false,"level":"info","message":{"dbconnectorRegion":"us-west-2","environment":"production","errorMessage":"Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n","estimatedResponseSizeBytes":1121,"internalLatencyMs":53,"isLatestConnectorVersion":true,"isPreview":false,"orgSid":"org_58bbf69b679f4c63a198fe90d7776e82","queryResponseTimeMs":194,"resourceName":"c40d1972-4457-4d90-85bc-c40f378bdbe1","resourceType":"vertica","shardingEnabled":false,"statusCode":400,"success":false,"type":"QUERY_RESULT"},"orgSid":"org_58bbf69b679f4c63a198fe90d7776e82","organization":{"id":1,"name":"syahmi@email.com"},"pid":84,"queryName":"undefined","requestId":"3edf74e3-4249-438e-bb36-fd986d41a389","resourceName":"c40d1972-4457-4d90-85bc-c40f378bdbe1","resourceType":"vertica","timestamp":"2024-11-22T03:46:37.685Z","user":{"email":"syahmi@email.com","sid":"user_22ccf937ebc34c3887bbbb72ff499155"}}
2024-11-22 11:46:37 workflows-backend-1   | {"context":"workflow","environment":"production","isEditorMode":"undefined","isPreview":false,"isRetoolSystemError":false,"level":"info","message":"RunQueryV2 error detected","msg":"Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n","orgSid":"org_58bbf69b679f4c63a198fe90d7776e82","organization":{"id":1,"name":"syahmi@email.com"},"pid":84,"queryName":"undefined","requestId":"3edf74e3-4249-438e-bb36-fd986d41a389","resource":"vertica","resourceName":"c40d1972-4457-4d90-85bc-c40f378bdbe1","resourceType":"vertica","stack":"eb: Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n\n    at /retool_backend/bundle/main.js:6837:15965\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n    at async Lw7 (/retool_backend/bundle/main.js:6855:42524)\n    at async YSd (/retool_backend/bundle/main.js:8175:107)\n    at async /retool_backend/bundle/main.js:8175:26241","timestamp":"2024-11-22T03:46:37.708Z","user":{"email":"syahmi@email.com","sid":"user_22ccf937ebc34c3887bbbb72ff499155"}}
2024-11-22 11:46:37 workflows-backend-1   | {"endpoint":{"criticalityTier":"tier3","path":"/api/workflow/runQueryForSandboxed","service":"backend","team":"@tryretool/workflows"},"environment":"production","isEditorMode":"undefined","isPreview":false,"level":"warn","msg":"Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n","orgSid":"org_58bbf69b679f4c63a198fe90d7776e82","organization":{"id":1,"name":"syahmi@email.com"},"pid":84,"queryName":"undefined","requestId":"3edf74e3-4249-438e-bb36-fd986d41a389","resourceName":"c40d1972-4457-4d90-85bc-c40f378bdbe1","resourceType":"vertica","stack":"eb: Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n\n    at /retool_backend/bundle/main.js:6837:15965\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n    at async Lw7 (/retool_backend/bundle/main.js:6855:42524)\n    at async YSd (/retool_backend/bundle/main.js:8175:107)\n    at async /retool_backend/bundle/main.js:8175:26241","timestamp":"2024-11-22T03:46:37.711Z","type":"MicroserviceError","user":{"email":"syahmi@email.com","sid":"user_22ccf937ebc34c3887bbbb72ff499155"}}
2024-11-22 11:46:37 workflows-backend-1   | {"endpoint":{"criticalityTier":"tier3","path":"/api/workflow/runQueryForSandboxed","service":"backend","team":"@tryretool/workflows"},"environment":"production","error":{"level":"warn","msg":"Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n","stack":"eb: Error running instance method\njava.sql.SQLDataException: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query \"CREATE TABLE Umat1 AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel',\nexclude_columns='id', key_columns='id') OVER() FROM small_svd;\". Query not executed. \n\tat com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)\n\tat com.vertica.jdbc.common.SStatement.checkCondition(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeWithParams(Unknown Source)\n\tat com.vertica.jdbc.common.SPreparedStatement.executeQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n\n    at /retool_backend/bundle/main.js:6837:15965\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n    at async Lw7 (/retool_backend/bundle/main.js:6855:42524)\n    at async YSd (/retool_backend/bundle/main.js:8175:107)\n    at async /retool_backend/bundle/main.js:8175:26241","type":"MicroserviceError"},"isEditorMode":"undefined","isPreview":false,"level":"info","message":{"http":{"method":"POST","request":{"time":0.43630123100010676},"status_code":400,"url_base":"http://workflows-backend:3000","url_path":"/runQueryForSandboxed"},"type":"REQUEST_FINISH"},"orgSid":"org_58bbf69b679f4c63a198fe90d7776e82","organization":{"id":1,"name":"syahmi@email.com"},"pid":84,"queryName":"undefined","requestId":"3edf74e3-4249-438e-bb36-fd986d41a389","resourceName":"c40d1972-4457-4d90-85bc-c40f378bdbe1","resourceType":"vertica","timestamp":"2024-11-22T03:46:37.721Z","user":{"email":"syahmi@email.com","sid":"user_22ccf937ebc34c3887bbbb72ff499155"}}

Tess · November 22, 2024, 11:50pm

Hi @syahmispectrum Thanks! Do you have access to any Vertica logs as well? Does this query work if you run it directly on your database, and if so, what is the response?

It looks like we have seen similar errors before when the query doesn't return any data, but in those cases, the query still works

jeesylorie · November 23, 2024, 12:21pm

Hi @Tess can you tell me more about this

syahmispectrum · November 25, 2024, 2:00am

Hi @Tess ,

I can run the command successfully using vsql and also from vertica_python.

The vsql terminal just returned CREATE TABLE after running the query. While the vertica_python returned empty/nothing, but the table was still created.

Those similar cases mentioned where the query still works, do they require some settings that I may missing out?

Tess · December 24, 2024, 12:11am

No other settings We just noticed that despite the error, it worked.

I will follow up if we're able to find a fix for this bug