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 <tdsi...@apache.org> Authored: Mon Oct 23 20:19:15 2017 -0700 Committer: James Taylor <jtay...@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(); + } }
