This is an automated email from the ASF dual-hosted git repository. chenglei pushed a commit to branch 4.x in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/4.x by this push: new 8da1582 PHOENIX-5753 Fix erroneous query result when RVC is clipped with desc column 8da1582 is described below commit 8da1582ea8322955db6692823a68d222d310805d Author: chenglei <cheng...@apache.org> AuthorDate: Wed Mar 18 12:33:31 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 f66f196..ccba651 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; @@ -655,4 +656,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))")); + } + } }