Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-0.98 0a96ec481 -> 770b9e410


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/770b9e41
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/770b9e41
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/770b9e41

Branch: refs/heads/4.x-HBase-0.98
Commit: 770b9e41037ffc581267d756fa0cca790e32a197
Parents: 0a96ec4
Author: chenglei <cheng...@apache.org>
Authored: Wed Feb 14 15:11:57 2018 +0800
Committer: chenglei <cheng...@apache.org>
Committed: Wed Feb 14 15:11:57 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/770b9e41/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 5e7b996..ccf073a 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
@@ -661,6 +661,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;
@@ -759,31 +762,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/770b9e41/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 fdab6a7..5741160 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/770b9e41/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 3fae790..7be2b6f 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;
@@ -1019,4 +1020,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;
+    }
+
 }

Reply via email to