Some SQL Query throw error `ResultSet` for Vertica DB

  • 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:

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 ?

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"}}

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 :thinking:

Hi @Tess can you tell me more about this

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?