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 2017/11/15 18:48:06 UTC
[11/37] phoenix git commit: PHOENIX-4292 Filters on Tables and Views
with composite PK of VARCHAR fields with sort direction DESC do not work
(addendum)
PHOENIX-4292 Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work (addendum)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/87f8b1ed
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/87f8b1ed
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/87f8b1ed
Branch: refs/heads/4.x-HBase-1.1
Commit: 87f8b1ed0f64f2504fdf6b084f81ad7f98641c77
Parents: 3f453e1
Author: Thomas D'Silva <td...@apache.org>
Authored: Mon Oct 23 20:19:15 2017 -0700
Committer: James Taylor <jt...@salesforce.com>
Committed: Wed Nov 15 10:41:23 2017 -0800
----------------------------------------------------------------------
.../java/org/apache/phoenix/end2end/ViewIT.java | 202 ++++++++++++-------
1 file changed, 129 insertions(+), 73 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/87f8b1ed/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
index 66e2430..5c0d100 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ViewIT.java
@@ -791,77 +791,133 @@ public class ViewIT extends BaseViewIT {
assertArrayEquals(expectedPKs, actualPKs);
}
- @Test
- public void testCompositeDescPK() throws SQLException {
- Properties props = new Properties();
- try (Connection globalConn = DriverManager.getConnection(getUrl(), props)) {
- String tableName = generateUniqueName();
- String viewName = generateUniqueName();
-
- // create global base table
- globalConn.createStatement().execute("CREATE TABLE " + tableName
- + " (TENANT_ID CHAR(15) NOT NULL, KEY_PREFIX CHAR(3) NOT NULL, CREATED_DATE DATE, CREATED_BY CHAR(15), SYSTEM_MODSTAMP DATE CONSTRAINT PK PRIMARY KEY (TENANT_ID, KEY_PREFIX)) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1");
-
- // create various tenant specific views
- globalConn.createStatement()
- .execute("CREATE VIEW " + viewName
- + " (pk1 VARCHAR(10) NOT NULL, pk2 VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC, pk2 DESC)) AS SELECT * FROM "
- + tableName + " WHERE KEY_PREFIX = 'abc' ");
-
- String tenantId = "tenantId";
- Properties tenantProps = new Properties();
- tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
- // create a tenant specific view
- try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) {
- // upsert rows
- tenantConn.createStatement().execute("UPSERT INTO " + viewName
- + " (pk1, pk2, col1, col3) VALUES ('testa', 'testb', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
- tenantConn.createStatement().execute("UPSERT INTO " + viewName
- + " (pk1, pk2, col1, col3) VALUES ('testa', 'testc', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
- tenantConn.createStatement().execute("UPSERT INTO " + viewName
- + " (pk1, pk2, col1, col3) VALUES ('testa', 'testd', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
- tenantConn.createStatement().execute("UPSERT INTO " + viewName
- + " (pk1, pk2, col1, col3) VALUES ('testa', 'teste', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
- tenantConn.createStatement().execute("UPSERT INTO " + viewName
- + " (pk1, pk2, col1, col3) VALUES ('testb', 'testa', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
- tenantConn.commit();
-
- // run queries
- ResultSet rs = tenantConn.createStatement()
- .executeQuery("SELECT count(*) FROM " + viewName + " WHERE pk1 = 'testa'");
- assertTrue(rs.next());
- assertEquals(4, rs.getLong(1));
- assertFalse(rs.next());
-
- rs = tenantConn.createStatement().executeQuery("SELECT count(*) FROM " + viewName);
- assertTrue(rs.next());
- assertEquals(5, rs.getLong(1));
- assertFalse(rs.next());
-
- rs = tenantConn.createStatement()
- .executeQuery("SELECT count(*) FROM " + viewName + " WHERE pk1 >= 'testa'");
- assertTrue(rs.next());
- assertEquals(5, rs.getLong(1));
- assertFalse(rs.next());
-
- rs = tenantConn.createStatement()
- .executeQuery("SELECT count(*) FROM " + viewName + " WHERE pk1 <= 'testa'");
- assertTrue(rs.next());
- assertEquals(4, rs.getLong(1));
- assertFalse(rs.next());
-
- rs = tenantConn.createStatement()
- .executeQuery("SELECT count(*) FROM " + viewName + " WHERE pk1 > 'testa'");
- assertTrue(rs.next());
- assertEquals(1, rs.getLong(1));
- assertFalse(rs.next());
-
- rs = tenantConn.createStatement()
- .executeQuery("SELECT count(*) FROM " + viewName + " WHERE pk1 < 'testa'");
- assertTrue(rs.next());
- assertEquals(0, rs.getLong(1));
- assertFalse(rs.next());
- }
- }
- }
+ @Test
+ public void testCompositeDescPK() throws SQLException {
+ Properties props = new Properties();
+ try (Connection globalConn = DriverManager.getConnection(getUrl(), props)) {
+ String tableName = generateUniqueName();
+ String viewName1 = generateUniqueName();
+ String viewName2 = generateUniqueName();
+ String viewName3 = generateUniqueName();
+ String viewName4 = generateUniqueName();
+
+ // create global base table
+ globalConn.createStatement().execute("CREATE TABLE " + tableName
+ + " (TENANT_ID CHAR(15) NOT NULL, KEY_PREFIX CHAR(3) NOT NULL, CREATED_DATE DATE, CREATED_BY CHAR(15), SYSTEM_MODSTAMP DATE CONSTRAINT PK PRIMARY KEY (TENANT_ID, KEY_PREFIX)) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1");
+
+ String tenantId = "tenantId";
+ Properties tenantProps = new Properties();
+ tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
+ // create a tenant specific view
+ try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) {
+ // create various tenant specific views
+ // view with composite PK with multiple PK values of VARCHAR values DESC
+ tenantConn.createStatement()
+ .execute("CREATE VIEW " + viewName1
+ + " (pk1 VARCHAR(10) NOT NULL, pk2 VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC, pk2 DESC)) AS SELECT * FROM "
+ + tableName + " WHERE KEY_PREFIX = 'abc' ");
+ // view with composite PK with single pk value DESC
+ tenantConn.createStatement()
+ .execute("CREATE VIEW " + viewName2
+ + " (pk1 VARCHAR(10) NOT NULL, pk2 VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC, pk2 DESC)) AS SELECT * FROM "
+ + tableName + " WHERE KEY_PREFIX = 'abc' ");
+
+ // upsert rows
+ upsertRows(viewName1, tenantConn);
+ upsertRows(viewName2, tenantConn);
+
+ // run queries
+ String[] whereClauses =
+ new String[] { "pk1 = 'testa'", "", "pk1 >= 'testa'", "pk1 <= 'testa'",
+ "pk1 > 'testa'", "pk1 < 'testa'" };
+ long[] expectedArray = new long[] { 4, 5, 5, 4, 1, 0 };
+ validate(viewName1, tenantConn, whereClauses, expectedArray);
+ validate(viewName2, tenantConn, whereClauses, expectedArray);
+
+ // view with composite PK with multiple Date PK values DESC
+ tenantConn.createStatement()
+ .execute("CREATE VIEW " + viewName3
+ + " (pk1 DATE(10) NOT NULL, pk2 DATE(10) NOT NULL, col1 VARCHAR(10), col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC, pk2 DESC)) AS SELECT * FROM "
+ + tableName + " WHERE KEY_PREFIX = 'ab3' ");
+
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName3
+ + " (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:00:00', 'yyyy-MM-dd HH:mm:ss'), 'txt1', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName3
+ + " (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:01:00', 'yyyy-MM-dd HH:mm:ss'), 'txt1', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName3
+ + " (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:02:00', 'yyyy-MM-dd HH:mm:ss'), 'txt1', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName3
+ + " (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:03:00', 'yyyy-MM-dd HH:mm:ss'), 'txt1', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName3
+ + " (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 23:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:04:00', 'yyyy-MM-dd HH:mm:ss'), 'txt1', 10)");
+ tenantConn.commit();
+
+ String[] view3WhereClauses =
+ new String[] {
+ "pk1 = TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')", "",
+ "pk1 >= TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')",
+ "pk1 <= TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')",
+ "pk1 > TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')",
+ "pk1 < TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')" };
+ validate(viewName3, tenantConn, view3WhereClauses, expectedArray);
+
+ tenantConn.createStatement()
+ .execute("CREATE VIEW " + viewName4
+ + " (pk1 DATE(10) NOT NULL, pk2 DECIMAL NOT NULL, pk3 VARCHAR(10) NOT NULL, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC, pk2 DESC, pk3 DESC)) AS SELECT * FROM "
+ + tableName + " WHERE KEY_PREFIX = 'ab4' ");
+
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName4
+ + " (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 1, 'txt1', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName4
+ + " (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 2, 'txt2', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName4
+ + " (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 3, 'txt3', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName4
+ + " (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 4, 'txt4', 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName4
+ + " (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 23:00:00', 'yyyy-MM-dd HH:mm:ss'), 1, 'txt1', 10)");
+ tenantConn.commit();
+
+ String[] view4WhereClauses =
+ new String[] {
+ "pk1 = TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')",
+ "pk1 = TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss') AND pk2 = 2",
+ "pk1 = TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss') AND pk2 > 2",
+ "", "pk1 >= TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')",
+ "pk1 <= TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')",
+ "pk1 > TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')",
+ "pk1 < TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss')" };
+ long[] view4ExpectedArray = new long[] { 4, 1, 2, 5, 5, 4, 1, 0 };
+ validate(viewName4, tenantConn, view4WhereClauses, view4ExpectedArray);
+
+ }
+ }
+ }
+
+ private void validate(String viewName, Connection tenantConn, String[] whereClauseArray,
+ long[] expectedArray) throws SQLException {
+ for (int i = 0; i < whereClauseArray.length; ++i) {
+ String where = !whereClauseArray[i].isEmpty() ? (" WHERE " + whereClauseArray[i]) : "";
+ ResultSet rs =
+ tenantConn.createStatement()
+ .executeQuery("SELECT count(*) FROM " + viewName + where);
+ assertTrue(rs.next());
+ assertEquals(expectedArray[i], rs.getLong(1));
+ assertFalse(rs.next());
+ }
+ }
+
+ private void upsertRows(String viewName1, Connection tenantConn) throws SQLException {
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName1
+ + " (pk1, pk2, col1, col3) VALUES ('testa', 'testb', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName1
+ + " (pk1, pk2, col1, col3) VALUES ('testa', 'testc', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName1
+ + " (pk1, pk2, col1, col3) VALUES ('testa', 'testd', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName1
+ + " (pk1, pk2, col1, col3) VALUES ('testa', 'teste', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
+ tenantConn.createStatement().execute("UPSERT INTO " + viewName1
+ + " (pk1, pk2, col1, col3) VALUES ('testb', 'testa', TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10)");
+ tenantConn.commit();
+ }
}