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 2014/03/12 23:40:11 UTC
[30/41] PHOENIX-130 Separate execution of slow (integration) tests
from fast unit tests (GabrielReid)
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryPlanIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryPlanIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryPlanIT.java
new file mode 100644
index 0000000..029cd99
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryPlanIT.java
@@ -0,0 +1,201 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.ATABLE_NAME;
+import static org.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.PTSDB3_NAME;
+import static org.apache.phoenix.util.TestUtil.PTSDB_NAME;
+import static org.junit.Assert.assertEquals;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.util.Map;
+import java.util.Properties;
+
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import com.google.common.collect.Maps;
+
+public class QueryPlanIT extends BaseConnectedQueryIT {
+
+ @BeforeClass
+ public static void doSetup() throws Exception {
+ Map<String,String> props = Maps.newHashMapWithExpectedSize(1);
+ // Override date format so we don't have a bunch of zeros
+ props.put(QueryServices.DATE_FORMAT_ATTRIB, "yyyy-MM-dd");
+ // Must update config before starting server
+ startServer(getUrl(), new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @Test
+ public void testExplainPlan() throws Exception {
+ ensureTableCreated(getUrl(), ATABLE_NAME, getDefaultSplits(getOrganizationId()));
+ ensureTableCreated(getUrl(), PTSDB_NAME, getDefaultSplits(getOrganizationId()));
+ ensureTableCreated(getUrl(), PTSDB3_NAME, getDefaultSplits(getOrganizationId()));
+ String[] queryPlans = new String[] {
+
+ "SELECT host FROM PTSDB3 WHERE host IN ('na1', 'na2','na3')",
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 3 KEYS OVER PTSDB3 [~'na3'] - [~'na1']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+
+ "SELECT host FROM PTSDB WHERE inst IS NULL AND host IS NOT NULL AND date >= to_date('2013-01-01')",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER PTSDB [null,not null]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY AND DATE >= '2013-01-01 00:00:00.000'",
+
+ // Since inst IS NOT NULL is unbounded, we won't continue optimizing
+ "SELECT host FROM PTSDB WHERE inst IS NOT NULL AND host IS NULL AND date >= to_date('2013-01-01')",
+ "CLIENT PARALLEL 4-WAY RANGE SCAN OVER PTSDB [not null]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY AND (HOST IS NULL AND DATE >= '2013-01-01 00:00:00.000')",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' AND entity_id = '000000000000002' AND x_integer = 2 AND a_integer < 5 ",
+ "CLIENT PARALLEL 1-WAY POINT LOOKUP ON 1 KEY OVER ATABLE\n" +
+ " SERVER FILTER BY (X_INTEGER = 2 AND A_INTEGER < 5)",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' AND entity_id > '000000000000002' AND entity_id < '000000000000008' AND (organization_id,entity_id) >= ('000000000000001','000000000000005') ",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001','000000000000005'] - ['000000000000001','000000000000008']",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' AND entity_id > '000000000000002' AND entity_id < '000000000000008' AND (organization_id,entity_id) <= ('000000000000001','000000000000005') ",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001','000000000000003'] - ['000000000000001','000000000000006']",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id > '000000000000001' AND entity_id > '000000000000002' AND entity_id < '000000000000008' AND (organization_id,entity_id) >= ('000000000000003','000000000000005') ",
+ "CLIENT PARALLEL 4-WAY RANGE SCAN OVER ATABLE ['000000000000003','000000000000005'] - [*]\n" +
+ " SERVER FILTER BY (ENTITY_ID > '000000000000002' AND ENTITY_ID < '000000000000008')",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' AND entity_id >= '000000000000002' AND entity_id < '000000000000008' AND (organization_id,entity_id) >= ('000000000000000','000000000000005') ",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001','000000000000002'] - ['000000000000001','000000000000008']",
+
+ "SELECT * FROM atable",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE",
+
+ "SELECT inst,host FROM PTSDB WHERE REGEXP_SUBSTR(INST, '[^-]+', 1) IN ('na1', 'na2','na3')", // REVIEW: should this use skip scan given the regexpr_substr
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 3 RANGES OVER PTSDB ['na1'] - ['na4']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY AND REGEXP_SUBSTR(INST, '[^-]+', 1) IN ('na1','na2','na3')",
+
+ "SELECT inst,host FROM PTSDB WHERE inst IN ('na1', 'na2','na3') AND host IN ('a','b') AND date >= to_date('2013-01-01') AND date < to_date('2013-01-02')",
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 6 RANGES OVER PTSDB ['na1','a','2013-01-01'] - ['na3','b','2013-01-02']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+
+ "SELECT inst,host FROM PTSDB WHERE inst LIKE 'na%' AND host IN ('a','b') AND date >= to_date('2013-01-01') AND date < to_date('2013-01-02')",
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 RANGES OVER PTSDB ['na','a','2013-01-01'] - ['nb','b','2013-01-02']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+
+ "SELECT count(*) FROM atable",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO SINGLE ROW",
+
+ // TODO: review: why does this change with parallelized non aggregate queries?
+ "SELECT count(*) FROM atable WHERE organization_id='000000000000001' AND SUBSTR(entity_id,1,3) > '002' AND SUBSTR(entity_id,1,3) <= '003'",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001','003'] - ['000000000000001','004']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO SINGLE ROW",
+
+ "SELECT a_string FROM atable WHERE organization_id='000000000000001' AND SUBSTR(entity_id,1,3) > '002' AND SUBSTR(entity_id,1,3) <= '003'",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001','003'] - ['000000000000001','004']",
+
+ "SELECT count(1) FROM atable GROUP BY a_string",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING]\n" +
+ "CLIENT MERGE SORT",
+
+ "SELECT count(1) FROM atable GROUP BY a_string LIMIT 5",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT 5 ROW LIMIT",
+
+ "SELECT a_string FROM atable ORDER BY a_string DESC LIMIT 3",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+ " SERVER TOP 3 ROWS SORTED BY [A_STRING DESC]\n" +
+ "CLIENT MERGE SORT",
+
+ "SELECT count(1) FROM atable GROUP BY a_string,b_string HAVING max(a_string) = 'a'",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING, B_STRING]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT FILTER BY MAX(A_STRING) = 'a'",
+
+ "SELECT count(1) FROM atable WHERE a_integer = 1 GROUP BY ROUND(a_time,'HOUR',2),entity_id HAVING max(a_string) = 'a'",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+ " SERVER FILTER BY A_INTEGER = 1\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [ENTITY_ID, ROUND(A_TIME)]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT FILTER BY MAX(A_STRING) = 'a'",
+
+ "SELECT count(1) FROM atable WHERE a_integer = 1 GROUP BY a_string,b_string HAVING max(a_string) = 'a' ORDER BY b_string",
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+ " SERVER FILTER BY A_INTEGER = 1\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING, B_STRING]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT FILTER BY MAX(A_STRING) = 'a'\n" +
+ "CLIENT SORTED BY [B_STRING]",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' AND entity_id != '000000000000002' AND x_integer = 2 AND a_integer < 5 LIMIT 10",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" +
+ " SERVER FILTER BY (ENTITY_ID != '000000000000002' AND X_INTEGER = 2 AND A_INTEGER < 5)\n" +
+ " SERVER 10 ROW LIMIT\n" +
+ "CLIENT 10 ROW LIMIT",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' ORDER BY a_string ASC NULLS FIRST LIMIT 10",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" +
+ " SERVER TOP 10 ROWS SORTED BY [A_STRING]\n" +
+ "CLIENT MERGE SORT",
+
+ "SELECT max(a_integer) FROM atable WHERE organization_id = '000000000000001' GROUP BY organization_id,entity_id,ROUND(a_date,'HOUR') ORDER BY entity_id NULLS LAST LIMIT 10",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [ORGANIZATION_ID, ENTITY_ID, ROUND(A_DATE)]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT TOP 10 ROWS SORTED BY [ENTITY_ID NULLS LAST]",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' ORDER BY a_string DESC NULLS LAST LIMIT 10",
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" +
+ " SERVER TOP 10 ROWS SORTED BY [A_STRING DESC NULLS LAST]\n" +
+ "CLIENT MERGE SORT",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id IN ('000000000000001', '000000000000005')",
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER ATABLE ['000000000000001'] - ['000000000000005']",
+
+ "SELECT a_string,b_string FROM atable WHERE organization_id IN ('00D000000000001', '00D000000000005') AND entity_id IN('00E00000000000X','00E00000000000Z')",
+ "CLIENT PARALLEL 1-WAY POINT LOOKUP ON 4 KEYS OVER ATABLE",
+ };
+ for (int i = 0; i < queryPlans.length; i+=2) {
+ String query = queryPlans[i];
+ String plan = queryPlans[i+1];
+ Properties props = new Properties();
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ Statement statement = conn.createStatement();
+ ResultSet rs = statement.executeQuery("EXPLAIN " + query);
+ // TODO: figure out a way of verifying that query isn't run during explain execution
+ assertEquals(query, plan, QueryUtil.getExplainPlan(rs));
+ } catch (Exception e) {
+ throw new Exception(query + ": "+ e.getMessage(), e);
+ } finally {
+ conn.close();
+ }
+ }
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReadIsolationLevelIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReadIsolationLevelIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReadIsolationLevelIT.java
new file mode 100644
index 0000000..e53583e
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReadIsolationLevelIT.java
@@ -0,0 +1,150 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.*;
+import static org.junit.Assert.*;
+
+import java.sql.*;
+import java.util.Properties;
+
+import org.junit.Test;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+
+
+public class ReadIsolationLevelIT extends BaseClientManagedTimeIT {
+ private static final String ENTITY_ID1= "000000000000001";
+ private static final String ENTITY_ID2= "000000000000002";
+ private static final String VALUE1 = "a";
+ private static final String VALUE2= "b";
+
+ protected static void initTableValues(long ts, byte[][] splits) throws Exception {
+ String tenantId = getOrganizationId();
+ ensureTableCreated(getUrl(),ATABLE_NAME,splits, ts-2);
+
+ Properties props = new Properties();
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+ Connection upsertConn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ // Insert all rows at ts
+ PreparedStatement stmt = upsertConn.prepareStatement(
+ "upsert into ATABLE VALUES (?, ?, ?)");
+ stmt.setString(1, tenantId);
+ stmt.setString(2, ENTITY_ID1);
+ stmt.setString(3, VALUE1);
+ stmt.execute(); // should commit too
+
+ stmt.setString(2, ENTITY_ID2);
+ stmt.setString(3, VALUE2);
+ stmt.execute(); // should commit too
+
+ upsertConn.commit();
+ upsertConn.close();
+ }
+
+ @Test
+ public void testStatementReadIsolationLevel() throws Exception {
+ long ts = nextTimestamp();
+ initTableValues(ts, null);
+ String query = "SELECT A_STRING FROM ATABLE WHERE ORGANIZATION_ID=? AND ENTITY_ID=?";
+ Properties props = new Properties();
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts+1));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ conn.setAutoCommit(true);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts+2));
+ Connection conn2 = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts+1));
+ Connection conn3 = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ String tenantId = getOrganizationId();
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ statement.setString(2, ENTITY_ID1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(VALUE1, rs.getString(1));
+ assertFalse(rs.next());
+
+ // Locate existing row and reset one of it's KVs.
+ // Insert all rows at ts
+ PreparedStatement stmt = conn.prepareStatement("upsert into ATABLE VALUES (?, ?, ?)");
+ stmt.setString(1, tenantId);
+ stmt.setString(2, ENTITY_ID1);
+ stmt.setString(3, VALUE2);
+ stmt.execute();
+
+ PreparedStatement statement2 = conn2.prepareStatement(query);
+ statement2.setString(1, tenantId);
+ statement2.setString(2, ENTITY_ID1);
+ // Run another query through same connection and make sure
+ // you can find the new row
+ rs = statement2.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(VALUE2, rs.getString(1));
+ assertFalse(rs.next());
+
+ PreparedStatement statement3 = conn3.prepareStatement(query);
+ statement3.setString(1, tenantId);
+ statement3.setString(2, ENTITY_ID1);
+ rs = statement3.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(VALUE1, rs.getString(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ conn2.close();
+ }
+ }
+
+ @Test
+ public void testConnectionReadIsolationLevel() throws Exception {
+ long ts = nextTimestamp();
+ initTableValues(ts, null);
+ String query = "SELECT A_STRING FROM ATABLE WHERE ORGANIZATION_ID=? AND ENTITY_ID=?";
+ String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts+1);
+ Connection conn = DriverManager.getConnection(url, TEST_PROPERTIES);
+ conn.setAutoCommit(true);
+ try {
+ String tenantId = getOrganizationId();
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ statement.setString(2, ENTITY_ID1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(VALUE1, rs.getString(1));
+ assertFalse(rs.next());
+
+ // Locate existing row and reset one of it's KVs.
+ // Insert all rows at ts
+ PreparedStatement stmt = conn.prepareStatement("upsert into ATABLE VALUES (?, ?, ?)");
+ stmt.setString(1, tenantId);
+ stmt.setString(2, ENTITY_ID1);
+ stmt.setString(3, VALUE2);
+ stmt.execute();
+
+ // Run another query through same connection and make sure
+ // you can't find the new row
+ rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(VALUE1, rs.getString(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReverseFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReverseFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReverseFunctionIT.java
new file mode 100644
index 0000000..f2f675f
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ReverseFunctionIT.java
@@ -0,0 +1,107 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+
+import org.junit.Test;
+
+public class ReverseFunctionIT extends BaseHBaseManagedTimeIT {
+ private void initTable(Connection conn, String sortOrder, String s) throws Exception {
+ String ddl = "CREATE TABLE REVERSE_TEST (pk VARCHAR NOT NULL PRIMARY KEY " + sortOrder + ", kv VARCHAR)";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO REVERSE_TEST VALUES(?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, s);
+ stmt.execute();
+ conn.commit();
+ }
+
+ private void testReverse(Connection conn, String s) throws Exception {
+ StringBuilder buf = new StringBuilder(s);
+ String reverse = buf.reverse().toString();
+
+ ResultSet rs;
+ rs = conn.createStatement().executeQuery("SELECT reverse(pk) FROM REVERSE_TEST");
+ assertTrue(rs.next());
+ assertEquals(reverse, rs.getString(1));
+ assertFalse(rs.next());
+
+ PreparedStatement stmt = conn.prepareStatement("SELECT pk FROM REVERSE_TEST WHERE pk=reverse(?)");
+ stmt.setString(1, reverse);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(s, rs.getString(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testSingleByteReverseAscending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String s = "abc";
+ initTable(conn, "ASC", s);
+ testReverse(conn, s);
+ }
+
+ @Test
+ public void testMultiByteReverseAscending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String s = "ɚɦɰɸ";
+ initTable(conn, "DESC", s);
+ testReverse(conn, s);
+ }
+
+
+ @Test
+ public void testSingleByteReverseDecending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String s = "abc";
+ initTable(conn, "DESC", s);
+ testReverse(conn, s);
+ }
+
+ @Test
+ public void testMultiByteReverseDecending() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String s = "ɚɦɰɸ";
+ initTable(conn, "ASC", s);
+ testReverse(conn, s);
+ }
+
+ @Test
+ public void testNullReverse() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String s = "abc";
+ initTable(conn, "ASC", s);
+
+ ResultSet rs;
+ rs = conn.createStatement().executeQuery("SELECT reverse(kv) FROM REVERSE_TEST");
+ assertTrue(rs.next());
+ assertNull(rs.getString(1));
+ assertFalse(rs.next());
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFunctionsEnd2EndIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFunctionsEnd2EndIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFunctionsEnd2EndIT.java
new file mode 100644
index 0000000..0f3953f
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RoundFloorCeilFunctionsEnd2EndIT.java
@@ -0,0 +1,344 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.closeStmtAndConn;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Time;
+import java.sql.Timestamp;
+
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import org.apache.phoenix.expression.function.CeilFunction;
+import org.apache.phoenix.expression.function.FloorFunction;
+import org.apache.phoenix.expression.function.RoundFunction;
+import org.apache.phoenix.util.DateUtil;
+
+/**
+ *
+ * End to end tests for {@link RoundFunction}, {@link FloorFunction}, {@link CeilFunction}
+ *
+ *
+ * @since 3.0.0
+ */
+public class RoundFloorCeilFunctionsEnd2EndIT extends BaseClientManagedTimeIT {
+
+ private static long millisPart = 660;
+ private static int nanosPart = 500100;
+ private static BigDecimal decimalUpserted = BigDecimal.valueOf(1.264);
+
+ @BeforeClass
+ public static void initTable() throws Exception {
+ String testString = "abc";
+ Connection conn = null;
+ PreparedStatement stmt = null;
+ try {
+ conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS ROUND_DATE_TIME_TS_DECIMAL (s VARCHAR NOT NULL PRIMARY KEY, dt DATE, t TIME, ts TIMESTAMP, dec DECIMAL)";
+ conn.createStatement().execute(ddl);
+
+ Date dateUpserted = DateUtil.parseDate("2012-01-01 14:25:28");
+ dateUpserted = new Date(dateUpserted.getTime() + millisPart); // this makes the dateUpserted equivalent to 2012-01-01 14:25:28.660
+ long millis = dateUpserted.getTime();
+
+ Time timeUpserted = new Time(millis);
+ Timestamp tsUpserted = DateUtil.getTimestamp(millis, nanosPart);
+
+ stmt = conn.prepareStatement("UPSERT INTO ROUND_DATE_TIME_TS_DECIMAL VALUES (?, ?, ?, ?, ?)");
+ stmt.setString(1, testString);
+ stmt.setDate(2, dateUpserted);
+ stmt.setTime(3, timeUpserted);
+ stmt.setTimestamp(4, tsUpserted);
+ stmt.setBigDecimal(5, decimalUpserted);
+ stmt.executeUpdate();
+ conn.commit();
+ } finally {
+ closeStmtAndConn(stmt, conn);
+ }
+ }
+
+ @Test
+ public void testRoundingUpDate() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT ROUND(dt, 'day'), ROUND(dt, 'hour', 1), ROUND(dt, 'minute', 1), ROUND(dt, 'second', 1), ROUND(dt, 'millisecond', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Date expectedDate = DateUtil.parseDate("2012-01-02 00:00:00");
+ assertEquals(expectedDate, rs.getDate(1));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:00:00");
+ assertEquals(expectedDate, rs.getDate(2));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:00");
+ assertEquals(expectedDate, rs.getDate(3));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:29");
+ assertEquals(expectedDate, rs.getDate(4));
+ }
+
+ @Test
+ public void testRoundingUpDateInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE ROUND(dt, 'day') = to_date('2012-01-02 00:00:00')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testFloorDate() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT FLOOR(dt, 'day', 1), FLOOR(dt, 'hour', 1), FLOOR(dt, 'minute', 1), FLOOR(dt, 'second', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Date expectedDate = DateUtil.parseDate("2012-01-01 00:00:00");
+ assertEquals(expectedDate, rs.getDate(1));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:00:00");
+ assertEquals(expectedDate, rs.getDate(2));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:00");
+ assertEquals(expectedDate, rs.getDate(3));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:28");
+ assertEquals(expectedDate, rs.getDate(4));
+ }
+
+ @Test
+ public void testFloorDateInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE FLOOR(dt, 'hour') = to_date('2012-01-01 14:00:00')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testCeilDate() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT CEIL(dt, 'day', 1), CEIL(dt, 'hour', 1), CEIL(dt, 'minute', 1), CEIL(dt, 'second', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ //Date upserted is 2012-01-01 14:25:28.660. So we will end up bumping up in every case.
+ Date expectedDate = DateUtil.parseDate("2012-01-02 00:00:00");
+ assertEquals(expectedDate, rs.getDate(1));
+ expectedDate = DateUtil.parseDate("2012-01-01 15:00:00");
+ assertEquals(expectedDate, rs.getDate(2));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:26:00");
+ assertEquals(expectedDate, rs.getDate(3));
+ expectedDate = DateUtil.parseDate("2012-01-01 14:25:29");
+ assertEquals(expectedDate, rs.getDate(4));
+ }
+
+ @Test
+ public void testCeilDateInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE CEIL(dt, 'second') = to_date('2012-01-01 14:25:29')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testRoundingUpTimestamp() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT ROUND(ts, 'day'), ROUND(ts, 'hour', 1), ROUND(ts, 'minute', 1), ROUND(ts, 'second', 1), ROUND(ts, 'millisecond', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Timestamp expectedTimestamp;
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(1));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(2));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(3));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(4));
+
+ // Rounding of "2012-01-01 14:25:28.660" + nanosPart will end up bumping up the millisecond part of date.
+ // That is, it should be evaluated as "2012-01-01 14:25:28.661".
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime() + millisPart + 1);
+ assertEquals(expectedTimestamp, rs.getTimestamp(5));
+ }
+
+ @Test
+ public void testRoundingUpTimestampInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE ROUND(ts, 'second') = to_date('2012-01-01 14:25:29')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testFloorTimestamp() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT FLOOR(ts, 'day'), FLOOR(ts, 'hour', 1), FLOOR(ts, 'minute', 1), FLOOR(ts, 'second', 1), FLOOR(ts, 'millisecond', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Timestamp expectedTimestamp;
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(1));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(2));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(3));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(4));
+
+ // FLOOR of "2012-01-01 14:25:28.660" + nanosPart will end up removing the nanos part.
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime() + millisPart);
+ assertEquals(expectedTimestamp, rs.getTimestamp(5));
+ }
+
+ @Test
+ public void testFloorTimestampInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE FLOOR(ts, 'second') = to_date('2012-01-01 14:25:28')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testCeilTimestamp() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT CEIL(ts, 'day'), CEIL(ts, 'hour', 1), CEIL(ts, 'minute', 1), CEIL(ts, 'second', 1), CEIL(ts, 'millisecond', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Timestamp expectedTimestamp;
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(1));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 15:00:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(2));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:26:00").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(3));
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTimestamp, rs.getTimestamp(4));
+
+ // CEIL of "2012-01-01 14:25:28.660" + nanosPart will end up bumping up the millisecond part of date.
+ // That is, it should be evaluated as "2012-01-01 14:25:28.661".
+ expectedTimestamp = new Timestamp(DateUtil.parseDate("2012-01-01 14:25:28").getTime() + millisPart + 1);
+ assertEquals(expectedTimestamp, rs.getTimestamp(5));
+ }
+
+ @Test
+ public void testCeilTimestampInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE CEIL(ts, 'second') = to_date('2012-01-01 14:25:29')");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testRoundingUpTime() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT ROUND(t, 'day', 1), ROUND(t, 'hour', 1), ROUND(t, 'minute', 1), ROUND(t, 'second', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Time expectedTime = new Time(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(1));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(2));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTime, rs.getTime(3));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTime, rs.getTime(4));
+ }
+
+ @Test
+ public void testFloorTime() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT FLOOR(t, 'day', 1), FLOOR(t, 'hour', 1), FLOOR(t, 'minute', 1), FLOOR(t, 'second', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Time expectedTime = new Time(DateUtil.parseDate("2012-01-01 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(1));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(2));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:00").getTime());
+ assertEquals(expectedTime, rs.getTime(3));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:28").getTime());
+ assertEquals(expectedTime, rs.getTime(4));
+ }
+
+ @Test
+ public void testCeilTime() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT CEIL(t, 'day', 1), CEIL(t, 'hour', 1), CEIL(t, 'minute', 1), CEIL(t, 'second', 1) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ Time expectedTime = new Time(DateUtil.parseDate("2012-01-02 00:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(1));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 15:00:00").getTime());
+ assertEquals(expectedTime, rs.getTime(2));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:26:00").getTime());
+ assertEquals(expectedTime, rs.getTime(3));
+ expectedTime = new Time(DateUtil.parseDate("2012-01-01 14:25:29").getTime());
+ assertEquals(expectedTime, rs.getTime(4));
+ }
+
+ @Test
+ public void testRoundingUpDecimal() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT ROUND(dec), ROUND(dec, 1), ROUND(dec, 2), ROUND(dec, 3) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ BigDecimal expectedBd = BigDecimal.valueOf(1);
+ assertEquals(expectedBd, rs.getBigDecimal(1));
+ expectedBd = BigDecimal.valueOf(1.3);
+ assertEquals(expectedBd, rs.getBigDecimal(2));
+ expectedBd = BigDecimal.valueOf(1.26);
+ assertEquals(expectedBd, rs.getBigDecimal(3));
+ expectedBd = BigDecimal.valueOf(1.264);
+ assertEquals(expectedBd, rs.getBigDecimal(4));
+ }
+
+ @Test
+ public void testRoundingUpDecimalInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE ROUND(dec, 2) = 1.26");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testFloorDecimal() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT FLOOR(dec), FLOOR(dec, 1), FLOOR(dec, 2), FLOOR(dec, 3) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ BigDecimal expectedBd = BigDecimal.valueOf(1);
+ assertEquals(expectedBd, rs.getBigDecimal(1));
+ expectedBd = BigDecimal.valueOf(1.2);
+ assertEquals(expectedBd, rs.getBigDecimal(2));
+ expectedBd = BigDecimal.valueOf(1.26);
+ assertEquals(expectedBd, rs.getBigDecimal(3));
+ expectedBd = BigDecimal.valueOf(1.264);
+ assertEquals(expectedBd, rs.getBigDecimal(4));
+ }
+
+ @Test
+ public void testFloorDecimalInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE FLOOR(dec, 2) = 1.26");
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testCeilDecimal() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT CEIL(dec), CEIL(dec, 1), CEIL(dec, 2), CEIL(dec, 3) FROM ROUND_DATE_TIME_TS_DECIMAL");
+ assertTrue(rs.next());
+ BigDecimal expectedBd = BigDecimal.valueOf(2);
+ assertEquals(expectedBd, rs.getBigDecimal(1));
+ expectedBd = BigDecimal.valueOf(1.3);
+ assertEquals(expectedBd, rs.getBigDecimal(2));
+ expectedBd = BigDecimal.valueOf(1.27);
+ assertEquals(expectedBd, rs.getBigDecimal(3));
+ expectedBd = BigDecimal.valueOf(1.264);
+ assertEquals(expectedBd, rs.getBigDecimal(4));
+ }
+
+ @Test
+ public void testCeilDecimalInWhere() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM ROUND_DATE_TIME_TS_DECIMAL WHERE CEIL(dec, 2) = 1.27");
+ assertTrue(rs.next());
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
new file mode 100644
index 0000000..c7a5344
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
@@ -0,0 +1,868 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.ENTITYHISTID1;
+import static org.apache.phoenix.util.TestUtil.ENTITYHISTID3;
+import static org.apache.phoenix.util.TestUtil.ENTITYHISTID7;
+import static org.apache.phoenix.util.TestUtil.ENTITYHISTIDS;
+import static org.apache.phoenix.util.TestUtil.ENTITY_HISTORY_SALTED_TABLE_NAME;
+import static org.apache.phoenix.util.TestUtil.ENTITY_HISTORY_TABLE_NAME;
+import static org.apache.phoenix.util.TestUtil.PARENTID1;
+import static org.apache.phoenix.util.TestUtil.PARENTID3;
+import static org.apache.phoenix.util.TestUtil.PARENTID7;
+import static org.apache.phoenix.util.TestUtil.PARENTIDS;
+import static org.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.ROW1;
+import static org.apache.phoenix.util.TestUtil.ROW2;
+import static org.apache.phoenix.util.TestUtil.ROW3;
+import static org.apache.phoenix.util.TestUtil.ROW4;
+import static org.apache.phoenix.util.TestUtil.ROW5;
+import static org.apache.phoenix.util.TestUtil.ROW6;
+import static org.apache.phoenix.util.TestUtil.ROW7;
+import static org.apache.phoenix.util.TestUtil.ROW8;
+import static org.apache.phoenix.util.TestUtil.ROW9;
+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 java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Timestamp;
+import java.util.Properties;
+
+import org.apache.phoenix.util.DateUtil;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.Test;
+
+public class RowValueConstructorIT extends BaseClientManagedTimeIT {
+
+ @Test
+ public void testRowValueConstructorInWhereWithEqualsExpression() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND (a_integer, x_integer) = (7, 5)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ assertTrue(rs.getInt(1) == 7);
+ assertTrue(rs.getInt(2) == 5);
+ count++;
+ }
+ assertTrue(count == 1);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorInWhereWithGreaterThanExpression() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND (a_integer, x_integer) >= (4, 4)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ assertTrue(rs.getInt(1) >= 4);
+ assertTrue(rs.getInt(1) == 4 ? rs.getInt(2) >= 4 : rs.getInt(2) >= 0);
+ count++;
+ }
+ // we have 6 values for a_integer present in the atable where a >= 4. x_integer is null for a_integer = 4. So the query should have returned 5 rows.
+ assertTrue(count == 5);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorInWhereWithUnEqualNumberArgs() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND (a_integer, x_integer, y_integer) >= (7, 5)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ assertTrue(rs.getInt(1) >= 7);
+ assertTrue(rs.getInt(1) == 7 ? rs.getInt(2) >= 5 : rs.getInt(2) >= 0);
+ count++;
+ }
+ // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records.
+ assertTrue(count == 3);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testBindVarsInRowValueConstructor() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND (a_integer, x_integer) = (?, ?)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ statement.setInt(2, 7);
+ statement.setInt(3, 5);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ assertTrue(rs.getInt(1) == 7);
+ assertTrue(rs.getInt(2) == 5);
+ count++;
+ }
+ assertTrue(count == 1);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorOnLHSAndLiteralExpressionOnRHS() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND (a_integer, x_integer) >= 7";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records.
+ assertTrue(count == 3);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorOnRHSLiteralExpressionOnLHS() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND 7 <= (a_integer, x_integer)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records.
+ assertTrue(count == 3);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorOnLHSBuiltInFunctionOperatingOnIntegerLiteralRHS() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND (a_integer, x_integer) >= to_number('7')";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records.
+ assertEquals(3, count);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorOnRHSWithBuiltInFunctionOperatingOnIntegerLiteralOnLHS() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND to_number('7') <= (a_integer, x_integer)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records.
+ assertEquals(3, count);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorOnLHSWithBuiltInFunctionOperatingOnColumnRefOnRHS() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts - 1);
+ String upsertQuery = "UPSERT INTO aTable(organization_id, entity_id, a_string) values (?, ?, ?)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ conn.setAutoCommit(true);
+ try {
+ PreparedStatement statement = conn.prepareStatement(upsertQuery);
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW1);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW2);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW3);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW4);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW5);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW6);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW7);
+ statement.setString(3, "7");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW8);
+ statement.setString(3, "7");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW9);
+ statement.setString(3, "7");
+ statement.executeUpdate();
+ conn.commit();
+
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+ conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ statement = conn.prepareStatement("select a_string from atable where organization_id = ? and (6, x_integer) <= to_number(a_string)");
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records.
+ assertTrue(count == 3);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRowValueConstructorOnRHSWithBuiltInFunctionOperatingOnColumnRefOnLHS() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts - 1);
+ String upsertQuery = "UPSERT INTO aTable(organization_id, entity_id, a_string) values (?, ?, ?)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ conn.setAutoCommit(true);
+ try {
+ PreparedStatement statement = conn.prepareStatement(upsertQuery);
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW1);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW2);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW3);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW4);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW5);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW6);
+ statement.setString(3, "1");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW7);
+ statement.setString(3, "7");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW8);
+ statement.setString(3, "7");
+ statement.executeUpdate();
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW9);
+ statement.setString(3, "7");
+ statement.executeUpdate();
+ conn.commit();
+
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+ conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ statement = conn.prepareStatement("select a_string from atable where organization_id = ? and to_number(a_string) >= (6, 6)");
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ // we have key values (7,5) (8,4) and (9,3) present in aTable. So the query should return the 3 records.
+ assertTrue(count == 3);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testQueryMoreWithInListRowValueConstructor() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ Date date = new Date(System.currentTimeMillis());
+ initEntityHistoryTableValues(tenantId, getDefaultSplits(tenantId), date, ts);
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+
+ PreparedStatement statement = conn.prepareStatement("select parent_id from " + ENTITY_HISTORY_TABLE_NAME +
+ " WHERE (organization_id, parent_id, created_date, entity_history_id) IN ((?, ?, ?, ?),(?,?,?,?))");
+ statement.setString(1, tenantId);
+ statement.setString(2, PARENTID3);
+ statement.setDate(3, date);
+ statement.setString(4, ENTITYHISTID3);
+ statement.setString(5, tenantId);
+ statement.setString(6, PARENTID7);
+ statement.setDate(7, date);
+ statement.setString(8, ENTITYHISTID7);
+ ResultSet rs = statement.executeQuery();
+
+ assertTrue(rs.next());
+ assertEquals(PARENTID3, rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals(PARENTID7, rs.getString(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testQueryMoreFunctionalityUsingAllPKColsInRowValueConstructor() throws Exception {
+ _testQueryMoreFunctionalityUsingAllPkColsInRowValueConstructor(false);
+ }
+
+ @Test
+ public void testQueryMoreFunctionalityUsingAllPKColsInRowValueConstructor_Salted() throws Exception {
+ _testQueryMoreFunctionalityUsingAllPkColsInRowValueConstructor(true);
+ }
+
+ private void _testQueryMoreFunctionalityUsingAllPkColsInRowValueConstructor(boolean salted) throws Exception, SQLException {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ Date date = new Date(System.currentTimeMillis());
+ if(salted) {
+ initSaltedEntityHistoryTableValues(tenantId, null, date, ts - 1);
+ } else {
+ initEntityHistoryTableValues(tenantId, getDefaultSplits(tenantId), date, ts - 1);
+ }
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+
+ String startingOrgId = tenantId;
+ String startingParentId = PARENTID1;
+ Date startingDate = date;
+ String startingEntityHistId = ENTITYHISTID1;
+ PreparedStatement statement = null;
+ if(salted) {
+ statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + ENTITY_HISTORY_SALTED_TABLE_NAME +
+ " WHERE (organization_id, parent_id, created_date, entity_history_id) > (?, ?, ?, ?) ORDER BY organization_id, parent_id, created_date, entity_history_id LIMIT 3 ");
+ } else {
+ statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + ENTITY_HISTORY_TABLE_NAME +
+ " WHERE (organization_id, parent_id, created_date, entity_history_id) > (?, ?, ?, ?) ORDER BY organization_id, parent_id, created_date, entity_history_id LIMIT 3 ");
+ }
+ statement.setString(1, startingOrgId);
+ statement.setString(2, startingParentId);
+ statement.setDate(3, startingDate);
+ statement.setString(4, startingEntityHistId);
+ ResultSet rs = statement.executeQuery();
+
+ int count = 0;
+ int i = 1;
+ //this loop should work on rows 2, 3, 4.
+ while(rs.next()) {
+ assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
+ assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
+ count++;
+ i++;
+ if(count == 3) {
+ startingOrgId = rs.getString(1);
+ startingParentId = rs.getString(2);
+ startingDate = rs.getDate(3);
+ startingEntityHistId = rs.getString(4);
+ }
+ }
+
+ assertTrue("Number of rows returned: ", count == 3);
+ //We will now use the row 4's pk values for bind variables.
+ statement.setString(1, startingOrgId);
+ statement.setString(2, startingParentId);
+ statement.setDate(3, startingDate);
+ statement.setString(4, startingEntityHistId);
+ rs = statement.executeQuery();
+ //this loop now should work on rows 5, 6, 7.
+ while(rs.next()) {
+ assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
+ assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
+ i++;
+ count++;
+ }
+ assertTrue("Number of rows returned: ", count == 6);
+ }
+
+ @Test
+ public void testQueryMoreWithSubsetofPKColsInRowValueConstructor() throws Exception {
+ _testQueryMoreWithSubsetofPKColsInRowValueConstructor(false);
+ }
+
+ @Test
+ public void testQueryMoreWithSubsetofPKColsInRowValueConstructor_salted() throws Exception {
+ _testQueryMoreWithSubsetofPKColsInRowValueConstructor(true);
+ }
+
+ /**
+ * Entity History table has primary keys defined in the order
+ * PRIMARY KEY (organization_id, parent_id, created_date, entity_history_id).
+ * This test uses (organization_id, parent_id, entity_history_id) in RVC and checks if the query more functionality
+ * still works.
+ * @throws Exception
+ */
+ private void _testQueryMoreWithSubsetofPKColsInRowValueConstructor(boolean salted) throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ Date date = new Date(System.currentTimeMillis());
+ if(salted) {
+ initSaltedEntityHistoryTableValues(tenantId, null, date, ts - 1);
+ } else {
+ initEntityHistoryTableValues(tenantId, getDefaultSplits(tenantId), date, ts - 1);
+ }
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+
+ //initial values of pk.
+ String startingOrgId = tenantId;
+ String startingParentId = PARENTID1;
+
+ String startingEntityHistId = ENTITYHISTID1;
+
+ PreparedStatement statement = null;
+ if(salted) {
+ statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + ENTITY_HISTORY_SALTED_TABLE_NAME +
+ " WHERE (organization_id, parent_id, entity_history_id) > (?, ?, ?) ORDER BY organization_id, parent_id, entity_history_id LIMIT 3 ");
+ } else {
+ statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + ENTITY_HISTORY_TABLE_NAME +
+ " WHERE (organization_id, parent_id, entity_history_id) > (?, ?, ?) ORDER BY organization_id, parent_id, entity_history_id LIMIT 3 ");
+ }
+ statement.setString(1, startingOrgId);
+ statement.setString(2, startingParentId);
+ statement.setString(3, startingEntityHistId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ //this loop should work on rows 2, 3, 4.
+ int i = 1;
+ while(rs.next()) {
+ assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
+ assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
+ i++;
+ count++;
+ if(count == 3) {
+ startingOrgId = rs.getString(1);
+ startingParentId = rs.getString(2);
+ startingEntityHistId = rs.getString(4);
+ }
+ }
+ assertTrue("Number of rows returned: " + count, count == 3);
+ //We will now use the row 4's pk values for bind variables.
+ statement.setString(1, startingOrgId);
+ statement.setString(2, startingParentId);
+ statement.setString(3, startingEntityHistId);
+ rs = statement.executeQuery();
+ //this loop now should work on rows 5, 6, 7.
+ while(rs.next()) {
+ assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
+ assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
+ i++;
+ count++;
+ }
+ assertTrue("Number of rows returned: " + count, count == 6);
+ }
+
+ @Test
+ public void testQueryMoreWithLeadingPKColSkippedInRowValueConstructor() throws Exception {
+ _testQueryMoreWithLeadingPKColSkippedInRowValueConstructor(false);
+ }
+
+ @Test
+ public void testQueryMoreWithLeadingPKColSkippedInRowValueConstructor_salted() throws Exception {
+ _testQueryMoreWithLeadingPKColSkippedInRowValueConstructor(true);
+ }
+
+ /**
+ * Entity History table has primary keys defined in the order
+ * PRIMARY KEY (organization_id, parent_id, created_date, entity_history_id).
+ * This test skips the leading column organization_id and uses (parent_id, created_date, entity_history_id) in RVC.
+ * In such a case Phoenix won't be able to optimize the hbase scan. However, the query more functionality
+ * should still work.
+ * @throws Exception
+ */
+ private void _testQueryMoreWithLeadingPKColSkippedInRowValueConstructor(boolean salted) throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ Date date = new Date(System.currentTimeMillis());
+ if(salted) {
+ initSaltedEntityHistoryTableValues(tenantId, null, date, ts - 1);
+ } else {
+ initEntityHistoryTableValues(tenantId, getDefaultSplits(tenantId), date, ts - 1);
+ }
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ String startingParentId = PARENTID1;
+ Date startingDate = date;
+ String startingEntityHistId = ENTITYHISTID1;
+ PreparedStatement statement = null;
+ if(salted) {
+ statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + ENTITY_HISTORY_SALTED_TABLE_NAME +
+ " WHERE (parent_id, created_date, entity_history_id) > (?, ?, ?) ORDER BY parent_id, created_date, entity_history_id LIMIT 3 ");
+ } else {
+ statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + ENTITY_HISTORY_TABLE_NAME +
+ " WHERE (parent_id, created_date, entity_history_id) > (?, ?, ?) ORDER BY parent_id, created_date, entity_history_id LIMIT 3 ");
+ }
+ statement.setString(1, startingParentId);
+ statement.setDate(2, startingDate);
+ statement.setString(3, startingEntityHistId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ //this loop should work on rows 2, 3, 4.
+ int i = 1;
+ while(rs.next()) {
+ assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
+ assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
+ i++;
+ count++;
+ if(count == 3) {
+ startingParentId = rs.getString(2);
+ startingDate = rs.getDate(3);
+ startingEntityHistId = rs.getString(4);
+ }
+ }
+ assertTrue("Number of rows returned: " + count, count == 3);
+ //We will now use the row 4's pk values for bind variables.
+ statement.setString(1, startingParentId);
+ statement.setDate(2, startingDate);
+ statement.setString(3, startingEntityHistId);
+ rs = statement.executeQuery();
+ //this loop now should work on rows 5, 6, 7.
+ while(rs.next()) {
+ assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
+ assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
+ i++;
+ count++;
+ }
+ assertTrue("Number of rows returned: " + count, count == 6);
+ }
+
+ @Test
+ public void testRVCWithNonLeadingPkColsOfTypesIntegerAndString() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, a_string FROM aTable WHERE ?=organization_id AND (a_integer, a_string) <= (5, 'a')";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ //we have 4 rows present in a table with (a_integer, a_string) <= (5, 'a'). All have a_string set to "a".
+ while(rs.next()) {
+ assertTrue(rs.getInt(1) <= 5);
+ assertTrue(rs.getString(2).compareTo("a") == 0);
+ count++;
+ }
+ assertTrue(count == 4);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRVCWithNonLeadingPkColsOfTypesTimeStampAndString() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String updateStmt =
+ "upsert into " +
+ "ATABLE(" +
+ " ORGANIZATION_ID, " +
+ " ENTITY_ID, " +
+ " A_TIMESTAMP) " +
+ "VALUES (?, ?, ?)";
+ String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 1);
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection upsertConn = DriverManager.getConnection(url, props);
+ upsertConn.setAutoCommit(true);
+ PreparedStatement stmt = upsertConn.prepareStatement(updateStmt);
+ stmt.setString(1, tenantId);
+ stmt.setString(2, ROW4);
+ Timestamp tsValue = new Timestamp(System.nanoTime());
+ stmt.setTimestamp(3, tsValue);
+ stmt.execute();
+
+ String query = "SELECT a_timestamp, a_string FROM aTable WHERE ?=organization_id AND (a_timestamp, a_string) = (?, 'a')";
+ props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ statement.setTimestamp(2, tsValue);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ assertTrue(rs.getTimestamp(1).equals(tsValue));
+ assertTrue(rs.getString(2).compareTo("a") == 0);
+ count++;
+ }
+ assertTrue(count == 1);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testNestedRVCBasic() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ //all the three queries should return the same rows.
+ String[] queries = {"SELECT organization_id, entity_id, a_string FROM aTable WHERE ((organization_id, entity_id), a_string) >= ((?, ?), ?)",
+ "SELECT organization_id, entity_id, a_string FROM aTable WHERE (organization_id, entity_id, a_string) >= (?, ?, ?)",
+ "SELECT organization_id, entity_id, a_string FROM aTable WHERE (organization_id, (entity_id, a_string)) >= (?, (?, ?))"
+ };
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ PreparedStatement statement = null;
+ try {
+ try {
+ for (int i = 0; i <=2; i++) {
+ statement = conn.prepareStatement(queries[i]);
+ statement.setString(1, tenantId);
+ statement.setString(2, ROW1);
+ statement.setString(3, "a");
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ assertEquals(9, count);
+ }
+ } finally {
+ if(statement != null) {
+ statement.close();
+ }
+ }
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRVCWithInListClausePossibleNullValues() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ //we have a row present in aTable where x_integer = 5 and y_integer = NULL which gets translated to 0 when retriving from HBase.
+ String query = "SELECT x_integer, y_integer FROM aTable WHERE ? = organization_id AND (x_integer, y_integer) IN ((5))";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals(0, rs.getInt(2));
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRVCWithInListClauseUsingSubsetOfPKColsInOrder() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ // Though we have a row present in aTable where organization_id = tenantId and x_integer = 5,
+ // we'd also need to have an entity_id that is null (which we don't have).
+ String query = "SELECT organization_id, entity_id FROM aTable WHERE (organization_id, entity_id) IN (('" + tenantId + "')) AND x_integer = 5";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = null;
+ PreparedStatement statement = null;
+ try {
+ try {
+ conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertFalse(rs.next());
+ } finally {
+ if(statement != null) {
+ statement.close();
+ }
+ }
+ } finally {
+ if(conn != null) {
+ conn.close();
+ }
+ }
+ }
+
+ @Test
+ public void testRVCWithCeilAndFloorNeededForDecimal() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT a_integer, x_integer FROM aTable WHERE ?=organization_id AND (a_integer, x_integer) < (8.6, 4.5) AND (a_integer, x_integer) > (6.8, 4)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ assertEquals(7, rs.getInt(1));
+ assertEquals(5, rs.getInt(2));
+ }
+ assertEquals(1, count);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRVCWithCeilAndFloorNeededForTimestamp() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ Date dateUpserted = DateUtil.parseDate("2012-01-01 14:25:28");
+ dateUpserted = new Date(dateUpserted.getTime() + 660); // this makes the dateUpserted equivalent to 2012-01-01 14:25:28.660
+ initATableValues(tenantId, getDefaultSplits(tenantId), dateUpserted, ts);
+ String query = "SELECT a_integer, a_date FROM aTable WHERE ?=organization_id AND (a_integer, a_date) <= (9, ?) AND (a_integer, a_date) >= (6, ?)";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, tenantId);
+ Timestamp timestampWithNanos = DateUtil.getTimestamp(dateUpserted.getTime() + 2 * TestUtil.MILLIS_IN_DAY, 300);
+ timestampWithNanos.setNanos(0);
+ statement.setTimestamp(2, timestampWithNanos);
+ statement.setTimestamp(3, timestampWithNanos);
+ ResultSet rs = statement.executeQuery();
+ int count = 0;
+ while(rs.next()) {
+ count++;
+ }
+ /*
+ * We have following rows with values for (a_integer, a_date) present:
+ * (7, date), (8, date + TestUtil.MILLIS_IN_DAY), (9, date 2 * TestUtil.MILLIS_IN_DAY) among others present.
+ * Above query should return 3 rows since the CEIL operation on timestamp with non-zero nanoseconds
+ * will bump up the milliseconds. That is (a_integer, a_date) < (6, CEIL(timestampWithNanos)).
+ */
+ assertEquals(3, count);
+ } finally {
+ conn.close();
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java
new file mode 100644
index 0000000..a6fc562
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SaltedViewIT.java
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import org.junit.Test;
+
+public class SaltedViewIT extends BaseViewIT {
+
+ /**
+ * Salted tests must be in their own test file to ensure that the underlying
+ * table is dropped. Otherwise, the splits may not be performed.
+ * TODO: we should throw in that case
+ *
+ * @throws Exception
+ */
+ @Test
+ public void testSaltedUpdatableViewWithIndex() throws Exception {
+ testUpdatableViewWithIndex(3);
+ }
+}