Wrong result of 'insert into' caused by large string in batches #375
Description
Driver version: 6.2.0
I create a table and insert five rows.
The resulting table in SQL Server contains five rows, but the third row is missing while the fourth row is duplicated.
The table has two columns, int and nvarchar(max), and I insert each row in a separate batch.
The rows look like these:
0,a
1,b
2,ccc...
3,d
4,e
'ccc...' means a string which consists of 4001 characters.
Selecting the rows of the table returns this result:
0,a
1,b
2,ccc...
4,e
4,e
If I create the table with a primary index on the first column, then inserting the rows leads to a primary key violation.
Here is the code to reproduce the issue.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;
public class BatchesWithLargeStringTest {
public static void main(String[] args) throws Exception {
try (Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=DATABASE", "user", "password")) {
connection.setAutoCommit(false);
// create a table with two columns
boolean createPrimaryKey = false;
try (Statement createStatement = connection.createStatement()) {
createStatement.execute("if object_id('TEST_TABLE', 'U') is not null\ndrop table TEST_TABLE;");
if (createPrimaryKey) {
createStatement.execute("create table TEST_TABLE ( ID int, DATA nvarchar(max), primary key (ID) );");
} else {
createStatement.execute("create table TEST_TABLE ( ID int, DATA nvarchar(max) );");
}
}
connection.commit();
// build a String with 4001 characters
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < 4001; i++) {
stringBuilder.append('c');
}
String largeString = stringBuilder.toString();
// insert five rows into the table; use a batch for each row
try (PreparedStatement statement = connection.prepareStatement("insert into TEST_TABLE values (?,?)")) {
// 0,a
statement.setInt(1, 0);
statement.setNString(2, "a");
statement.addBatch();
// 1,b
statement.setInt(1, 1);
statement.setNString(2, "b");
statement.addBatch();
// 2,ccc...
statement.setInt(1, 2);
statement.setNString(2, largeString);
statement.addBatch();
// 3,d
statement.setInt(1, 3);
statement.setNString(2, "d");
statement.addBatch();
// 4,e
statement.setInt(1, 4);
statement.setNString(2, "e");
statement.addBatch();
statement.executeBatch();
}
connection.commit();
// check the data in the table
Map<Integer, String> selectedValues = new LinkedHashMap<>();
try (PreparedStatement statement = connection.prepareStatement("select * from TEST_TABLE;")) {
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
int id = resultSet.getInt(1);
String data = resultSet.getNString(2);
System.out.println(id + "=" + data);
if (selectedValues.containsKey(id)) {
throw new IllegalStateException("Found duplicate id: " + id);
}
selectedValues.put(id, data);
}
}
}
}
}
}