in phoenix-core/src/it/java/org/apache/phoenix/end2end/VarBinaryEncoded1IT.java [1128:1519]
public void testVarBinaryPkSchema5() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
final String tableName = generateUniqueName();
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
conn.createStatement().execute("CREATE TABLE " + tableName
+ " (PK1 VARCHAR, PK2 VARBINARY_ENCODED, PK3 VARCHAR,"
+ " COL1 VARBINARY_ENCODED, COL2 VARBINARY_ENCODED,"
+ " COL3 VARBINARY_ENCODED CONSTRAINT pk PRIMARY KEY(PK1, PK2, PK3)) "
+ tableDDLOptions);
String b1 = "Rq1MxfBzM8DaPIjTamS94s9KbaC098Tou4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b2 = new byte[] {57, -83, 2, 83, -7, 12, -13, 4};
String b3 = "RTHsOtc26ErkbgPDtTvsQl9M0fLOGQ6b";
byte[] b4 = new byte[] {56, 50, 19, 34, 83, -101, -102, 91};
byte[] b5 = new byte[] {10, 55, 19, -5, -34, 0, 0, 0, 0, 1};
byte[] b6 = new byte[] {-11, 55, -119, 8, 0, 1, 2, -4, 33};
String b10 = "Rq1MxfBzM8DaPIjTamS94s9KbaC098Uou4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b20 = new byte[] {57, -83, -2, 83, 0, -7, -12, -13, 4};
String b30 = "YfDgzIC56WYUzouTGGISJdB4egYpmqbt";
byte[] b40 = new byte[] {56, 50, 19, 34, 83, -101, -102, 91};
byte[] b50 = new byte[] {10, 55, 19, -5, -34, 0, 0, 0, 0, 1};
byte[] b60 = new byte[] {-11, 55, -119, 8, 0, 1, 2, -4, 33};
String b11 = "Rq1MxfBzM8HDaPIjTamS94s9KbaC098Uou4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b21 = new byte[] {57, -83, 0, -2, 0, -7, -12, -13, 4};
String b31 = "waWm2saGCnR8uE9fd0kww23947fu9@#54zGVOHoE0w4";
byte[] b41 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91};
byte[] b51 = new byte[] {10, 55, 0, 19, -5, -34, 0, 0, 0, 0, 1};
byte[] b61 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
String b12 = "Rq1MxfBzM8HDaPIjTamS94s9KbaC098Uou4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b22 = new byte[] {57, -83, 0, -2, 0, -7, -12, -13, 4, 0};
String b32 = "u4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b42 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91};
byte[] b52 = new byte[] {10, 55, 0, 19, -5, -34, 0, 0, 0, 0, 1};
byte[] b62 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
String b13 = "Rq1MxfBzM8HDaPIjTamS94s9KbaC098Uou4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b23 = new byte[] {57, -83, 0, -2, 0, -7, -12, -13, 4};
String b33 = "fV5EeHtPbrEd92iNh7dnVJctScCT2jvP";
byte[] b43 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
byte[] b53 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
byte[] b63 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
String b14 = "Rq1MxfBzM8HDaPIjTamS94s9KbaC098Uou4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b24 = null;
String b34 = "ifgTLSG0IlTqjmzN3rp0!@#%c93%^8Yu4d56NRNTHYbuR";
byte[] b44 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
byte[] b54 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
byte[] b64 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
String b15 = "Rq1MxfBzM8HDaPIjTamS94s9KbaC098Uou4V7kj4nQJ7YwiNBmfS5lg1WGE83s1z0";
byte[] b25 = new byte[] {57, -83, 0, -2, 0, -7, -12, -13, 4};
String b35 = null;
byte[] b45 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
byte[] b55 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
byte[] b65 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
try (PreparedStatement preparedStatement = conn.prepareStatement("UPSERT INTO " + tableName
+ "(PK1, PK2, PK3, COL1, COL2, COL3) VALUES (?, ?, ?, ?, ?, ?)")) {
upsertRow(preparedStatement, "pk1-ehgir4jf", Bytes.toBytes("pk22p0jfdkhrgi"),
"pk33ogjirhhf", Bytes.toBytes("col19fnbb0hf0t"),
Bytes.toBytes("col21048rnbfpe3-"), Bytes.toBytes("col319efnrugifj"));
upsertRow(preparedStatement, b10, b20, b30, b40, b50, b60);
upsertRow(preparedStatement, b1, b2, b3, b4, b5, b6);
upsertRow(preparedStatement, b11, b21, b31, b41, b51, b61);
upsertRow(preparedStatement, b12, b22, b32, b42, b52, b62);
upsertRow(preparedStatement, b13, b23, b33, b43, b53, b63);
upsertRow(preparedStatement, b14, b24, b34, b44, b54, b64);
upsertRow(preparedStatement, b15, b25, b35, b45, b55, b65);
}
conn.commit();
PreparedStatement pst = conn.prepareStatement("SELECT * FROM " + tableName);
ResultSet resultSet = pst.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b1, resultSet.getString(1));
Assert.assertArrayEquals(b2, resultSet.getBytes(2));
Assert.assertEquals(b3, resultSet.getString(3));
Assert.assertArrayEquals(b4, resultSet.getBytes(4));
Assert.assertArrayEquals(b5, resultSet.getBytes(5));
Assert.assertArrayEquals(b6, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b10, resultSet.getString(1));
Assert.assertArrayEquals(b20, resultSet.getBytes(2));
Assert.assertEquals(b30, resultSet.getString(3));
Assert.assertArrayEquals(b40, resultSet.getBytes(4));
Assert.assertArrayEquals(b50, resultSet.getBytes(5));
Assert.assertArrayEquals(b60, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b14, resultSet.getString(1));
Assert.assertNull(resultSet.getBytes(2));
Assert.assertEquals(b34, resultSet.getString(3));
Assert.assertArrayEquals(b44, resultSet.getBytes(4));
Assert.assertArrayEquals(b54, resultSet.getBytes(5));
Assert.assertArrayEquals(b64, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b15, resultSet.getString(1));
Assert.assertArrayEquals(b25, resultSet.getBytes(2));
Assert.assertNull(resultSet.getBytes(3));
Assert.assertArrayEquals(b45, resultSet.getBytes(4));
Assert.assertArrayEquals(b55, resultSet.getBytes(5));
Assert.assertArrayEquals(b65, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b13, resultSet.getString(1));
Assert.assertArrayEquals(b23, resultSet.getBytes(2));
Assert.assertEquals(b33, resultSet.getString(3));
Assert.assertArrayEquals(b43, resultSet.getBytes(4));
Assert.assertArrayEquals(b53, resultSet.getBytes(5));
Assert.assertArrayEquals(b63, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b11, resultSet.getString(1));
Assert.assertArrayEquals(b21, resultSet.getBytes(2));
Assert.assertEquals(b31, resultSet.getString(3));
Assert.assertArrayEquals(b41, resultSet.getBytes(4));
Assert.assertArrayEquals(b51, resultSet.getBytes(5));
Assert.assertArrayEquals(b61, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b12, resultSet.getString(1));
Assert.assertArrayEquals(b22, resultSet.getBytes(2));
Assert.assertEquals(b32, resultSet.getString(3));
Assert.assertArrayEquals(b42, resultSet.getBytes(4));
Assert.assertArrayEquals(b52, resultSet.getBytes(5));
Assert.assertArrayEquals(b62, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals("pk1-ehgir4jf", resultSet.getString(1));
Assert.assertArrayEquals(Bytes.toBytes("pk22p0jfdkhrgi"), resultSet.getBytes(2));
Assert.assertEquals("pk33ogjirhhf", resultSet.getString(3));
Assert.assertArrayEquals(Bytes.toBytes("col19fnbb0hf0t"), resultSet.getBytes(4));
Assert.assertArrayEquals(Bytes.toBytes("col21048rnbfpe3-"), resultSet.getBytes(5));
Assert.assertArrayEquals(Bytes.toBytes("col319efnrugifj"), resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
PreparedStatement preparedStatement =
conn.prepareStatement("SELECT * FROM " + tableName + " WHERE PK1 = ?");
preparedStatement.setString(1, b11);
resultSet = preparedStatement.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b14, resultSet.getString(1));
Assert.assertNull(resultSet.getBytes(2));
Assert.assertEquals(b34, resultSet.getString(3));
Assert.assertArrayEquals(b44, resultSet.getBytes(4));
Assert.assertArrayEquals(b54, resultSet.getBytes(5));
Assert.assertArrayEquals(b64, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b15, resultSet.getString(1));
Assert.assertArrayEquals(b25, resultSet.getBytes(2));
Assert.assertNull(resultSet.getBytes(3));
Assert.assertArrayEquals(b45, resultSet.getBytes(4));
Assert.assertArrayEquals(b55, resultSet.getBytes(5));
Assert.assertArrayEquals(b65, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b13, resultSet.getString(1));
Assert.assertArrayEquals(b23, resultSet.getBytes(2));
Assert.assertEquals(b33, resultSet.getString(3));
Assert.assertArrayEquals(b43, resultSet.getBytes(4));
Assert.assertArrayEquals(b53, resultSet.getBytes(5));
Assert.assertArrayEquals(b63, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b11, resultSet.getString(1));
Assert.assertArrayEquals(b21, resultSet.getBytes(2));
Assert.assertEquals(b31, resultSet.getString(3));
Assert.assertArrayEquals(b41, resultSet.getBytes(4));
Assert.assertArrayEquals(b51, resultSet.getBytes(5));
Assert.assertArrayEquals(b61, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b12, resultSet.getString(1));
Assert.assertArrayEquals(b22, resultSet.getBytes(2));
Assert.assertEquals(b32, resultSet.getString(3));
Assert.assertArrayEquals(b42, resultSet.getBytes(4));
Assert.assertArrayEquals(b52, resultSet.getBytes(5));
Assert.assertArrayEquals(b62, resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
preparedStatement = conn.prepareStatement(
"SELECT * FROM " + tableName + " WHERE PK1 = ? AND PK2 IS NOT NULL ");
preparedStatement.setString(1, b11);
resultSet = preparedStatement.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b15, resultSet.getString(1));
Assert.assertArrayEquals(b25, resultSet.getBytes(2));
Assert.assertNull(resultSet.getBytes(3));
Assert.assertArrayEquals(b45, resultSet.getBytes(4));
Assert.assertArrayEquals(b55, resultSet.getBytes(5));
Assert.assertArrayEquals(b65, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b13, resultSet.getString(1));
Assert.assertArrayEquals(b23, resultSet.getBytes(2));
Assert.assertEquals(b33, resultSet.getString(3));
Assert.assertArrayEquals(b43, resultSet.getBytes(4));
Assert.assertArrayEquals(b53, resultSet.getBytes(5));
Assert.assertArrayEquals(b63, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b11, resultSet.getString(1));
Assert.assertArrayEquals(b21, resultSet.getBytes(2));
Assert.assertEquals(b31, resultSet.getString(3));
Assert.assertArrayEquals(b41, resultSet.getBytes(4));
Assert.assertArrayEquals(b51, resultSet.getBytes(5));
Assert.assertArrayEquals(b61, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b12, resultSet.getString(1));
Assert.assertArrayEquals(b22, resultSet.getBytes(2));
Assert.assertEquals(b32, resultSet.getString(3));
Assert.assertArrayEquals(b42, resultSet.getBytes(4));
Assert.assertArrayEquals(b52, resultSet.getBytes(5));
Assert.assertArrayEquals(b62, resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
preparedStatement =
conn.prepareStatement("SELECT * FROM " + tableName + " WHERE PK1 = ? AND PK2 = ?");
preparedStatement.setString(1, b11);
preparedStatement.setBytes(2, b21);
resultSet = preparedStatement.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b15, resultSet.getString(1));
Assert.assertArrayEquals(b25, resultSet.getBytes(2));
Assert.assertNull(resultSet.getBytes(3));
Assert.assertArrayEquals(b45, resultSet.getBytes(4));
Assert.assertArrayEquals(b55, resultSet.getBytes(5));
Assert.assertArrayEquals(b65, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b13, resultSet.getString(1));
Assert.assertArrayEquals(b23, resultSet.getBytes(2));
Assert.assertEquals(b33, resultSet.getString(3));
Assert.assertArrayEquals(b43, resultSet.getBytes(4));
Assert.assertArrayEquals(b53, resultSet.getBytes(5));
Assert.assertArrayEquals(b63, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b11, resultSet.getString(1));
Assert.assertArrayEquals(b21, resultSet.getBytes(2));
Assert.assertEquals(b31, resultSet.getString(3));
Assert.assertArrayEquals(b41, resultSet.getBytes(4));
Assert.assertArrayEquals(b51, resultSet.getBytes(5));
Assert.assertArrayEquals(b61, resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
preparedStatement = conn.prepareStatement("SELECT * FROM " + tableName
+ " WHERE PK1 = ? AND PK2 BETWEEN ? AND ? AND PK3 IS NOT NULL");
preparedStatement.setString(1, b11);
preparedStatement.setBytes(2, new byte[] {57, -83, 0, -2, 0, -7, -12, -13, 3, 24, -121});
preparedStatement.setBytes(3, new byte[] {57, -83, 0, -2, 0, -7, -12, -13, 4});
resultSet = preparedStatement.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b13, resultSet.getString(1));
Assert.assertArrayEquals(b23, resultSet.getBytes(2));
Assert.assertEquals(b33, resultSet.getString(3));
Assert.assertArrayEquals(b43, resultSet.getBytes(4));
Assert.assertArrayEquals(b53, resultSet.getBytes(5));
Assert.assertArrayEquals(b63, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b11, resultSet.getString(1));
Assert.assertArrayEquals(b21, resultSet.getBytes(2));
Assert.assertEquals(b31, resultSet.getString(3));
Assert.assertArrayEquals(b41, resultSet.getBytes(4));
Assert.assertArrayEquals(b51, resultSet.getBytes(5));
Assert.assertArrayEquals(b61, resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
preparedStatement = conn.prepareStatement("SELECT * FROM " + tableName
+ " WHERE PK1 = ? AND PK2 IN (?, ?, ?)");
preparedStatement.setString(1, b11);
preparedStatement.setBytes(2, b21);
preparedStatement.setBytes(3, b23);
preparedStatement.setBytes(4, b22);
resultSet = preparedStatement.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b15, resultSet.getString(1));
Assert.assertArrayEquals(b25, resultSet.getBytes(2));
Assert.assertEquals(b35, resultSet.getString(3));
Assert.assertArrayEquals(b45, resultSet.getBytes(4));
Assert.assertArrayEquals(b55, resultSet.getBytes(5));
Assert.assertArrayEquals(b65, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b13, resultSet.getString(1));
Assert.assertArrayEquals(b23, resultSet.getBytes(2));
Assert.assertEquals(b33, resultSet.getString(3));
Assert.assertArrayEquals(b43, resultSet.getBytes(4));
Assert.assertArrayEquals(b53, resultSet.getBytes(5));
Assert.assertArrayEquals(b63, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b11, resultSet.getString(1));
Assert.assertArrayEquals(b21, resultSet.getBytes(2));
Assert.assertEquals(b31, resultSet.getString(3));
Assert.assertArrayEquals(b41, resultSet.getBytes(4));
Assert.assertArrayEquals(b51, resultSet.getBytes(5));
Assert.assertArrayEquals(b61, resultSet.getBytes(6));
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b12, resultSet.getString(1));
Assert.assertArrayEquals(b22, resultSet.getBytes(2));
Assert.assertEquals(b32, resultSet.getString(3));
Assert.assertArrayEquals(b42, resultSet.getBytes(4));
Assert.assertArrayEquals(b52, resultSet.getBytes(5));
Assert.assertArrayEquals(b62, resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
preparedStatement = conn.prepareStatement(
"SELECT * FROM " + tableName + " WHERE PK1 = ? AND PK2 = ? AND PK3 = ?");
preparedStatement.setString(1, b11);
preparedStatement.setBytes(2, b21);
preparedStatement.setString(3, b31);
resultSet = preparedStatement.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b11, resultSet.getString(1));
Assert.assertArrayEquals(b21, resultSet.getBytes(2));
Assert.assertEquals(b31, resultSet.getString(3));
Assert.assertArrayEquals(b41, resultSet.getBytes(4));
Assert.assertArrayEquals(b51, resultSet.getBytes(5));
Assert.assertArrayEquals(b61, resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
preparedStatement = conn.prepareStatement(
"SELECT * FROM " + tableName + " WHERE PK1 = ? AND PK2 = ? AND PK3 IS NULL");
preparedStatement.setString(1, b11);
preparedStatement.setBytes(2, b21);
resultSet = preparedStatement.executeQuery();
Assert.assertTrue(resultSet.next());
Assert.assertEquals(b15, resultSet.getString(1));
Assert.assertArrayEquals(b25, resultSet.getBytes(2));
Assert.assertNull(resultSet.getBytes(3));
Assert.assertArrayEquals(b45, resultSet.getBytes(4));
Assert.assertArrayEquals(b55, resultSet.getBytes(5));
Assert.assertArrayEquals(b65, resultSet.getBytes(6));
Assert.assertFalse(resultSet.next());
}
}