Major Description
This likely has to do with the padding of the fixed width PK column. If I change the value of tenant_id column from "tenant1" to "tenant1tenant12" (15 characters), query passes.
@Test
public void testRowValueConstructorWithFixedWidthTrailingPK() throws Exception {
String baseTable = "testIndexesOnTenantViews".toUpperCase();
long ts = nextTimestamp();
try (Connection conn = getConnection(ts)) {
conn.createStatement().execute("CREATE TABLE " + baseTable + " (PK2 DATE NOT NULL, PK3 INTEGER NOT NULL, TENANT_ID CHAR(15) NOT NULL, KV1 VARCHAR, KV2 VARCHAR, KV3 VARCHAR CONSTRAINT PK PRIMARY KEY(PK2, PK3, TENANT_ID))");
}
Date upsertedDate = new Date(5);
String tenantId = "tenant1";
try (Connection conn = getConnection(nextTimestamp())) {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + baseTable + " (TENANT_ID, PK2, PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?, ?, ?)");
stmt.setString(1, tenantId);
stmt.setDate(2, upsertedDate);
stmt.setInt(3, 33);
stmt.setString(4, "KV1");
stmt.setString(5, "KV2");
stmt.setString(6, "KV33");
stmt.executeUpdate();
conn.commit();
conn.prepareStatement("UPSERT INTO " + baseTable + " (TENANT_ID, PK2, PK3, KV1, KV2, KV3) VALUES (?, ?, ?, ?, ?, ?)");
stmt.setString(1, tenantId);
stmt.setDate(2, upsertedDate);
stmt.setInt(3, 44);
stmt.setString(4, "KV11");
stmt.setString(5, "KV22");
stmt.setString(6, "KV44");
stmt.executeUpdate();
conn.commit();
}
// Verify that data can be queried using tenant view and tenant view index
try (Connection conn = getConnection(nextTimestamp())) {
// Query the tenant view
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + baseTable + " WHERE (PK2, KV3, TENANT_ID) IN ((?, ?, ?), (?, ?, ?))");
stmt.setDate(1, upsertedDate);
stmt.setString(2, "KV33");
stmt.setString(3, tenantId);
stmt.setDate(4, upsertedDate);
stmt.setString(5, "KV44");
stmt.setString(6, tenantId);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
assertTrue(rs.next());
assertFalse(rs.next());
}
}