phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From cheng...@apache.org
Subject phoenix git commit: PHOENIX-4602 OrExpression should can also push non-leading pk columns to scan
Date Wed, 14 Feb 2018 07:19:31 GMT
Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-1.2 97680f24f -> a93ed98e0


PHOENIX-4602 OrExpression should can also push non-leading pk columns to scan


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/a93ed98e
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/a93ed98e
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/a93ed98e

Branch: refs/heads/4.x-HBase-1.2
Commit: a93ed98e0045fda5ff662f805146951f783337f0
Parents: 97680f2
Author: chenglei <chenglei@apache.org>
Authored: Wed Feb 14 15:18:56 2018 +0800
Committer: chenglei <chenglei@apache.org>
Committed: Wed Feb 14 15:18:56 2018 +0800

----------------------------------------------------------------------
 .../apache/phoenix/compile/WhereOptimizer.java  |  23 +-
 .../phoenix/compile/WhereOptimizerTest.java     | 235 +++++++++++++++++++
 .../java/org/apache/phoenix/util/TestUtil.java  |   8 +
 3 files changed, 246 insertions(+), 20 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/a93ed98e/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 7bf8259..87f00e4 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
@@ -656,6 +656,9 @@ public class WhereOptimizer {
         }
         
         private KeySlots andKeySlots(AndExpression andExpression, List<KeySlots> childSlots)
{
+            if(childSlots.isEmpty()) {
+                return null;
+            }
             int nColumns = table.getPKColumns().size();
             KeySlot[] keySlot = new KeySlot[nColumns];
             KeyRange minMaxRange = KeyRange.EVERYTHING_RANGE;
@@ -754,31 +757,11 @@ public class WhereOptimizer {
                     	}
                     }
                 } else {
-                    boolean hasFirstSlot = true;
-                    boolean prevIsNull = false;
                     // TODO: Do the same optimization that we do for IN if the childSlots
specify a fully qualified row key
                     for (KeySlot slot : childSlot) {
-                        if (hasFirstSlot) {
-                            // if the first slot is null, return null immediately
-                            if (slot == null) {
-                                return null;
-                            }
-                            // mark that we've handled the first slot
-                            hasFirstSlot = false;
-                        }
-
-                        // now if current slot is the first one, it must not be null
-                        // if not the first, then it might be null, so check if all the rest
are null
                         if (slot == null) {
-                            prevIsNull = true;
                             continue;
-                        } else {
-                            // current slot is not null but prev one is null, cannot OR these
together (PHOENIX-3328)
-                            if (prevIsNull) {
-                                return null;
-                            }
                         }
-
                         /*
                          * If we see a different PK column than before, we can't
                          * optimize it because our SkipScanFilter only handles

http://git-wip-us.apache.org/repos/asf/phoenix/blob/a93ed98e/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 b37312b..4b21a89 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,10 +49,14 @@ import java.util.Properties;
 
 import org.apache.hadoop.hbase.HConstants;
 import org.apache.hadoop.hbase.client.Scan;
+import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
 import org.apache.hadoop.hbase.filter.Filter;
 import org.apache.hadoop.hbase.filter.FilterList;
+import org.apache.hadoop.hbase.filter.FilterList.Operator;
 import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.expression.Expression;
 import org.apache.phoenix.filter.RowKeyComparisonFilter;
+import org.apache.phoenix.filter.SingleCQKeyValueComparisonFilter;
 import org.apache.phoenix.filter.SingleKeyValueComparisonFilter;
 import org.apache.phoenix.filter.SkipScanFilter;
 import org.apache.phoenix.jdbc.PhoenixConnection;
@@ -77,6 +81,7 @@ import org.apache.phoenix.util.PropertiesUtil;
 import org.apache.phoenix.util.ScanUtil;
 import org.apache.phoenix.util.StringUtil;
 import org.apache.phoenix.util.TestUtil;
+import org.apache.phoenix.schema.ColumnRef;
 import org.junit.Test;
 
 public class WhereOptimizerTest extends BaseConnectionlessQueryTest {
@@ -2145,4 +2150,234 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest
{
         assertArrayEquals(stopRow, scan.getStopRow());
     }
 
+    @Test
+    public void testOrExpressionNonLeadingPKPushToScanBug4602() throws Exception {
+        Connection conn = null;
+        try {
+            conn= DriverManager.getConnection(getUrl());
+            String testTableName="OR_NO_LEADING_PK4602";
+            String sql="CREATE TABLE "+ testTableName +"("+
+                    "PK1 INTEGER NOT NULL,"+
+                    "PK2 INTEGER NOT NULL,"+
+                    "PK3 INTEGER NOT NULL,"+
+                    "DATA INTEGER, "+
+                    "CONSTRAINT TEST_PK PRIMARY KEY (PK1,PK2,PK3))";
+            conn.createStatement().execute(sql);
+
+            //case 1: pk1 is equal,pk2 is multiRange
+            sql="select * from "+testTableName+" t where (t.pk1 = 2) and ((t.pk2 >= 4
and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
+            QueryPlan queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            Scan scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof SkipScanFilter);
+            List<List<KeyRange>> rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
+            assertEquals(
+                    Arrays.asList(
+                        Arrays.asList(KeyRange.POINT.apply(PInteger.INSTANCE.toBytes(2))),
+                        Arrays.asList(
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true,
PInteger.INSTANCE.toBytes(6), false),
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true,
PInteger.INSTANCE.toBytes(9), false)
+                                )
+                            ),
+                     rowKeyRanges
+                    );
+
+            assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2),
PInteger.INSTANCE.toBytes(4)));
+            assertArrayEquals(scan.getStopRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2),
PInteger.INSTANCE.toBytes(9)));
+
+            //case 2: pk1 is range,pk2 is multiRange
+            sql="select * from "+testTableName+" t where (t.pk1 >=2 and t.pk1<5) and
((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and t.pk2 <9))";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof SkipScanFilter);
+            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
+            assertEquals(
+                    Arrays.asList(
+                        Arrays.asList(KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2),
true, PInteger.INSTANCE.toBytes(5), false)),
+                        Arrays.asList(
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true,
PInteger.INSTANCE.toBytes(6), false),
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true,
PInteger.INSTANCE.toBytes(9), false)
+                                )
+                            ),
+                     rowKeyRanges
+                    );
+            assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2),
PInteger.INSTANCE.toBytes(4)));
+            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(5));
+
+             //case 3 : pk1 has multiRange,,pk2 is multiRange
+            sql="select * from "+testTableName+" t where ((t.pk1 >=2 and t.pk1<5) or
(t.pk1 >=7 and t.pk1 <9)) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and
t.pk2 <9))";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof SkipScanFilter);
+            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
+            assertEquals(
+                    Arrays.asList(
+                        Arrays.asList(
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true,
PInteger.INSTANCE.toBytes(5), false),
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(7), true,
PInteger.INSTANCE.toBytes(9), false)
+                                ),
+                        Arrays.asList(
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true,
PInteger.INSTANCE.toBytes(6), false),
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true,
PInteger.INSTANCE.toBytes(9), false)
+                                )
+                            ),
+                     rowKeyRanges
+                    );
+            assertArrayEquals(scan.getStartRow(), ByteUtil.concat(PInteger.INSTANCE.toBytes(2),
PInteger.INSTANCE.toBytes(4)));
+            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));
+
+            //case4 : only pk1 and pk3, no pk2
+            sql="select * from "+testTableName+" t where ((t.pk1 >=2 and t.pk1<5) or
(t.pk1 >=7 and t.pk1 <9)) and ((t.pk3 >= 4 and t.pk3 <6) or (t.pk3 >= 8 and
t.pk3 <9))";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof FilterList);
+            FilterList filterList = (FilterList)scan.getFilter();
+
+            assertTrue(filterList.getOperator() == Operator.MUST_PASS_ALL);
+            assertEquals(filterList.getFilters().size(),2);
+            assertTrue(filterList.getFilters().get(0) instanceof SkipScanFilter);
+            rowKeyRanges = ((SkipScanFilter)(filterList.getFilters().get(0))).getSlots();
+            assertEquals(
+                    Arrays.asList(
+                        Arrays.asList(
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(2), true,
PInteger.INSTANCE.toBytes(5), false),
+                                KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(7), true,
PInteger.INSTANCE.toBytes(9), false)
+                                ),
+                       Arrays.asList(KeyRange.EVERYTHING_RANGE),
+                       Arrays.asList(
+                               KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true, PInteger.INSTANCE.toBytes(6),
false),
+                               KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true, PInteger.INSTANCE.toBytes(9),
false)
+                               )
+                            ),
+                     rowKeyRanges
+                    );
+            assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(2));
+            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));
+
+            assertTrue(filterList.getFilters().get(1) instanceof RowKeyComparisonFilter);
+            RowKeyComparisonFilter rowKeyComparisonFilter =(RowKeyComparisonFilter) filterList.getFilters().get(1);
+            Expression pk3Expression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK3").getPosition()).newColumnExpression();
+            assertEquals(
+                      TestUtil.rowKeyFilter(
+                            TestUtil.or(
+                                    TestUtil.and(
+                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk3Expression,
4),
+                                            TestUtil.constantComparison(CompareOp.LESS,pk3Expression,
6)),
+                                    TestUtil.and(
+                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk3Expression,
8),
+                                            TestUtil.constantComparison(CompareOp.LESS,pk3Expression,
9))
+                                    )
+                              ),
+                     rowKeyComparisonFilter);
+
+            //case 5: pk1 or data column
+            sql="select * from "+testTableName+" t where ((t.pk1 >=2) or (t.data >=
4 and t.data <9))";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof SingleCQKeyValueComparisonFilter);
+            Expression pk1Expression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK1").getPosition()).newColumnExpression();
+            Expression dataExpression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("DATA").getPosition()).newColumnExpression();
+            assertEquals(
+                    TestUtil.singleKVFilter(
+                        TestUtil.or(
+                                TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL, pk1Expression,
2),
+                                TestUtil.and(
+                                        TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,
dataExpression, 4),
+                                        TestUtil.constantComparison(CompareOp.LESS, dataExpression,
9)
+                                        )
+                                )
+                    ),
+                    scan.getFilter());
+            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
+            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
+
+            //case 6: pk1 or pk2,but pk2 is empty range
+            sql ="select * from "+testTableName+" t where (t.pk1 >=2 and t.pk1<5) or
((t.pk2 >= 4 and t.pk2 <6) and (t.pk2 >= 8 and t.pk2 <9))";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            scan = queryPlan.getContext().getScan();
+            assertNull(scan.getFilter());
+            assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(2));
+            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(5));
+
+            //case 7: pk1 or pk2,but pk2 is all range
+            sql ="select * from "+testTableName+" t where (t.pk1 >=2 and t.pk1<5) or
(t.pk2 >=7 or t.pk2 <9)";
+            queryPlan= TestUtil.getOptimizeQueryPlan(conn, sql);
+
+            Expression pk2Expression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK2").getPosition()).newColumnExpression();
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
+            assertEquals(
+                      TestUtil.rowKeyFilter(
+                            TestUtil.or(
+                                    TestUtil.and(
+                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk1Expression,
2),
+                                            TestUtil.constantComparison(CompareOp.LESS,pk1Expression,
5)),
+                                    TestUtil.or(
+                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk2Expression,
7),
+                                            TestUtil.constantComparison(CompareOp.LESS,pk2Expression,
9))
+                                    )
+                              ),
+                     scan.getFilter());
+            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
+            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
+
+            //case 8: pk1 and pk2, but pk1 has a or allRange
+            sql="select * from "+testTableName+" t where ((t.pk1 >=2 and t.pk1<5) or
(t.pk1 >=7 or t.pk1 <9)) and ((t.pk2 >= 4 and t.pk2 <6) or (t.pk2 >= 8 and
t.pk2 <9))";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
+            assertEquals(
+                      TestUtil.rowKeyFilter(
+                            TestUtil.or(
+                                    TestUtil.and(
+                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk2Expression,
4),
+                                            TestUtil.constantComparison(CompareOp.LESS,pk2Expression,
6)),
+                                    TestUtil.and(
+                                            TestUtil.constantComparison(CompareOp.GREATER_OR_EQUAL,pk2Expression,
8),
+                                            TestUtil.constantComparison(CompareOp.LESS,pk2Expression,
9))
+                                    )
+                              ),
+                     scan.getFilter());
+
+            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
+            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
+
+            //case 9:  pk1 and pk2, but pk2 has a or allRange
+            sql="select * from "+testTableName+" t where ((t.pk1 >= 4 and t.pk1 <6)
or (t.pk1 >= 8 and t.pk1 <9)) and ((t.pk2 >=2 and t.pk2<5) or (t.pk2 >=7 or
t.pk2 <9))";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof SkipScanFilter);
+            rowKeyRanges = ((SkipScanFilter)(scan.getFilter())).getSlots();
+            assertEquals(
+                    Arrays.asList(
+                            Arrays.asList(
+                                    KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(4), true,
PInteger.INSTANCE.toBytes(6), false),
+                                    KeyRange.getKeyRange(PInteger.INSTANCE.toBytes(8), true,
PInteger.INSTANCE.toBytes(9), false)
+                                    ),
+                            Arrays.asList(KeyRange.EVERYTHING_RANGE)),
+                    rowKeyRanges);
+            assertArrayEquals(scan.getStartRow(), PInteger.INSTANCE.toBytes(4));
+            assertArrayEquals(scan.getStopRow(), PInteger.INSTANCE.toBytes(9));
+
+            //case 10: only pk2
+            sql = "select * from "+testTableName+" t where (pk2 <=7 or pk2>9)";
+            queryPlan = TestUtil.getOptimizeQueryPlan(conn, sql);
+            pk2Expression =  new ColumnRef(queryPlan.getTableRef(), queryPlan.getTableRef().getTable().getColumnForColumnName("PK2").getPosition()).newColumnExpression();
+            scan = queryPlan.getContext().getScan();
+            assertTrue(scan.getFilter() instanceof RowKeyComparisonFilter);
+                assertEquals(
+                      TestUtil.rowKeyFilter(
+                            TestUtil.or(
+                                    TestUtil.constantComparison(CompareOp.LESS_OR_EQUAL,pk2Expression,
7),
+                                    TestUtil.constantComparison(CompareOp.GREATER,pk2Expression,
9))),
+                     scan.getFilter());
+            assertArrayEquals(scan.getStartRow(), HConstants.EMPTY_START_ROW);
+            assertArrayEquals(scan.getStopRow(), HConstants.EMPTY_END_ROW);
+        }
+        finally {
+            if(conn!=null) {
+                conn.close();
+            }
+        }
+    }
+
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/a93ed98e/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
index c7a7e8d..4a105f6 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
@@ -70,6 +70,7 @@ import org.apache.hadoop.hbase.ipc.BlockingRpcCallback;
 import org.apache.hadoop.hbase.ipc.ServerRpcController;
 import org.apache.hadoop.hbase.util.Bytes;
 import org.apache.phoenix.compile.AggregationManager;
+import org.apache.phoenix.compile.QueryPlan;
 import org.apache.phoenix.compile.SequenceManager;
 import org.apache.phoenix.compile.StatementContext;
 import org.apache.phoenix.coprocessor.generated.MetaDataProtos.ClearCacheRequest;
@@ -1017,4 +1018,11 @@ public class TestUtil {
         return ByteUtil.compare(op, compareResult);
     }
 
+    public static QueryPlan getOptimizeQueryPlan(Connection conn,String sql) throws SQLException
{
+        PhoenixPreparedStatement statement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
+        QueryPlan queryPlan = statement.optimizeQuery(sql);
+        queryPlan.iterator();
+        return queryPlan;
+    }
+
 }


Mime
View raw message