PHOENIX-2194 order by should not require all PK fields with = constraint
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/d18afe18 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/d18afe18 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/d18afe18 Branch: refs/heads/calcite Commit: d18afe183525db7e1b9aba4e469ab39931204070 Parents: b3c05bf Author: James Taylor <jamestay...@apache.org> Authored: Tue Sep 8 15:41:41 2015 -0700 Committer: James Taylor <jamestay...@apache.org> Committed: Tue Sep 8 15:41:41 2015 -0700 ---------------------------------------------------------------------- .../phoenix/end2end/RowValueConstructorIT.java | 69 ++++++++ .../org/apache/phoenix/end2end/SortOrderIT.java | 27 +++ .../java/org/apache/phoenix/end2end/ViewIT.java | 24 +++ .../phoenix/compile/OrderPreservingTracker.java | 21 ++- .../org/apache/phoenix/compile/ScanRanges.java | 136 +++++++++++---- .../apache/phoenix/compile/WhereOptimizer.java | 72 ++++---- .../coprocessor/MetaDataEndpointImpl.java | 7 +- .../apache/phoenix/filter/SkipScanFilter.java | 28 +-- .../phoenix/index/PhoenixIndexBuilder.java | 5 +- .../apache/phoenix/iterate/ExplainTable.java | 7 +- .../apache/phoenix/optimize/QueryOptimizer.java | 6 +- .../java/org/apache/phoenix/util/ScanUtil.java | 49 +++--- .../phoenix/compile/QueryCompilerTest.java | 18 +- .../compile/ScanRangesIntersectTest.java | 37 +--- .../apache/phoenix/compile/ScanRangesTest.java | 3 +- .../TenantSpecificViewIndexCompileTest.java | 172 +++++++++++++++++++ .../phoenix/compile/ViewCompilerTest.java | 1 - .../phoenix/compile/WhereOptimizerTest.java | 22 ++- .../query/ParallelIteratorsSplitTest.java | 3 +- .../org/apache/phoenix/query/QueryPlanTest.java | 10 +- .../org/apache/phoenix/util/ScanUtilTest.java | 10 +- 21 files changed, 537 insertions(+), 190 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java index 5bf0a1e..749da5d 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java @@ -1522,4 +1522,73 @@ public class RowValueConstructorIT extends BaseClientManagedTimeIT { assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); } + + @Test + public void testRVCRequiringExtractNodeClear() throws Exception { + Connection conn = nextConnection(getUrl()); + String tableName = "testRVCWithTrailingGT"; + String ddl = "CREATE TABLE " + tableName + " (k1 VARCHAR, k2 VARCHAR, k3 VARCHAR, k4 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1,k2,k3,k4))"; + conn.createStatement().execute(ddl); + + conn = nextConnection(getUrl()); + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('a','b','c','d')"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('b', 'b', 'c', 'e')"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('c', 'b','c','f')"); + stmt.execute(); + conn.commit(); + + conn = nextConnection(getUrl()); + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT k1 from " + tableName + " WHERE k1 IN ('a','c') AND (k2,k3) IN (('b','c'),('f','g')) AND k4 > 'c'"); + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertTrue(rs.next()); + assertEquals("c", rs.getString(1)); + assertFalse(rs.next()); + } + + @Test + public void testRVCRequiringNoSkipScan() throws Exception { + Connection conn = nextConnection(getUrl()); + String tableName = "testRVCWithTrailingGT"; + String ddl = "CREATE TABLE " + tableName + " (k1 VARCHAR, k2 VARCHAR, k3 VARCHAR, k4 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1,k2,k3,k4))"; + conn.createStatement().execute(ddl); + + conn = nextConnection(getUrl()); + PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('','','a')"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('', '', 'a', 'a')"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('', '','b')"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('', '','b','a')"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('a', '','c')"); + stmt.execute(); + stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES('a', '','c', 'a')"); + stmt.execute(); + conn.commit(); + + conn = nextConnection(getUrl()); + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT k1,k3,k4 from " + tableName + " WHERE (k1,k2,k3) IN (('','','a'),('','','b'),('a','','c')) AND k4 is not null"); + assertTrue(rs.next()); + assertEquals(null, rs.getString(1)); + assertEquals("a", rs.getString(2)); + assertEquals("a", rs.getString(3)); + + assertTrue(rs.next()); + assertEquals(null, rs.getString(1)); + assertEquals("b", rs.getString(2)); + assertEquals("a", rs.getString(3)); + + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertEquals("c", rs.getString(2)); + assertEquals("a", rs.getString(3)); + + assertFalse(rs.next()); + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java index fdbd26d..b906ae2 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderIT.java @@ -402,6 +402,33 @@ public class SortOrderIT extends BaseHBaseManagedTimeIT { new WhereCondition("k2", "<=", "'bb'"), null, null); } + @Test + public void varLengthAscLT() throws Exception { + String ddl = "CREATE TABLE " + TABLE + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1, k2))"; + Object[][] insertedRows = new Object[][]{{"a", ""}, {"b",""}, {"b","a"}}; + Object[][] expectedRows = new Object[][]{{"a"}}; + runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, + new WhereCondition("k1", "<", "'b'"), null, null); + } + + @Test + public void varLengthDescLT() throws Exception { + String ddl = "CREATE TABLE " + TABLE + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 desc, k2))"; + Object[][] insertedRows = new Object[][]{{"a", ""}, {"b",""}, {"b","a"}}; + Object[][] expectedRows = new Object[][]{{"a"}}; + runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, + new WhereCondition("k1", "<", "'b'"), null, null); + } + + @Test + public void varLengthDescGT() throws Exception { + String ddl = "CREATE TABLE " + TABLE + " (k1 VARCHAR NOT NULL, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 desc, k2))"; + Object[][] insertedRows = new Object[][]{{"a", ""}, {"b",""}, {"b","a"}, {"ba","a"}}; + Object[][] expectedRows = new Object[][]{{"ba"}}; + runQueryTest(ddl, upsert("k1", "k2"), select("k1"), insertedRows, expectedRows, + new WhereCondition("k1", ">", "'b'"), null, null); + } + @Test public void testNonPKCompare() throws Exception { List<Integer> expectedResults = Lists.newArrayList(2,3,4); http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java index 3254c2e..123fd85 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java @@ -30,15 +30,18 @@ import static org.junit.Assert.fail; import java.sql.Connection; import java.sql.DriverManager; +import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; +import org.apache.phoenix.compile.QueryPlan; import org.apache.phoenix.exception.SQLExceptionCode; import org.apache.phoenix.query.KeyRange; import org.apache.phoenix.schema.ColumnAlreadyExistsException; import org.apache.phoenix.schema.ReadOnlyTableException; import org.apache.phoenix.schema.TableNotFoundException; +import org.apache.phoenix.util.PhoenixRuntime; import org.apache.phoenix.util.QueryUtil; import org.junit.Test; @@ -572,6 +575,27 @@ public class ViewIT extends BaseViewIT { } } + @Test + public void testQueryViewStatementOptimization() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String sql = "CREATE TABLE tp (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, v1 DECIMAL, CONSTRAINT pk PRIMARY KEY (k1, k2))"; + conn.createStatement().execute(sql); + sql = "CREATE VIEW v1 AS SELECT * FROM tp"; + conn.createStatement().execute(sql); + sql = "CREATE VIEW v2 AS SELECT * FROM tp WHERE k1 = 1.0"; + conn.createStatement().execute(sql); + + sql = "SELECT * FROM v1 order by k1, k2"; + PreparedStatement stmt = conn.prepareStatement(sql); + QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); + assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); + + sql = "SELECT * FROM v2 order by k1, k2"; + stmt = conn.prepareStatement(sql); + plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); + assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); + } + private void assertPKs(ResultSet rs, String[] expectedPKs) throws SQLException { List<String> pkCols = newArrayListWithExpectedSize(expectedPKs.length); while (rs.next()) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java index 70ae231..65245f3 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/OrderPreservingTracker.java @@ -65,6 +65,7 @@ public class OrderPreservingTracker { this.orderPreserving = orderPreserving; } } + private final StatementContext context; private final TrackOrderPreservingExpressionVisitor visitor; private final GroupBy groupBy; private final Ordering ordering; @@ -78,6 +79,7 @@ public class OrderPreservingTracker { } public OrderPreservingTracker(StatementContext context, GroupBy groupBy, Ordering ordering, int nNodes, TupleProjector projector) { + this.context = context; int pkPositionOffset = 0; PTable table = context.getResolver().getTables().get(0).getTable(); isOrderPreserving = table.rowKeyOrderOptimizable(); @@ -156,7 +158,12 @@ public class OrderPreservingTracker { Collections.sort(orderPreservingInfos, new Comparator<Info>() { @Override public int compare(Info o1, Info o2) { - return o1.pkPosition-o2.pkPosition; + int cmp = o1.pkPosition-o2.pkPosition; + if (cmp != 0) return cmp; + // After pk position, sort on reverse OrderPreserving ordinal: NO, YES_IF_LAST, YES + // In this way, if we have an ORDER BY over a YES_IF_LAST followed by a YES, we'll + // allow subsequent columns to be ordered. + return o2.orderPreserving.ordinal() - o1.orderPreserving.ordinal(); } }); } @@ -169,7 +176,7 @@ public class OrderPreservingTracker { for (int i = 0; i < orderPreservingInfos.size() && isOrderPreserving; i++) { Info entry = orderPreservingInfos.get(i); int pos = entry.pkPosition; - isOrderPreserving &= (entry.orderPreserving != OrderPreserving.NO) && (pos == prevPos || ((pos - prevSlotSpan == prevPos) && (prevOrderPreserving == OrderPreserving.YES))); + isOrderPreserving &= entry.orderPreserving != OrderPreserving.NO && prevOrderPreserving == OrderPreserving.YES && (pos == prevPos || pos - prevSlotSpan == prevPos || hasEqualityConstraints(prevPos+prevSlotSpan, pos)); prevPos = pos; prevSlotSpan = entry.slotSpan; prevOrderPreserving = entry.orderPreserving; @@ -177,6 +184,16 @@ public class OrderPreservingTracker { return isOrderPreserving; } + private boolean hasEqualityConstraints(int startPos, int endPos) { + ScanRanges ranges = context.getScanRanges(); + for (int pos = startPos; pos < endPos; pos++) { + if (!ranges.hasEqualityConstraint(pos)) { + return false; + } + } + return true; + } + public boolean isReverse() { return Boolean.TRUE.equals(isReverse); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java index 298cd4e..2a032a3 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java @@ -52,18 +52,24 @@ public class ScanRanges { public static final ScanRanges NOTHING = new ScanRanges(null,ScanUtil.SINGLE_COLUMN_SLOT_SPAN,NOTHING_RANGES, KeyRange.EMPTY_RANGE, KeyRange.EMPTY_RANGE, false, false, null); private static final Scan HAS_INTERSECTION = new Scan(); - public static ScanRanges create(RowKeySchema schema, List<List<KeyRange>> ranges, int[] slotSpan) { - return create(schema, ranges, slotSpan, KeyRange.EVERYTHING_RANGE, false, null); + public static ScanRanges createPointLookup(List<KeyRange> keys) { + return ScanRanges.create(SchemaUtil.VAR_BINARY_SCHEMA, Collections.singletonList(keys), ScanUtil.SINGLE_COLUMN_SLOT_SPAN, KeyRange.EVERYTHING_RANGE, null, true); } - public static ScanRanges create(RowKeySchema schema, List<List<KeyRange>> ranges, int[] slotSpan, KeyRange minMaxRange, boolean forceRangeScan, Integer nBuckets) { + // For testing + public static ScanRanges createSingleSpan(RowKeySchema schema, List<List<KeyRange>> ranges) { + return create(schema, ranges, ScanUtil.getDefaultSlotSpans(ranges.size()), KeyRange.EVERYTHING_RANGE, null, true); + } + + public static ScanRanges create(RowKeySchema schema, List<List<KeyRange>> ranges, int[] slotSpan, KeyRange minMaxRange, Integer nBuckets, boolean useSkipScan) { int offset = nBuckets == null ? 0 : SaltingUtil.NUM_SALTING_BYTES; - if (ranges.size() == offset && minMaxRange == KeyRange.EVERYTHING_RANGE) { + int nSlots = ranges.size(); + if (nSlots == offset && minMaxRange == KeyRange.EVERYTHING_RANGE) { return EVERYTHING; - } else if (minMaxRange == KeyRange.EMPTY_RANGE || (ranges.size() == 1 + offset && ranges.get(offset).size() == 1 && ranges.get(offset).get(0) == KeyRange.EMPTY_RANGE)) { + } else if (minMaxRange == KeyRange.EMPTY_RANGE || (nSlots == 1 + offset && ranges.get(offset).size() == 1 && ranges.get(offset).get(0) == KeyRange.EMPTY_RANGE)) { return NOTHING; } - boolean isPointLookup = !forceRangeScan && ScanRanges.isPointLookup(schema, ranges, slotSpan); + boolean isPointLookup = isPointLookup(schema, ranges, slotSpan, useSkipScan); if (isPointLookup) { // TODO: consider keeping original to use for serialization as it would be smaller? List<byte[]> keys = ScanRanges.getPointKeys(ranges, slotSpan, schema, nBuckets); @@ -86,6 +92,7 @@ public class ScanRanges { } } ranges = Collections.singletonList(keyRanges); + useSkipScan = keyRanges.size() > 1; // Treat as binary if descending because we've got a separator byte at the end // which is not part of the value. if (keys.size() > 1 || SchemaUtil.getSeparatorByte(schema.rowKeyOrderOptimizable(), false, schema.getField(0)) == QueryConstants.DESC_SEPARATOR_BYTE) { @@ -103,7 +110,6 @@ public class ScanRanges { Collections.sort(sorted, KeyRange.COMPARATOR); sortedRanges.add(ImmutableList.copyOf(sorted)); } - boolean useSkipScanFilter = useSkipScanFilter(forceRangeScan, isPointLookup, sortedRanges); // Don't set minMaxRange for point lookup because it causes issues during intersect // by going across region boundaries @@ -111,7 +117,7 @@ public class ScanRanges { // if (!isPointLookup && (nBuckets == null || !useSkipScanFilter)) { // if (! ( isPointLookup || (nBuckets != null && useSkipScanFilter) ) ) { // if (nBuckets == null || (nBuckets != null && (!isPointLookup || !useSkipScanFilter))) { - if (nBuckets == null || !isPointLookup || !useSkipScanFilter) { + if (nBuckets == null || !isPointLookup || !useSkipScan) { byte[] minKey = ScanUtil.getMinKey(schema, sortedRanges, slotSpan); byte[] maxKey = ScanUtil.getMaxKey(schema, sortedRanges, slotSpan); // If the maxKey has crossed the salt byte boundary, then we do not @@ -133,7 +139,7 @@ public class ScanRanges { if (scanRange == KeyRange.EMPTY_RANGE) { return NOTHING; } - return new ScanRanges(schema, slotSpan, sortedRanges, scanRange, minMaxRange, useSkipScanFilter, isPointLookup, nBuckets); + return new ScanRanges(schema, slotSpan, sortedRanges, scanRange, minMaxRange, useSkipScan, isPointLookup, nBuckets); } private SkipScanFilter filter; @@ -163,8 +169,13 @@ public class ScanRanges { this.ranges = ImmutableList.copyOf(ranges); this.slotSpan = slotSpan; this.schema = schema; - if (schema != null && !ranges.isEmpty()) { // TODO: only create if useSkipScanFilter is true? - this.filter = new SkipScanFilter(this.ranges, slotSpan, schema); + if (schema != null && !ranges.isEmpty()) { + if (!this.useSkipScanFilter) { + int boundSlotCount = this.getBoundSlotCount(); + ranges = ranges.subList(0, boundSlotCount); + slotSpan = Arrays.copyOf(slotSpan, boundSlotCount); + } + this.filter = new SkipScanFilter(ranges, slotSpan, this.schema); } } @@ -405,12 +416,16 @@ public class ScanRanges { return ranges; } + public List<List<KeyRange>> getBoundRanges() { + return ranges.subList(0, getBoundSlotCount()); + } + public RowKeySchema getSchema() { return schema; } public boolean isEverything() { - return this == EVERYTHING; + return this == EVERYTHING || ranges.get(0).get(0) == KeyRange.EVERYTHING_RANGE; } public boolean isDegenerate() { @@ -427,33 +442,48 @@ public class ScanRanges { return useSkipScanFilter; } - private static boolean useSkipScanFilter(boolean forceRangeScan, boolean isPointLookup, List<List<KeyRange>> ranges) { - if (forceRangeScan) { - return false; - } - if (isPointLookup) { - return getPointLookupCount(isPointLookup, ranges) > 1; - } - boolean hasRangeKey = false, useSkipScan = false; - for (List<KeyRange> orRanges : ranges) { - useSkipScan |= (orRanges.size() > 1 || hasRangeKey); - if (useSkipScan) { - return true; - } + /** + * Finds the total number of row keys spanned by this ranges / slotSpan pair. + * This accounts for slots in the ranges that may span more than on row key. + * @param ranges the KeyRange slots paired with this slotSpan. corresponds to {@link ScanRanges#ranges} + * @param slotSpan the extra span per skip scan slot. corresponds to {@link ScanRanges#slotSpan} + * @return the total number of row keys spanned yb this ranges / slotSpan pair. + */ + private static int getBoundPkSpan(List<List<KeyRange>> ranges, int[] slotSpan) { + int count = 0; + boolean hasUnbound = false; + int nRanges = ranges.size(); + + for(int i = 0; i < nRanges && !hasUnbound; i++) { + List<KeyRange> orRanges = ranges.get(i); for (KeyRange range : orRanges) { - hasRangeKey |= !range.isSingleKey(); + if (range == KeyRange.EVERYTHING_RANGE) { + return count; + } + if (range.isUnbound()) { + hasUnbound = true; + } } + count += slotSpan[i] + 1; } - return false; + + return count; } - private static boolean isPointLookup(RowKeySchema schema, List<List<KeyRange>> ranges, int[] slotSpan) { - if (ScanUtil.getTotalSpan(ranges, slotSpan) < schema.getMaxFields()) { + private static boolean isFullyQualified(RowKeySchema schema, List<List<KeyRange>> ranges, int[] slotSpan) { + return getBoundPkSpan(ranges, slotSpan) == schema.getMaxFields(); + } + + private static boolean isPointLookup(RowKeySchema schema, List<List<KeyRange>> ranges, int[] slotSpan, boolean useSkipScan) { + if (!isFullyQualified(schema, ranges, slotSpan)) { return false; } int lastIndex = ranges.size()-1; for (int i = lastIndex; i >= 0; i--) { List<KeyRange> orRanges = ranges.get(i); + if (!useSkipScan && orRanges.size() > 1) { + return false; + } for (KeyRange keyRange : orRanges) { // Special case for single trailing IS NULL. We cannot consider this as a point key because // we strip trailing nulls when we form the key. @@ -519,8 +549,29 @@ public class ScanRanges { return isPointLookup ? ranges.get(0).iterator() : Iterators.<KeyRange>emptyIterator(); } - public int getPkColumnSpan() { - return this == ScanRanges.NOTHING ? 0 : ScanUtil.getTotalSpan(ranges, slotSpan); + public int getBoundPkColumnCount() { + return this.useSkipScanFilter ? ScanUtil.getRowKeyPosition(slotSpan, ranges.size()) : getBoundPkSpan(ranges, slotSpan); + } + + public int getBoundSlotCount() { + int count = 0; + boolean hasUnbound = false; + int nRanges = ranges.size(); + + for(int i = 0; i < nRanges && !hasUnbound; i++) { + List<KeyRange> orRanges = ranges.get(i); + for (KeyRange range : orRanges) { + if (range == KeyRange.EVERYTHING_RANGE) { + return count; + } + if (range.isUnbound()) { + hasUnbound = true; + } + } + count++; + } + + return count; } @Override @@ -528,4 +579,27 @@ public class ScanRanges { return "ScanRanges[" + ranges.toString() + "]"; } + public int[] getSlotSpans() { + return slotSpan; + } + + public boolean hasEqualityConstraint(int pkPosition) { + if (isPointLookup) { + return true; + } + + int pkOffset = 0; + int nRanges = ranges.size(); + + for(int i = 0; i < nRanges; i++) { + if (pkOffset + slotSpan[i] >= pkPosition) { + List<KeyRange> range = ranges.get(i); + return range.size() == 1 && range.get(0).isSingleKey(); + } + pkOffset += slotSpan[i] + 1; + } + + return false; + + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java index 95cea83..2f607cc 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java @@ -157,7 +157,6 @@ public class WhereOptimizer { int pkPos = 0; int nPKColumns = table.getPKColumns().size(); int[] slotSpan = new int[nPKColumns]; - List<Expression> removeFromExtractNodes = null; List<List<KeyRange>> cnf = Lists.newArrayListWithExpectedSize(schema.getMaxFields()); KeyRange minMaxRange = keySlots.getMinMaxRange(); if (minMaxRange == null) { @@ -225,9 +224,10 @@ public class WhereOptimizer { boolean forcedRangeScan = statement.getHint().hasHint(Hint.RANGE_SCAN); boolean hasUnboundedRange = false; boolean hasMultiRanges = false; - boolean hasMultiColumnSpan = false; - boolean hasNonPointKey = false; + boolean hasRangeKey = false; boolean stopExtracting = false; + boolean useSkipScan = false; + //boolean useSkipScan = !forcedRangeScan && nBuckets != null; // Concat byte arrays of literals to form scan start key while (iterator.hasNext()) { KeyExpressionVisitor.KeySlot slot = iterator.next(); @@ -235,11 +235,14 @@ public class WhereOptimizer { // then we have to handle in the next phase through a key filter. // If the slot is null this means we have no entry for this pk position. if (slot == null || slot.getKeyRanges().isEmpty()) { - if (!forcedSkipScan || hasMultiColumnSpan) break; continue; } if (slot.getPKPosition() != pkPos) { - if (!forcedSkipScan || hasMultiColumnSpan) break; + if (!forcedSkipScan) { + stopExtracting = true; + } else { + useSkipScan |= !stopExtracting && !forcedRangeScan && forcedSkipScan; + } for (int i=pkPos; i < slot.getPKPosition(); i++) { cnf.add(Collections.singletonList(KeyRange.EVERYTHING_RANGE)); } @@ -247,29 +250,36 @@ public class WhereOptimizer { KeyPart keyPart = slot.getKeyPart(); slotSpan[cnf.size()] = slot.getPKSpan() - 1; pkPos = slot.getPKPosition() + slot.getPKSpan(); - hasMultiColumnSpan |= slot.getPKSpan() > 1; // Skip span-1 slots as we skip one at the top of the loop for (int i = 1; i < slot.getPKSpan() && iterator.hasNext(); i++) { iterator.next(); } List<KeyRange> keyRanges = slot.getKeyRanges(); - for (int i = 0; (!hasUnboundedRange || !hasNonPointKey) && i < keyRanges.size(); i++) { + cnf.add(keyRanges); + + // TODO: when stats are available, we may want to use a skip scan if the + // cardinality of this slot is low. + /* + * Stop extracting nodes once we encounter: + * 1) An unbound range unless we're forcing a skip scan and havn't encountered + * a multi-column span. Even if we're trying to force a skip scan, we can't + * execute it over a multi-column span. + * 2) A non range key as we can extract the first one, but further ones need + * to be evaluated in a filter. + */ + stopExtracting |= (hasUnboundedRange && !forcedSkipScan) || (hasRangeKey && forcedRangeScan); + useSkipScan |= !stopExtracting && !forcedRangeScan && (keyRanges.size() > 1 || hasRangeKey); + + for (int i = 0; (!hasUnboundedRange || !hasRangeKey) && i < keyRanges.size(); i++) { KeyRange range = keyRanges.get(i); if (range.isUnbound()) { - hasUnboundedRange = hasNonPointKey = true; + hasUnboundedRange = hasRangeKey = true; } else if (!range.isSingleKey()) { - hasNonPointKey = true; + hasRangeKey = true; } } hasMultiRanges |= keyRanges.size() > 1; - // Force a range scan if we've encountered a multi-span slot (i.e. RVC) - // and a non point key, as our skip scan only handles fully qualified - // RVC in our skip scan. This will force us to not extract nodes any - // longer as well. - // TODO: consider ending loop here if true. - forcedRangeScan |= (hasMultiColumnSpan && hasNonPointKey); - cnf.add(keyRanges); // We cannot extract if we have multiple ranges and are forcing a range scan. stopExtracting |= forcedRangeScan && hasMultiRanges; @@ -282,36 +292,14 @@ public class WhereOptimizer { // that, so must filter on the remaining conditions (see issue #467). if (!stopExtracting) { List<Expression> nodesToExtract = keyPart.getExtractNodes(); - // Detect case of a RVC used in a range. We do not want to - // remove these from the extract nodes. - if (hasMultiColumnSpan && !hasUnboundedRange) { - if (removeFromExtractNodes == null) { - removeFromExtractNodes = Lists.newArrayListWithExpectedSize(nodesToExtract.size() + table.getPKColumns().size() - pkPos); - } - removeFromExtractNodes.addAll(nodesToExtract); - } extractNodes.addAll(nodesToExtract); } - /* - * Stop building start/stop key once we encounter An unbound range unless we're - * forcing a skip scan and havn't encountered a multi-column span. Even if we're - * trying to force a skip scan, we can't execute it over a multi-column span. - */ - if (hasUnboundedRange && (!forcedSkipScan || hasMultiColumnSpan)) { - // TODO: when stats are available, we may want to continue this loop if the - // cardinality of this slot is low. We could potentially even continue this - // loop in the absence of a range for a key slot. - break; - } - // Set stopExtracting if we're forcing a range scan and have anything other than - // an equality constraint. We can extract the first one, but no future ones. - stopExtracting |= forcedRangeScan && hasNonPointKey; } // If we have fully qualified point keys with multi-column spans (i.e. RVC), // we can still use our skip scan. The ScanRanges.create() call will explode // out the keys. slotSpan = Arrays.copyOf(slotSpan, cnf.size()); - ScanRanges scanRanges = ScanRanges.create(schema, cnf, slotSpan, minMaxRange, forcedRangeScan, nBuckets); + ScanRanges scanRanges = ScanRanges.create(schema, cnf, slotSpan, minMaxRange, nBuckets, useSkipScan); context.setScanRanges(scanRanges); if (whereClause == null) { return null; @@ -332,8 +320,9 @@ public class WhereOptimizer { public static boolean getKeyExpressionCombination(List<Expression> result, StatementContext context, FilterableStatement statement, List<Expression> expressions) throws SQLException { List<Integer> candidateIndexes = Lists.newArrayList(); final List<Integer> pkPositions = Lists.newArrayList(); + PTable table = context.getCurrentTable().getTable(); for (int i = 0; i < expressions.size(); i++) { - KeyExpressionVisitor visitor = new KeyExpressionVisitor(context, context.getCurrentTable().getTable()); + KeyExpressionVisitor visitor = new KeyExpressionVisitor(context, table); KeyExpressionVisitor.KeySlots keySlots = expressions.get(i).accept(visitor); int minPkPos = Integer.MAX_VALUE; if (keySlots != null) { @@ -376,6 +365,7 @@ public class WhereOptimizer { } int count = 0; + int offset = table.getBucketNum() == null ? 0 : SaltingUtil.NUM_SALTING_BYTES; int maxPkSpan = 0; Expression remaining = null; while (count < candidates.size()) { @@ -388,7 +378,7 @@ public class WhereOptimizer { count++; break; // found the best match } - int pkSpan = context.getScanRanges().getPkColumnSpan(); + int pkSpan = context.getScanRanges().getBoundPkColumnCount() - offset; if (pkSpan <= maxPkSpan) { break; } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java index 13788e1..001615b 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/MetaDataEndpointImpl.java @@ -182,15 +182,12 @@ import org.apache.phoenix.util.IndexUtil; import org.apache.phoenix.util.KeyValueUtil; import org.apache.phoenix.util.MetaDataUtil; import org.apache.phoenix.util.QueryUtil; -import org.apache.phoenix.util.ScanUtil; import org.apache.phoenix.util.SchemaUtil; import org.apache.phoenix.util.ServerUtil; import org.apache.phoenix.util.UpgradeUtil; import org.slf4j.Logger; import org.slf4j.LoggerFactory; -import com.google.common.base.Objects; -import com.google.common.base.Preconditions; import com.google.common.cache.Cache; import com.google.common.collect.Lists; import com.google.protobuf.ByteString; @@ -498,9 +495,7 @@ public class MetaDataEndpointImpl extends MetaDataProtocol implements Coprocesso } Scan scan = new Scan(); scan.setTimeRange(MIN_TABLE_TIMESTAMP, clientTimeStamp); - ScanRanges scanRanges = - ScanRanges.create(SchemaUtil.VAR_BINARY_SCHEMA, - Collections.singletonList(keyRanges), ScanUtil.SINGLE_COLUMN_SLOT_SPAN); + ScanRanges scanRanges = ScanRanges.createPointLookup(keyRanges); scanRanges.initializeScan(scan); scan.setFilter(scanRanges.getSkipScanFilter()); http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/filter/SkipScanFilter.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/filter/SkipScanFilter.java b/phoenix-core/src/main/java/org/apache/phoenix/filter/SkipScanFilter.java index ff58a18..d1c8532 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/filter/SkipScanFilter.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/filter/SkipScanFilter.java @@ -42,6 +42,8 @@ import org.apache.phoenix.util.ByteUtil; import org.apache.phoenix.util.ScanUtil; import org.apache.phoenix.util.ScanUtil.BytesComparator; import org.apache.phoenix.util.SchemaUtil; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; import com.google.common.base.Objects; import com.google.common.collect.Lists; @@ -60,6 +62,8 @@ import com.google.common.hash.Hashing; * @since 0.1 */ public class SkipScanFilter extends FilterBase implements Writable { + private static final Logger logger = LoggerFactory.getLogger(SkipScanFilter.class); + private enum Terminate {AT, AFTER}; // Conjunctive normal form of or-ed ranges or point lookups private List<List<KeyRange>> slots; @@ -151,11 +155,15 @@ public class SkipScanFilter extends FilterBase implements Writable { } Cell previousCellHint = nextCellHintMap.put(family, nextCellHint); // we should either have no previous hint, or the next hint should always come after the previous hint - assert previousCellHint == null + boolean isHintAfterPrevious = previousCellHint == null || Bytes.compareTo(nextCellHint.getRowArray(), nextCellHint.getRowOffset(), - nextCellHint.getRowLength(), previousCellHint.getRowArray(), previousCellHint - .getRowOffset(), previousCellHint.getRowLength()) > 0 : "next hint must come after previous hint (prev=" - + previousCellHint + ", next=" + nextCellHint + ", kv=" + kv + ")"; + nextCellHint.getRowLength(), previousCellHint.getRowArray(), previousCellHint + .getRowOffset(), previousCellHint.getRowLength()) > 0; + if (!isHintAfterPrevious) { + String msg = "The next hint must come after previous hint (prev=" + previousCellHint + ", next=" + nextCellHint + ", kv=" + kv + ")"; + assert isHintAfterPrevious : msg; + logger.warn(msg); + } } @Override @@ -431,7 +439,7 @@ public class SkipScanFilter extends FilterBase implements Writable { setStartKey(); schema.reposition(ptr, ScanUtil.getRowKeyPosition(slotSpan, i), ScanUtil.getRowKeyPosition(slotSpan, j), minOffset, maxOffset, slotSpan[j]); } else { - int currentLength = setStartKey(ptr, minOffset, j+1); + int currentLength = setStartKey(ptr, minOffset, j+1, nSlots); // From here on, we use startKey as our buffer (resetting minOffset and maxOffset) // We've copied the part of the current key above that we need into startKey // Reinitialize the iterator to be positioned at previous slot position @@ -446,7 +454,7 @@ public class SkipScanFilter extends FilterBase implements Writable { } else if (slots.get(i).get(position[i]).compareLowerToUpperBound(ptr, comparator) > 0) { // Our current key is less than the lower range of the current position in the current slot. // Seek to the lower range, since it's bigger than the current key - setStartKey(ptr, minOffset, i); + setStartKey(ptr, minOffset, i, nSlots); return ReturnCode.SEEK_NEXT_USING_HINT; } else { // We're in range, check the next slot if (!slots.get(i).get(position[i]).isSingleKey() && i < earliestRangeIndex) { @@ -469,7 +477,7 @@ public class SkipScanFilter extends FilterBase implements Writable { break; } // Otherwise we seek to the next start key because we're before it now - setStartKey(ptr, minOffset, i); + setStartKey(ptr, minOffset, i, nSlots); return ReturnCode.SEEK_NEXT_USING_HINT; } } @@ -513,12 +521,12 @@ public class SkipScanFilter extends FilterBase implements Writable { startKeyLength = setKey(Bound.LOWER, startKey, 0, 0); } - private int setStartKey(ImmutableBytesWritable ptr, int offset, int i) { + private int setStartKey(ImmutableBytesWritable ptr, int offset, int i, int nSlots) { int length = ptr.getOffset() - offset; startKey = copyKey(startKey, length + this.maxKeyLength, ptr.get(), offset, length); startKeyLength = length; - // Add separator byte if we're at the end of the buffer, since trailing separator bytes are stripped - if (ptr.getOffset() + ptr.getLength() == offset + length && i-1 > 0 && !schema.getField(i-1).getDataType().isFixedWidth()) { + // Add separator byte if we're at end of the key, since trailing separator bytes are stripped + if (ptr.getLength() == 0 && i > 0 && i-1 < nSlots && !schema.getField(i-1).getDataType().isFixedWidth()) { startKey[startKeyLength++] = SchemaUtil.getSeparatorByte(schema.rowKeyOrderOptimizable(), ptr.getLength()==0, schema.getField(i-1)); } startKeyLength += setKey(Bound.LOWER, startKey, startKeyLength, i); http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/index/PhoenixIndexBuilder.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/index/PhoenixIndexBuilder.java b/phoenix-core/src/main/java/org/apache/phoenix/index/PhoenixIndexBuilder.java index 7a45e21..3ef01fe 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/index/PhoenixIndexBuilder.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/index/PhoenixIndexBuilder.java @@ -19,7 +19,6 @@ package org.apache.phoenix.index; import java.io.IOException; import java.util.ArrayList; -import java.util.Collections; import java.util.HashMap; import java.util.List; import java.util.Map; @@ -36,8 +35,6 @@ import org.apache.phoenix.hbase.index.covered.CoveredColumnsIndexBuilder; import org.apache.phoenix.hbase.index.util.IndexManagementUtil; import org.apache.phoenix.query.KeyRange; import org.apache.phoenix.schema.types.PVarbinary; -import org.apache.phoenix.util.ScanUtil; -import org.apache.phoenix.util.SchemaUtil; import com.google.common.collect.Lists; @@ -70,7 +67,7 @@ public class PhoenixIndexBuilder extends CoveredColumnsIndexBuilder { } if (maintainers.isEmpty()) return; Scan scan = IndexManagementUtil.newLocalStateScan(new ArrayList<IndexMaintainer>(maintainers.values())); - ScanRanges scanRanges = ScanRanges.create(SchemaUtil.VAR_BINARY_SCHEMA, Collections.singletonList(keys), ScanUtil.SINGLE_COLUMN_SLOT_SPAN); + ScanRanges scanRanges = ScanRanges.createPointLookup(keys); scanRanges.initializeScan(scan); scan.setFilter(scanRanges.getSkipScanFilter()); Region region = this.env.getRegion(); http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java b/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java index cf19a6c..da81dd2 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/iterate/ExplainTable.java @@ -82,7 +82,9 @@ public abstract class ExplainTable { buf.append("SKIP SCAN "); int count = 1; boolean hasRanges = false; - for (List<KeyRange> ranges : scanRanges.getRanges()) { + int nSlots = scanRanges.getBoundSlotCount(); + for (int i = 0; i < nSlots; i++) { + List<KeyRange> ranges = scanRanges.getRanges().get(i); count *= ranges.size(); for (KeyRange range : ranges) { hasRanges |= !range.isSingleKey(); @@ -242,7 +244,8 @@ public abstract class ExplainTable { minMaxIterator = new RowKeyValueIterator(schema, minMaxRange.getRange(bound)); } } - int nRanges = scanRanges.getRanges().size(); + boolean forceSkipScan = this.hint.hasHint(Hint.SKIP_SCAN); + int nRanges = forceSkipScan ? scanRanges.getRanges().size() : scanRanges.getBoundSlotCount(); for (int i = 0, minPos = 0; minPos < nRanges || minMaxIterator.hasNext(); i++) { List<KeyRange> ranges = minPos >= nRanges ? EVERYTHING : scanRanges.getRanges().get(minPos++); KeyRange range = bound == Bound.LOWER ? ranges.get(0) : ranges.get(ranges.size()-1); http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java index 99ca46e..bc318d2 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java @@ -40,9 +40,9 @@ import org.apache.phoenix.parse.AliasedNode; import org.apache.phoenix.parse.AndParseNode; import org.apache.phoenix.parse.BooleanParseNodeVisitor; import org.apache.phoenix.parse.ColumnParseNode; -import org.apache.phoenix.parse.IndexExpressionParseNodeRewriter; import org.apache.phoenix.parse.HintNode; import org.apache.phoenix.parse.HintNode.Hint; +import org.apache.phoenix.parse.IndexExpressionParseNodeRewriter; import org.apache.phoenix.parse.ParseNode; import org.apache.phoenix.parse.ParseNodeFactory; import org.apache.phoenix.parse.ParseNodeRewriter; @@ -384,8 +384,8 @@ public class QueryOptimizer { // For shared indexes (i.e. indexes on views and local indexes), // a) add back any view constants as these won't be in the index, and // b) ignore the viewIndexId which will be part of the row key columns. - int c = (plan2.getContext().getScanRanges().getRanges().size() + (table2.getViewIndexId() == null ? 0 : (boundRanges - 1))) - - (plan1.getContext().getScanRanges().getRanges().size() + (table1.getViewIndexId() == null ? 0 : (boundRanges - 1))); + int c = (plan2.getContext().getScanRanges().getBoundPkColumnCount() + (table2.getViewIndexId() == null ? 0 : (boundRanges - 1))) - + (plan1.getContext().getScanRanges().getBoundPkColumnCount() + (table1.getViewIndexId() == null ? 0 : (boundRanges - 1))); if (c != 0) return c; if (plan1.getGroupBy()!=null && plan2.getGroupBy()!=null) { if (plan1.getGroupBy().isOrderPreserving() != plan2.getGroupBy().isOrderPreserving()) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java index 2a73e25..7b76a2b 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java @@ -25,7 +25,6 @@ import java.io.IOException; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; -import java.util.Collections; import java.util.Iterator; import java.util.List; import java.util.Map; @@ -48,7 +47,8 @@ import org.apache.phoenix.coprocessor.BaseScannerRegionObserver; import org.apache.phoenix.coprocessor.MetaDataProtocol; import org.apache.phoenix.exception.SQLExceptionCode; import org.apache.phoenix.exception.SQLExceptionInfo; -import org.apache.phoenix.execute.DescVarLengthFastByteComparisons;import org.apache.phoenix.filter.BooleanExpressionFilter; +import org.apache.phoenix.execute.DescVarLengthFastByteComparisons; +import org.apache.phoenix.filter.BooleanExpressionFilter; import org.apache.phoenix.filter.SkipScanFilter; import org.apache.phoenix.query.KeyRange; import org.apache.phoenix.query.KeyRange.Bound; @@ -57,7 +57,6 @@ import org.apache.phoenix.query.QueryServices; import org.apache.phoenix.query.QueryServicesOptions; import org.apache.phoenix.schema.IllegalDataException; import org.apache.phoenix.schema.PName; -import org.apache.phoenix.schema.PNameFactory; import org.apache.phoenix.schema.RowKeySchema; import org.apache.phoenix.schema.SortOrder; import org.apache.phoenix.schema.ValueSchema.Field; @@ -335,6 +334,7 @@ public class ScanUtil { int offset = byteOffset; boolean lastInclusiveUpperSingleKey = false; boolean anyInclusiveUpperRangeKey = false; + boolean lastUnboundUpper = false; // The index used for slots should be incremented by 1, // but the index for the field it represents in the schema // should be incremented by 1 + value in the current slotSpan index @@ -348,7 +348,6 @@ public class ScanUtil { // Use last slot in a multi-span column to determine if fixed width field = schema.getField(fieldIndex + slotSpan[i]); boolean isFixedWidth = field.getDataType().isFixedWidth(); - fieldIndex += slotSpan[i] + 1; /* * If the current slot is unbound then stop if: * 1) setting the upper bound. There's no value in @@ -357,21 +356,27 @@ public class ScanUtil { * for the same reason. However, if the type is variable width * continue building the key because null values will be filtered * since our separator byte will be appended and incremented. + * 3) if the range includes everything as we cannot add any more useful + * information to the key after that. */ + lastUnboundUpper = false; if ( range.isUnbound(bound) && - ( bound == Bound.UPPER || isFixedWidth) ){ + ( bound == Bound.UPPER || isFixedWidth || range == KeyRange.EVERYTHING_RANGE) ){ + lastUnboundUpper = (bound == Bound.UPPER); break; } byte[] bytes = range.getRange(bound); System.arraycopy(bytes, 0, key, offset, bytes.length); offset += bytes.length; + /* * We must add a terminator to a variable length key even for the last PK column if * the lower key is non inclusive or the upper key is inclusive. Otherwise, we'd be * incrementing the key value itself, and thus bumping it up too much. */ - boolean inclusiveUpper = range.isInclusive(bound) && bound == Bound.UPPER; - boolean exclusiveLower = !range.isInclusive(bound) && bound == Bound.LOWER; + boolean inclusiveUpper = range.isUpperInclusive() && bound == Bound.UPPER; + boolean exclusiveLower = !range.isLowerInclusive() && bound == Bound.LOWER; + boolean exclusiveUpper = !range.isUpperInclusive() && bound == Bound.UPPER; // If we are setting the upper bound of using inclusive single key, we remember // to increment the key if we exit the loop after this iteration. // @@ -387,12 +392,20 @@ public class ScanUtil { // A null or empty byte array is always represented as a zero byte byte sepByte = SchemaUtil.getSeparatorByte(schema.rowKeyOrderOptimizable(), bytes.length == 0, field); - if (!isFixedWidth && ( fieldIndex < schema.getMaxFields() || inclusiveUpper || exclusiveLower || sepByte == QueryConstants.DESC_SEPARATOR_BYTE)) { + if ( !isFixedWidth && ( sepByte == QueryConstants.DESC_SEPARATOR_BYTE + || ( !exclusiveUpper + && (fieldIndex < schema.getMaxFields() || inclusiveUpper || exclusiveLower) ) ) ) { key[offset++] = sepByte; // Set lastInclusiveUpperSingleKey back to false if this is the last pk column // as we don't want to increment the null byte in this case lastInclusiveUpperSingleKey &= i < schema.getMaxFields()-1; } + if (exclusiveUpper) { + // Cannot include anything else on the key, as otherwise + // keys that match the upper range will be included. For example WHERE k1 < 2 and k2 = 3 + // would match k1 = 2, k2 = 3 which is wrong. + break; + } // If we are setting the lower bound with an exclusive range key, we need to bump the // slot up for each key part. For an upper bound, we bump up an inclusive key, but // only after the last key part. @@ -414,8 +427,10 @@ public class ScanUtil { key[offset++] = QueryConstants.DESC_SEPARATOR_BYTE; } } + + fieldIndex += slotSpan[i] + 1; } - if (lastInclusiveUpperSingleKey || anyInclusiveUpperRangeKey) { + if (lastInclusiveUpperSingleKey || anyInclusiveUpperRangeKey || lastUnboundUpper) { if (!ByteUtil.nextKey(key, offset)) { // Special case for not being able to increment. // In this case we return a negative byteOffset to @@ -501,7 +516,7 @@ public class ScanUtil { for (Mutation m : mutations) { keys.add(PVarbinary.INSTANCE.getKeyRange(m.getRow())); } - ScanRanges keyRanges = ScanRanges.create(SchemaUtil.VAR_BINARY_SCHEMA, Collections.singletonList(keys), ScanUtil.SINGLE_COLUMN_SLOT_SPAN); + ScanRanges keyRanges = ScanRanges.createPointLookup(keys); return keyRanges; } @@ -627,19 +642,6 @@ public class ScanUtil { } /** - * Finds the total number of row keys spanned by this ranges / slotSpan pair. - * This accounts for slots in the ranges that may span more than on row key. - * @param ranges the KeyRange slots paired with this slotSpan. corresponds to {@link ScanRanges#ranges} - * @param slotSpan the extra span per skip scan slot. corresponds to {@link ScanRanges#slotSpan} - * @return the total number of row keys spanned yb this ranges / slotSpan pair. - * @see #getRowKeyPosition(int[], int) - */ - public static int getTotalSpan(List<List<KeyRange>> ranges, int[] slotSpan) { - // finds the position at the "end" of the ranges, which is also the total span - return getRowKeyPosition(slotSpan, ranges.size()); - } - - /** * Finds the position in the row key schema for a given position in the scan slots. * For example, with a slotSpan of {0, 1, 0}, the slot at index 1 spans an extra column in the row key. This means * that the slot at index 2 has a slot index of 2 but a row key index of 3. @@ -648,7 +650,6 @@ public class ScanUtil { * @param slotSpan the extra span per skip scan slot. corresponds to {@link ScanRanges#slotSpan} * @param slotPosition the index of a slot in the SkipScan slots list. * @return the equivalent row key position in the RowKeySchema - * @see #getTotalSpan(java.util.List, int[]) */ public static int getRowKeyPosition(int[] slotSpan, int slotPosition) { int offset = 0; http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java index 6225c6b..076b49e 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java @@ -469,7 +469,7 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { List<Object> binds = Collections.emptyList(); for (String query : queries) { QueryPlan plan = getQueryPlan(query, binds); - assertEquals(plan.getGroupBy().getScanAttribName(), BaseScannerRegionObserver.KEY_ORDERED_GROUP_BY_EXPRESSIONS); + assertEquals(query, BaseScannerRegionObserver.KEY_ORDERED_GROUP_BY_EXPRESSIONS, plan.getGroupBy().getScanAttribName()); } } @@ -864,15 +864,15 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { String query = "SELECT host FROM ptsdb WHERE regexp_substr(inst, '[a-zA-Z]+') = 'abc'"; List<Object> binds = Collections.emptyList(); Scan scan = compileQuery(query, binds); - assertArrayEquals(ByteUtil.concat(Bytes.toBytes("abc")), scan.getStartRow()); - assertArrayEquals(ByteUtil.concat(ByteUtil.nextKey(Bytes.toBytes("abc")), QueryConstants.SEPARATOR_BYTE_ARRAY),scan.getStopRow()); + assertArrayEquals(Bytes.toBytes("abc"), scan.getStartRow()); + assertArrayEquals(ByteUtil.nextKey(Bytes.toBytes("abc")),scan.getStopRow()); assertTrue(scan.getFilter() != null); query = "SELECT host FROM ptsdb WHERE regexp_substr(inst, '[a-zA-Z]+', 0) = 'abc'"; binds = Collections.emptyList(); scan = compileQuery(query, binds); - assertArrayEquals(ByteUtil.concat(Bytes.toBytes("abc")), scan.getStartRow()); - assertArrayEquals(ByteUtil.concat(ByteUtil.nextKey(Bytes.toBytes("abc")),QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow()); + assertArrayEquals(Bytes.toBytes("abc"), scan.getStartRow()); + assertArrayEquals(ByteUtil.nextKey(Bytes.toBytes("abc")), scan.getStopRow()); assertTrue(scan.getFilter() != null); // Test scan keys are not set when the offset is not 0 or 1. @@ -980,7 +980,7 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { List<Object> binds = Collections.emptyList(); Scan scan = compileQuery(query, binds); assertArrayEquals(Bytes.toBytes("abc"), scan.getStartRow()); - assertArrayEquals(ByteUtil.concat(ByteUtil.nextKey(Bytes.toBytes("abc")), QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow()); + assertArrayEquals(ByteUtil.nextKey(Bytes.toBytes("abc")), scan.getStopRow()); assertTrue(scan.getFilter() == null); // Extracted. } @@ -989,8 +989,8 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { String query = "SELECT inst FROM ptsdb WHERE rtrim(inst) = 'abc'"; List<Object> binds = Collections.emptyList(); Scan scan = compileQuery(query, binds); - assertArrayEquals(ByteUtil.concat(Bytes.toBytes("abc")), scan.getStartRow()); - assertArrayEquals(ByteUtil.concat(ByteUtil.nextKey(Bytes.toBytes("abc ")), QueryConstants.SEPARATOR_BYTE_ARRAY), scan.getStopRow()); + assertArrayEquals(Bytes.toBytes("abc"), scan.getStartRow()); + assertArrayEquals(ByteUtil.nextKey(Bytes.toBytes("abc ")), scan.getStopRow()); assertNotNull(scan.getFilter()); } @@ -1732,6 +1732,7 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 date not null, k3 varchar, v varchar, constraint pk primary key(k1,k2,k3))"); String[] queries = { + "SELECT * FROM T WHERE k2=CURRENT_DATE() ORDER BY k1, k3", "SELECT * FROM T ORDER BY (k1,k2), k3", "SELECT * FROM T ORDER BY k1,k2,k3 NULLS FIRST", "SELECT * FROM T ORDER BY k1,k2,k3", @@ -1741,6 +1742,7 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest { "SELECT * FROM T ORDER BY (k1,k2,k3)", "SELECT * FROM T ORDER BY TRUNC(k1, 'DAY'), CEIL(k2, 'HOUR')", "SELECT * FROM T ORDER BY INVERT(k1) DESC", + "SELECT * FROM T WHERE k1=CURRENT_DATE() ORDER BY k2", }; String query; for (int i = 0; i < queries.length; i++) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesIntersectTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesIntersectTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesIntersectTest.java index e5a9878..c2bbc06 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesIntersectTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesIntersectTest.java @@ -29,13 +29,7 @@ import java.util.List; import org.apache.hadoop.hbase.client.Scan; import org.apache.phoenix.filter.SkipScanFilter; import org.apache.phoenix.query.KeyRange; -import org.apache.phoenix.schema.types.PDataType; -import org.apache.phoenix.schema.PDatum; import org.apache.phoenix.schema.types.PVarchar; -import org.apache.phoenix.schema.RowKeySchema; -import org.apache.phoenix.schema.RowKeySchema.RowKeySchemaBuilder; -import org.apache.phoenix.schema.SortOrder; -import org.apache.phoenix.util.ScanUtil; import org.junit.Test; import com.google.common.collect.Lists; @@ -44,10 +38,8 @@ public class ScanRangesIntersectTest { @Test public void testPointLookupIntersect() throws Exception { - RowKeySchema schema = schema(); - int[] slotSpan = ScanUtil.SINGLE_COLUMN_SLOT_SPAN; List<KeyRange> keys = points("a","j","m","z"); - ScanRanges ranges = ScanRanges.create(schema, Collections.singletonList(keys), slotSpan); + ScanRanges ranges = ScanRanges.createPointLookup(keys); assertIntersect(ranges, "b", "l", "j"); } @@ -76,31 +68,4 @@ public class ScanRangesIntersectTest { } return keys; } - - private static RowKeySchema schema() { - RowKeySchemaBuilder builder = new RowKeySchemaBuilder(1); - builder.addField(new PDatum() { - @Override - public boolean isNullable() { - return false; - } - @Override - public PDataType getDataType() { - return PVarchar.INSTANCE; - } - @Override - public Integer getMaxLength() { - return null; - } - @Override - public Integer getScale() { - return null; - } - @Override - public SortOrder getSortOrder() { - return SortOrder.getDefault(); - } - }, false, SortOrder.getDefault()); - return builder.build(); - } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesTest.java index b5d20ab..0292244 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/ScanRangesTest.java @@ -31,7 +31,6 @@ import org.apache.phoenix.schema.types.PVarchar; import org.apache.phoenix.schema.RowKeySchema.RowKeySchemaBuilder; import org.apache.phoenix.schema.SortOrder; import org.apache.phoenix.util.ByteUtil; -import org.apache.phoenix.util.ScanUtil; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.Parameterized; @@ -525,7 +524,7 @@ public class ScanRangesTest { }, false, SortOrder.getDefault()); } } - ScanRanges scanRanges = ScanRanges.create(builder.build(), slots, ScanUtil.getDefaultSlotSpans(slots.size())); + ScanRanges scanRanges = ScanRanges.createSingleSpan(builder.build(), slots); return foreach(scanRanges, widths, keyRange, expectedResult); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java index 6d735f9..63e513b 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/TenantSpecificViewIndexCompileTest.java @@ -21,10 +21,15 @@ import static org.junit.Assert.assertEquals; import java.sql.Connection; import java.sql.DriverManager; +import java.sql.PreparedStatement; import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Calendar; import java.util.Properties; +import java.util.TimeZone; import org.apache.phoenix.query.BaseConnectionlessQueryTest; +import org.apache.phoenix.util.DateUtil; import org.apache.phoenix.util.PhoenixRuntime; import org.apache.phoenix.util.QueryUtil; import org.junit.Test; @@ -50,6 +55,132 @@ public class TenantSpecificViewIndexCompileTest extends BaseConnectionlessQueryT } @Test + public void testOrderByOptimizedOutWithoutPredicateInView() throws Exception { + + Connection conn = DriverManager.getConnection(getUrl()); + conn.createStatement().execute("CREATE TABLE t(t_id CHAR(15) NOT NULL, k1 CHAR(3) NOT NULL, k2 CHAR(15) NOT NULL, k3 DATE NOT NULL, v1 VARCHAR," + + " CONSTRAINT pk PRIMARY KEY(t_id, k1, k2, k3)) multi_tenant=true"); + conn.createStatement().execute("CREATE VIEW v1 AS SELECT * FROM t"); + + conn = createTenantSpecificConnection(); + + // Query without predicate ordered by full row key + String sql = "SELECT * FROM v1 ORDER BY k1, k2, k3"; + String expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + // Predicate with valid partial PK + sql = "SELECT * FROM v1 WHERE k1 = 'xyz' ORDER BY k1, k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + sql = "SELECT * FROM v1 WHERE k1 > 'xyz' ORDER BY k1, k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xy{'] - ['tenant123456789',*]"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + String datePredicate = createStaticDate(); + sql = "SELECT * FROM v1 WHERE k1 = 'xyz' AND k2 = '123456789012345' AND k3 < TO_DATE('" + datePredicate + "') ORDER BY k1, k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','123456789012345',*] - ['tenant123456789','xyz','123456789012345','2015-01-01 08:00:00.000']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + + // Predicate without valid partial PK + sql = "SELECT * FROM v1 WHERE k2 < 'abcde1234567890' ORDER BY k1, k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789']\n" + + " SERVER FILTER BY K2 < 'abcde1234567890'"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + } + + @Test + public void testOrderByOptimizedOutWithPredicateInView() throws Exception { + // Arrange + Connection conn = DriverManager.getConnection(getUrl()); + conn.createStatement().execute("CREATE TABLE t(t_id CHAR(15) NOT NULL, k1 CHAR(3) NOT NULL, k2 CHAR(15) NOT NULL, k3 DATE NOT NULL, v1 VARCHAR," + + " CONSTRAINT pk PRIMARY KEY(t_id, k1, k2, k3)) multi_tenant=true"); + conn.createStatement().execute("CREATE VIEW v1 AS SELECT * FROM t WHERE k1 = 'xyz'"); + conn = createTenantSpecificConnection(); + + // Query without predicate ordered by full row key + String sql = "SELECT * FROM v1 ORDER BY k2, k3"; + String expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + // Query without predicate ordered by full row key, but without column view predicate + sql = "SELECT * FROM v1 ORDER BY k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + // Predicate with valid partial PK + sql = "SELECT * FROM v1 WHERE k1 = 'xyz' ORDER BY k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + sql = "SELECT * FROM v1 WHERE k2 < 'abcde1234567890' ORDER BY k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz',*] - ['tenant123456789','xyz','abcde1234567890']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + // Predicate with full PK + String datePredicate = createStaticDate(); + sql = "SELECT * FROM v1 WHERE k2 = '123456789012345' AND k3 < TO_DATE('" + datePredicate + "') ORDER BY k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','123456789012345',*] - ['tenant123456789','xyz','123456789012345','2015-01-01 08:00:00.000']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + + // Predicate with valid partial PK + sql = "SELECT * FROM v1 WHERE k3 < TO_DATE('" + datePredicate + "') ORDER BY k2, k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz']\n" + + " SERVER FILTER BY K3 < DATE '" + datePredicate + "'"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + } + + @Test + public void testOrderByOptimizedOutWithMultiplePredicatesInView() throws Exception { + // Arrange + Connection conn = DriverManager.getConnection(getUrl()); + conn.createStatement().execute("CREATE TABLE t(t_id CHAR(15) NOT NULL, k1 CHAR(3) NOT NULL, k2 CHAR(5) NOT NULL, k3 DATE NOT NULL, v1 VARCHAR," + + " CONSTRAINT pk PRIMARY KEY(t_id, k1, k2, k3 DESC)) multi_tenant=true"); + conn.createStatement().execute("CREATE VIEW v1 AS SELECT * FROM t WHERE k1 = 'xyz' AND k2='abcde'"); + conn = createTenantSpecificConnection(); + + // Query without predicate ordered by full row key + String sql = "SELECT * FROM v1 ORDER BY k3 DESC"; + String expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','abcde']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + // Query without predicate ordered by full row key, but without column view predicate + sql = "SELECT * FROM v1 ORDER BY k3 DESC"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','abcde']"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + // Query with predicate ordered by full row key + sql = "SELECT * FROM v1 WHERE k3 < TO_DATE('" + createStaticDate() + "') ORDER BY k3 DESC"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER T ['tenant123456789','xyz','abcde',~'2015-01-01 07:59:59.999'] - ['tenant123456789','xyz','abcde',*]"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + // Query with predicate ordered by full row key with date in reverse order + sql = "SELECT * FROM v1 WHERE k3 < TO_DATE('" + createStaticDate() + "') ORDER BY k3"; + expectedExplainOutput = "CLIENT PARALLEL 1-WAY REVERSE RANGE SCAN OVER T ['tenant123456789','xyz','abcde',~'2015-01-01 07:59:59.999'] - ['tenant123456789','xyz','abcde',*]"; + assertExplainPlanIsCorrect(conn, sql, expectedExplainOutput); + assertOrderByHasBeenOptimizedOut(conn, sql); + + } + + + @Test public void testViewConstantsOptimizedOut() throws Exception { Properties props = new Properties(); Connection conn = DriverManager.getConnection(getUrl()); @@ -99,4 +230,45 @@ public class TenantSpecificViewIndexCompileTest extends BaseConnectionlessQueryT assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_T ['me',-32767,'a'] - ['me',-32767,*]", QueryUtil.getExplainPlan(rs)); } + + //----------------------------------------------------------------- + // Private Helper Methods + //----------------------------------------------------------------- + private Connection createTenantSpecificConnection() throws SQLException { + Connection conn; + Properties props = new Properties(); + String tenantId = "tenant123456789"; + props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId); // connection is tenant-specific + conn = DriverManager.getConnection(getUrl(), props); + return conn; + } + + + private void assertExplainPlanIsCorrect(Connection conn, String sql, + String expectedExplainOutput) throws SQLException { + ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql); + assertEquals(expectedExplainOutput, QueryUtil.getExplainPlan(rs)); + } + + private void assertOrderByHasBeenOptimizedOut(Connection conn, String sql) throws SQLException { + PreparedStatement stmt = conn.prepareStatement(sql); + QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); + assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); + } + + /** + * Returns the default String representation of 1/1/2015 00:00:00 + */ + private String createStaticDate() { + Calendar cal = Calendar.getInstance(); + cal.set(Calendar.DAY_OF_YEAR, 1); + cal.set(Calendar.YEAR, 2015); + cal.set(Calendar.HOUR_OF_DAY, 0); + cal.set(Calendar.MINUTE, 0); + cal.set(Calendar.SECOND, 0); + cal.set(Calendar.MILLISECOND, 0); + cal.setTimeZone(TimeZone.getTimeZone("America/Los_Angeles")); + return DateUtil.DEFAULT_DATE_FORMATTER.format(cal.getTime()); + } + } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/compile/ViewCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/ViewCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/ViewCompilerTest.java index 7a0bac6..1d95058 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/ViewCompilerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/ViewCompilerTest.java @@ -85,7 +85,6 @@ public class ViewCompilerTest extends BaseConnectionlessQueryTest { conn.createStatement().execute("CREATE VIEW s2.v3 AS SELECT * FROM s1.t WHERE v = 'bar'"); // TODO: should it be an error to remove columns from a VIEW that we're defined there? - // TOOD: should we require an ALTER VIEW instead of ALTER TABLE? conn.createStatement().execute("ALTER VIEW s2.v3 DROP COLUMN v"); try { conn.createStatement().executeQuery("SELECT * FROM s2.v3"); http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java index 2413013..c5c4125 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java @@ -388,7 +388,7 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { assertNull(scan.getFilter()); byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, - PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY); + PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/); assertArrayEquals(startRow, scan.getStartRow()); byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY, @@ -408,7 +408,7 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { assertNull(scan.getFilter()); byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, - PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY); + PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/); assertArrayEquals(startRow, scan.getStartRow()); byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY, @@ -428,7 +428,7 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { assertNull(scan.getFilter()); byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, - PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY); + PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/); assertArrayEquals(startRow, scan.getStartRow()); byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY, @@ -448,7 +448,7 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { assertNull(scan.getFilter()); byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, - PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY); + PVarchar.INSTANCE.toBytes(host)/*,QueryConstants.SEPARATOR_BYTE_ARRAY*/); assertArrayEquals(startRow, scan.getStartRow()); byte[] stopRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes(inst),QueryConstants.SEPARATOR_BYTE_ARRAY, PVarchar.INSTANCE.toBytes(host),QueryConstants.SEPARATOR_BYTE_ARRAY, @@ -486,8 +486,7 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { // loop during skip scan. We could end up having a first slot just under the upper // limit of slot one and a value equal to the value in slot two and we need this to // be less than the upper range that would get formed. - byte[] stopRow = ByteUtil.concat(StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId.substring(0,3))),15),ByteUtil.nextKey( - PVarchar.INSTANCE.toBytes(entityId))); + byte[] stopRow = ByteUtil.concat(StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId.substring(0,3))),15)); assertArrayEquals(stopRow, scan.getStopRow()); } @@ -701,7 +700,14 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { Scan scan = context.getScan(); assertNotNull(scan.getFilter()); - assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow()); +// assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow()); + assertArrayEquals( + ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId), + PChar.INSTANCE.toBytes( + PChar.INSTANCE.pad( + PChar.INSTANCE.toObject(ByteUtil.nextKey(PChar.INSTANCE.toBytes(keyPrefix))), + 15))), + scan.getStartRow()); assertArrayEquals(HConstants.EMPTY_END_ROW, scan.getStopRow()); } @@ -1267,7 +1273,7 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest { PVarchar.INSTANCE.toBytes(tenantId), StringUtil.padChar(PVarchar.INSTANCE.toBytes(keyPrefix),15), PDate.INSTANCE.toBytes(startTime)); assertArrayEquals(expectedStartRow, scan.getStartRow()); byte[] expectedStopRow = ByteUtil.concat( - PVarchar.INSTANCE.toBytes(tenantId), StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15), PDate.INSTANCE.toBytes(stopTime)); + PVarchar.INSTANCE.toBytes(tenantId), StringUtil.padChar(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(keyPrefix)),15)); assertArrayEquals(expectedStopRow, scan.getStopRow()); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java index ad65373..ed1f3e4 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java @@ -65,7 +65,6 @@ import org.apache.phoenix.schema.SortOrder; import org.apache.phoenix.schema.TableRef; import org.apache.phoenix.util.ByteUtil; import org.apache.phoenix.util.PropertiesUtil; -import org.apache.phoenix.util.ScanUtil; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.Parameterized; @@ -297,7 +296,7 @@ public class ParallelIteratorsSplitTest extends BaseConnectionlessQueryTest { // Always set start and stop key to max to verify we are using the information in skipscan // filter over the scan's KMIN and KMAX. Scan scan = new Scan().setFilter(filter); - ScanRanges scanRanges = ScanRanges.create(schema, slots, ScanUtil.getDefaultSlotSpans(ranges.length)); + ScanRanges scanRanges = ScanRanges.createSingleSpan(schema, slots); List<Object> ret = Lists.newArrayList(); ret.add(new Object[] {scan, scanRanges, Arrays.<KeyRange>asList(expectedSplits)}); return ret; http://git-wip-us.apache.org/repos/asf/phoenix/blob/d18afe18/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java index 2f8088d..fdae749 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java @@ -35,6 +35,10 @@ public class QueryPlanTest extends BaseConnectionlessQueryTest { public void testExplainPlan() throws Exception { String[] queryPlans = new String[] { + "SELECT host FROM PTSDB WHERE inst IS NULL AND host IS NOT NULL AND date >= to_date('2013-01-01')", + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER PTSDB [null,not null]\n" + + " SERVER FILTER BY FIRST KEY ONLY AND DATE >= DATE '2013-01-01 00:00:00.000'", + "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' AND entity_id > '000000000000002' AND entity_id < '000000000000008' AND (organization_id,entity_id) >= ('000000000000001','000000000000005') ", "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001','000000000000005'] - ['000000000000001','000000000000008']", @@ -50,10 +54,6 @@ public class QueryPlanTest extends BaseConnectionlessQueryTest { "CLIENT PARALLEL 1-WAY REVERSE RANGE SCAN OVER PTSDB2 ['na1']\n" + " SERVER FILTER BY FIRST KEY ONLY", - "SELECT host FROM PTSDB WHERE inst IS NULL AND host IS NOT NULL AND date >= to_date('2013-01-01')", - "CLIENT PARALLEL 1-WAY RANGE SCAN OVER PTSDB [null,not null]\n" + - " SERVER FILTER BY FIRST KEY ONLY AND DATE >= DATE '2013-01-01 00:00:00.000'", - // Since inst IS NOT NULL is unbounded, we won't continue optimizing "SELECT host FROM PTSDB WHERE inst IS NOT NULL AND host IS NULL AND date >= to_date('2013-01-01')", "CLIENT PARALLEL 1-WAY RANGE SCAN OVER PTSDB [not null]\n" + @@ -149,7 +149,7 @@ public class QueryPlanTest extends BaseConnectionlessQueryTest { "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" + " SERVER AGGREGATE INTO DISTINCT ROWS BY [ORGANIZATION_ID, ENTITY_ID, ROUND(A_DATE)]\n" + "CLIENT MERGE SORT\n" + - "CLIENT TOP 10 ROWS SORTED BY [ENTITY_ID NULLS LAST]", + "CLIENT 10 ROW LIMIT", "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' ORDER BY a_string DESC NULLS LAST LIMIT 10", "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" +
