phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [36/41] PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (Gabriel Reid)
Date Wed, 12 Mar 2014 21:32:03 GMT
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();
+    }
+
+}


Mime
View raw message