Examples with SkipScanFilter org.apache.phoenix.filter.SkipScanFilter used on opensource projects

Example 21 with SkipScanFilter

use of org.apache.phoenix.filter.SkipScanFilter in project phoenix by apache.

the class WhereOptimizerTest method testForceSkipScanOnSaltedTable.

@Test
public void testForceSkipScanOnSaltedTable() throws SQLException {
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute("CREATE TABLE IF NOT EXISTS user_messages (\n" + "        SENDER_ID UNSIGNED_LONG NOT NULL,\n" + "        RECIPIENT_ID UNSIGNED_LONG NOT NULL,\n" + "        SENDER_IP VARCHAR,\n" + "        IS_READ VARCHAR,\n" + "        IS_DELETED VARCHAR,\n" + "        M_TEXT VARCHAR,\n" + "        M_TIMESTAMP timestamp  NOT NULL,\n" + "        ROW_ID UNSIGNED_LONG NOT NULL\n" + "        constraint rowkey primary key (SENDER_ID,RECIPIENT_ID,M_TIMESTAMP DESC,ROW_ID))\n" + "SALT_BUCKETS=12\n");
    String query = "select /*+ SKIP_SCAN */ count(*) from user_messages where is_read='N' and recipient_id=5399179882";
    StatementContext context = compileStatement(query);
    Scan scan = context.getScan();
    Filter filter = scan.getFilter();
    assertNotNull(filter);
    assertTrue(filter instanceof FilterList);
    FilterList filterList = (FilterList) filter;
    assertEquals(FilterList.Operator.MUST_PASS_ALL, filterList.getOperator());
    assertEquals(2, filterList.getFilters().size());
    assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
    assertTrue(filterList.getFilters().get(1) instanceof SingleKeyValueComparisonFilter);
    ScanRanges scanRanges = context.getScanRanges();
    assertNotNull(scanRanges);
    assertEquals(3, scanRanges.getRanges().size());
    assertEquals(1, scanRanges.getRanges().get(1).size());
    assertEquals(KeyRange.EVERYTHING_RANGE, scanRanges.getRanges().get(1).get(0));
    assertEquals(1, scanRanges.getRanges().get(2).size());
    assertTrue(scanRanges.getRanges().get(2).get(0).isSingleKey());
    assertEquals(Long.valueOf(5399179882L), PUnsignedLong.INSTANCE.toObject(scanRanges.getRanges().get(2).get(0).getLowerRange()));
}

Example 22 with SkipScanFilter

use of org.apache.phoenix.filter.SkipScanFilter in project phoenix by apache.

the class WhereOptimizerTest method testOrSameColRangeExpression.

@Test
public void testOrSameColRangeExpression() throws SQLException {
    String query = "select * from atable where substr(organization_id,1,3) = ? or organization_id LIKE 'foo%'";
    List<Object> binds = Arrays.<Object>asList("00D");
    StatementContext context = compileStatement(query, binds);
    Scan scan = context.getScan();
    Filter filter = scan.getFilter();
    assertNotNull(filter);
    assertTrue(filter instanceof SkipScanFilter);
    ScanRanges scanRanges = context.getScanRanges();
    assertNotNull(scanRanges);
    List<List<KeyRange>> ranges = scanRanges.getRanges();
    assertEquals(1, ranges.size());
    List<List<KeyRange>> expectedRanges = Collections.singletonList(Arrays.asList(PChar.INSTANCE.getKeyRange(StringUtil.padChar(PChar.INSTANCE.toBytes("00D"), 15), true, StringUtil.padChar(ByteUtil.nextKey(PChar.INSTANCE.toBytes("00D")), 15), false), PChar.INSTANCE.getKeyRange(StringUtil.padChar(PChar.INSTANCE.toBytes("foo"), 15), true, StringUtil.padChar(ByteUtil.nextKey(PChar.INSTANCE.toBytes("foo")), 15), false)));
    assertEquals(expectedRanges, ranges);
}

Example 23 with SkipScanFilter

use of org.apache.phoenix.filter.SkipScanFilter in project phoenix by apache.

the class WhereOptimizerTest method testOrPKRangesNotOptimized.

@Test
public void testOrPKRangesNotOptimized() throws SQLException {
    Connection conn = DriverManager.getConnection(getUrl());
    ensureTableCreated(getUrl(), TestUtil.BTABLE_NAME);
    Statement stmt = conn.createStatement();
    // BTABLE has 5 PK columns
    String[] queries = { "select * from " + BTABLE_NAME + " where (a_string > '1' and a_string < '5') or (a_string > '6' and a_string < '9' and a_id = 'foo')", "select * from " + BTABLE_NAME + " where (a_id > 'aaa' and a_id < 'ccc') or (a_id > 'jjj' and a_id < 'mmm')" };
    for (String query : queries) {
        StatementContext context = compileStatement(query);
        Iterator<Filter> it = ScanUtil.getFilterIterator(context.getScan());
        while (it.hasNext()) {
            assertFalse(it.next() instanceof SkipScanFilter);
        }
        TestUtil.assertNotDegenerate(context.getScan());
    }
    stmt.close();
    conn.close();
}

Example 24 with SkipScanFilter

use of org.apache.phoenix.filter.SkipScanFilter in project phoenix by apache.

the class WhereOptimizerTest method testOrSameColExpression.

@Test
public void testOrSameColExpression() throws SQLException {
    String tenantId1 = "000000000000001";
    String tenantId2 = "000000000000003";
    String query = "select * from atable where organization_id = ? or organization_id  = ?";
    List<Object> binds = Arrays.<Object>asList(tenantId1, tenantId2);
    StatementContext context = compileStatement(query, binds);
    Scan scan = context.getScan();
    Filter filter = scan.getFilter();
    assertNotNull(filter);
    assertTrue(filter instanceof SkipScanFilter);
    ScanRanges scanRanges = context.getScanRanges();
    assertNotNull(scanRanges);
    List<List<KeyRange>> ranges = scanRanges.getRanges();
    assertEquals(1, ranges.size());
    List<List<KeyRange>> expectedRanges = Collections.singletonList(Arrays.asList(PChar.INSTANCE.getKeyRange(PChar.INSTANCE.toBytes(tenantId1), true, PChar.INSTANCE.toBytes(tenantId1), true), PChar.INSTANCE.getKeyRange(PChar.INSTANCE.toBytes(tenantId2), true, PChar.INSTANCE.toBytes(tenantId2), true)));
    assertEquals(expectedRanges, ranges);
    byte[] startRow = PVarchar.INSTANCE.toBytes(tenantId1);
    assertArrayEquals(startRow, scan.getStartRow());
    assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId2)), scan.getStopRow());
}

Example 25 with SkipScanFilter

use of org.apache.phoenix.filter.SkipScanFilter in project phoenix by apache.

the class WhereOptimizerTest method testOrExpressionNonLeadingPKPushToScanBug4602.

@Test
public void testOrExpressionNonLeadingPKPushToScanBug4602() throws Exception {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(getUrl());
        String testTableName = "OR_NO_LEADING_PK4602";
        String sql = "CREATE TABLE " + testTableName + "(" + "PK1 INTEGER NOT NULL," + "PK2 INTEGER NOT NULL," + "PK3 INTEGER NOT NULL," + "DATA INTEGER, " + "CONSTRAINT TEST_PK PRIMARY KEY (PK1,PK2,PK3))";
        conn.createStatement().execute(sql);
        // case 1: pk1 is equal,pk2 is multiRange
        sql = "select * from " + testTableName + " t where (t.pk1 = 2) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
        QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        Scan scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof SkipScanFilter);
        List<List<KeyRange>> rowKeyRanges = ((SkipScanFilter) (scan.getFilter())).getSlots();
        assertEquals(Arrays.asList(Arrays.asList(KeyRange.POINT.apply(PInteger.INSTANCE.toBytes(2))), Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false), KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false))), rowKeyRanges);
        assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(4)));
        assertArrayEquals(scan.getStopRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(9)));
        // case 2: pk1 is range,pk2 is multiRange
        sql = "select * from " + testTableName + " t where (t.pk1 >=2 and t.pk1<5) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof SkipScanFilter);
        rowKeyRanges = ((SkipScanFilter) (scan.getFilter())).getSlots();
        assertEquals(Arrays.asList(Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true, PInteger.INSTANCE.toBytes(5), false)), Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false), KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false))), rowKeyRanges);
        assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(4)));
        assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(5));
        // case 3 : pk1 has multiRange,,pk2 is multiRange
        sql = "select * from " + testTableName + " t where ((t.pk1 >=2 and t.pk1<5) or (t.pk1 >=7 and t.pk1 <9)) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof SkipScanFilter);
        rowKeyRanges = ((SkipScanFilter) (scan.getFilter())).getSlots();
        assertEquals(Arrays.asList(Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true, PInteger.INSTANCE.toBytes(5), false), KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(7), true, PInteger.INSTANCE.toBytes(9), false)), Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false), KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false))), rowKeyRanges);
        assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2), PInteger.INSTANCE.toBytes(4)));
        assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));
        // case4 : only pk1 and pk3, no pk2
        sql = "select * from " + testTableName + " t where ((t.pk1 >=2 and t.pk1<5) or (t.pk1 >=7 and t.pk1 <9)) and ((t.pk3 >= 4 and t.pk3 <6) or (t.pk3 >= 8 and t.pk3 <9))";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof FilterList);
        FilterList filterList = (FilterList) scan.getFilter();
        assertTrue(filterList.getOperator() == Operator.MUST_PASS_ALL);
        assertEquals(filterList.getFilters().size(), 2);
        assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
        rowKeyRanges = ((SkipScanFilter) (filterList.getFilters().get(0))).getSlots();
        assertEquals(Arrays.asList(Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true, PInteger.INSTANCE.toBytes(5), false), KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(7), true, PInteger.INSTANCE.toBytes(9), false)), Arrays.asList(KeyRange.EVERYTHING_RANGE), Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false), KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false))), rowKeyRanges);
        assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(2));
        assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));
        assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
        RowKeyComparisonFilter rowKeyComparisonFilter = (RowKeyComparisonFilter) filterList.getFilters().get(1);
        Expression pk3Expression = new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK3").getPosition()).newColumnExpression();
        assertEquals(TestUtil.rowKeyFilter(TestUtil.or(TestUtil.and(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk3Expression, 4), TestUtil.constantComparison(CompareOp.LESS, pk3Expression, 6)), TestUtil.and(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk3Expression, 8), TestUtil.constantComparison(CompareOp.LESS, pk3Expression, 9)))), rowKeyComparisonFilter);
        // case 5: pk1 or data column
        sql = "select * from " + testTableName + " t where ((t.pk1 >=2) or (t.data >= 4 and t.data <9))";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof SingleCQKeyValueComparisonFilter);
        Expression pk1Expression = new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK1").getPosition()).newColumnExpression();
        Expression dataExpression = new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("DATA").getPosition()).newColumnExpression();
        assertEquals(TestUtil.singleKVFilter(TestUtil.or(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk1Expression, 2), TestUtil.and(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, dataExpression, 4), TestUtil.constantComparison(CompareOp.LESS, dataExpression, 9)))), scan.getFilter());
        assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
        assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
        // case 6: pk1 or pk2,but pk2 is empty range
        sql = "select * from " + testTableName + " t where (t.pk1 >=2 and t.pk1<5) or ((t.pk2 >= 4 and t.pk2 <6) and (t.pk2 >= 8 and t.pk2 <9))";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        scan = queryPlan.getContext().getScan();
        assertNull(scan.getFilter());
        assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(2));
        assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(5));
        // case 7: pk1 or pk2,but pk2 is all range
        sql = "select * from " + testTableName + " t where (t.pk1 >=2 and t.pk1<5) or (t.pk2 >=7 or t.pk2 <9)";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        Expression pk2Expression = new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK2").getPosition()).newColumnExpression();
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
        assertEquals(TestUtil.rowKeyFilter(TestUtil.or(TestUtil.and(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk1Expression, 2), TestUtil.constantComparison(CompareOp.LESS, pk1Expression, 5)), TestUtil.or(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk2Expression, 7), TestUtil.constantComparison(CompareOp.LESS, pk2Expression, 9)))), scan.getFilter());
        assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
        assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
        // case 8: pk1 and pk2, but pk1 has a or allRange
        sql = "select * from " + testTableName + " t where ((t.pk1 >=2 and t.pk1<5) or (t.pk1 >=7 or t.pk1 <9)) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
        assertEquals(TestUtil.rowKeyFilter(TestUtil.or(TestUtil.and(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk2Expression, 4), TestUtil.constantComparison(CompareOp.LESS, pk2Expression, 6)), TestUtil.and(TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk2Expression, 8), TestUtil.constantComparison(CompareOp.LESS, pk2Expression, 9)))), scan.getFilter());
        assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
        assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
        // case 9:  pk1 and pk2, but pk2 has a or allRange
        sql = "select * from " + testTableName + " t where ((t.pk1 >= 4 and t.pk1 <6) or (t.pk1 >= 8 and t.pk1 <9)) and ((t.pk2 >=2 and t.pk2<5) or (t.pk2 >=7 or t.pk2 <9))";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof SkipScanFilter);
        rowKeyRanges = ((SkipScanFilter) (scan.getFilter())).getSlots();
        assertEquals(Arrays.asList(Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6), false), KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9), false)), Arrays.asList(KeyRange.EVERYTHING_RANGE)), rowKeyRanges);
        assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(4));
        assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));
        // case 10: only pk2
        sql = "select * from " + testTableName + " t where (pk2 <=7 or pk2>9)";
        queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
        pk2Expression = new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK2").getPosition()).newColumnExpression();
        scan = queryPlan.getContext().getScan();
        assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
        assertEquals(TestUtil.rowKeyFilter(TestUtil.or(TestUtil.constantComparison(CompareOp.LESS_OR_EQUAL, pk2Expression, 7), TestUtil.constantComparison(CompareOp.GREATER, pk2Expression, 9))), scan.getFilter());
        assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
        assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}