virajjasani commented on code in PR #2126:
URL: https://github.com/apache/phoenix/pull/2126#discussion_r2059555337
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java:
##########
@@ -0,0 +1,324 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Assert;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+@Category(ParallelStatsDisabledTest.class)
+public class SubBinaryFunctionIT extends ParallelStatsDisabledIT {
+
+ @Test
+ public void testBinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK BINARY(4) NOT NULL,\n" +
+ " BIN_COL BINARY(8) \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+ byte[] b11 = new byte[] {83, -101, -102, 91};
+ byte[] b12 = new byte[] {4, 1, -19, 8, 0, -73, 3, 4};
+ byte[] b21 = new byte[] {-1, 1, 20, -28,};
+ byte[] b22 = new byte[] {10, 55, 0, 19, -5, -34, 0, 0};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 1, 3), SUBBINARY(BIN_COL, 0, 4) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 3);
+ assertSubBinary(b12, rs.getBytes(2), 0, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 3);
+ assertSubBinary(b22, rs.getBytes(2), 0, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 2),
SUBBINARY(BIN_COL, 5) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 3);
+ assertSubBinary(b12, rs.getBytes(2), 4, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 3);
+ assertSubBinary(b22, rs.getBytes(2), 4, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -3,
2), SUBBINARY(BIN_COL, -6, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 2);
+ assertSubBinary(b12, rs.getBytes(2), 2, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 2);
+ assertSubBinary(b22, rs.getBytes(2), 2, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-1), SUBBINARY(BIN_COL, -3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 3, 1);
+ assertSubBinary(b12, rs.getBytes(2), 5, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 3, 1);
+ assertSubBinary(b22, rs.getBytes(2), 5, 3);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {55, 0, 19, -5, -34, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY NOT NULL,\n" +
+ " BIN_COL VARBINARY \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 0, 4), SUBBINARY(BIN_COL, 1, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 4);
+ assertSubBinary(b12, rs.getBytes(2), 0, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 4);
+ assertSubBinary(b22, rs.getBytes(2), 0, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 5),
SUBBINARY(BIN_COL, 7) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 4, 6);
+ assertSubBinary(b12, rs.getBytes(2), 6, 6);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 4, 6);
+ assertSubBinary(b22, rs.getBytes(2), 6, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -4,
3), SUBBINARY(BIN_COL, -3, 1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 6, 3);
+ assertSubBinary(b12, rs.getBytes(2), 9, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 6, 3);
+ assertSubBinary(b22, rs.getBytes(2), 7, 1);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-2), SUBBINARY(BIN_COL, -2) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 8, 2);
+ assertSubBinary(b12, rs.getBytes(2), 10, 2);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 8, 2);
+ assertSubBinary(b22, rs.getBytes(2), 8, 2);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {1, 20, -28, 0, -1, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinaryEncoded() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY_ENCODED NOT NULL,\n" +
+ " BIN_COL VARBINARY_ENCODED \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, -1, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 0, 4), SUBBINARY(BIN_COL, 1, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 4);
+ assertSubBinary(b12, rs.getBytes(2), 0, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 4);
+ assertSubBinary(b22, rs.getBytes(2), 0, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 5),
SUBBINARY(BIN_COL, 7) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 4, 6);
+ assertSubBinary(b12, rs.getBytes(2), 6, 6);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 4, 6);
+ assertSubBinary(b22, rs.getBytes(2), 6, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -4,
3), SUBBINARY(BIN_COL, -3, 1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 6, 3);
+ assertSubBinary(b12, rs.getBytes(2), 9, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 6, 3);
+ assertSubBinary(b22, rs.getBytes(2), 7, 1);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-1), SUBBINARY(BIN_COL, -1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 9, 1);
+ assertSubBinary(b12, rs.getBytes(2), 11, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 9, 1);
+ assertSubBinary(b22, rs.getBytes(2), 9, 1);
+
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {1, 20, -28, 0, -1, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
Review Comment:
After this, `assertFalse(rs.next())`?
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java:
##########
@@ -0,0 +1,324 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Assert;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+@Category(ParallelStatsDisabledTest.class)
+public class SubBinaryFunctionIT extends ParallelStatsDisabledIT {
+
+ @Test
+ public void testBinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK BINARY(4) NOT NULL,\n" +
+ " BIN_COL BINARY(8) \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+ byte[] b11 = new byte[] {83, -101, -102, 91};
+ byte[] b12 = new byte[] {4, 1, -19, 8, 0, -73, 3, 4};
+ byte[] b21 = new byte[] {-1, 1, 20, -28,};
+ byte[] b22 = new byte[] {10, 55, 0, 19, -5, -34, 0, 0};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 1, 3), SUBBINARY(BIN_COL, 0, 4) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 3);
+ assertSubBinary(b12, rs.getBytes(2), 0, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 3);
+ assertSubBinary(b22, rs.getBytes(2), 0, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 2),
SUBBINARY(BIN_COL, 5) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 3);
+ assertSubBinary(b12, rs.getBytes(2), 4, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 3);
+ assertSubBinary(b22, rs.getBytes(2), 4, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -3,
2), SUBBINARY(BIN_COL, -6, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 2);
+ assertSubBinary(b12, rs.getBytes(2), 2, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 2);
+ assertSubBinary(b22, rs.getBytes(2), 2, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-1), SUBBINARY(BIN_COL, -3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 3, 1);
+ assertSubBinary(b12, rs.getBytes(2), 5, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 3, 1);
+ assertSubBinary(b22, rs.getBytes(2), 5, 3);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {55, 0, 19, -5, -34, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY NOT NULL,\n" +
+ " BIN_COL VARBINARY \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 0, 4), SUBBINARY(BIN_COL, 1, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 4);
+ assertSubBinary(b12, rs.getBytes(2), 0, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 4);
+ assertSubBinary(b22, rs.getBytes(2), 0, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 5),
SUBBINARY(BIN_COL, 7) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 4, 6);
+ assertSubBinary(b12, rs.getBytes(2), 6, 6);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 4, 6);
+ assertSubBinary(b22, rs.getBytes(2), 6, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -4,
3), SUBBINARY(BIN_COL, -3, 1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 6, 3);
+ assertSubBinary(b12, rs.getBytes(2), 9, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 6, 3);
+ assertSubBinary(b22, rs.getBytes(2), 7, 1);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-2), SUBBINARY(BIN_COL, -2) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 8, 2);
+ assertSubBinary(b12, rs.getBytes(2), 10, 2);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 8, 2);
+ assertSubBinary(b22, rs.getBytes(2), 8, 2);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {1, 20, -28, 0, -1, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinaryEncoded() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY_ENCODED NOT NULL,\n" +
+ " BIN_COL VARBINARY_ENCODED \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, -1, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 0, 4), SUBBINARY(BIN_COL, 1, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 4);
+ assertSubBinary(b12, rs.getBytes(2), 0, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 4);
+ assertSubBinary(b22, rs.getBytes(2), 0, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 5),
SUBBINARY(BIN_COL, 7) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 4, 6);
+ assertSubBinary(b12, rs.getBytes(2), 6, 6);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 4, 6);
+ assertSubBinary(b22, rs.getBytes(2), 6, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -4,
3), SUBBINARY(BIN_COL, -3, 1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 6, 3);
+ assertSubBinary(b12, rs.getBytes(2), 9, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 6, 3);
+ assertSubBinary(b22, rs.getBytes(2), 7, 1);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-1), SUBBINARY(BIN_COL, -1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 9, 1);
+ assertSubBinary(b12, rs.getBytes(2), 11, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 9, 1);
+ assertSubBinary(b22, rs.getBytes(2), 9, 1);
+
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {1, 20, -28, 0, -1, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+
+ stmt2 = conn.prepareStatement("SELECT id FROM " + tableName + " WHERE
SUBBINARY(BIN_COL, 0, 3) = ?");
+ stmt2.setBytes(1, new byte[] {10, 55, -1});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(1, rs.getInt(1));
+
+ stmt2 = conn.prepareStatement("SELECT id FROM " + tableName + " WHERE
SUBBINARY(BIN_COL, 1, 3) = ?");
+ stmt2.setBytes(1, new byte[] {10, 55, -1});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(1, rs.getInt(1));
Review Comment:
same here and all other places where we are expecting limited num of rows
returned
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java:
##########
@@ -0,0 +1,324 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Assert;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+@Category(ParallelStatsDisabledTest.class)
+public class SubBinaryFunctionIT extends ParallelStatsDisabledIT {
+
+ @Test
+ public void testBinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK BINARY(4) NOT NULL,\n" +
+ " BIN_COL BINARY(8) \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+ byte[] b11 = new byte[] {83, -101, -102, 91};
+ byte[] b12 = new byte[] {4, 1, -19, 8, 0, -73, 3, 4};
+ byte[] b21 = new byte[] {-1, 1, 20, -28,};
+ byte[] b22 = new byte[] {10, 55, 0, 19, -5, -34, 0, 0};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 1, 3), SUBBINARY(BIN_COL, 0, 4) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 3);
+ assertSubBinary(b12, rs.getBytes(2), 0, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 3);
+ assertSubBinary(b22, rs.getBytes(2), 0, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 2),
SUBBINARY(BIN_COL, 5) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 3);
+ assertSubBinary(b12, rs.getBytes(2), 4, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 3);
+ assertSubBinary(b22, rs.getBytes(2), 4, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -3,
2), SUBBINARY(BIN_COL, -6, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 2);
+ assertSubBinary(b12, rs.getBytes(2), 2, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 2);
+ assertSubBinary(b22, rs.getBytes(2), 2, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-1), SUBBINARY(BIN_COL, -3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 3, 1);
+ assertSubBinary(b12, rs.getBytes(2), 5, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 3, 1);
+ assertSubBinary(b22, rs.getBytes(2), 5, 3);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {55, 0, 19, -5, -34, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY NOT NULL,\n" +
+ " BIN_COL VARBINARY \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 0, 4), SUBBINARY(BIN_COL, 1, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 4);
+ assertSubBinary(b12, rs.getBytes(2), 0, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 4);
+ assertSubBinary(b22, rs.getBytes(2), 0, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 5),
SUBBINARY(BIN_COL, 7) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 4, 6);
+ assertSubBinary(b12, rs.getBytes(2), 6, 6);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 4, 6);
+ assertSubBinary(b22, rs.getBytes(2), 6, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -4,
3), SUBBINARY(BIN_COL, -3, 1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 6, 3);
+ assertSubBinary(b12, rs.getBytes(2), 9, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 6, 3);
+ assertSubBinary(b22, rs.getBytes(2), 7, 1);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-2), SUBBINARY(BIN_COL, -2) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 8, 2);
+ assertSubBinary(b12, rs.getBytes(2), 10, 2);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 8, 2);
+ assertSubBinary(b22, rs.getBytes(2), 8, 2);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {1, 20, -28, 0, -1, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinaryEncoded() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY_ENCODED NOT NULL,\n" +
+ " BIN_COL VARBINARY_ENCODED \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, -1, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 0, 4), SUBBINARY(BIN_COL, 1, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 4);
+ assertSubBinary(b12, rs.getBytes(2), 0, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 4);
+ assertSubBinary(b22, rs.getBytes(2), 0, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 5),
SUBBINARY(BIN_COL, 7) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 4, 6);
+ assertSubBinary(b12, rs.getBytes(2), 6, 6);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 4, 6);
+ assertSubBinary(b22, rs.getBytes(2), 6, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -4,
3), SUBBINARY(BIN_COL, -3, 1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 6, 3);
+ assertSubBinary(b12, rs.getBytes(2), 9, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 6, 3);
+ assertSubBinary(b22, rs.getBytes(2), 7, 1);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-1), SUBBINARY(BIN_COL, -1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 9, 1);
+ assertSubBinary(b12, rs.getBytes(2), 11, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 9, 1);
+ assertSubBinary(b22, rs.getBytes(2), 9, 1);
+
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {1, 20, -28, 0, -1, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+
+ stmt2 = conn.prepareStatement("SELECT id FROM " + tableName + " WHERE
SUBBINARY(BIN_COL, 0, 3) = ?");
+ stmt2.setBytes(1, new byte[] {10, 55, -1});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(1, rs.getInt(1));
Review Comment:
same here
##########
phoenix-core-client/src/main/java/org/apache/phoenix/expression/function/SubBinaryFunction.java:
##########
@@ -0,0 +1,214 @@
+package org.apache.phoenix.expression.function;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.parse.FunctionParseNode;
+import org.apache.phoenix.schema.SortOrder;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.PBinary;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PLong;
+import org.apache.phoenix.schema.types.PVarbinary;
+import org.apache.phoenix.schema.types.PVarbinaryEncoded;
+
+import java.io.DataInput;
+import java.io.IOException;
+import java.util.Arrays;
+import java.util.List;
+
+/**
+ *
+ * Implementation of the {@code SUBBINARY(<binary>,<offset>[,<length>]) }
built-in function
+ * where {@code <offset> } is the offset from the start of {@code <binary>
}. A positive offset
+ * is treated as 1-based, a zero offset is treated as 0-based, and a negative
+ * offset starts from the end of the byte array working backwards. The optional
+ * {@code <length> } argument is the number of bytes to return. In the absence
of the
+ * {@code <length> } argument, the rest of the byte array starting from
{@code <offset> } is returned.
+ * If {@code <length> } is less than 1, null is returned.
+
+ */
[email protected](name=SubBinaryFunction.NAME, args={
+
@FunctionParseNode.Argument(allowedTypes={PBinary.class,PVarbinary.class,
PVarbinaryEncoded.class}),
+ @FunctionParseNode.Argument(allowedTypes={PLong.class}), // These are
LONG because negative numbers end up as longs
+
@FunctionParseNode.Argument(allowedTypes={PLong.class},defaultValue="null")} )
+public class SubBinaryFunction extends PrefixFunction {
+
+ public static final String NAME = "SUBBINARY";
+ private boolean hasLengthExpression;
+ private boolean isOffsetConstant;
+ private boolean isLengthConstant;
+ private boolean isFixedWidth;
+ private Integer maxLength;
+
+ public SubBinaryFunction() {
+ }
+
+ public SubBinaryFunction(List<Expression> children) {
+ super(children);
+ init();
+ }
+
+ private void init() {
+ isOffsetConstant = getOffsetExpression() instanceof LiteralExpression;
+ isLengthConstant = getLengthExpression() instanceof LiteralExpression;
+ hasLengthExpression = !isLengthConstant ||
((LiteralExpression)getLengthExpression()).getValue() != null;
+ isFixedWidth = getBinaryExpression().getDataType().isFixedWidth() &&
((hasLengthExpression && isLengthConstant) || (!hasLengthExpression &&
isOffsetConstant));
+ if (hasLengthExpression && isLengthConstant) {
+ Integer maxLength =
((Number)((LiteralExpression)getLengthExpression()).getValue()).intValue();
+ this.maxLength = maxLength >=0 ? maxLength : 0;
+ } else if (isOffsetConstant) {
+ Number offsetNumber =
(Number)((LiteralExpression)getOffsetExpression()).getValue();
+ if (offsetNumber != null) {
+ int offset = offsetNumber.intValue();
+ PDataType type = getBinaryExpression().getDataType();
+ if (type.isFixedWidth()) {
+ if (offset >= 0) {
+ Integer maxLength =
getBinaryExpression().getMaxLength();
+ this.maxLength = maxLength - offset + (offset == 0 ? 0
: 1);
+ }
+ }
+ }
+ }
+ }
+
+ @Override
+ public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
+ Expression offsetExpression = getOffsetExpression();
+ if (!offsetExpression.evaluate(tuple, ptr)) {
+ return false;
+ }
+ if (ptr.getLength() == 0) {
+ return true;
+ }
+ int offset = offsetExpression.getDataType().getCodec().decodeInt(ptr,
offsetExpression.getSortOrder());
+ int length = -1;
+ if (hasLengthExpression) {
+ Expression lengthExpression = getLengthExpression();
+ if (!lengthExpression.evaluate(tuple, ptr)) {
+ return false;
+ }
+ if (ptr.getLength() == 0) {
+ return true;
+ }
+ length = lengthExpression.getDataType().getCodec().decodeInt(ptr,
lengthExpression.getSortOrder());
+ if (length <= 0) {
+ return false;
+ }
+ }
+ if (!getBinaryExpression().evaluate(tuple, ptr)) {
+ return false;
+ }
+ if (ptr.getLength()==0) {
+ return true;
+ }
+ byte[] bytes = new byte[]{};
+ int binLength;
+ if (getDataType() == PVarbinaryEncoded.INSTANCE) {
+ // get decoded bytes
+ bytes = (byte[]) PVarbinaryEncoded.INSTANCE.toObject(ptr.get(),
ptr.getOffset(),
+ ptr.getLength());
+ binLength = bytes.length;
+ } else {
+ binLength = ptr.getLength();
+ }
+ // Account for 1 versus 0-based offset
+ offset = offset - (offset <= 0 ? 0 : 1);
+ if (offset < 0) { // Offset < 0 means get from end
+ offset = binLength + offset;
+ }
+ if (offset < 0 || offset >= binLength) {
+ return false;
+ }
+ int maxLength = binLength - offset;
+ length = length == -1 ? maxLength : Math.min(length,maxLength);
+ if (getDataType() == PVarbinaryEncoded.INSTANCE) {
+ // get the slice of decoded bytes based on offset and length
+ byte[] result = Arrays.copyOfRange(bytes, offset, offset + length);
+ // encode the slice and set
+ ptr.set(PVarbinaryEncoded.INSTANCE.toBytes(result));
Review Comment:
Does `ptr.set(PVarbinaryEncoded.INSTANCE.toBytes(bytes, offset, offset +
length))` work here instead of array copy?
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java:
##########
@@ -0,0 +1,324 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Assert;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+@Category(ParallelStatsDisabledTest.class)
+public class SubBinaryFunctionIT extends ParallelStatsDisabledIT {
+
+ @Test
+ public void testBinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK BINARY(4) NOT NULL,\n" +
+ " BIN_COL BINARY(8) \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+ byte[] b11 = new byte[] {83, -101, -102, 91};
+ byte[] b12 = new byte[] {4, 1, -19, 8, 0, -73, 3, 4};
+ byte[] b21 = new byte[] {-1, 1, 20, -28,};
+ byte[] b22 = new byte[] {10, 55, 0, 19, -5, -34, 0, 0};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 1, 3), SUBBINARY(BIN_COL, 0, 4) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 3);
+ assertSubBinary(b12, rs.getBytes(2), 0, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 3);
+ assertSubBinary(b22, rs.getBytes(2), 0, 4);
Review Comment:
here also, in all places it would be good to assertFalse for rs.next()
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java:
##########
@@ -0,0 +1,324 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Assert;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+@Category(ParallelStatsDisabledTest.class)
+public class SubBinaryFunctionIT extends ParallelStatsDisabledIT {
+
+ @Test
+ public void testBinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK BINARY(4) NOT NULL,\n" +
+ " BIN_COL BINARY(8) \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+ byte[] b11 = new byte[] {83, -101, -102, 91};
+ byte[] b12 = new byte[] {4, 1, -19, 8, 0, -73, 3, 4};
+ byte[] b21 = new byte[] {-1, 1, 20, -28,};
+ byte[] b22 = new byte[] {10, 55, 0, 19, -5, -34, 0, 0};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 1, 3), SUBBINARY(BIN_COL, 0, 4) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 3);
+ assertSubBinary(b12, rs.getBytes(2), 0, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 3);
+ assertSubBinary(b22, rs.getBytes(2), 0, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 2),
SUBBINARY(BIN_COL, 5) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 3);
+ assertSubBinary(b12, rs.getBytes(2), 4, 4);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 3);
+ assertSubBinary(b22, rs.getBytes(2), 4, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -3,
2), SUBBINARY(BIN_COL, -6, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 1, 2);
+ assertSubBinary(b12, rs.getBytes(2), 2, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 1, 2);
+ assertSubBinary(b22, rs.getBytes(2), 2, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-1), SUBBINARY(BIN_COL, -3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 3, 1);
+ assertSubBinary(b12, rs.getBytes(2), 5, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 3, 1);
+ assertSubBinary(b22, rs.getBytes(2), 5, 3);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {55, 0, 19, -5, -34, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinary() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY NOT NULL,\n" +
+ " BIN_COL VARBINARY \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(BIN_PK, 0, 4), SUBBINARY(BIN_COL, 1, 3) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 0, 4);
+ assertSubBinary(b12, rs.getBytes(2), 0, 3);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 0, 4);
+ assertSubBinary(b22, rs.getBytes(2), 0, 3);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, 5),
SUBBINARY(BIN_COL, 7) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 4, 6);
+ assertSubBinary(b12, rs.getBytes(2), 6, 6);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 4, 6);
+ assertSubBinary(b22, rs.getBytes(2), 6, 4);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK, -4,
3), SUBBINARY(BIN_COL, -3, 1) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 6, 3);
+ assertSubBinary(b12, rs.getBytes(2), 9, 1);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 6, 3);
+ assertSubBinary(b22, rs.getBytes(2), 7, 1);
+
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_PK,
-2), SUBBINARY(BIN_COL, -2) FROM " + tableName);
+ rs.next();
+ assertSubBinary(b11, rs.getBytes(1), 8, 2);
+ assertSubBinary(b12, rs.getBytes(2), 10, 2);
+ rs.next();
+ assertSubBinary(b21, rs.getBytes(1), 8, 2);
+ assertSubBinary(b22, rs.getBytes(2), 8, 2);
+
+ PreparedStatement stmt2 = conn.prepareStatement("SELECT id FROM " +
tableName + " WHERE SUBBINARY(BIN_COL, 2, 6) = ?");
+ stmt2.setBytes(1, new byte[] {1, 20, -28, 0, -1, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ }
+
+ @Test
+ public void testVarbinaryEncoded() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER NOT NULL,\n" +
+ " BIN_PK VARBINARY_ENCODED NOT NULL,\n" +
+ " BIN_COL VARBINARY_ENCODED \n" +
+ " CONSTRAINT pk PRIMARY KEY (id, BIN_PK)" +
+ ")");
+
+
+ byte[] b11 = new byte[] {56, 50, 19, 0, 34, 83, -101, -102, 91, 92};
+ byte[] b12 = new byte[] {10, 55, -1, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b21 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ byte[] b22 = new byte[] {1, 1, 20, -28, 0, -1, 0, -11, -21, -1};
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES(?, ?, ?)");
+ upsertRow(stmt, 1, b11, b12);
+ upsertRow(stmt, 2, b21, b22);
Review Comment:
Maybe upsert 2 more rows such that at least 2/4 rows can be selected with
prefix from offset 0?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
