org.apache.phoenix.jdbc.PhoenixPreparedStatement Java Examples

The following examples show how to use org.apache.phoenix.jdbc.PhoenixPreparedStatement. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId1), PVarchar.INSTANCE.toBytes(entityId1));
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId3), PVarchar.INSTANCE.toBytes(entityId2));
    assertArrayEquals(ByteUtil.concat(stopRow, QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
    // TODO: validate scan ranges
}
Example #2
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.EQUAL,
            A_INTEGER,
            0)),
        filter);
}
Example #3
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
        PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
        QueryPlan plan = pstmt.optimizeQuery();
        Scan scan = plan.getContext().getScan();
        Filter filter = scan.getFilter();

        assertEquals(
            singleKVFilter(constantComparison(
                CompareOp.GREATER_OR_EQUAL,
                X_DECIMAL,
                expectedDecimal)),
            filter);
}
Example #4
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(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(ENTITY_ID,new RowKeyValueAccessor(ATABLE.getPKColumns(),1)),
                        LiteralExpression.newConstant(1),
                        LiteralExpression.newConstant(3))
                    ),
                keyPrefix),
            QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES),
        filter);
}
Example #5
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@Test
public void testSingleVariableFullPkSalted() throws SQLException {
    PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(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 = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertNull(filter);
    byte[] key = new byte[2];
    PVarchar.INSTANCE.toBytes("a", key, 1);
    key[0] = SaltingUtil.getSaltingByte(key, 1, 1, 20);
    byte[] expectedStartKey = key;
    byte[] expectedEndKey = ByteUtil.nextKey(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);
}
Example #6
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    bindParams(pstmt, binds);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    
    assertEquals(
        multiKVFilter(and(
            constantComparison(
                CompareOp.EQUAL,
                A_INTEGER,
                0),
            constantComparison(
                CompareOp.EQUAL,
                A_STRING,
                "foo"))),
        filter);
}
Example #7
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    
    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.LESS_OR_EQUAL,
            A_INTEGER,
            0)),
        filter);
}
Example #8
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();

    Date date = DateUtil.parseDate(dateStr);

    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.GREATER_OR_EQUAL,
            A_DATE,
            date)),
        filter);
}
Example #9
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.EQUAL,
            A_INTEGER,
            0)),
        filter);
    byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId);
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = startRow;
    assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
}
Example #10
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(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);
}
Example #11
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.EQUAL,
            A_INTEGER,
            0)),
        filter);

    byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId);
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = startRow;
    assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
}
Example #12
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.EQUAL,
            A_INTEGER,
            0)),
        filter);
    byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId);
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = startRow;
    assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());
}
Example #13
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId);
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = startRow;
    assertArrayEquals(ByteUtil.nextKey(stopRow), scan.getStopRow());

    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(in(
            A_STRING,
            "a",
            "b")),
        filter);
}
Example #14
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId1);
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = PVarchar.INSTANCE.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);
}
Example #15
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId1), PVarchar.INSTANCE.toBytes(entityId));
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId3), PVarchar.INSTANCE.toBytes(entityId));
    assertArrayEquals(ByteUtil.concat(stopRow, QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
    // TODO: validate scan ranges
}
Example #16
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId1);
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = PVarchar.INSTANCE.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.getTableRef().getTable().getRowKeySchema()),
        filter);
}
Example #17
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertEquals(
            singleKVFilter(and(
                constantComparison(
                    CompareOp.GREATER_OR_EQUAL,
                    A_INTEGER,
                    0),
                constantComparison(
                    CompareOp.LESS_OR_EQUAL,
                    A_INTEGER,
                    10))),
            filter);
}
Example #18
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId1), PVarchar.INSTANCE.toBytes(entityId));
    assertArrayEquals(startRow, scan.getStartRow());
    byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(tenantId3), PVarchar.INSTANCE.toBytes(entityId));
    assertArrayEquals(ByteUtil.concat(stopRow, QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow());
    // TODO: validate scan ranges
}
Example #19
Source File: TestUtil.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
public static List<KeyRange> getSplits(Connection conn, String tableName, String pkCol, byte[] lowerRange, byte[] upperRange, String whereClauseSuffix) throws SQLException {
    String whereClauseStart = 
            (lowerRange == null && upperRange == null ? "" : 
                " WHERE " + ((lowerRange != null ? (pkCol + " >= ? " + (upperRange != null ? " AND " : "")) : "") 
                          + (upperRange != null ? (pkCol + " < ?") : "" )));
    String whereClause = whereClauseSuffix == null ? whereClauseStart : whereClauseStart.length() == 0 ? (" WHERE " + whereClauseSuffix) : (" AND " + whereClauseSuffix);
    String query = "SELECT /*+ NO_INDEX */ COUNT(*) FROM " + tableName + whereClause;
    PhoenixPreparedStatement pstmt = conn.prepareStatement(query).unwrap(PhoenixPreparedStatement.class);
    if (lowerRange != null) {
        pstmt.setBytes(1, lowerRange);
    }
    if (upperRange != null) {
        pstmt.setBytes(lowerRange != null ? 2 : 1, upperRange);
    }
    pstmt.execute();
    List<KeyRange> keyRanges = pstmt.getQueryPlan().getSplits();
    return keyRanges;
}
Example #20
Source File: TestUtil.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
public static Collection<GuidePostsInfo> getGuidePostsList(Connection conn, String tableName, String pkCol,
        byte[] lowerRange, byte[] upperRange, String whereClauseSuffix) throws SQLException {
    String whereClauseStart = (lowerRange == null && upperRange == null ? ""
            : " WHERE "
                    + ((lowerRange != null ? (pkCol + " >= ? " + (upperRange != null ? " AND " : "")) : "") + (upperRange != null ? (pkCol + " < ?")
                            : "")));
    String whereClause = whereClauseSuffix == null ? whereClauseStart
            : whereClauseStart.length() == 0 ? (" WHERE " + whereClauseSuffix) : (" AND " + whereClauseSuffix);
    String query = "SELECT /*+ NO_INDEX */ COUNT(*) FROM " + tableName + whereClause;
    PhoenixPreparedStatement pstmt = conn.prepareStatement(query).unwrap(PhoenixPreparedStatement.class);
    if (lowerRange != null) {
        pstmt.setBytes(1, lowerRange);
    }
    if (upperRange != null) {
        pstmt.setBytes(lowerRange != null ? 2 : 1, upperRange);
    }
    pstmt.execute();
    TableRef tableRef = pstmt.getQueryPlan().getTableRef();
    PTableStats tableStats = tableRef.getTable().getTableStats();
    return tableStats.getGuidePosts().values();
}
Example #21
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertEquals(
            singleKVFilter(and(
                constantComparison(
                    CompareOp.GREATER_OR_EQUAL,
                    A_INTEGER,
                    0),
                constantComparison(
                    CompareOp.LESS_OR_EQUAL,
                    A_INTEGER,
                    10))),
            filter);
}
Example #22
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.EQUAL,
            A_INTEGER,
            0)),
        filter);
}
Example #23
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@Test
public void testAndPKAndNotPK() throws SQLException {
    String query = "select * from bugTable where ID = 'i2' and company = 'c3'";
    PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    pconn.createStatement().execute("create table bugTable(ID varchar primary key,company varchar)");
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    PColumn column = plan.getTableRef().getTable().getColumnForColumnName("COMPANY");
    assertEquals(
            singleKVFilter(constantComparison(
                CompareOp.EQUAL,
                new KeyValueColumnExpression(column),
                "c3")),
            filter);
}
Example #24
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@Test
public void testSingleVariableFullPkSalted() throws SQLException {
    PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    pconn.createStatement().execute("CREATE TABLE t (k varchar(10) primary key, v varchar) SALT_BUCKETS=20");
    String query = "select * from t where k='a'";
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    assertNull(filter);
    byte[] key = new byte[2];
    PVarchar.INSTANCE.toBytes("a", key, 1);
    key[0] = SaltingUtil.getSaltingByte(key, 1, 1, 20);
    byte[] expectedStartKey = key;
    //lexicographically this is the next PK
    byte[] expectedEndKey = ByteUtil.concat(key,new byte[]{0});
    byte[] startKey = scan.getStartRow();
    byte[] stopKey = scan.getStopRow();
    assertTrue(Bytes.compareTo(expectedStartKey, startKey) == 0);
    assertTrue(Bytes.compareTo(expectedEndKey, stopKey) == 0);
}
Example #25
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(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);
}
Example #26
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    bindParams(pstmt, binds);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();

    assertEquals(
        multiEncodedKVFilter(and(
            constantComparison(
                CompareOp.EQUAL,
                A_INTEGER,
                0),
            constantComparison(
                CompareOp.EQUAL,
                A_STRING,
                "foo")), TWO_BYTE_QUALIFIERS),
        filter);
}
Example #27
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();

    Filter filter = scan.getFilter();
    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.LESS_OR_EQUAL,
            A_INTEGER,
            0)),
        filter);
}
Example #28
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();

    Date date = DateUtil.parseDate(dateStr);

    assertEquals(
        singleKVFilter(constantComparison(
            CompareOp.GREATER_OR_EQUAL,
            A_DATE,
            date)),
        filter);
}
Example #29
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
        PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
        QueryPlan plan = pstmt.optimizeQuery();
        Scan scan = plan.getContext().getScan();
        Filter filter = scan.getFilter();

        assertEquals(
            singleKVFilter(constantComparison(
                CompareOp.GREATER_OR_EQUAL,
                X_DECIMAL,
                expectedDecimal)),
            filter);
}
Example #30
Source File: WhereCompilerTest.java    From phoenix with Apache License 2.0 6 votes vote down vote up 
@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(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    PhoenixPreparedStatement pstmt = newPreparedStatement(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(ENTITY_ID,new RowKeyValueAccessor(ATABLE.getPKColumns(),1)),
                        LiteralExpression.newConstant(1),
                        LiteralExpression.newConstant(3))
                    ),
                keyPrefix),
            QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES),
        filter);
}