This is an automated email from the ASF dual-hosted git repository.
apurtell pushed a commit to branch PHOENIX-7562-feature
in repository https://gitbox.apache.org/repos/asf/phoenix.git
commit d1a8bd0ac376320fb9dfad08082d7c0bdd011cb1
Author: Palash Chauhan <palashc...@gmail.com>
AuthorDate: Mon Apr 28 18:13:10 2025 -0700
PHOENIX-7588 : SUBBINARY() - Substring like function for Binary data types
(#2126)
Co-authored-by: Palash Chauhan
<p.chau...@pchauha-ltmgv47.internal.salesforce.com>
---
.../apache/phoenix/expression/ExpressionType.java | 4 +-
.../expression/function/SubBinaryFunction.java | 231 +++++++++++++
.../phoenix/end2end/SubBinaryFunctionIT.java | 377 +++++++++++++++++++++
3 files changed, 611 insertions(+), 1 deletion(-)
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
index 453925ef5d..19e0ec81a0 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
@@ -203,7 +203,9 @@ public enum ExpressionType {
PartitionIdFunction(PartitionIdFunction.class),
DecodeBinaryFunction(DecodeBinaryFunction.class),
EncodeBinaryFunction(EncodeBinaryFunction.class),
- DecodeViewIdFunction(DecodeViewIndexIdFunction.class);
+ DecodeViewIdFunction(DecodeViewIndexIdFunction.class),
+ SubBinaryFunction(SubBinaryFunction.class);
+
ExpressionType(Class<? extends Expression> clazz) {
this.clazz = clazz;
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/function/SubBinaryFunction.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/function/SubBinaryFunction.java
new file mode 100644
index 0000000000..946aa1ab82
--- /dev/null
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/function/SubBinaryFunction.java
@@ -0,0 +1,231 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+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.
+
+ */
+@FunctionParseNode.BuiltInFunction(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));
+ }
+ else {
+ ptr.set(ptr.get(), ptr.getOffset() + offset, length);
+ }
+ return true;
+ }
+
+ @Override
+ public String getName() {
+ return NAME;
+ }
+
+ @Override
+ public PDataType getDataType() {
+ return getBinaryExpression().getDataType();
+ }
+
+ @Override
+ public boolean isNullable() {
+ return getBinaryExpression().isNullable() || !isFixedWidth ||
getOffsetExpression().isNullable();
+ }
+
+ @Override
+ public Integer getMaxLength() {
+ return maxLength;
+ }
+
+ @Override
+ public SortOrder getSortOrder() {
+ return getBinaryExpression().getSortOrder();
+ }
+
+ @Override
+ public void readFields(DataInput input) throws IOException {
+ super.readFields(input);
+ init();
+ }
+
+ @Override
+ public OrderPreserving preservesOrder() {
+ if (isOffsetConstant) {
+ LiteralExpression literal = (LiteralExpression)
getOffsetExpression();
+ Number offsetNumber = (Number) literal.getValue();
+ if (offsetNumber != null) {
+ int offset = offsetNumber.intValue();
+ if ((offset == 0 || offset == 1) && (!hasLengthExpression ||
isLengthConstant)) {
+ return OrderPreserving.YES_IF_LAST;
+ }
+ }
+ }
+ return OrderPreserving.NO;
+ }
+
+ private Expression getBinaryExpression() {
+ return children.get(0);
+ }
+
+ private Expression getOffsetExpression() {
+ return children.get(1);
+ }
+
+ private Expression getLengthExpression() {
+ return children.get(2);
+ }
+
+ @Override
+ public String toString() {
+ StringBuilder buf = new StringBuilder(getName() + "(");
+ if (children.isEmpty())
+ return buf.append(")").toString();
+ if (hasLengthExpression) {
+ buf.append(getBinaryExpression());
+ buf.append(", ");
+ buf.append(getOffsetExpression());
+ buf.append(", ");
+ buf.append(getLengthExpression());
+ } else {
+ buf.append(getBinaryExpression());
+ buf.append(", ");
+ buf.append(getOffsetExpression());
+ }
+ buf.append(")");
+ return buf.toString();
+ }
+}
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java
new file mode 100644
index 0000000000..8ba75c0064
--- /dev/null
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubBinaryFunctionIT.java
@@ -0,0 +1,377 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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));
+ Assert.assertFalse(rs.next());
+ }
+
+ @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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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));
+ Assert.assertFalse(rs.next());
+ }
+
+ @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};
+ byte[] b112 = new byte[] {56, 50, 19, 0, -1, -11, -12, 1, 9};
+ byte[] b122 = new byte[] {5, 1, 0, 0, 0, 0, 22, 122, 48, -121, 73, 3,
0, 23};
+ byte[] b31 = new byte[] {10, 55, 0, 19, -5, -34, 0, -12, 0, 0, 0, 1};
+ byte[] b32 = new byte[] {-11, 55, -119, 0, 8, 0, 1, 2, -4, 33};
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+ 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);
+ Assert.assertFalse(rs.next());
+
+
+ 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));
+ Assert.assertFalse(rs.next());
+
+ 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));
+ Assert.assertFalse(rs.next());
+
+ 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));
+ Assert.assertFalse(rs.next());
+
+ stmt2 = conn.prepareStatement("SELECT id FROM " + tableName + " WHERE
SUBBINARY(BIN_COL, -5, 3) = ?");
+ stmt2.setBytes(1, new byte[] {-1, 0, -11});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertEquals(2, rs.getInt(1));
+ Assert.assertFalse(rs.next());
+
+ upsertRow(stmt, 1, b112, b122);
+ upsertRow(stmt, 3, b31, b32);
+ conn.commit();
+
+ stmt2 = conn.prepareStatement("SELECT BIN_COL FROM " + tableName + "
WHERE id = 1 AND SUBBINARY(BIN_PK, 0, 4) = ?");
+ stmt2.setBytes(1, new byte[] {56, 50, 19, 0});
+ rs = stmt2.executeQuery();
+ Assert.assertTrue(rs.next());
+ Assert.assertArrayEquals(b12, rs.getBytes(1));
+ Assert.assertTrue(rs.next());
+ Assert.assertArrayEquals(b122, rs.getBytes(1));
+ Assert.assertFalse(rs.next());
+
+ }
+
+
+
+ @Test
+ public void testSubBinaryFunction() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName + "(" +
+ " id INTEGER PRIMARY KEY,\n" +
+ " VAR_BIN_COL VARBINARY,\n" +
+ " BIN_COL BINARY(8)" +
+ ")");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(1, X'010203040506', X'01020304')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(2, X'000000000000', X'00000000')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(3, X'FFEEAABB', X'FFEEAABBFFEEAABB')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(4, null, X'0101010101010101')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(5, X'', X'04030201')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(6, X'0204', X'')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(7, X'010101', null)");
+ conn.commit();
+
+ ResultSet rs;
+
+ // first n bytes
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(VAR_BIN_COL, 1, 4) = X'01020304'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 1);
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(BIN_COL, 1, 3) = X'FFEEAA'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 3);
+
+ // length not provided, padding for fixed-length binary
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(VAR_BIN_COL, 2) = X'EEAABB'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 3);
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(BIN_COL, 2) = X'03020100000000'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 5);
+
+ // offset > length of column value
+ rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(VAR_BIN_COL, 15, 2) FROM " + tableName + " WHERE id = 1");
+ Assert.assertTrue(rs.next() && rs.getBytes(1) == null);
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_COL,
15, 2) FROM " + tableName + " WHERE id = 2");
+ Assert.assertTrue(rs.next() && rs.getBytes(1) == null);
+
+ // negative offset with length
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(VAR_BIN_COL, -2, 1) = X'05'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 1);
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(BIN_COL, -2, 1) = X'AA'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 3);
+
+ //negative offset without length
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(VAR_BIN_COL, -3) = X'000000'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 2);
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName
+ " WHERE SUBBINARY(BIN_COL, -2) = X'0101'");
+ Assert.assertTrue(rs.next() && rs.getInt(1) == 4);
+
+ // empty column value
+ rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(VAR_BIN_COL, 2, 3) FROM " + tableName + " WHERE id = 5");
+ Assert.assertTrue(rs.next() && rs.getBytes(1) == null);
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_COL, 1,
4) FROM " + tableName + " WHERE id = 6");
+ Assert.assertTrue(rs.next() && rs.getBytes(1) == null);
+
+ // null values
+ rs = conn.createStatement().executeQuery("SELECT
SUBBINARY(VAR_BIN_COL, 1, 4) FROM " + tableName + " WHERE id = 4");
+ Assert.assertTrue(rs.next() && rs.getBytes(1) == null);
+ rs = conn.createStatement().executeQuery("SELECT SUBBINARY(BIN_COL, 2,
3) FROM " + tableName + " WHERE id = 7");
+ Assert.assertTrue(rs.next() && rs.getBytes(1) == null);
+ }
+
+ @Test
+ public void testExplainPlanWithSubBinaryFunctionInPK() throws Exception {
+ String tableName = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE " + tableName
+ + " (id INTEGER NOT NULL, VAR_BIN_COL VARBINARY NOT NULL,
DESCRIPTION VARCHAR CONSTRAINT pk PRIMARY KEY (id, VAR_BIN_COL))");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(1, X'0102030405', 'desc1')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(1, X'010101', 'desc5')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(2, X'0000000001', 'desc2')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(3, X'FFEEAABB', 'desc3')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(4, X'AA', 'desc4')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(6, X'AAFFAA', 'desc6')");
+ conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES
(7, X'BB', 'desc7')");
+ conn.commit();
+
+ String sql = "SELECT * FROM " + tableName + " WHERE id = 1 AND
SUBBINARY(VAR_BIN_COL, 0, 1) = X'01'";
+ ResultSet rs = conn.createStatement().executeQuery(sql);
+ int count = 0;
+ while (rs.next()) {
+ count++;
+ }
+ Assert.assertEquals(2, count);
+ rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+ String plan = QueryUtil.getExplainPlan(rs);
+ Assert.assertTrue(plan.contains("RANGE SCAN OVER " + tableName + "
[1,X'01'] - [1,X'02']"));
+
+ sql = "SELECT * FROM " + tableName + " WHERE id = 1 AND
SUBBINARY(VAR_BIN_COL, 2, 1) = X'01'";
+ rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
+ plan = QueryUtil.getExplainPlan(rs);
+ Assert.assertTrue(plan.contains("RANGE SCAN OVER " + tableName + "
[1]"));
+ }
+
+ private void upsertRow(PreparedStatement stmt, int id, byte[] b1, byte[]
b2) throws SQLException {
+ stmt.setInt(1, id);
+ stmt.setBytes(2, b1);
+ stmt.setBytes(3, b2);
+ stmt.executeUpdate();
+ }
+
+ private void assertSubBinary(byte[] expected, byte[] actual, int start,
int length) {
+ for (int i = 0; i < length; i++) {
+ Assert.assertEquals(expected[start + i], actual[i]);
+ }
+ }
+}
