This is an automated email from the ASF dual-hosted git repository.
stoty 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 0d4a3f2 PHOENIX-5884 Join query return empty result when filters for
both the tables are present
0d4a3f2 is described below
commit 0d4a3f2415dd75d46f3800d6e0e8777ae6c88063
Author: Ankit Singhal <[email protected]>
AuthorDate: Fri May 22 16:30:20 2020 +0200
PHOENIX-5884 Join query return empty result when filters for both the
tables are present
---
.../java/org/apache/phoenix/end2end/InListIT.java | 18 ++++++
.../join/WhereOptimizerForJoinFiltersIT.java | 73 ++++++++++++++++++++++
.../org/apache/phoenix/compile/WhereOptimizer.java | 23 ++++---
3 files changed, 104 insertions(+), 10 deletions(-)
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
index 68d27cb..b0aee8f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InListIT.java
@@ -434,6 +434,24 @@ public class InListIT extends ParallelStatsDisabledIT {
testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause, expecteds);
}
+ @Test public void testOverlappingRVCWithMiddleColumn() throws Exception {
+ String whereClause =
+ "WHERE pk2=3 and (pk1, pk2, pk3, pk4) IN ((2, 3, 6, 6), (2, 3, 4,
5)) ";
+ List<String> expecteds = singletonList("row2");
+
+ testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause,
+ expecteds);
+ }
+
+ @Test public void testOverlappingRVCWithMultipleMiddleColumn() throws
Exception {
+ String whereClause =
+ "WHERE (pk2,pk3) in ((3,4)) and (pk1, pk2, pk3, pk4) IN ((2, 3, 6,
6), (2, 3, 4, 5)) ";
+ List<String> expecteds = singletonList("row2");
+
+ testWithIntegerTypesWithVariedSaltingAndTenancy(DEFAULT_UPSERT_BODIES,
whereClause,
+ expecteds);
+ }
+
@Test
public void testOverlappingRVCAndRVCPartiallyQualifiedBegin() throws
Exception {
String whereClause = "WHERE (pk1, pk2) IN ((1, 2), (2, 3)) AND (pk2,
pk3) IN ((3, 4), (3, 6))";
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/WhereOptimizerForJoinFiltersIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/WhereOptimizerForJoinFiltersIT.java
new file mode 100644
index 0000000..33f1bd8
--- /dev/null
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/WhereOptimizerForJoinFiltersIT.java
@@ -0,0 +1,73 @@
+package org.apache.phoenix.end2end.join;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.junit.Test;
+
+public class WhereOptimizerForJoinFiltersIT extends ParallelStatsDisabledIT {
+
+ String allValues =
+
"('201904','ID2_VAL','ID3_VAL','01','000000','ID4_VAL','ID5_VAL','2019-05-30
22:41:37.000')\n"
+ +
"('201905','ID2_VAL','ID3_VAL','01','000000','ID4_VAL','ID5_VAL','2019-12-31
22:59:59.000') \n"
+ +
"('201905','ID2_VAL','ID3_VAL','30','000000','ID4_VAL','ID5_VAL','2019-12-31
22:59:59.000') \n"
+ +
"('201904','ID2_VAL','ID3_VAL2','01','000000','ID4_VAL','ID5_VAL','2019-05-30
22:41:37.000')\n"
+ +
"('201905','ID2_VAL','ID3_VAL2','30','000000','ID4_VAL','ID5_VAL','2019-12-31
22:59:59.000')";
+
+ private void createTable(Connection conn, String tableName) throws
SQLException {
+ conn.createStatement().execute("CREATE TABLE " + tableName + " (" + "
id1 CHAR(6) NOT NULL,"
+ + " id2 VARCHAR(22) NOT NULL," + " id3 VARCHAR(12) NOT NULL,"
+ + " id4 CHAR(2) NOT NULL," + " id5 CHAR(6) NOT NULL, "
+ + " id6 VARCHAR(200) NOT NULL," + " id7 VARCHAR(50) NOT NULL," +
" ts TIMESTAMP ,"
+ + " CONSTRAINT PK_JOIN_AND_INTERSECTION_TABLE PRIMARY
KEY(id1,id2,id3,id4,id5,id6,id7)"
+ + ")");
+ }
+
+ @Test public void testJoin() throws SQLException {
+ String leftTable = generateUniqueName();
+ String rightTable = generateUniqueName();
+ Connection conn = DriverManager.getConnection(getUrl());
+ createTable(conn, leftTable);
+ createTable(conn, rightTable);
+ for (String values : allValues.split("\n")) {
+ conn.createStatement().execute(
+ "UPSERT INTO " + leftTable + "(id1,id2,id3,id4, id5, id6, id7,ts)
VALUES" + values);
+ conn.createStatement().execute(
+ "UPSERT INTO " + rightTable + "(id1,id2,id3,id4, id5, id6, id7,ts)
VALUES" + values);
+ }
+ conn.commit();
+ ResultSet rs =
+ conn.createStatement().executeQuery("select count(*) from "+leftTable);
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ String query =
+ "SELECT m.*,r.* FROM "+leftTable+" m join "+rightTable+" r "
+ + " on m.id3 = r.id3 and m.id2 = r.id2 "
+ + " and m.id4 = r.id4 and m.id5 = r.id5 "
+ + " and m.id1 = r.id1 and m.ts = r.ts "
+ + " where m.id1 IN ('201904','201905') "
+ + " and r.id1 IN ('201904','201905') and r.id2 = 'ID2_VAL' and
m.id2 = 'ID2_VAL' "
+ + " and m.id3 IN ('ID3_VAL','ID3_VAL2') "
+ + " and r.id3 IN ('ID3_VAL','ID3_VAL2') LIMIT 1000000000";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ query =
+ "SELECT m.*,r.* FROM "+leftTable+" m join "+rightTable+" r "
+ + " on m.id3 = r.id3 and m.id2 = r.id2 "
+ + " and m.id4 = r.id4 and m.id5 = r.id5 "
+ + " and m.id1 = r.id1 and m.ts = r.ts "
+ + " where r.id1 IN ('201904','201905') "
+ + " and r.id2 = 'ID2_VAL' "
+ + " and r.id3 IN ('ID3_VAL','ID3_VAL2') LIMIT 1000000000";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ conn.close();
+ }
+
+}
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 34b75dc..b1fd238 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
@@ -1370,19 +1370,22 @@ public class WhereOptimizer {
// will never overlap. We do not need to process both the
lower and upper
// ranges since they are the same.
if (result.isSingleKey() && otherRange.isSingleKey()) {
- // Find the span of the trailing bytes as it could be more
than one.
- // We need this to determine if the slot at the last
position would
- // have a separator byte (i.e. is variable length).
- int pos = otherPKPos;
- rowKeySchema.iterator(trailingBytes, ptr, otherPKPos);
- while (rowKeySchema.next(ptr, pos, trailingBytes.length)
!= null) {
- pos++;
+ int minSpan = rowKeySchema.computeMinSpan(pkPos, result,
ptr);
+ int otherMinSpan =
+ rowKeySchema.computeMinSpan(otherPKPos, otherRange,
ptr);
+ byte[] otherLowerRange;
+ boolean isFixedWidthAtEnd;
+ if (pkPos + minSpan <= otherPKPos + otherMinSpan) {
+ otherLowerRange = otherRange.getLowerRange();
+ isFixedWidthAtEnd = table.getPKColumns().get(pkPos +
minSpan -1).getDataType().isFixedWidth();
+ } else {
+ otherLowerRange = trailingBytes;
+ trailingBytes = otherRange.getLowerRange();
+ isFixedWidthAtEnd =
table.getPKColumns().get(otherPKPos + otherMinSpan
-1).getDataType().isFixedWidth();
}
- byte[] otherLowerRange = otherRange.getLowerRange();
- boolean isFixedWidthAtEnd =
table.getPKColumns().get(pos).getDataType().isFixedWidth();
// If the otherRange starts with the overlapping trailing
byte *and* we're comparing
// the entire key (i.e. not just a leading subset), then
we have an intersection.
- if (Bytes.startsWith(otherLowerRange, trailingBytes) &&
+ if (Bytes.startsWith(otherLowerRange, trailingBytes) &&
(isFixedWidthAtEnd ||
otherLowerRange.length == trailingBytes.length ||
otherLowerRange[trailingBytes.length] ==
QueryConstants.SEPARATOR_BYTE)) {
