You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2014/02/10 18:14:00 UTC
[3/8] Fix failing unit test, rename tests for consistency
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/0ea954ea/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
new file mode 100644
index 0000000..82f3bcd
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
@@ -0,0 +1,931 @@
+/*
+ * Copyright 2014 The Apache Software Foundation
+ *
+ * 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.compile;
+
+import static org.apache.phoenix.util.TestUtil.ATABLE_NAME;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.apache.phoenix.util.TestUtil.and;
+import static org.apache.phoenix.util.TestUtil.assertDegenerate;
+import static org.apache.phoenix.util.TestUtil.columnComparison;
+import static org.apache.phoenix.util.TestUtil.constantComparison;
+import static org.apache.phoenix.util.TestUtil.in;
+import static org.apache.phoenix.util.TestUtil.multiKVFilter;
+import static org.apache.phoenix.util.TestUtil.not;
+import static org.apache.phoenix.util.TestUtil.or;
+import static org.apache.phoenix.util.TestUtil.singleKVFilter;
+import static org.junit.Assert.assertArrayEquals;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.math.BigDecimal;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.text.Format;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.List;
+
+import org.apache.hadoop.hbase.client.Scan;
+import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
+import org.apache.hadoop.hbase.filter.Filter;
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.expression.RowKeyColumnExpression;
+import org.apache.phoenix.expression.function.SubstrFunction;
+import org.apache.phoenix.filter.RowKeyComparisonFilter;
+import org.apache.phoenix.filter.SkipScanFilter;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.jdbc.PhoenixPreparedStatement;
+import org.apache.phoenix.query.BaseConnectionlessQueryTest;
+import org.apache.phoenix.query.KeyRange;
+import org.apache.phoenix.query.QueryConstants;
+import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.schema.RowKeyValueAccessor;
+import org.apache.phoenix.schema.SaltingUtil;
+import org.apache.phoenix.util.ByteUtil;
+import org.apache.phoenix.util.DateUtil;
+import org.apache.phoenix.util.NumberUtil;
+import org.apache.phoenix.util.SchemaUtil;
+import org.apache.phoenix.util.StringUtil;
+import org.junit.Ignore;
+import org.junit.Test;
+
+import com.google.common.collect.ImmutableList;
+
+
+public class WhereCompilerTest extends BaseConnectionlessQueryTest {
+
+ @Test
+ public void testSingleEqualFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_integer=0";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter(constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0)),
+ filter);
+ }
+
+ @Test
+ public void testSingleFixedFullPkSalted() throws SQLException {
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ pconn.createStatement().execute("CREATE TABLE t (k bigint not null primary key, v varchar) SALT_BUCKETS=20");
+ String query = "select * from t where k=" + 1;
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertNull(filter);
+ byte[] key = new byte[PDataType.LONG.getByteSize() + 1];
+ PDataType.LONG.toBytes(1L, key, 1);
+ key[0] = SaltingUtil.getSaltingByte(key, 1, PDataType.LONG.getByteSize(), 20);
+ byte[] expectedStartKey = key;
+ byte[] expectedEndKey = ByteUtil.concat(key, QueryConstants.SEPARATOR_BYTE_ARRAY);
+ byte[] startKey = scan.getStartRow();
+ byte[] stopKey = scan.getStopRow();
+ assertTrue(Bytes.compareTo(expectedStartKey, startKey) == 0);
+ assertTrue(Bytes.compareTo(expectedEndKey, stopKey) == 0);
+ }
+
+ @Test
+ public void testSingleVariableFullPkSalted() throws SQLException {
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ pconn.createStatement().execute("CREATE TABLE t (k varchar primary key, v varchar) SALT_BUCKETS=20");
+ String query = "select * from t where k='a'";
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertNull(filter);
+ byte[] key = new byte[2];
+ PDataType.VARCHAR.toBytes("a", key, 1);
+ key[0] = SaltingUtil.getSaltingByte(key, 1, 1, 20);
+ byte[] expectedStartKey = key;
+ byte[] expectedEndKey = ByteUtil.concat(key, QueryConstants.SEPARATOR_BYTE_ARRAY);
+ byte[] startKey = scan.getStartRow();
+ byte[] stopKey = scan.getStopRow();
+ assertTrue(Bytes.compareTo(expectedStartKey, startKey) == 0);
+ assertTrue(Bytes.compareTo(expectedEndKey, stopKey) == 0);
+ }
+
+ @Test
+ public void testMultiFixedFullPkSalted() throws SQLException {
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ pconn.createStatement().execute("CREATE TABLE t (k bigint not null primary key, v varchar) SALT_BUCKETS=20");
+ String query = "select * from t where k in (1,3)";
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ byte[] key = new byte[PDataType.LONG.getByteSize() + 1];
+ PDataType.LONG.toBytes(1L, key, 1);
+ key[0] = SaltingUtil.getSaltingByte(key, 1, PDataType.LONG.getByteSize(), 20);
+ byte[] startKey1 = key;
+
+ key = new byte[PDataType.LONG.getByteSize() + 1];
+ PDataType.LONG.toBytes(3L, key, 1);
+ key[0] = SaltingUtil.getSaltingByte(key, 1, PDataType.LONG.getByteSize(), 20);
+ byte[] startKey2 = key;
+
+ byte[] startKey = scan.getStartRow();
+ byte[] stopKey = scan.getStopRow();
+
+ // Due to salting byte, the 1 key may be after the 3 key
+ byte[] expectedStartKey;
+ byte[] expectedEndKey;
+ List<List<KeyRange>> expectedRanges = Collections.singletonList(
+ Arrays.asList(KeyRange.getKeyRange(startKey1),
+ KeyRange.getKeyRange(startKey2)));
+ if (Bytes.compareTo(startKey1, startKey2) > 0) {
+ expectedStartKey = startKey2;
+ expectedEndKey = ByteUtil.concat(startKey1, QueryConstants.SEPARATOR_BYTE_ARRAY);
+ Collections.reverse(expectedRanges.get(0));
+ } else {
+ expectedStartKey = startKey1;
+ expectedEndKey = ByteUtil.concat(startKey2, QueryConstants.SEPARATOR_BYTE_ARRAY);;
+ }
+ assertTrue(Bytes.compareTo(expectedStartKey, startKey) == 0);
+ assertTrue(Bytes.compareTo(expectedEndKey, stopKey) == 0);
+
+ assertNotNull(filter);
+ assertTrue(filter instanceof SkipScanFilter);
+ StatementContext context = plan.getContext();
+ ScanRanges scanRanges = context.getScanRanges();
+ List<List<KeyRange>> ranges = scanRanges.getRanges();
+ assertEquals(expectedRanges, ranges);
+ }
+
+ @Test
+ public void testMultiColumnEqualFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_string=b_string";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ multiKVFilter(columnComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_STRING,
+ BaseConnectionlessQueryTest.B_STRING)),
+ filter);
+ }
+
+ @Test
+ public void testCollapseFunctionToNull() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and substr(entity_id,null) = 'foo'";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertNull(filter);
+
+ assertArrayEquals(scan.getStartRow(),KeyRange.EMPTY_RANGE.getLowerRange());
+ assertArrayEquals(scan.getStopRow(),KeyRange.EMPTY_RANGE.getUpperRange());
+ }
+
+ private static void bindParams(PhoenixPreparedStatement stmt, List<Object> binds) throws SQLException {
+ for (int i = 0; i < binds.size(); i++) {
+ stmt.setObject(i+1, binds.get(i));
+ }
+ }
+
+ @Test
+ public void testAndFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id=? and a_integer=0 and a_string='foo'";
+ List<Object> binds = Arrays.<Object>asList(tenantId);
+
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ bindParams(pstmt, binds);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+
+ assertEquals(
+ multiKVFilter(and(
+ constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0),
+ constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_STRING,
+ "foo"))),
+ filter);
+ }
+
+ @Test
+ public void testRHSLiteral() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and 0 >= a_integer";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter(constantComparison(
+ CompareOp.LESS_OR_EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0)),
+ filter);
+ }
+
+ @Test
+ public void testToDateFilter() throws Exception {
+ String tenantId = "000000000000001";
+ String dateStr = "2012-01-01 12:00:00";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_date >= to_date('" + dateStr + "')";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+
+ Format format = DateUtil.getDateParser(DateUtil.DEFAULT_DATE_FORMAT);
+ Object date = format.parseObject(dateStr);
+
+ assertEquals(
+ singleKVFilter(constantComparison(
+ CompareOp.GREATER_OR_EQUAL,
+ BaseConnectionlessQueryTest.A_DATE,
+ date)),
+ filter);
+ }
+
+ private void helpTestToNumberFilter(String toNumberClause, BigDecimal expectedDecimal) throws Exception {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and x_decimal >= " + toNumberClause;
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+
+ assertEquals(
+ singleKVFilter(constantComparison(
+ CompareOp.GREATER_OR_EQUAL,
+ BaseConnectionlessQueryTest.X_DECIMAL,
+ expectedDecimal)),
+ filter);
+}
+
+ private void helpTestToNumberFilterWithNoPattern(String stringValue) throws Exception {
+ String toNumberClause = "to_number('" + stringValue + "')";
+ BigDecimal expectedDecimal = NumberUtil.normalize(new BigDecimal(stringValue));
+ helpTestToNumberFilter(toNumberClause, expectedDecimal);
+ }
+
+ @Test
+ public void testToNumberFilterWithInteger() throws Exception {
+ String stringValue = "123";
+ helpTestToNumberFilterWithNoPattern(stringValue);
+ }
+
+ @Test
+ public void testToNumberFilterWithDecimal() throws Exception {
+ String stringValue = "123.33";
+ helpTestToNumberFilterWithNoPattern(stringValue);
+ }
+
+ @Test
+ public void testToNumberFilterWithNegativeDecimal() throws Exception {
+ String stringValue = "-123.33";
+ helpTestToNumberFilterWithNoPattern(stringValue);
+ }
+
+ @Test
+ public void testToNumberFilterWithPatternParam() throws Exception {
+ String toNumberClause = "to_number('$1.23333E2', '\u00A40.00000E0')";
+ BigDecimal expectedDecimal = NumberUtil.normalize(new BigDecimal("123.333"));
+ helpTestToNumberFilter(toNumberClause, expectedDecimal);
+ }
+
+ @Test(expected=AssertionError.class) // compileStatement() fails because zero rows are found by to_number()
+ public void testToNumberFilterWithPatternParamNegativeTest() throws Exception {
+ String toNumberClause = "to_number('$123.33', '000.00')"; // no currency sign in pattern param
+ BigDecimal expectedDecimal = NumberUtil.normalize(new BigDecimal("123.33"));
+ helpTestToNumberFilter(toNumberClause, expectedDecimal);
+ }
+
+ @Test
+ public void testRowKeyFilter() throws SQLException {
+ String keyPrefix = "foo";
+ String query = "select * from atable where substr(entity_id,1,3)=?";
+ List<Object> binds = Arrays.<Object>asList(keyPrefix);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ bindParams(pstmt, binds);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+
+ assertEquals(
+ new RowKeyComparisonFilter(
+ constantComparison(CompareOp.EQUAL,
+ new SubstrFunction(
+ Arrays.<Expression>asList(
+ new RowKeyColumnExpression(BaseConnectionlessQueryTest.ENTITY_ID,new RowKeyValueAccessor(BaseConnectionlessQueryTest.ATABLE.getPKColumns(),1)),
+ LiteralExpression.newConstant(1),
+ LiteralExpression.newConstant(3))
+ ),
+ keyPrefix),
+ QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES),
+ filter);
+ }
+
+ @Test
+ public void testPaddedRowKeyFilter() throws SQLException {
+ String keyPrefix = "fo";
+ String query = "select * from atable where entity_id=?";
+ List<Object> binds = Arrays.<Object>asList(keyPrefix);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ bindParams(pstmt, binds);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ assertEquals(0,scan.getStartRow().length);
+ assertEquals(0,scan.getStopRow().length);
+ assertNotNull(scan.getFilter());
+ }
+
+ @Test
+ public void testPaddedStartStopKey() throws SQLException {
+ String tenantId = "000000000000001";
+ String keyPrefix = "fo";
+ String query = "select * from atable where organization_id=? AND entity_id=?";
+ List<Object> binds = Arrays.<Object>asList(tenantId,keyPrefix);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ bindParams(pstmt, binds);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ byte[] expectedStartRow = ByteUtil.concat(Bytes.toBytes(tenantId), StringUtil.padChar(Bytes.toBytes(keyPrefix), 15));
+ assertArrayEquals(expectedStartRow,scan.getStartRow());
+ assertArrayEquals(ByteUtil.concat(expectedStartRow,QueryConstants.SEPARATOR_BYTE_ARRAY),scan.getStopRow());
+ }
+
+ @Test
+ public void testDegenerateRowKeyFilter() throws SQLException {
+ String keyPrefix = "foobar";
+ String query = "select * from atable where substr(entity_id,1,3)=?";
+ List<Object> binds = Arrays.<Object>asList(keyPrefix);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ bindParams(pstmt, binds);
+ QueryPlan plan = pstmt.optimizeQuery();
+ // Degenerate b/c "foobar" is more than 3 characters
+ assertDegenerate(plan.getContext());
+ }
+
+ @Test
+ public void testDegenerateBiggerThanMaxLengthVarchar() throws SQLException {
+ byte[] tooBigValue = new byte[101];
+ Arrays.fill(tooBigValue, (byte)50);
+ String aString = (String)PDataType.VARCHAR.toObject(tooBigValue);
+ String query = "select * from atable where a_string=?";
+ List<Object> binds = Arrays.<Object>asList(aString);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ bindParams(pstmt, binds);
+ QueryPlan plan = pstmt.optimizeQuery();
+ // Degenerate b/c a_string length is 100
+ assertDegenerate(plan.getContext());
+ }
+
+ @Test
+ public void testOrFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String keyPrefix = "foo";
+ int aInt = 2;
+ String query = "select * from atable where organization_id=? and (substr(entity_id,1,3)=? or a_integer=?)";
+ List<Object> binds = Arrays.<Object>asList(tenantId, keyPrefix, aInt);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ bindParams(pstmt, binds);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter( // single b/c one column is a row key column
+ or(
+ constantComparison(
+ CompareOp.EQUAL,
+ new SubstrFunction(Arrays.<Expression> asList(
+ new RowKeyColumnExpression(
+ BaseConnectionlessQueryTest.ENTITY_ID,
+ new RowKeyValueAccessor(BaseConnectionlessQueryTest.ATABLE.getPKColumns(), 1)),
+ LiteralExpression.newConstant(1),
+ LiteralExpression.newConstant(3))),
+ keyPrefix),
+ constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ aInt))),
+ filter);
+ }
+
+ @Test
+ public void testTypeMismatch() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_integer > 'foo'";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+
+ try {
+ pstmt.optimizeQuery();
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("Type mismatch"));
+ }
+ }
+
+ @Test
+ public void testAndFalseFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_integer=0 and 2=3";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ assertDegenerate(plan.getContext());
+ }
+
+ @Test
+ public void testFalseFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and 2=3";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ assertDegenerate(plan.getContext());
+ }
+
+ @Test
+ public void testTrueFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and 2<=2";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ assertNull(scan.getFilter());
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = startRow;
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+ }
+
+ @Test
+ public void testAndTrueFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_integer=0 and 2<3";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter(constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0)),
+ filter);
+
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = startRow;
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+ }
+
+ @Test
+ public void testOrFalseFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and (a_integer=0 or 3!=3)";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter(constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0)),
+ filter);
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = startRow;
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+ }
+
+ @Test
+ public void testOrTrueFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and (a_integer=0 or 3>2)";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertNull(filter);
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = startRow;
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+ }
+
+ @Test
+ public void testInFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_string IN ('a','b')";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = startRow;
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter(in(
+ BaseConnectionlessQueryTest.A_STRING,
+ "a",
+ "b")),
+ filter);
+ }
+
+ @Test
+ public void testInListFilter() throws SQLException {
+ String tenantId1 = "000000000000001";
+ String tenantId2 = "000000000000002";
+ String tenantId3 = "000000000000003";
+ String query = String.format("select * from %s where organization_id IN ('%s','%s','%s')",
+ ATABLE_NAME, tenantId1, tenantId3, tenantId2);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId1);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = PDataType.VARCHAR.toBytes(tenantId3);
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+
+ Filter filter = scan.getFilter();
+ assertEquals(
+ new SkipScanFilter(
+ ImmutableList.of(Arrays.asList(
+ pointRange(tenantId1),
+ pointRange(tenantId2),
+ pointRange(tenantId3))),
+ plan.getContext().getResolver().getTables().get(0).getTable().getRowKeySchema()),
+ filter);
+ }
+
+ @Test @Ignore("OR not yet optimized")
+ public void testOr2InFilter() throws SQLException {
+ String tenantId1 = "000000000000001";
+ String tenantId2 = "000000000000002";
+ String tenantId3 = "000000000000003";
+ String query = String.format("select * from %s where organization_id='%s' OR organization_id='%s' OR organization_id='%s'",
+ ATABLE_NAME, tenantId1, tenantId3, tenantId2);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+
+ Filter filter = scan.getFilter();
+ assertEquals(
+ new SkipScanFilter(
+ ImmutableList.of(Arrays.asList(
+ pointRange(tenantId1),
+ pointRange(tenantId2),
+ pointRange(tenantId3))),
+ plan.getContext().getResolver().getTables().get(0).getTable().getRowKeySchema()),
+ filter);
+
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId1);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = PDataType.VARCHAR.toBytes(tenantId3);
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+ }
+
+ @Test
+ public void testSecondPkColInListFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String entityId1 = "00000000000000X";
+ String entityId2 = "00000000000000Y";
+ String query = String.format("select * from %s where organization_id='%s' AND entity_id IN ('%s','%s')",
+ ATABLE_NAME, tenantId, entityId1, entityId2);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId + entityId1);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = PDataType.VARCHAR.toBytes(tenantId + entityId2);
+ assertArrayEquals(ByteUtil.concat(stopRow, QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
+
+ Filter filter = scan.getFilter();
+
+ assertEquals(
+ new SkipScanFilter(
+ ImmutableList.of(
+ Arrays.asList(
+ pointRange(tenantId,entityId1),
+ pointRange(tenantId,entityId2))),
+ SchemaUtil.VAR_BINARY_SCHEMA),
+ filter);
+ }
+
+ @Test
+ public void testInListWithAnd1GTEFilter() throws SQLException {
+ String tenantId1 = "000000000000001";
+ String tenantId2 = "000000000000002";
+ String tenantId3 = "000000000000003";
+ String entityId1 = "00000000000000X";
+ String entityId2 = "00000000000000Y";
+ String query = String.format("select * from %s where organization_id IN ('%s','%s','%s') AND entity_id>='%s' AND entity_id<='%s'",
+ ATABLE_NAME, tenantId1, tenantId3, tenantId2, entityId1, entityId2);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ new SkipScanFilter(
+ ImmutableList.of(
+ Arrays.asList(
+ pointRange(tenantId1),
+ pointRange(tenantId2),
+ pointRange(tenantId3)),
+ Arrays.asList(PDataType.CHAR.getKeyRange(
+ Bytes.toBytes(entityId1),
+ true,
+ Bytes.toBytes(entityId2),
+ true))),
+ plan.getContext().getResolver().getTables().get(0).getTable().getRowKeySchema()),
+ filter);
+ }
+
+ @Test
+ public void testInListWithAnd1Filter() throws SQLException {
+ String tenantId1 = "000000000000001";
+ String tenantId2 = "000000000000002";
+ String tenantId3 = "000000000000003";
+ String entityId = "00000000000000X";
+ String query = String.format("select * from %s where organization_id IN ('%s','%s','%s') AND entity_id='%s'",
+ ATABLE_NAME, tenantId1, tenantId3, tenantId2, entityId);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ new SkipScanFilter(
+ ImmutableList.of(
+ Arrays.asList(
+ pointRange(tenantId1, entityId),
+ pointRange(tenantId2, entityId),
+ pointRange(tenantId3, entityId))),
+ SchemaUtil.VAR_BINARY_SCHEMA),
+ filter);
+ }
+ @Test
+ public void testInListWithAnd1FilterScankey() throws SQLException {
+ String tenantId1 = "000000000000001";
+ String tenantId2 = "000000000000002";
+ String tenantId3 = "000000000000003";
+ String entityId = "00000000000000X";
+ String query = String.format("select * from %s where organization_id IN ('%s','%s','%s') AND entity_id='%s'",
+ ATABLE_NAME, tenantId1, tenantId3, tenantId2, entityId);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ byte[] startRow = ByteUtil.concat(PDataType.VARCHAR.toBytes(tenantId1), PDataType.VARCHAR.toBytes(entityId));
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = ByteUtil.concat(PDataType.VARCHAR.toBytes(tenantId3), PDataType.VARCHAR.toBytes(entityId));
+ assertArrayEquals(ByteUtil.concat(stopRow, QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
+ // TODO: validate scan ranges
+ }
+
+ private static KeyRange pointRange(String... ids) {
+ byte[] theKey = ByteUtil.EMPTY_BYTE_ARRAY;
+ for (String id : ids) {
+ theKey = ByteUtil.concat(theKey, Bytes.toBytes(id));
+ }
+ return pointRange(theKey);
+ }
+ private static KeyRange pointRange(byte[] bytes) {
+ return KeyRange.POINT.apply(bytes);
+ }
+
+ @Test
+ public void testInListWithAnd2Filter() throws SQLException {
+ String tenantId1 = "000000000000001";
+ String tenantId2 = "000000000000002";
+ String entityId1 = "00000000000000X";
+ String entityId2 = "00000000000000Y";
+ String query = String.format("select * from %s where organization_id IN ('%s','%s') AND entity_id IN ('%s', '%s')",
+ ATABLE_NAME, tenantId1, tenantId2, entityId1, entityId2);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+
+ Filter filter = scan.getFilter();
+ assertEquals(
+ new SkipScanFilter(
+ ImmutableList.<List<KeyRange>>of(ImmutableList.of(
+ pointRange(tenantId1, entityId1),
+ pointRange(tenantId1, entityId2),
+ pointRange(tenantId2, entityId1),
+ pointRange(tenantId2, entityId2))),
+ SchemaUtil.VAR_BINARY_SCHEMA),
+ filter);
+ }
+
+ @Test
+ public void testPartialRangeFilter() throws SQLException {
+ // I know these id's are ridiculous, but users can write queries that look like this
+ String tenantId1 = "001";
+ String tenantId2 = "02";
+ String query = String.format("select * from %s where organization_id > '%s' AND organization_id < '%s'",
+ ATABLE_NAME, tenantId1, tenantId2);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+
+ assertNull(scan.getFilter());
+ byte[] wideLower = ByteUtil.nextKey(StringUtil.padChar(Bytes.toBytes(tenantId1), 15));
+ byte[] wideUpper = StringUtil.padChar(Bytes.toBytes(tenantId2), 15);
+ assertArrayEquals(wideLower, scan.getStartRow());
+ assertArrayEquals(wideUpper, scan.getStopRow());
+ }
+
+ @Test
+ public void testInListWithAnd2FilterScanKey() throws SQLException {
+ String tenantId1 = "000000000000001";
+ String tenantId2 = "000000000000002";
+ String tenantId3 = "000000000000003";
+ String entityId1 = "00000000000000X";
+ String entityId2 = "00000000000000Y";
+ String query = String.format("select * from %s where organization_id IN ('%s','%s','%s') AND entity_id IN ('%s', '%s')",
+ ATABLE_NAME, tenantId1, tenantId3, tenantId2, entityId1, entityId2);
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ byte[] startRow = ByteUtil.concat(PDataType.VARCHAR.toBytes(tenantId1),PDataType.VARCHAR.toBytes(entityId1));
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = ByteUtil.concat(PDataType.VARCHAR.toBytes(tenantId3),PDataType.VARCHAR.toBytes(entityId2));
+ assertArrayEquals(ByteUtil.concat(stopRow, QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
+ // TODO: validate scan ranges
+ }
+
+ @Test
+ public void testBetweenFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_integer between 0 and 10";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter(and(
+ constantComparison(
+ CompareOp.GREATER_OR_EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0),
+ constantComparison(
+ CompareOp.LESS_OR_EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 10))),
+ filter);
+ }
+
+ @Test
+ public void testNotBetweenFilter() throws SQLException {
+ String tenantId = "000000000000001";
+ String query = "select * from atable where organization_id='" + tenantId + "' and a_integer not between 0 and 10";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+ assertEquals(
+ singleKVFilter(not(and(
+ constantComparison(
+ CompareOp.GREATER_OR_EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0),
+ constantComparison(
+ CompareOp.LESS_OR_EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 10)))).toString(),
+ filter.toString());
+ }
+
+ @Test
+ public void testTenantConstraintsAddedToScan() throws SQLException {
+ String tenantTypeId = "5678";
+ String tenantId = "000000000000123";
+ String url = getUrl(tenantId);
+ createTestTable(getUrl(), "create table base_table_for_tenant_filter_test (tenant_id char(15) not null, type_id char(4) not null, " +
+ "id char(5) not null, a_integer integer, a_string varchar(100) constraint pk primary key (tenant_id, type_id, id)) multi_tenant=true");
+ createTestTable(url, "create view tenant_filter_test (tenant_col integer) AS SELECT * FROM BASE_TABLE_FOR_TENANT_FILTER_TEST WHERE type_id= '" + tenantTypeId + "'");
+
+ String query = "select * from tenant_filter_test where a_integer=0 and a_string='foo'";
+ PhoenixConnection pconn = DriverManager.getConnection(url, TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+
+ assertEquals(
+ multiKVFilter(and(
+ constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0),
+ constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_STRING,
+ "foo"))),
+ filter);
+
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId + tenantTypeId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = startRow;
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+ }
+
+ @Test
+ public void testTenantConstraintsAddedToScanWithNullTenantTypeId() throws SQLException {
+ String tenantId = "000000000000123";
+ createTestTable(getUrl(), "create table base_table_for_tenant_filter_test (tenant_id char(15) not null, " +
+ "id char(5) not null, a_integer integer, a_string varchar(100) constraint pk primary key (tenant_id, id)) multi_tenant=true");
+ createTestTable(getUrl(tenantId), "create view tenant_filter_test (tenant_col integer) AS SELECT * FROM BASE_TABLE_FOR_TENANT_FILTER_TEST");
+
+ String query = "select * from tenant_filter_test where a_integer=0 and a_string='foo'";
+ PhoenixConnection pconn = DriverManager.getConnection(getUrl(tenantId), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+ PhoenixPreparedStatement pstmt = new PhoenixPreparedStatement(pconn, query);
+ QueryPlan plan = pstmt.optimizeQuery();
+ Scan scan = plan.getContext().getScan();
+ Filter filter = scan.getFilter();
+
+ assertEquals(
+ multiKVFilter(and(
+ constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_INTEGER,
+ 0),
+ constantComparison(
+ CompareOp.EQUAL,
+ BaseConnectionlessQueryTest.A_STRING,
+ "foo"))),
+ filter);
+
+ byte[] startRow = PDataType.VARCHAR.toBytes(tenantId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = startRow;
+ assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
+ }
+}