You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ch...@apache.org on 2020/03/18 04:27:39 UTC
[phoenix] branch master updated: PHOENIX-5753 Fix erroneous query
result when RVC is clipped with desc column
This is an automated email from the ASF dual-hosted git repository.
chenglei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push:
new fb4f857 PHOENIX-5753 Fix erroneous query result when RVC is clipped with desc column
fb4f857 is described below
commit fb4f857696a29777a5ad9d68dd61fd51800f8c6b
Author: chenglei <ch...@apache.org>
AuthorDate: Wed Mar 18 12:26:15 2020 +0800
PHOENIX-5753 Fix erroneous query result when RVC is clipped with desc column
---
.../apache/phoenix/end2end/SkipScanQueryIT.java | 57 +++
.../end2end/index/GlobalIndexOptimizationIT.java | 8 +-
.../org/apache/phoenix/compile/WhereOptimizer.java | 92 +++--
.../java/org/apache/phoenix/query/KeyRange.java | 12 +
.../apache/phoenix/compile/WhereCompilerTest.java | 57 ---
.../apache/phoenix/compile/WhereOptimizerTest.java | 388 +++++++++++++++++++--
6 files changed, 495 insertions(+), 119 deletions(-)
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java
index 5a6bc23..64b897dd 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java
@@ -18,6 +18,7 @@
package org.apache.phoenix.end2end;
import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.apache.phoenix.util.TestUtil.assertResultSet;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
@@ -656,4 +657,60 @@ public class SkipScanQueryIT extends ParallelStatsDisabledIT {
assertFalse(rs.next());
}
}
+
+ @Test
+ public void testRVCClipBug5753() throws Exception {
+ String tableName = generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ Statement stmt = conn.createStatement();
+
+ String sql = "CREATE TABLE "+tableName+" (" +
+ " pk1 INTEGER NOT NULL , " +
+ " pk2 INTEGER NOT NULL, " +
+ " pk3 INTEGER NOT NULL, " +
+ " pk4 INTEGER NOT NULL, " +
+ " pk5 INTEGER NOT NULL, " +
+ " pk6 INTEGER NOT NULL, " +
+ " pk7 INTEGER NOT NULL, " +
+ " pk8 INTEGER NOT NULL, " +
+ " v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4,pk5,pk6 desc,pk7,pk8))";;
+
+ stmt.execute(sql);
+
+ stmt.execute(
+ "UPSERT INTO " + tableName + " (pk1,pk2,pk3,pk4,pk5,pk6,pk7,pk8,v) "+
+ "VALUES (1,3,4,10,2,6,7,9,1)");
+
+ sql = "select pk1,pk2,pk3,pk4 from " + tableName +
+ " where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7) order by pk1,pk2,pk3";
+
+ ResultSet rs = conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,3,4,10}});
+
+ sql = "select * from " + tableName +
+ " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) < (5,7) and "+
+ " (pk5,pk6,pk7) < (5,6,7) and pk8 > 8 order by pk1,pk2,pk3";
+ rs = conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,3,4,10}});
+
+ stmt.execute(
+ "UPSERT INTO " + tableName + " (pk1,pk2,pk3,pk4,pk5,pk6,pk7,pk8,v) "+
+ "VALUES (1,3,2,10,5,4,3,9,1)");
+ rs = conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,3,2,10},{1,3,4,10}});
+
+ stmt.execute(
+ "UPSERT INTO " + tableName + " (pk1,pk2,pk3,pk4,pk5,pk6,pk7,pk8,v) "+
+ "VALUES (1,3,5,6,4,7,8,9,1)");
+ rs = conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,3,2,10},{1,3,4,10},{1,3,5,6}});
+
+ sql = "select * from " + tableName +
+ " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) in ((5,6),(2,10)) and "+
+ " (pk5,pk6,pk7) in ((4,7,8),(5,4,3)) and pk8 > 8 order by pk1,pk2,pk3";
+ rs = conn.prepareStatement(sql).executeQuery();
+ assertResultSet(rs, new Object[][]{{1,3,2,10},{1,3,5,6}});
+ }
+ }
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
index 9567d36..5c2558e 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
@@ -338,14 +338,18 @@ public class GlobalIndexOptimizationIT extends ParallelStatsDisabledIT {
String query = "SELECT /*+ INDEX(" + viewName + " " + viewIndex + ")*/ t_id,k1,k2,k3,v1 FROM " + viewName + " where k1 IN (1,2) and k2 IN (3,4)";
rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
-
+
+ /**
+ * This inner "_IDX_" + dataTableName use skipScan, and all the whereExpressions are already in SkipScanFilter,
+ * so there is no other RowKeyComparisonFilter needed.
+ */
String actual = QueryUtil.getExplainPlan(rs);
String expected =
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + dataTableName + "\n" +
" SERVER FILTER BY V1 = 'a'\n" +
" SKIP-SCAN-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER _IDX_" + dataTableName + " \\[" + Short.MIN_VALUE + ",1\\] - \\[" + Short.MIN_VALUE + ",2\\]\n" +
- " SERVER FILTER BY FIRST KEY ONLY AND \"K2\" IN \\(3,4\\)\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
" DYNAMIC SERVER FILTER BY \\(\"" + viewName + ".T_ID\", \"" + viewName + ".K1\", \"" + viewName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)";
assertTrue("Expected:\n" + expected + "\ndid not match\n" + actual, Pattern.matches(expected,actual));
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 7e461d4..34b75dc 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
@@ -197,9 +197,8 @@ public class WhereOptimizer {
boolean hasUnboundedRange = false;
boolean hasMultiRanges = 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();
@@ -209,13 +208,12 @@ public class WhereOptimizer {
if (slot == null || slot.getKeyRanges().isEmpty()) {
continue;
}
+ if(slot.getPKPosition() < pkPos) {
+ continue;
+ }
if (slot.getPKPosition() != pkPos) {
- if (!forcedSkipScan) {
- stopExtracting = true;
- } else {
- useSkipScan |= !stopExtracting && !forcedRangeScan && forcedSkipScan;
- }
- for (int i=pkPos; i < slot.getPKPosition(); i++) {
+ hasUnboundedRange = hasRangeKey = true;
+ for (int i= pkPos; i < slot.getPKPosition(); i++) {
cnf.add(Collections.singletonList(KeyRange.EVERYTHING_RANGE));
}
}
@@ -224,8 +222,10 @@ public class WhereOptimizer {
SortOrder prevSortOrder = null;
int slotOffset = 0;
int clipLeftSpan = 0;
-
+ boolean onlySplittedRVCLeftValid = false;
+ boolean stopExtracting = false;
// Iterate through all spans of this slot
+ boolean areAllSingleKey = KeyRange.areAllSingleKey(keyRanges);
while (true) {
SortOrder sortOrder =
schema.getField(slot.getPKPosition() + slotOffset).getSortOrder();
@@ -259,52 +259,76 @@ public class WhereOptimizer {
keyRanges =
clipRight(schema, slot.getPKPosition() + slotOffset - 1, keyRanges,
leftRanges, ptr);
+ leftRanges = KeyRange.coalesce(leftRanges);
+ keyRanges = KeyRange.coalesce(keyRanges);
if (prevSortOrder == SortOrder.DESC) {
leftRanges = invertKeyRanges(leftRanges);
}
slotSpanArray[cnf.size()] = clipLeftSpan-1;
cnf.add(leftRanges);
+ pkPos = slot.getPKPosition() + slotOffset;
clipLeftSpan = 0;
prevSortOrder = sortOrder;
// since we have to clip the portion with the same sort order, we can no longer
// extract the nodes from the where clause
// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC and query
// WHERE (A,B) < ('a','b')
- // the range (* - a\xFFb) is converted to (~a-*)(*-b)
+ // the range (* - a\xFFb) is converted to [~a-*)(*-b)
// so we still need to filter on A,B
stopExtracting = true;
+ if(!areAllSingleKey) {
+ //for cnf, we only add [~a-*) to it, (*-b) is skipped.
+ //but for all single key, we can continue.
+ onlySplittedRVCLeftValid = true;
+ break;
+ }
}
clipLeftSpan++;
slotOffset++;
if (slotOffset >= slot.getPKSpan()) {
break;
}
- if (iterator.hasNext()) {
- iterator.next();
- }
}
- if (schema.getField(slot.getPKPosition() + slotOffset - 1).getSortOrder() == SortOrder.DESC) {
- keyRanges = invertKeyRanges(keyRanges);
+
+ if(onlySplittedRVCLeftValid) {
+ keyRanges = cnf.get(cnf.size()-1);
+ } else {
+ if (schema.getField(
+ slot.getPKPosition() + slotOffset - 1).getSortOrder() == SortOrder.DESC) {
+ keyRanges = invertKeyRanges(keyRanges);
+ }
+ pkPos = slot.getPKPosition() + slotOffset;
+ slotSpanArray[cnf.size()] = clipLeftSpan-1;
+ cnf.add(keyRanges);
}
- pkPos = slot.getPKPosition() + slot.getPKSpan();
-
- slotSpanArray[cnf.size()] = clipLeftSpan-1;
- 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 haven'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.
- * 3) As above a non-contiguous range due to sort order
+ /**
+ * We use skip scan when:
+ * 1.previous slot has unbound and force skip scan and
+ * 2.not force Range Scan and
+ * 3.previous rowkey slot has range or current rowkey slot have multiple ranges.
+ *
+ * Once we can not use skip scan and we have a non-contiguous range, we can not remove
+ * the whereExpressions of current rowkey slot from the current {@link SelectStatement#where},
+ * because the {@link Scan#startRow} and {@link Scan#endRow} could not exactly represent
+ * currentRowKeySlotRanges.
+ * So we should stop extracting whereExpressions of current rowkey slot once we encounter:
+ * 1. we now use range scan and
+ * 2. previous rowkey slot has unbound or
+ * previous rowkey slot has range or
+ * current rowkey slot have multiple ranges.
*/
- stopExtracting |= (hasUnboundedRange && !forcedSkipScan) || (hasRangeKey && forcedRangeScan);
- useSkipScan |= !stopExtracting && !forcedRangeScan && (keyRanges.size() > 1 || hasRangeKey);
-
+ hasMultiRanges |= keyRanges.size() > 1;
+ useSkipScan |=
+ (!hasUnboundedRange || forcedSkipScan) &&
+ !forcedRangeScan &&
+ (hasRangeKey || hasMultiRanges);
+
+ stopExtracting |=
+ !useSkipScan &&
+ (hasUnboundedRange || hasRangeKey || hasMultiRanges);
+
for (int i = 0; (!hasUnboundedRange || !hasRangeKey) && i < keyRanges.size(); i++) {
KeyRange range = keyRanges.get(i);
if (range.isUnbound()) {
@@ -313,12 +337,6 @@ public class WhereOptimizer {
hasRangeKey = true;
}
}
-
- hasMultiRanges |= keyRanges.size() > 1;
-
- // We cannot extract if we have multiple ranges and are forcing a range scan.
- stopExtracting |= forcedRangeScan && hasMultiRanges;
-
// Will be null in cases for which only part of the expression was factored out here
// to set the start/end key. An example would be <column> LIKE 'foo%bar' where we can
// set the start key to 'foo' but still need to match the regex at filter time.
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/query/KeyRange.java b/phoenix-core/src/main/java/org/apache/phoenix/query/KeyRange.java
index 4229dfa..689e8eb 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/query/KeyRange.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/query/KeyRange.java
@@ -446,6 +446,18 @@ public class KeyRange implements Writable {
return lowerRange == KeyRange.EMPTY_RANGE.getLowerRange() && upperRange == KeyRange.EMPTY_RANGE.getUpperRange();
}
+ public static boolean areAllSingleKey(List<KeyRange> rowKeyRanges) {
+ if(rowKeyRanges == null || rowKeyRanges.isEmpty()) {
+ return false;
+ }
+ for(KeyRange rowKeyRange : rowKeyRanges) {
+ if(!rowKeyRange.isSingleKey()) {
+ return false;
+ }
+ }
+ return true;
+ }
+
/**
* @return list of at least size 1
*/
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
index ce3135b..0476842 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
@@ -1026,61 +1026,4 @@ public class WhereCompilerTest extends BaseConnectionlessQueryTest {
assertEquals(FETCH_SIZE, pstmt.getFetchSize());
assertEquals(FETCH_SIZE, scan.getCaching());
}
-
- @Test
- public void testLastPkColumnIsVariableLengthAndDescBug5307() throws Exception {
- Connection conn = null;
- try {
- conn = DriverManager.getConnection(getUrl());
- String sql = "CREATE TABLE t1 (\n" +
- "OBJECT_VERSION VARCHAR NOT NULL,\n" +
- "LOC VARCHAR,\n" +
- "CONSTRAINT PK PRIMARY KEY (OBJECT_VERSION DESC))";
- conn.createStatement().execute(sql);
-
- byte[] startKey = ByteUtil.concat(
- PVarchar.INSTANCE.toBytes("2222", SortOrder.DESC),
- QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
- byte[] endKey = ByteUtil.concat(
- PVarchar.INSTANCE.toBytes("1111", SortOrder.DESC),
- QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
- ByteUtil.nextKey(endKey, endKey.length);
- sql = "SELECT /*+ RANGE_SCAN */ OBJ.OBJECT_VERSION, OBJ.LOC from t1 AS OBJ "+
- "where OBJ.OBJECT_VERSION in ('1111','2222')";
- QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
- Scan scan = queryPlan.getContext().getScan();
- assertArrayEquals(startKey, scan.getStartRow());
- assertArrayEquals(endKey, scan.getStopRow());
-
- sql = "CREATE TABLE t2 (\n" +
- "OBJECT_ID VARCHAR NOT NULL,\n" +
- "OBJECT_VERSION VARCHAR NOT NULL,\n" +
- "LOC VARCHAR,\n" +
- "CONSTRAINT PK PRIMARY KEY (OBJECT_ID, OBJECT_VERSION DESC))";
- conn.createStatement().execute(sql);
-
- startKey = ByteUtil.concat(
- PVarchar.INSTANCE.toBytes("obj1", SortOrder.ASC),
- QueryConstants.SEPARATOR_BYTE_ARRAY,
- PVarchar.INSTANCE.toBytes("2222", SortOrder.DESC),
- QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
- endKey = ByteUtil.concat(
- PVarchar.INSTANCE.toBytes("obj3", SortOrder.ASC),
- QueryConstants.SEPARATOR_BYTE_ARRAY,
- PVarchar.INSTANCE.toBytes("1111", SortOrder.DESC),
- QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
- ByteUtil.nextKey(endKey, endKey.length);
- sql = "SELECT OBJ.OBJECT_ID, OBJ.OBJECT_VERSION, OBJ.LOC from t2 AS OBJ "+
- "where (OBJ.OBJECT_ID, OBJ.OBJECT_VERSION) in (('obj1', '2222'),('obj2', '1111'),('obj3', '1111'))";
- queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
- scan = queryPlan.getContext().getScan();
- assertArrayEquals(startKey, scan.getStartRow());
- assertArrayEquals(endKey, scan.getStopRow());
- }
- finally {
- if(conn != null) {
- conn.close();
- }
- }
- }
}
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 f3a8fa1..18683b7 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
@@ -49,7 +49,6 @@ import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
-
import com.google.common.collect.Lists;
import org.apache.hadoop.hbase.HConstants;
import org.apache.hadoop.hbase.client.Scan;
@@ -90,6 +89,7 @@ import org.apache.phoenix.util.DateUtil;
import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.ScanUtil;
+import org.apache.phoenix.util.SchemaUtil;
import org.apache.phoenix.util.StringUtil;
import org.apache.phoenix.util.TestUtil;
import org.junit.Test;
@@ -2524,13 +2524,13 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest {
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();
+ /**
+ * This sql use skipScan, and all the whereExpressions are in SkipScanFilter,
+ * so there is no other RowKeyComparisonFilter needed.
+ */
+ assertTrue(scan.getFilter() instanceof SkipScanFilter);
- 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();
+ rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
assertEquals(
Arrays.asList(
Arrays.asList(
@@ -2548,22 +2548,6 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest {
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);
@@ -2693,4 +2677,362 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest {
}
}
+ @Test
+ public void testLastPkColumnIsVariableLengthAndDescBug5307() throws Exception {
+ Connection conn = null;
+ try {
+ conn = DriverManager.getConnection(getUrl());
+ String sql = "CREATE TABLE t1 (\n" +
+ "OBJECT_VERSION VARCHAR NOT NULL,\n" +
+ "LOC VARCHAR,\n" +
+ "CONSTRAINT PK PRIMARY KEY (OBJECT_VERSION DESC))";
+ conn.createStatement().execute(sql);
+
+ byte[] startKey = ByteUtil.concat(
+ PVarchar.INSTANCE.toBytes("2222", SortOrder.DESC),
+ QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
+ byte[] endKey = ByteUtil.concat(
+ PVarchar.INSTANCE.toBytes("1111", SortOrder.DESC),
+ QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
+ ByteUtil.nextKey(endKey, endKey.length);
+ sql = "SELECT /*+ RANGE_SCAN */ OBJ.OBJECT_VERSION, OBJ.LOC from t1 AS OBJ "+
+ "where OBJ.OBJECT_VERSION in ('1111','2222')";
+ QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+ Scan scan = queryPlan.getContext().getScan();
+ assertArrayEquals(startKey, scan.getStartRow());
+ assertArrayEquals(endKey, scan.getStopRow());
+
+ sql = "CREATE TABLE t2 (\n" +
+ "OBJECT_ID VARCHAR NOT NULL,\n" +
+ "OBJECT_VERSION VARCHAR NOT NULL,\n" +
+ "LOC VARCHAR,\n" +
+ "CONSTRAINT PK PRIMARY KEY (OBJECT_ID, OBJECT_VERSION DESC))";
+ conn.createStatement().execute(sql);
+
+ startKey = ByteUtil.concat(
+ PVarchar.INSTANCE.toBytes("obj1", SortOrder.ASC),
+ QueryConstants.SEPARATOR_BYTE_ARRAY,
+ PVarchar.INSTANCE.toBytes("2222", SortOrder.DESC),
+ QueryConstants.DESC_SEPARATOR_BYTE_ARRAY);
+ /**
+ * For following sql, queryPlan would use SkipScan and is regarded as PointLookup,
+ * so the endKey is computed as {@link SchemaUtil#VAR_BINARY_SCHEMA},see {@link ScanRanges#create}.
+ */
+ endKey = ByteUtil.concat(
+ PVarchar.INSTANCE.toBytes("obj3", SortOrder.ASC),
+ QueryConstants.SEPARATOR_BYTE_ARRAY,
+ PVarchar.INSTANCE.toBytes("1111", SortOrder.DESC),
+ QueryConstants.DESC_SEPARATOR_BYTE_ARRAY,
+ QueryConstants.SEPARATOR_BYTE_ARRAY);
+
+ sql = "SELECT OBJ.OBJECT_ID, OBJ.OBJECT_VERSION, OBJ.LOC from t2 AS OBJ "+
+ "where (OBJ.OBJECT_ID, OBJ.OBJECT_VERSION) in (('obj1', '2222'),('obj2', '1111'),('obj3', '1111'))";
+ queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+ scan = queryPlan.getContext().getScan();
+ FilterList filterList = (FilterList)scan.getFilter();
+ assertTrue(filterList.getOperator() == Operator.MUST_PASS_ALL);
+ assertEquals(filterList.getFilters().size(),2);
+ assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
+ assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
+ RowKeyComparisonFilter rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
+ assertTrue(rowKeyComparisonFilter.toString().equals(
+ "(OBJECT_ID, OBJECT_VERSION) IN ([111,98,106,49,0,205,205,205,205],[111,98,106,50,0,206,206,206,206],[111,98,106,51,0,206,206,206,206])"));
+
+ assertTrue(queryPlan.getContext().getScanRanges().isPointLookup());
+ assertArrayEquals(startKey, scan.getStartRow());
+ assertArrayEquals(endKey, scan.getStopRow());
+ }
+ finally {
+ if(conn != null) {
+ conn.close();
+ }
+ }
+ }
+
+ @Test
+ public void testRVCClipBug5753() throws Exception {
+ String tableName = generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl())) {
+ conn.setAutoCommit(true);
+ Statement stmt = conn.createStatement();
+
+ String sql = "CREATE TABLE "+tableName+" (" +
+ " pk1 INTEGER NOT NULL , " +
+ " pk2 INTEGER NOT NULL, " +
+ " pk3 INTEGER NOT NULL, " +
+ " pk4 INTEGER NOT NULL, " +
+ " pk5 INTEGER NOT NULL, " +
+ " pk6 INTEGER NOT NULL, " +
+ " pk7 INTEGER NOT NULL, " +
+ " pk8 INTEGER NOT NULL, " +
+ " v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4,pk5,pk6 desc,pk7,pk8))";;
+
+ stmt.execute(sql);
+
+ List<List<KeyRange>> rowKeyRanges = null;
+ RowKeyComparisonFilter rowKeyComparisonFilter = null;
+ QueryPlan queryPlan = null;
+ Scan scan = null;
+
+ sql = "SELECT /*+ RANGE_SCAN */ * FROM "+ tableName +
+ " WHERE (pk1, pk2) IN ((2, 3), (2, 4)) AND pk3 = 5";
+ queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+ scan = queryPlan.getContext().getScan();
+ assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
+ rowKeyComparisonFilter = (RowKeyComparisonFilter)scan.getFilter();
+ assertTrue(rowKeyComparisonFilter.toString().equals(
+ "((PK1, PK2) IN ([128,0,0,2,128,0,0,3],[128,0,0,2,128,0,0,4]) AND PK3 = 5)"));
+ assertArrayEquals(
+ scan.getStartRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(3),
+ PInteger.INSTANCE.toBytes(5, SortOrder.DESC)));
+ assertArrayEquals(
+ scan.getStopRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(4),
+ ByteUtil.nextKey(PInteger.INSTANCE.toBytes(5, SortOrder.DESC))));
+
+ sql = "select * from " + tableName +
+ " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) < (3,5)";
+ 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(1),
+ true,
+ PInteger.INSTANCE.toBytes(2),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(2),
+ true,
+ PInteger.INSTANCE.toBytes(3),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
+ true,
+ KeyRange.UNBOUND,
+ false))
+ ),
+ rowKeyRanges);
+ assertArrayEquals(
+ scan.getStartRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(1),
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(3, SortOrder.DESC)));
+ assertArrayEquals(
+ scan.getStopRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(4)));
+
+ assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
+ rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
+ assertTrue(rowKeyComparisonFilter.toString().equals(
+ "(TO_INTEGER(PK3), PK4) < (TO_INTEGER(TO_INTEGER(3)), 5)"));
+
+ /**
+ * RVC is singleKey
+ */
+ sql = "select * from " + tableName +
+ " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) in ((3,4),(4,5)) and "+
+ " (pk5,pk6,pk7) in ((5,6,7),(6,7,8)) and pk8 > 8";
+ queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+ scan = queryPlan.getContext().getScan();
+ assertTrue(scan.getFilter() instanceof 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(1),
+ true,
+ PInteger.INSTANCE.toBytes(2),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(2),
+ true,
+ PInteger.INSTANCE.toBytes(3),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(4, SortOrder.DESC),
+ true,
+ PInteger.INSTANCE.toBytes(4, SortOrder.DESC),
+ true),
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
+ true,
+ PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(4),
+ true,
+ PInteger.INSTANCE.toBytes(4),
+ true),
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(5),
+ true,
+ PInteger.INSTANCE.toBytes(5),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(5),
+ true,
+ PInteger.INSTANCE.toBytes(5),
+ true),
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(6),
+ true,
+ PInteger.INSTANCE.toBytes(6),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(7, SortOrder.DESC),
+ true,
+ PInteger.INSTANCE.toBytes(7, SortOrder.DESC),
+ true),
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(6, SortOrder.DESC),
+ true,
+ PInteger.INSTANCE.toBytes(6, SortOrder.DESC),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(7),
+ true,
+ PInteger.INSTANCE.toBytes(7),
+ true),
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(8),
+ true,
+ PInteger.INSTANCE.toBytes(8),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(9),
+ true,
+ KeyRange.UNBOUND,
+ false))
+ ),
+ rowKeyRanges);
+ assertArrayEquals(
+ scan.getStartRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(1),
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(4, SortOrder.DESC),
+ PInteger.INSTANCE.toBytes(4),
+ PInteger.INSTANCE.toBytes(5),
+ PInteger.INSTANCE.toBytes(7, SortOrder.DESC),
+ PInteger.INSTANCE.toBytes(7),
+ PInteger.INSTANCE.toBytes(9)));
+ assertArrayEquals(
+ scan.getStopRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(3),
+ PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
+ PInteger.INSTANCE.toBytes(5),
+ PInteger.INSTANCE.toBytes(6),
+ PInteger.INSTANCE.toBytes(6, SortOrder.DESC),
+ PInteger.INSTANCE.toBytes(9)));
+
+ assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
+ rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
+ assertTrue(rowKeyComparisonFilter.toString().equals(
+ "((PK3, PK4) IN ([127,255,255,251,128,0,0,5],[127,255,255,252,128,0,0,4])"+
+ " AND (PK5, PK6, PK7) IN ([128,0,0,5,127,255,255,249,128,0,0,7],[128,0,0,6,127,255,255,248,128,0,0,8]))"));
+ /**
+ * RVC is not singleKey
+ */
+ sql = "select * from " + tableName +
+ " where (pk1 >=1 and pk1<=2) and (pk2>=2 and pk2<=3) and (pk3,pk4) < (3,4) and "+
+ " (pk5,pk6,pk7) < (5,6,7) and pk8 > 8";
+ queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+ scan = queryPlan.getContext().getScan();
+ assertTrue(scan.getFilter() instanceof 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(1),
+ true,
+ PInteger.INSTANCE.toBytes(2),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(2),
+ true,
+ PInteger.INSTANCE.toBytes(3),
+ true)),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(3, SortOrder.DESC),
+ true,
+ KeyRange.UNBOUND,
+ false)),
+ Arrays.asList(KeyRange.EVERYTHING_RANGE),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ KeyRange.UNBOUND,
+ false,
+ PInteger.INSTANCE.toBytes(5),
+ true)),
+ Arrays.asList(KeyRange.EVERYTHING_RANGE),
+ Arrays.asList(KeyRange.EVERYTHING_RANGE),
+ Arrays.asList(
+ KeyRange.getKeyRange(
+ PInteger.INSTANCE.toBytes(9),
+ true,
+ KeyRange.UNBOUND,
+ false))
+ ),
+ rowKeyRanges);
+ assertArrayEquals(
+ scan.getStartRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(1),
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(3, SortOrder.DESC)));
+ assertArrayEquals(
+ scan.getStopRow(),
+ ByteUtil.concat(
+ PInteger.INSTANCE.toBytes(2),
+ PInteger.INSTANCE.toBytes(4)));
+
+ assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
+ rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
+ assertTrue(rowKeyComparisonFilter.toString().equals(
+ "((TO_INTEGER(PK3), PK4) < (TO_INTEGER(TO_INTEGER(3)), 4) AND "+
+ "(PK5, TO_INTEGER(PK6), PK7) < (5, TO_INTEGER(TO_INTEGER(6)), 7))"));
+ }
+ }
}