When we adding batched query in form of:
insert ... values (:some_id, (SELECT X from other_table where fk = :some_id))

It seems that result of sub-query somewhere caches, and produces invalid data.

Here's test excerpt which also failing:

@Test
public void testNonCachingBatchUpdate() throws SQLException {

createTable("testNonCachingBatchUpdate1", "col int, val int");
createTable("testNonCachingBatchUpdate2", "col int, val int");
Statement statement = sharedConnection.createStatement();

//add 100 data
StringBuilder sb = new StringBuilder("INSERT INTO testNonCachingBatchUpdate1(col, val) VALUES (0,0), (1,1)");
statement.execute(sb.toString());

try (PreparedStatement preparedStatement = sharedConnection.prepareStatement(
"INSERT INTO testNonCachingBatchUpdate2(col, val) VALUES (?, " +
"(SELECT val FROM testNonCachingBatchUpdate1 where col = ?))"))

{ preparedStatement.setInt(1, 0); preparedStatement.setInt(2, 0); preparedStatement.addBatch(); preparedStatement.setInt(1, 1); preparedStatement.setInt(2, 1); preparedStatement.addBatch(); int[] results = preparedStatement.executeBatch(); assertEquals(2, results.length); }

//check results
try (ResultSet rs = statement.executeQuery("SELECT * FROM testNonCachingBatchUpdate2 order by col"))

{ assertTrue(rs.next()); assertEquals(rs.getInt(1), 0); assertEquals(rs.getInt(2), 0); assertTrue(rs.next()); assertEquals(rs.getInt(1), 1); //this fails assertEquals(rs.getInt(2), 1); assertFalse(rs.next()); }

}