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 22:32:03 UTC
[36/41] PHOENIX-130 Separate execution of slow (integration) tests
from fast unit tests (Gabriel Reid)
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExecuteStatementsIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExecuteStatementsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExecuteStatementsIT.java
new file mode 100644
index 0000000..f9d2e58
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExecuteStatementsIT.java
@@ -0,0 +1,301 @@
+/*
+ * 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.A_VALUE;
+import static org.apache.phoenix.util.TestUtil.BTABLE_NAME;
+import static org.apache.phoenix.util.TestUtil.B_VALUE;
+import static org.apache.phoenix.util.TestUtil.PTSDB_NAME;
+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.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.io.StringReader;
+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.SQLException;
+import java.util.Arrays;
+import java.util.List;
+
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.query.QueryConstants;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.junit.Test;
+
+
+public class ExecuteStatementsIT extends BaseHBaseManagedTimeIT {
+
+ @Test
+ public void testExecuteStatements() throws Exception {
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId));
+ String statements =
+ "create table if not exists " + ATABLE_NAME + // Shouldn't error out b/c of if not exists clause
+ " (organization_id char(15) not null, \n" +
+ " entity_id char(15) not null,\n" +
+ " a_string varchar(100),\n" +
+ " b_string varchar(100)\n" +
+ " CONSTRAINT pk PRIMARY KEY (organization_id,entity_id));\n" +
+ "create table " + PTSDB_NAME +
+ " (inst varchar null,\n" +
+ " host varchar null,\n" +
+ " date date not null,\n" +
+ " val decimal\n" +
+ " CONSTRAINT pk PRIMARY KEY (inst,host,date))\n" +
+ " split on (?,?,?);\n" +
+ "alter table " + PTSDB_NAME + " add if not exists val decimal;\n" + // Shouldn't error out b/c of if not exists clause
+ "alter table " + PTSDB_NAME + " drop column if exists blah;\n" + // Shouldn't error out b/c of if exists clause
+ "drop table if exists FOO.BAR;\n" + // Shouldn't error out b/c of if exists clause
+ "UPSERT INTO " + PTSDB_NAME + "(date, val, host) " +
+ " SELECT current_date(), x_integer+2, entity_id FROM ATABLE WHERE a_integer >= ?;" +
+ "UPSERT INTO " + PTSDB_NAME + "(date, val, inst)\n" +
+ " SELECT date+1, val*10, host FROM " + PTSDB_NAME + ";";
+
+ Date now = new Date(System.currentTimeMillis());
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.setAutoCommit(true);
+ List<Object> binds = Arrays.<Object>asList("a","j","s", 6);
+ int nStatements = PhoenixRuntime.executeStatements(conn, new StringReader(statements), binds);
+ assertEquals(7, nStatements);
+
+ Date then = new Date(System.currentTimeMillis() + QueryConstants.MILLIS_IN_DAY);
+ String query = "SELECT host,inst, date,val FROM " + PTSDB_NAME + " where inst is not null";
+ PreparedStatement statement = conn.prepareStatement(query);
+
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(null, rs.getString(1));
+ assertEquals(ROW6, rs.getString(2));
+ assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then));
+ assertEquals(null, rs.getBigDecimal(4));
+
+ assertTrue (rs.next());
+ assertEquals(null, rs.getString(1));
+ assertEquals(ROW7, rs.getString(2));
+ assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then));
+ assertTrue(BigDecimal.valueOf(70).compareTo(rs.getBigDecimal(4)) == 0);
+
+ assertTrue (rs.next());
+ assertEquals(null, rs.getString(1));
+ assertEquals(ROW8, rs.getString(2));
+ assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then));
+ assertTrue(BigDecimal.valueOf(60).compareTo(rs.getBigDecimal(4)) == 0);
+
+ assertTrue (rs.next());
+ assertEquals(null, rs.getString(1));
+ assertEquals(ROW9, rs.getString(2));
+ assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then));
+ assertTrue(BigDecimal.valueOf(50).compareTo(rs.getBigDecimal(4)) == 0);
+
+ assertFalse(rs.next());
+ conn.close();
+ }
+
+ @Test
+ public void testCharPadding() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String tableName = "foo";
+ String rowKey = "hello";
+ String testString = "world";
+ String query = "create table " + tableName +
+ "(a_id integer not null, \n" +
+ "a_string char(10) not null, \n" +
+ "b_string char(8) not null \n" +
+ "CONSTRAINT my_pk PRIMARY KEY (a_id, a_string))";
+
+
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.execute();
+ statement = conn.prepareStatement(
+ "upsert into " + tableName +
+ " (a_id, " +
+ " a_string, " +
+ " b_string)" +
+ "VALUES (?, ?, ?)");
+ statement.setInt(1, 1);
+ statement.setString(2, rowKey);
+ statement.setString(3, testString);
+ statement.execute();
+ conn.commit();
+
+ ensureTableCreated(getUrl(),BTABLE_NAME, null, nextTimestamp()-2);
+ statement = conn.prepareStatement(
+ "upsert into BTABLE VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
+ statement.setString(1, "abc");
+ statement.setString(2, "xyz");
+ statement.setString(3, "x");
+ statement.setInt(4, 9);
+ statement.setString(5, "ab");
+ statement.setInt(6, 1);
+ statement.setInt(7, 1);
+ statement.setString(8, "ab");
+ statement.setString(9, "morning1");
+ statement.execute();
+ conn.commit();
+ try {
+ // test rowkey and non-rowkey values in select statement
+ query = "select a_string, b_string from " + tableName;
+ assertCharacterPadding(conn.prepareStatement(query), rowKey, testString);
+
+ // test with rowkey in where clause
+ query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = '" + rowKey + "'";
+ assertCharacterPadding(conn.prepareStatement(query), rowKey, testString);
+
+ // test with non-rowkey in where clause
+ query = "select a_string, b_string from " + tableName + " where b_string = '" + testString + "'";
+ assertCharacterPadding(conn.prepareStatement(query), rowKey, testString);
+
+ // test with rowkey and id in where clause
+ query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = '" + rowKey + "'";
+ assertCharacterPadding(conn.prepareStatement(query), rowKey, testString);
+
+ // test with rowkey and id in where clause where rowkey is greater than the length of the char.len
+ query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = '" + rowKey + testString + "'";
+ statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertFalse(rs.next());
+
+ // test with rowkey and id in where clause where rowkey is lesser than the length of the char.len
+ query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = 'he'";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertFalse(rs.next());
+
+ String rowKey2 = "good";
+ String testString2 = "morning";
+ String testString8Char = "morning1";
+ String testString10Char = "morning123";
+ String upsert = "UPSERT INTO " + tableName + " values (2, '" + rowKey2 + "', '" + testString2+ "') ";
+ statement = conn.prepareStatement(upsert);
+ statement.execute();
+ conn.commit();
+
+ // test upsert statement with padding
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, nextTimestamp()-1);
+
+ upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " +
+ "SELECT A_INTEGER, A_STRING, B_STRING FROM ATABLE WHERE a_string = ?";
+
+ statement = conn.prepareStatement(upsert);
+ statement.setString(1, A_VALUE);
+ int rowsInserted = statement.executeUpdate();
+ assertEquals(4, rowsInserted);
+ conn.commit();
+
+ query = "select a_string, b_string from " + tableName + " where a_string = '" + A_VALUE+"'";
+ assertCharacterPadding(conn.prepareStatement(query), A_VALUE, B_VALUE);
+
+ upsert = "UPSERT INTO " + tableName + " values (3, '" + testString2 + "', '" + testString2+ "') ";
+ statement = conn.prepareStatement(upsert);
+ statement.execute();
+ conn.commit();
+ query = "select a_string, b_string from " + tableName + " where a_id = 3 and a_string = b_string";
+ assertCharacterPadding(conn.prepareStatement(query), testString2, testString2);
+
+ // compare a higher length col with lower length : a_string(10), b_string(8)
+ query = "select a_string, b_string from " + tableName + " where a_id = 3 and b_string = a_string";
+ statement = conn.prepareStatement(query);
+ statement.executeQuery();
+ assertCharacterPadding(conn.prepareStatement(query), testString2, testString2);
+
+ upsert = "UPSERT INTO " + tableName + " values (4, '" + rowKey2 + "', '" + rowKey2 + "') ";
+ statement = conn.prepareStatement(upsert);
+ statement.execute();
+ conn.commit();
+
+ // where both the columns have same value with different paddings
+ query = "select a_string, b_string from " + tableName + " where a_id = 4 and b_string = a_string";
+ assertCharacterPadding(conn.prepareStatement(query), rowKey2, rowKey2);
+
+ upsert = "UPSERT INTO " + tableName + " values (5, '" + testString10Char + "', '" + testString8Char + "') ";
+ statement = conn.prepareStatement(upsert);
+ statement.execute();
+ conn.commit();
+
+ // where smaller column is the subset of larger string
+ query = "select a_string, b_string from " + tableName + " where a_id = 5 and b_string = a_string";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertFalse(rs.next());
+
+ //where selecting from a CHAR(x) and upserting into a CHAR(y) where x>y
+ // upsert rowkey value greater than rowkey limit
+ try {
+
+ upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " +
+ "SELECT x_integer, organization_id, b_string FROM ATABLE WHERE a_string = ?";
+
+ statement = conn.prepareStatement(upsert);
+ statement.setString(1, A_VALUE);
+ statement.executeUpdate();
+ fail("Should fail when bigger than expected character is inserted");
+ } catch (SQLException ex) {
+ assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(), ex.getErrorCode());
+ }
+
+ // upsert non-rowkey value greater than its limit
+ try {
+
+ upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " +
+ "SELECT y_integer, a_string, entity_id FROM ATABLE WHERE a_string = ?";
+
+ statement = conn.prepareStatement(upsert);
+ statement.setString(1, A_VALUE);
+ statement.executeUpdate();
+ fail("Should fail when bigger than expected character is inserted");
+ }
+ catch (SQLException ex) {
+ assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(), ex.getErrorCode());
+ }
+
+ //where selecting from a CHAR(x) and upserting into a CHAR(y) where x<=y.
+ upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " +
+ "SELECT a_integer, e_string, a_id FROM BTABLE";
+
+ statement = conn.prepareStatement(upsert);
+ rowsInserted = statement.executeUpdate();
+ assertEquals(1, rowsInserted);
+ conn.commit();
+
+ query = "select a_string, b_string from " + tableName + " where a_string = 'morning1'";
+ assertCharacterPadding(conn.prepareStatement(query), "morning1", "xyz");
+ } finally {
+ conn.close();
+ }
+ }
+
+
+ private void assertCharacterPadding(PreparedStatement statement, String rowKey, String testString) throws SQLException {
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(rowKey, rs.getString(1));
+ assertEquals(testString, rs.getString(2));
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExtendedQueryExecIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExtendedQueryExecIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExtendedQueryExecIT.java
new file mode 100644
index 0000000..b463c74
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ExtendedQueryExecIT.java
@@ -0,0 +1,188 @@
+/*
+ * 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.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.*;
+
+import java.sql.*;
+import java.util.Properties;
+
+import org.junit.Test;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+
+
+
+/**
+ *
+ * Extended tests for Phoenix JDBC implementation
+ *
+ */
+public class ExtendedQueryExecIT extends BaseClientManagedTimeIT {
+
+ @Test
+ public void testToDateFunctionBind() throws Exception {
+ long ts = nextTimestamp();
+ Date date = new Date(1);
+ String tenantId = getOrganizationId();
+
+ initATableValues(tenantId, getDefaultSplits(tenantId),date, ts);
+
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts+1));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ String query = "SELECT a_date FROM atable WHERE organization_id='" + tenantId + "' and a_date < TO_DATE(?)";
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, "1970-1-1 12:00:00");
+ ResultSet rs = statement.executeQuery();
+ verifyDateResultSet(rs, date, 3);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @edu.umd.cs.findbugs.annotations.SuppressWarnings(
+ value="RV_RETURN_VALUE_IGNORED",
+ justification="Test code.")
+ @Test
+ public void testTypeMismatchToDateFunctionBind() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId),null, ts);
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ String query = "SELECT a_date FROM atable WHERE organization_id='" + tenantId + "' and a_date < TO_DATE(?)";
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setDate(1, new Date(2));
+ statement.executeQuery();
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("Type mismatch. expected: [VARCHAR] but was: DATE at TO_DATE"));
+ } finally {
+ conn.close();
+ }
+ }
+
+ /**
+ * Basic tests for date function
+ * Related bug: W-1190856
+ * @throws Exception
+ */
+ @Test
+ public void testDateFunctions() throws Exception {
+ long ts = nextTimestamp();
+ Date date = new Date(1);
+ String tenantId = getOrganizationId();
+
+ initATableValues(tenantId, getDefaultSplits(tenantId),date, ts);
+
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts+1));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ ResultSet rs;
+ String queryPrefix = "SELECT a_date FROM atable WHERE organization_id='" + tenantId + "' and ";
+
+ String queryDateArg = "a_date < TO_DATE('1970-1-1 12:00:00')";
+ rs = getResultSet(conn, queryPrefix + queryDateArg);
+ verifyDateResultSet(rs, date, 3);
+
+ // TODO: Bug #1 - Result should be the same as the the case above
+// queryDateArg = "a_date < TO_DATE('70-1-1 12:0:0')";
+// rs = getResultSet(conn, queryPrefix + queryDateArg);
+// verifyDateResultSet(rs, date, 3);
+
+ // TODO: Bug #2 - Exception should be generated for invalid date/time
+// queryDateArg = "a_date < TO_DATE('999-13-32 24:60:60')";
+// try {
+// getResultSet(conn, queryPrefix + queryDateArg);
+// fail("Expected SQLException");
+// } catch (SQLException ex) {
+// // expected
+// }
+
+ queryDateArg = "a_date >= TO_DATE('1970-1-2 23:59:59') and a_date <= TO_DATE('1970-1-3 0:0:1')";
+ rs = getResultSet(conn, queryPrefix + queryDateArg);
+ verifyDateResultSet(rs, new Date(date.getTime() + (2*60*60*24*1000)), 3);
+
+ } finally {
+ conn.close();
+ }
+ }
+
+ /**
+ * aggregation - group by
+ * @throws Exception
+ */
+ @Test
+ public void testDateGroupBy() throws Exception {
+ long ts = nextTimestamp();
+ Date date = new Date(1);
+ String tenantId = getOrganizationId();
+
+ initATableValues(tenantId, getDefaultSplits(tenantId),date, ts);
+
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts+1));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ ResultSet rs;
+ String query = "SELECT a_date, count(1) FROM atable WHERE organization_id='" + tenantId + "' group by a_date";
+ rs = getResultSet(conn, query);
+
+ /* 3 rows in expected result:
+ * 1969-12-31 3
+ * 1970-01-01 3
+ * 1970-01-02 3
+ * */
+
+ assertTrue(rs.next());
+ assertEquals(date, rs.getDate(1));
+ assertEquals(3, rs.getInt(2));
+
+ // the following assertions fails
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(2));
+ assertFalse(rs.next());
+
+
+ } finally {
+ conn.close();
+ }
+ }
+
+ private ResultSet getResultSet(Connection conn, String query) throws SQLException {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ return rs;
+ }
+
+ private void verifyDateResultSet(ResultSet rs, Date date, int rowCount) throws SQLException {
+ for (int i=0; i<rowCount; i++) {
+ assertTrue(rs.next());
+ assertEquals(date, rs.getDate(1));
+ }
+ assertFalse(rs.next());
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/FunkyNamesIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/FunkyNamesIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/FunkyNamesIT.java
new file mode 100644
index 0000000..ce33e36
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/FunkyNamesIT.java
@@ -0,0 +1,143 @@
+/*
+ * 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.schema.ColumnNotFoundException;
+import org.apache.phoenix.util.PhoenixRuntime;
+
+
+public class FunkyNamesIT extends BaseClientManagedTimeIT {
+
+ protected static void initTableValues(byte[][] splits, long ts) throws Exception {
+ ensureTableCreated(getUrl(),FUNKY_NAME,splits, ts-2);
+
+ String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(url, props);
+ conn.setAutoCommit(true);
+ // Insert all rows at ts
+ PreparedStatement stmt = conn.prepareStatement(
+ "upsert into " +
+ "FUNKY_NAMES(" +
+ " \"foo!\", " +
+ " \"#@$\", " +
+ " \"foo.bar-bas\", " +
+ " \"_blah^\"," +
+ " \"Value\", " +
+ " \"VALUE\", " +
+ " \"value\") " +
+ "VALUES (?, ?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, "a");
+ stmt.setString(2, "b");
+ stmt.setString(3, "c");
+ stmt.setString(4, "d");
+ stmt.setInt(5, 1);
+ stmt.setInt(6, 2);
+ stmt.setInt(7, 3);
+ stmt.executeUpdate();
+ conn.close();
+ }
+
+ @Test
+ public void testUnaliasedFunkyNames() throws Exception {
+ long ts = nextTimestamp();
+ String query = "SELECT \"foo!\",\"#@$\",\"foo.bar-bas\",\"_blah^\" FROM FUNKY_NAMES";
+ String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(url, props);
+ try {
+ initTableValues(null, ts);
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("a", rs.getString(1));
+ assertEquals("b", rs.getString(2));
+ assertEquals("c", rs.getString(3));
+ assertEquals("d", rs.getString(4));
+
+ assertEquals("a", rs.getString("foo!"));
+ assertEquals("b", rs.getString("#@$"));
+ assertEquals("c", rs.getString("foo.bar-bas"));
+ assertEquals("d", rs.getString("_blah^"));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCaseSensitive() throws Exception {
+ long ts = nextTimestamp();
+ String query = "SELECT \"Value\",\"VALUE\",\"value\" FROM FUNKY_NAMES";
+ String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(url, props);
+ try {
+ initTableValues(null, ts);
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(2, rs.getInt(2));
+ assertEquals(3, rs.getInt(3));
+
+ assertEquals(1, rs.getInt("Value"));
+ assertEquals(2, rs.getInt("VALUE"));
+ assertEquals(3, rs.getInt("value"));
+ try {
+ rs.getInt("vAlue");
+ fail();
+ } catch (ColumnNotFoundException e) {
+ }
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testAliasedFunkyNames() throws Exception {
+ long ts = nextTimestamp();
+ String query = "SELECT \"1-3.4$\".\"foo!\" as \"1-2\",\"#@$\" as \"[3]\",\"foo.bar-bas\" as \"$$$\",\"_blah^\" \"0\" FROM FUNKY_NAMES \"1-3.4$\"";
+ String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(url, props);
+ try {
+ initTableValues(null, ts);
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("a", rs.getString("1-2"));
+ assertEquals("b", rs.getString("[3]"));
+ assertEquals("c", rs.getString("$$$"));
+ assertEquals("d", rs.getString("0"));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+}
+
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java
new file mode 100644
index 0000000..91e12f5
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java
@@ -0,0 +1,194 @@
+/*
+ * 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.GROUPBYTEST_NAME;
+import static org.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+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.junit.Test;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+
+
+public class GroupByCaseIT extends BaseClientManagedTimeIT {
+
+ private static String GROUPBY1 = "select " +
+ "case when uri LIKE 'Report%' then 'Reports' else 'Other' END category" +
+ ", avg(appcpu) from " + GROUPBYTEST_NAME +
+ " group by category";
+
+ private static String GROUPBY2 = "select " +
+ "case uri when 'Report%' then 'Reports' else 'Other' END category" +
+ ", avg(appcpu) from " + GROUPBYTEST_NAME +
+ " group by appcpu, category";
+
+ private static String GROUPBY3 = "select " +
+ "case uri when 'Report%' then 'Reports' else 'Other' END category" +
+ ", avg(appcpu) from " + GROUPBYTEST_NAME +
+ " group by avg(appcpu), category";
+
+ private int id;
+
+ private long createTable() throws Exception {
+ long ts = nextTimestamp();
+ ensureTableCreated(getUrl(), GROUPBYTEST_NAME, null, ts-2);
+ return ts;
+ }
+
+ private void loadData(long ts) throws SQLException {
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ insertRow(conn, "Report1", 10);
+ insertRow(conn, "Report2", 10);
+ insertRow(conn, "Report3", 30);
+ insertRow(conn, "Report4", 30);
+ insertRow(conn, "SOQL1", 10);
+ insertRow(conn, "SOQL2", 10);
+ insertRow(conn, "SOQL3", 30);
+ insertRow(conn, "SOQL4", 30);
+ conn.commit();
+ conn.close();
+ }
+
+ private void insertRow(Connection conn, String uri, int appcpu) throws SQLException {
+ PreparedStatement statement = conn.prepareStatement("UPSERT INTO " + GROUPBYTEST_NAME + "(id, uri, appcpu) values (?,?,?)");
+ statement.setString(1, "id" + id);
+ statement.setString(2, uri);
+ statement.setInt(3, appcpu);
+ statement.executeUpdate();
+ id++;
+ }
+
+ /*
+ @Test
+ public void testGroupByCaseWithIndex() throws Exception {
+ Connection conn;
+ Properties props = new Properties(TEST_PROPERTIES);
+ GroupByCaseIT gbt = new GroupByCaseIT();
+ long ts = gbt.createTable();
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+ conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ conn.createStatement().execute("ALTER TABLE " + GROUPBYTEST_NAME + " SET IMMUTABLE_ROWS=true");
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
+ conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ conn.createStatement().execute("CREATE INDEX idx ON " + GROUPBYTEST_NAME + "(uri)");
+ gbt.loadData(ts);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
+ conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ gbt.executeQuery(conn,GROUPBY1);
+ gbt.executeQuery(conn,GROUPBY2);
+ // TODO: validate query results
+ try {
+ gbt.executeQuery(conn,GROUPBY3);
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("Aggregate expressions may not be used in GROUP BY"));
+ }
+ conn.close();
+ }
+ */
+
+ @Test
+ public void testScanUri() throws Exception {
+ GroupByCaseIT gbt = new GroupByCaseIT();
+ long ts = gbt.createTable();
+ gbt.loadData(ts);
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ Statement stmt = conn.createStatement();
+ ResultSet rs = stmt.executeQuery("select uri from " + GROUPBYTEST_NAME);
+ assertTrue(rs.next());
+ assertEquals("Report1", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("Report2", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("Report3", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("Report4", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("SOQL1", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("SOQL2", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("SOQL3", rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("SOQL4", rs.getString(1));
+ assertFalse(rs.next());
+ conn.close();
+ }
+
+ @Test
+ public void testCount() throws Exception {
+ GroupByCaseIT gbt = new GroupByCaseIT();
+ long ts = gbt.createTable();
+ gbt.loadData(ts);
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ Statement stmt = conn.createStatement();
+ ResultSet rs = stmt.executeQuery("select count(1) from " + GROUPBYTEST_NAME);
+ assertTrue(rs.next());
+ assertEquals(8, rs.getInt(1));
+ assertFalse(rs.next());
+ conn.close();
+ }
+
+ @edu.umd.cs.findbugs.annotations.SuppressWarnings(
+ value="RV_RETURN_VALUE_IGNORED",
+ justification="Test code.")
+ private void executeQuery(Connection conn, String query) throws SQLException {
+ PreparedStatement st = conn.prepareStatement(query);
+ st.executeQuery();
+ }
+
+ @Test
+ public void testGroupByCase() throws Exception {
+ GroupByCaseIT gbt = new GroupByCaseIT();
+ long ts = gbt.createTable();
+ gbt.loadData(ts);
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ gbt.executeQuery(conn,GROUPBY1);
+ gbt.executeQuery(conn,GROUPBY2);
+ // TODO: validate query results
+ try {
+ gbt.executeQuery(conn,GROUPBY3);
+ fail();
+ } catch (SQLException e) {
+ assertTrue(e.getMessage().contains("Aggregate expressions may not be used in GROUP BY"));
+ }
+ conn.close();
+ }
+
+}