You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2016/10/16 02:07:40 UTC
phoenix git commit: PHOENIX-3382 Query using Row Value Constructor
comprised of non leading PK columns returns incorrect results
Repository: phoenix
Updated Branches:
refs/heads/master 45fbc4177 -> 3e4aec1bc
PHOENIX-3382 Query using Row Value Constructor comprised of non leading PK columns returns incorrect results
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/3e4aec1b
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/3e4aec1b
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/3e4aec1b
Branch: refs/heads/master
Commit: 3e4aec1bc440ad503d5058237e3b508392df9266
Parents: 45fbc41
Author: James Taylor <ja...@apache.org>
Authored: Sat Oct 15 17:05:21 2016 -0700
Committer: James Taylor <ja...@apache.org>
Committed: Sat Oct 15 17:25:27 2016 -0700
----------------------------------------------------------------------
.../org/apache/phoenix/end2end/QueryMoreIT.java | 103 +++++++++++++++++++
.../apache/phoenix/compile/WhereOptimizer.java | 4 +
.../phoenix/compile/WhereOptimizerTest.java | 61 +++++++++++
3 files changed, 168 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/3e4aec1b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
index 041e96d..b9162de 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
@@ -368,4 +368,107 @@ public class QueryMoreIT extends ParallelStatsDisabledIT {
assertNull(rs.getBigDecimal(2, 10));
}
}
+
+ // FIXME: this repros PHOENIX-3382, but turned up two more issues:
+ // 1) PHOENIX-3383 Comparison between descending row keys used in RVC is reverse
+ // 2) PHOENIX-3384 Optimize RVC expressions for non leading row key columns
+ @Test
+ public void testRVCOnDescWithLeadingPKEquality() throws Exception {
+ final Connection conn = DriverManager.getConnection(getUrl());
+ String fullTableName = generateUniqueName();
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + fullTableName + "(\n" +
+ " ORGANIZATION_ID CHAR(15) NOT NULL,\n" +
+ " SCORE DOUBLE NOT NULL,\n" +
+ " ENTITY_ID CHAR(15) NOT NULL\n" +
+ " CONSTRAINT PAGE_SNAPSHOT_PK PRIMARY KEY (\n" +
+ " ORGANIZATION_ID,\n" +
+ " SCORE DESC,\n" +
+ " ENTITY_ID DESC\n" +
+ " )\n" +
+ ") MULTI_TENANT=TRUE");
+ }
+
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',3,'01')");
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',2,'04')");
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',2,'03')");
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',1,'02')");
+ conn.commit();
+
+ // FIXME: PHOENIX-3383
+ // This comparison is really backwards: it should be (score, entity_id) < (2, '04'),
+ // but because we're matching a descending key, our comparison has to be switched.
+ try (Statement stmt = conn.createStatement()) {
+ final ResultSet rs = stmt.executeQuery("SELECT entity_id, score\n" +
+ "FROM " + fullTableName + "\n" +
+ "WHERE organization_id = 'org1'\n" +
+ "AND (score, entity_id) > (2, '04')\n" +
+ "ORDER BY score DESC, entity_id DESC\n" +
+ "LIMIT 3");
+ assertTrue(rs.next());
+ assertEquals("03", rs.getString(1));
+ assertEquals(2.0, rs.getDouble(2), 0.001);
+ assertTrue(rs.next());
+ assertEquals("02", rs.getString(1));
+ assertEquals(1.0, rs.getDouble(2), 0.001);
+ assertFalse(rs.next());
+ }
+ // FIXME: PHOENIX-3384
+ // It should not be necessary to specify organization_id in this query
+ try (Statement stmt = conn.createStatement()) {
+ final ResultSet rs = stmt.executeQuery("SELECT entity_id, score\n" +
+ "FROM " + fullTableName + "\n" +
+ "WHERE organization_id = 'org1'\n" +
+ "AND (organization_id, score, entity_id) > ('org1', 2, '04')\n" +
+ "ORDER BY score DESC, entity_id DESC\n" +
+ "LIMIT 3");
+ assertTrue(rs.next());
+ assertEquals("03", rs.getString(1));
+ assertEquals(2.0, rs.getDouble(2), 0.001);
+ assertTrue(rs.next());
+ assertEquals("02", rs.getString(1));
+ assertEquals(1.0, rs.getDouble(2), 0.001);
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testSingleDescPKColumnComparison() throws Exception {
+ final Connection conn = DriverManager.getConnection(getUrl());
+ String fullTableName = generateUniqueName();
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + fullTableName + "(\n" +
+ " ORGANIZATION_ID CHAR(15) NOT NULL,\n" +
+ " SCORE DOUBLE NOT NULL,\n" +
+ " ENTITY_ID CHAR(15) NOT NULL\n" +
+ " CONSTRAINT PAGE_SNAPSHOT_PK PRIMARY KEY (\n" +
+ " ORGANIZATION_ID,\n" +
+ " SCORE DESC,\n" +
+ " ENTITY_ID DESC\n" +
+ " )\n" +
+ ") MULTI_TENANT=TRUE");
+ }
+
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',3,'01')");
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',2,'04')");
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',2,'03')");
+ conn.createStatement().execute("UPSERT INTO " + fullTableName + " VALUES ('org1',1,'02')");
+ conn.commit();
+
+ try (Statement stmt = conn.createStatement()) {
+ // Even though SCORE is descending, the > comparison makes sense logically
+ // and doesn't have to be reversed as RVC expression comparisons do (which
+ // is really just a bug - see PHOENIX-3383).
+ final ResultSet rs = stmt.executeQuery("SELECT entity_id, score\n" +
+ "FROM " + fullTableName + "\n" +
+ "WHERE organization_id = 'org1'\n" +
+ "AND score > 2.0\n" +
+ "ORDER BY score DESC\n" +
+ "LIMIT 3");
+ assertTrue(rs.next());
+ assertEquals("01", rs.getString(1));
+ assertEquals(3.0, rs.getDouble(2), 0.001);
+ assertFalse(rs.next());
+ }
+ }
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/3e4aec1b/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 f15a251..0681925 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
@@ -487,6 +487,10 @@ public class WhereOptimizer {
int initPosition = (table.getBucketNum() ==null ? 0 : 1) + (this.context.getConnection().getTenantId() != null && table.isMultiTenant() ? 1 : 0) + (table.getViewIndexId() == null ? 0 : 1);
if (initPosition == slot.getPKPosition()) {
minMaxRange = keyRange;
+ } else {
+ // If we cannot set the minMaxRange, then we must not extract the expression since
+ // we wouldn't be constraining the range at all based on it.
+ extractNode = null;
}
}
return newKeyParts(slot, extractNode, keyRanges, minMaxRange);
http://git-wip-us.apache.org/repos/asf/phoenix/blob/3e4aec1b/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 e056c47..7aec357 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
@@ -65,6 +65,7 @@ import org.apache.phoenix.schema.SortOrder;
import org.apache.phoenix.schema.types.PChar;
import org.apache.phoenix.schema.types.PDate;
import org.apache.phoenix.schema.types.PDecimal;
+import org.apache.phoenix.schema.types.PDouble;
import org.apache.phoenix.schema.types.PInteger;
import org.apache.phoenix.schema.types.PLong;
import org.apache.phoenix.schema.types.PUnsignedLong;
@@ -2085,4 +2086,64 @@ public class WhereOptimizerTest extends BaseConnectionlessQueryTest {
context = compileStatement("select avg(pk1) from test order by count(distinct pk2)");
assertEquals(2, context.getAggregationManager().getAggregators().getAggregatorCount());
}
+
+ @Test
+ public void testRVCWithLeadingPKEq() throws SQLException {
+ String tenantId = "o1";
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE COMMUNITIES.TEST (\n" +
+ " ORGANIZATION_ID CHAR(2) NOT NULL,\n" +
+ " SCORE DOUBLE NOT NULL,\n" +
+ " ENTITY_ID CHAR(2) NOT NULL\n" +
+ " CONSTRAINT PAGE_SNAPSHOT_PK PRIMARY KEY (\n" +
+ " ORGANIZATION_ID,\n" +
+ " SCORE,\n" +
+ " ENTITY_ID\n" +
+ " )\n" +
+ ") VERSIONS=1, MULTI_TENANT=TRUE");
+ String query = "SELECT entity_id, score\n" +
+ "FROM communities.test\n" +
+ "WHERE organization_id = '" + tenantId + "'\n" +
+ "AND (score, entity_id) > (2.0, '04')\n" +
+ "ORDER BY score, entity_id";
+ Scan scan = compileStatement(query).getScan();
+ assertNotNull(scan.getFilter());
+
+ // See PHOENIX-3384: Optimize RVC expressions for non leading row key columns.
+ // FIXME: We should be able to optimize this better, taking into account the
+ // (score, entity_id) > (2.0, '04') to form more of the start/stop row.
+ assertArrayEquals(PVarchar.INSTANCE.toBytes(tenantId), scan.getStartRow());
+ assertArrayEquals(ByteUtil.nextKey(PVarchar.INSTANCE.toBytes(tenantId)), scan.getStopRow());
+ }
+
+ @Test
+ public void testRVCWithCompDescRowKey() throws SQLException {
+ String tenantId = "o1";
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute("CREATE TABLE COMMUNITIES.TEST (\n" +
+ " ORGANIZATION_ID CHAR(2) NOT NULL,\n" +
+ " SCORE DOUBLE NOT NULL,\n" +
+ " ENTITY_ID CHAR(2) NOT NULL\n" +
+ " CONSTRAINT PAGE_SNAPSHOT_PK PRIMARY KEY (\n" +
+ " ORGANIZATION_ID,\n" +
+ " SCORE DESC,\n" +
+ " ENTITY_ID DESC\n" +
+ " )\n" +
+ ") VERSIONS=1, MULTI_TENANT=TRUE");
+ String query = "SELECT entity_id, score\n" +
+ "FROM communities.test\n" +
+ "WHERE organization_id = '" + tenantId + "'\n" +
+ "AND (organization_id, score, entity_id) < ('" + tenantId + "',2.0, '04')\n" +
+ "ORDER BY score DESC, entity_id DESC";
+ Scan scan = compileStatement(query).getScan();
+ assertNull(scan.getFilter());
+
+ // FIXME See PHOENIX-3383: Comparison between descending row keys used in RVC is reverse
+ // This should set the startRow, but instead it's setting the stopRow
+ byte[] startRow = PChar.INSTANCE.toBytes(tenantId);
+ assertArrayEquals(startRow, scan.getStartRow());
+ byte[] stopRow = ByteUtil.concat(PChar.INSTANCE.toBytes(tenantId), PDouble.INSTANCE.toBytes(2.0, SortOrder.DESC), PChar.INSTANCE.toBytes("04", SortOrder.DESC));
+ assertArrayEquals(stopRow, scan.getStopRow());
+ }
+
}