SNOW-2911288: Error when executing batch update with bindings

[OS-veracardoso]

❗ If you need urgent assistance then file a case with Snowflake Support.
Otherwise continue here.

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of JDBC driver are you using?
   3.27.1

2. What operating system and processor architecture are you using?
   Apple M4

3. What version of Java are you using?
   21

4. What did you do?

   Create the follwoing tables:

      CREATE TABLE QE_TEST_DEV.MARKETO_LEAD (
      	ID	 INTEGER NOT NULL autoincrement START 1 INCREMENT 1 ORDER CONSTRAINT PK_MARKETO_LEAD PRIMARY KEY,
      	NAME VARCHAR(200)
      );
      CREATE TABLE QE_TEST_DEV.PRODUCT_JOURNEY_REQUEST (
      	REQUEST_ID	 INTEGER PRIMARY KEY,
      	MARKETO_LEAD_ID INTEGER REFERENCES QE_TEST_DEV.MARKETO_LEAD (ID),
      	EVENT	VARCHAR(255),
      	STATUS  VARCHAR(5),
      	PRODUCT_JOURNEY_EVENT VARCHAR(255),
      	PRODUCT_JOURNEY_PROCESSED_ON DATETIME,
      	REQUEST_PAYLOAD VARCHAR(500)
      )
   

   MERGE INTO "PRODUCT_JOURNEY_REQUEST" AS "target" USING (
    	SELECT ? AS "REQUEST_ID", ? AS "MARKETO_LEAD_ID", ? AS "EVENT", ? AS "STATUS", ? AS "PRODUCT_JOURNEY_EVENT", ? AS "PRODUCT_JOURNEY_PROCESSED_ON", ? AS "REQUEST_PAYLOAD" 
    	FROM (VALUES (0)) AS "t" ("ZERO")
    ) AS "upsert" 
    ("REQUEST_ID", "MARKETO_LEAD_ID", "EVENT", "STATUS", "PRODUCT_JOURNEY_EVENT", "PRODUCT_JOURNEY_PROCESSED_ON", "REQUEST_PAYLOAD") 
    ON 
    ("target"."REQUEST_ID" = "upsert"."REQUEST_ID")
    WHEN MATCHED THEN 
    	UPDATE SET "MARKETO_LEAD_ID" = "upsert"."MARKETO_LEAD_ID", 
    		"EVENT" = "upsert"."EVENT", 
    		"STATUS" = "upsert"."STATUS", 
    		"PRODUCT_JOURNEY_EVENT" = "upsert"."PRODUCT_JOURNEY_EVENT", 
    		"PRODUCT_JOURNEY_PROCESSED_ON" = "upsert"."PRODUCT_JOURNEY_PROCESSED_ON", 
    		"REQUEST_PAYLOAD" = "upsert"."REQUEST_PAYLOAD"
    WHEN NOT MATCHED 
    	THEN INSERT ("REQUEST_ID", "MARKETO_LEAD_ID", "EVENT", "STATUS", "PRODUCT_JOURNEY_EVENT", "PRODUCT_JOURNEY_PROCESSED_ON", "REQUEST_PAYLOAD") 
    	VALUES ("upsert"."REQUEST_ID", "upsert"."MARKETO_LEAD_ID", "upsert"."EVENT", "upsert"."STATUS", "upsert"."PRODUCT_JOURNEY_EVENT", "upsert"."PRODUCT_JOURNEY_PROCESSED_ON", "upsert"."REQUEST_PAYLOAD")
   

   Try to perform a batch update by running (replace sql with the previous query):

    try (Connection connection = DriverManager.getConnection(qeUrl)) {
        try (
                PreparedStatement statement = connection.prepareStatement(sql);
        ) {
            for (int i = 1;  i <= batchSize; i++) {
                setDPs(statement, i);
                statement.addBatch();
            }
            statement.executeLargeBatch();
        }
    }
   
   
   private static void setDPs(PreparedStatement statement, int rowNum) throws SQLException {
    int i = 1;
    statement.setInt(i++, 1);
    statement.setInt(i++, 1);
    statement.setString(i++, "01-01-update");
    statement.setString(i++, "open%d".formatted(rowNum));
    statement.setString(i++, "event%d".formatted(rowNum));
    statement.setString(i++, Instant.now().toString());
    statement.setString(i++, "something updated %d".formatted(rowNum));
   }
   

   When setting the batchSize to 1 the query is executed successfully and the data is upserted.
   However, when using a batch size greater than 1, the query fails with SQL compilation error: error line 2 at position 14\nBind variable ? not set.

5. What did you expect to see?

The expected behaviour would be for the batch update to execute successfully for batches with more that 1 batch.

6. Can you set logging to DEBUG and collect the logs?

   Request sent to the Snowflake API:

{
  "sqlText" : "MERGE INTO \"QE_TEST_DEV\".\"PRODUCT_JOURNEY_REQUEST\" AS \"target\"\nUSING (SELECT ? AS \"REQUEST_ID\", ? AS \"MARKETO_LEAD_ID\", ? AS \"EVENT\", ? AS \"STATUS\", ? AS \"PRODUCT_JOURNEY_EVENT\", ? AS \"PRODUCT_JOURNEY_PROCESSED_ON\", ? AS \"REQUEST_PAYLOAD\"\nFROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"upsert\" (\"REQUEST_ID\", \"MARKETO_LEAD_ID\", \"EVENT\", \"STATUS\", \"PRODUCT_JOURNEY_EVENT\", \"PRODUCT_JOURNEY_PROCESSED_ON\", \"REQUEST_PAYLOAD\")\nON (\"target\".\"REQUEST_ID\" = \"upsert\".\"REQUEST_ID\")\nWHEN MATCHED THEN UPDATE SET \"MARKETO_LEAD_ID\" = \"upsert\".\"MARKETO_LEAD_ID\", \"EVENT\" = \"upsert\".\"EVENT\", \"STATUS\" = \"upsert\".\"STATUS\", \"PRODUCT_JOURNEY_EVENT\" = \"upsert\".\"PRODUCT_JOURNEY_EVENT\", \"PRODUCT_JOURNEY_PROCESSED_ON\" = \"upsert\".\"PRODUCT_JOURNEY_PROCESSED_ON\", \"REQUEST_PAYLOAD\" = \"upsert\".\"REQUEST_PAYLOAD\"\nWHEN NOT MATCHED THEN INSERT (\"REQUEST_ID\", \"MARKETO_LEAD_ID\", \"EVENT\", \"STATUS\", \"PRODUCT_JOURNEY_EVENT\", \"PRODUCT_JOURNEY_PROCESSED_ON\", \"REQUEST_PAYLOAD\") VALUES (\"upsert\".\"REQUEST_ID\", \"upsert\".\"MARKETO_LEAD_ID\", \"upsert\".\"EVENT\", \"upsert\".\"STATUS\", \"upsert\".\"PRODUCT_JOURNEY_EVENT\", \"upsert\".\"PRODUCT_JOURNEY_PROCESSED_ON\", \"upsert\".\"REQUEST_PAYLOAD\") ;",
  "sequenceId" : 10,
  "bindings" : {
    "1" : {
      "type" : "FIXED",
      "fmt" : null,
      "schema" : null,
      "value" : [ "1", "1" ]
    },
    "2" : {
      "type" : "FIXED",
      "fmt" : null,
      "schema" : null,
      "value" : [ "1", "1" ]
    },
    "3" : {
      "type" : "TEXT",
      "fmt" : null,
      "schema" : null,
      "value" : [ "01-01-update", "01-01-update" ]
    },
    "4" : {
      "type" : "TEXT",
      "fmt" : null,
      "schema" : null,
      "value" : [ "open1", "open2" ]
    },
    "5" : {
      "type" : "TEXT",
      "fmt" : null,
      "schema" : null,
      "value" : [ "event1", "event2" ]
    },
    "6" : {
      "type" : "TEXT",
      "fmt" : null,
      "schema" : null,
      "value" : [ "2025-12-18T13:32:54.191712Z", "2025-12-18T13:32:54.191751Z" ]
    },
    "7" : {
      "type" : "TEXT",
      "fmt" : null,
      "schema" : null,
      "value" : [ "something updated 1", "something updated 2" ]
    }
  },
  "bindStage" : null,
  "describeOnly" : false,
  "parameters" : {
    "NEW_SQL_FORMAT" : false,
    "CLIENT_RESULT_CHUNK_SIZE" : 96
  },
  "queryContextDTO" : {
    "entries" : [ {
      "id" : 0,
      "timestamp" : 1766064870431005,
      "priority" : 0,
      "context" : {
        "base64Data" : "COKOswI="
      }
    } ]
  },
  "describedJobId" : "01c1222e-0a13-31d8-000b-e103fbac416b",
  "querySubmissionTime" : 1766064892836,
  "isInternal" : false,
  "asyncExec" : false
} 

Response:

{
  "data" : {
  "internalError" : false,
  "sourceLocationValid" : true,
  "unredactedFromSecureObject" : false,
  "errorCode" : "002049",
  "age" : 0,
  "sqlState" : "42601",
  "queryId" : "01c1222b-0a13-31c7-000b-e103fbabe753",
  "line" : 2,
  "pos" : 14,
  "type" : "COMPILATION"
},
  "code" : "002049",
  "message" : "SQL compilation error: error line 2 at position 14\nBind variable ? not set.",
  "success" : false,
  "headers" : null
}
```

[sfc-gh-dszmolka]

hi @OS-veracardoso is is same as #1899, and you're observing the expected and documented behaviour:

Only INSERT INTO … VALUES statements can contain array bind variables.

This works the same in all the Snowflake drivers not just JDBC, since this is an existing backend limitation.

To move forward with this, kindly reach out to your Snowflake Sales representative and let them know that you have a use-case which is hampered by this backend limitations. Sales team can sometimes help the product team prioritize amongst the enhancement requests.

Closing this as it is not a JDBC driver issue, and is not even a driver issue.