ChinmaySKulkarni commented on a change in pull request #671: PHOENIX-4845
Support using Row Value Constructors in OFFSET clause fo…
URL: https://github.com/apache/phoenix/pull/671#discussion_r363566912
##########
File path:
phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
##########
@@ -0,0 +1,860 @@
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Properties;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Before;
+import org.junit.Test;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+// RVC Based Offset - Tests
+public class RowValueConstructorOffsetIT extends ParallelStatsDisabledIT {
+
+ static final String SIMPLE_DDL = "CREATE TABLE %s (t_id VARCHAR NOT
NULL,\n" + "k1 INTEGER NOT NULL,\n"
+ + "k2 INTEGER NOT NULL,\n" + "v1 INTEGER,\n" + "v2 VARCHAR,\n"
+ + "CONSTRAINT pk PRIMARY KEY (t_id, k1, k2)) ";
+
+ static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT NULL,\n" +
"k2 TINYINT NOT NULL,\n"
+ + "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+
+ String tableName = "T_" + generateUniqueName();
+
+ String dataTableName = "T_" + generateUniqueName();
+
+ String indexName = "INDEX_" + tableName;
+
+ String dataIndexName = "INDEX_" + dataTableName;
+
+ boolean initialized = false;
+ Connection conn;
+
+ @Before
+ public void init() throws SQLException {
+ if (!initialized) {
+ conn = DriverManager.getConnection(getUrl(),
PropertiesUtil.deepCopy(TEST_PROPERTIES));
+
+ String dataTableDDL = String.format(DATA_DDL,dataTableName);
+
+ conn.createStatement().execute(dataTableDDL);
+
+
conn.createStatement().execute(String.format(SIMPLE_DDL,tableName));
+
+ conn.commit();
+
+ String upsertDML = String.format("UPSERT INTO %s VALUES(?,?,?,?)",
dataTableName);
+
+ int nRows = 0;
+ PreparedStatement ps = conn.prepareStatement(upsertDML);
+ for (int k1 = 0; k1 < 4; k1++) {
+ ps.setInt(1, k1);
+ for (int k2 = 0; k2 < 4; k2++) {
+ ps.setInt(2, k2);
+ for (int k3 = 0; k3 < 4; k3++) {
+ ps.setInt(3, k3);
+ ps.setInt(4, nRows);
+ int result = ps.executeUpdate();
+ assertEquals(1, result);
+ nRows++;
+ }
+ }
+ }
+ conn.commit();
+
+ String createIndex = "CREATE INDEX IF NOT EXISTS " + indexName + "
ON " + tableName + " (k2 DESC,k1)";
+ conn.createStatement().execute(createIndex);
+
+
+ String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
dataIndexName + " ON " + dataTableName + " (k2 DESC,k1)";
+ conn.createStatement().execute(createDataIndex);
+ initialized = true;
+
+ conn.commit();
+ }
+ }
+
+ // Test RVC Offset columns must be coercible to a base table
+ @Test
+ public void testRVCOffsetNotCoercible() throws SQLException {
+ String failureSql = String.format("SELECT t_id, k1, k2 FROM %s OFFSET
(t_id, k1, k2)=('a', 'ab', 2)", tableName);
+ try (Statement statement = conn.createStatement()){
+ statement.execute(failureSql);
+ } catch (Exception e) {
+ System.out.println(e.getMessage());
+ return;
+ }
+ fail("Should not allow non coercible values to PK in RVC Offset");
+ }
+
+ // Test Order By Not PK Order By Exception
+ @Test
+ public void testRVCOffsetNotAllowNonPKOrderBy() throws SQLException {
+ String failureSql = String.format("SELECT t_id, k1, k2, v1 FROM %s
ORDER BY v1 OFFSET (t_id, k1, k2)=('a', 1, 2)", tableName);
+ try {
+ conn.createStatement().execute(failureSql);
+ } catch (Exception e) {
+ System.out.println(e.getMessage());
+ return;
+ }
+ fail("Should not allow no PK order by with RVC Offset");
+ }
+
+ // Test Order By Partial PK Order By Exception
+ @Test
+ public void testRVCOffsetNotAllowPartialPKOrderBy() throws SQLException {
+ String failureSql = String.format("SELECT t_id, k1, k2 FROM %s ORDER
BY k1 OFFSET (t_id, k1, k2)=('a', 1, 2)", tableName);
+ try {
+ conn.createStatement().execute(failureSql);
+ } catch (Exception e) {
+ assertEquals("Do not allow non-pk ORDER BY with RVC OFFSET",
e.getMessage());
+ return;
+ }
+ fail("Should not allow partial PK order by with RVC Offset");
+ }
+
+ // Test Order By Not PK Order By Exception
+ @Test
+ public void testRVCOffsetNotAllowDifferentPKOrderBy() throws SQLException {
+ String failureSql = String.format("SELECT t_id, k1, k2 FROM %s ORDER
BY t_id DESC, k1, k2 OFFSET (k1,k2,k3)=('a', 1, 2)",
+ tableName);
+ try {
+ conn.createStatement().execute(failureSql);
+ } catch (Exception e) {
+ System.out.println(e.getMessage());
+ return;
+ }
+ fail("Should not allow different PK order by with RVC Offset");
+ }
+
+ // Test Not allow joins
+ @Test
+ public void testRVCOffsetNotAllowedInJoins() throws SQLException {
+ String tableName2 = "T_" + generateUniqueName();
+ createTestTable(getUrl(), String.format(SIMPLE_DDL,tableName2));
+
+ String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (k1,k2,k3)=('a', 1, 2)",
+ tableName, tableName2); // literal works
+ try {
+ conn.createStatement().execute(failureSql);
+ } catch (Exception e) {
+ System.out.println(e.getMessage());
+ return;
+ }
+ fail("Should not have JOIN in RVC Offset");
+ }
+
+ // Test Not allowed in subsquery
+ @Test
+ public void testRVCOffsetNotAllowedInSubQuery() throws SQLException {
+ String failureSql = String.format("SELECT B.k2 FROM (SELECT t_id, k2
FROM %s OFFSET (k1,k2,k3)=('a', 1, 2)) AS B", tableName);
+ try {
+ conn.createStatement().execute(failureSql);
+ } catch (Exception e) {
Review comment:
ditto
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
