Return-Path: X-Original-To: apmail-phoenix-commits-archive@minotaur.apache.org Delivered-To: apmail-phoenix-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id B1714100C0 for ; Mon, 24 Feb 2014 05:43:27 +0000 (UTC) Received: (qmail 5179 invoked by uid 500); 24 Feb 2014 05:43:25 -0000 Delivered-To: apmail-phoenix-commits-archive@phoenix.apache.org Received: (qmail 5147 invoked by uid 500); 24 Feb 2014 05:43:24 -0000 Mailing-List: contact commits-help@phoenix.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.incubator.apache.org Delivered-To: mailing list commits@phoenix.incubator.apache.org Received: (qmail 4966 invoked by uid 99); 24 Feb 2014 05:43:20 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 24 Feb 2014 05:43:20 +0000 X-ASF-Spam-Status: No, hits=-2000.5 required=5.0 tests=ALL_TRUSTED,RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO mail.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 24 Feb 2014 05:43:15 +0000 Received: (qmail 4804 invoked by uid 99); 24 Feb 2014 05:42:52 -0000 Received: from tyr.zones.apache.org (HELO tyr.zones.apache.org) (140.211.11.114) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 24 Feb 2014 05:42:52 +0000 Received: by tyr.zones.apache.org (Postfix, from userid 65534) id 2AF6189E52B; Mon, 24 Feb 2014 05:42:52 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: anoopsamjohn@apache.org To: commits@phoenix.incubator.apache.org Date: Mon, 24 Feb 2014 05:42:57 -0000 Message-Id: <7790f68d3bd7470d83a0f60ebf62ff3d@git.apache.org> In-Reply-To: <047bba1744694b42afa7b1c6c4f3d620@git.apache.org> References: <047bba1744694b42afa7b1c6c4f3d620@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [7/8] git commit: PHOENIX-29 X-Virus-Checked: Checked by ClamAV on apache.org PHOENIX-29 Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/43381fcd Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/43381fcd Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/43381fcd Branch: refs/heads/master Commit: 43381fcdd44eb111b6b2feb36dc2d73efe6ead2c Parents: b75206d Author: anoopsjohn Authored: Mon Feb 24 01:16:56 2014 +0530 Committer: anoopsjohn Committed: Mon Feb 24 01:16:56 2014 +0530 ---------------------------------------------------------------------- .../ColumnProjectionOptimizationTest.java | 253 +++++++++++++++++++ 1 file changed, 253 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/43381fcd/phoenix-core/src/test/java/org/apache/phoenix/end2end/ColumnProjectionOptimizationTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/ColumnProjectionOptimizationTest.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/ColumnProjectionOptimizationTest.java new file mode 100644 index 0000000..e6aa02f --- /dev/null +++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/ColumnProjectionOptimizationTest.java @@ -0,0 +1,253 @@ +/* + * 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.apache.hadoop.hbase.HColumnDescriptor; +import org.apache.hadoop.hbase.HTableDescriptor; +import org.apache.hadoop.hbase.client.*; +import org.apache.hadoop.hbase.util.Bytes; +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.schema.PDataType; +import org.apache.phoenix.util.PhoenixRuntime; +import org.apache.phoenix.util.SchemaUtil; +import org.junit.Test; + +public class ColumnProjectionOptimizationTest extends BaseClientManagedTimeTest { + + @Test + public void testSelect() throws Exception { + long ts = nextTimestamp(); + String tenantId = getOrganizationId(); + initATableValues(tenantId, getDefaultSplits(tenantId), null, 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); + + // Table wildcard query + String query = "SELECT * FROM aTable"; + try { + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(ROW1, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW2, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW3, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW4, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW5, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW6, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW7, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW8, rs.getString(2)); + assertTrue(rs.next()); + assertEquals(ROW9, rs.getString(2)); + assertFalse(rs.next()); + + // Select only specific columns + query = "SELECT A_STRING, A_INTEGER FROM aTable"; + statement = conn.prepareStatement(query); + rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(A_VALUE, rs.getString(1)); + assertEquals(1, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(A_VALUE, rs.getString(1)); + assertEquals(2, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(A_VALUE, rs.getString(1)); + assertEquals(3, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(A_VALUE, rs.getString(1)); + assertEquals(4, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(B_VALUE, rs.getString(1)); + assertEquals(5, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(B_VALUE, rs.getString(1)); + assertEquals(6, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(B_VALUE, rs.getString(1)); + assertEquals(7, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(B_VALUE, rs.getString(1)); + assertEquals(8, rs.getInt(2)); + assertTrue(rs.next()); + assertEquals(C_VALUE, rs.getString(1)); + assertEquals(9, rs.getInt(2)); + assertFalse(rs.next()); + + // Select only specific columns with condition on another column (Not in select) + query = "SELECT B_STRING, A_SHORT FROM aTable WHERE X_INTEGER = ?"; + statement = conn.prepareStatement(query); + statement.setInt(1, 4); + rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(C_VALUE, rs.getString(1)); + assertEquals(135, rs.getShort(2)); + assertFalse(rs.next()); + + // Select only specific columns with condition on another column (Not in select) and one row elements are + // nulls + query = "SELECT X_LONG, X_INTEGER, Y_INTEGER FROM aTable WHERE B_STRING = ?"; + statement = conn.prepareStatement(query); + statement.setString(1, E_VALUE); + rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(0, rs.getLong(1)); + assertTrue(rs.wasNull()); + assertEquals(0, rs.getInt(2)); + assertTrue(rs.wasNull()); + assertEquals(0, rs.getInt(3)); + assertTrue(rs.wasNull()); + assertTrue(rs.next()); + assertEquals(0, rs.getLong(1)); + assertTrue(rs.wasNull()); + assertEquals(0, rs.getInt(2)); + assertTrue(rs.wasNull()); + assertEquals(0, rs.getInt(3)); + assertTrue(rs.wasNull()); + assertTrue(rs.next()); + assertEquals(Integer.MAX_VALUE + 1L, rs.getLong(1)); + assertEquals(3, rs.getInt(2)); + assertEquals(300, rs.getInt(3)); + assertFalse(rs.next()); + + // Select only specific columns with condition on one of the selected column + query = "SELECT A_STRING, A_INTEGER FROM aTable WHERE A_INTEGER = ?"; + statement = conn.prepareStatement(query); + statement.setInt(1, 9); + rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(C_VALUE, rs.getString(1)); + assertEquals(9, rs.getInt(2)); + } finally { + conn.close(); + } + } + + @Test + public void testSelectFromViewOnExistingTable() throws Exception { + PhoenixConnection pconn = DriverManager.getConnection(PHOENIX_JDBC_URL, TEST_PROPERTIES).unwrap( + PhoenixConnection.class); + byte[] cfB = Bytes.toBytes(SchemaUtil.normalizeIdentifier("b")); + byte[] cfC = Bytes.toBytes(SchemaUtil.normalizeIdentifier("c")); + byte[][] familyNames = new byte[][] { cfB, cfC }; + byte[] htableName = SchemaUtil.getTableNameAsBytes(MDTEST_SCHEMA_NAME, MDTEST_NAME); + HBaseAdmin admin = pconn.getQueryServices().getAdmin(); + + HTableDescriptor descriptor = new HTableDescriptor(htableName); + for (byte[] familyName : familyNames) { + HColumnDescriptor columnDescriptor = new HColumnDescriptor(familyName); + descriptor.addFamily(columnDescriptor); + } + admin.createTable(descriptor); + + long ts = nextTimestamp(); + Properties props = new Properties(); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5)); + Connection conn1 = DriverManager.getConnection(PHOENIX_JDBC_URL, props); + + String createStmt = "create view " + MDTEST_NAME + " (id integer not null primary key," + + " b.col1 integer, c.col2 bigint, c.col3 varchar(20))"; + conn1.createStatement().execute(createStmt); + conn1.close(); + + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 6)); + PhoenixConnection conn2 = DriverManager.getConnection(PHOENIX_JDBC_URL, props).unwrap(PhoenixConnection.class); + byte[] c1 = Bytes.toBytes("COL1"); + byte[] c2 = Bytes.toBytes("COL2"); + byte[] c3 = Bytes.toBytes("COL3"); + HTableInterface htable = null; + try { + htable = conn2.getQueryServices().getTable(htableName); + Put put = new Put(PDataType.INTEGER.toBytes(1)); + put.add(cfB, c1, ts + 6, PDataType.INTEGER.toBytes(1)); + put.add(cfC, c2, ts + 6, PDataType.LONG.toBytes(2)); + htable.put(put); + + put = new Put(PDataType.INTEGER.toBytes(2)); + put.add(cfC, c2, ts + 6, PDataType.LONG.toBytes(10)); + put.add(cfC, c3, ts + 6, PDataType.VARCHAR.toBytes("abcd")); + htable.put(put); + + put = new Put(PDataType.INTEGER.toBytes(3)); + put.add(cfB, c1, ts + 6, PDataType.INTEGER.toBytes(3)); + put.add(cfC, c2, ts + 6, PDataType.LONG.toBytes(10)); + put.add(cfC, c3, ts + 6, PDataType.VARCHAR.toBytes("abcd")); + htable.put(put); + + conn2.close(); + + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10)); + Connection conn7 = DriverManager.getConnection(PHOENIX_JDBC_URL, props); + String select = "SELECT id, b.col1 FROM " + MDTEST_NAME + " WHERE c.col2=?"; + PreparedStatement ps = conn7.prepareStatement(select); + ps.setInt(1, 10); + ResultSet rs = ps.executeQuery(); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + assertEquals(0, rs.getInt(2)); + assertTrue(rs.wasNull()); + assertTrue(rs.next()); + assertEquals(3, rs.getInt(1)); + assertEquals(3, rs.getInt(2)); + assertFalse(rs.next()); + + // Select contains only CF wildcards + select = "SELECT b.* FROM " + MDTEST_NAME + " WHERE c.col2=?"; + ps = conn7.prepareStatement(select); + ps.setInt(1, 10); + rs = ps.executeQuery(); + assertTrue(rs.next()); + assertEquals(0, rs.getInt(1)); + assertTrue(rs.wasNull()); + assertTrue(rs.next()); + assertEquals(3, rs.getInt(1)); + assertFalse(rs.next()); + + select = "SELECT b.* FROM " + MDTEST_NAME; + ps = conn7.prepareStatement(select); + rs = ps.executeQuery(); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertTrue(rs.next()); + assertEquals(0, rs.getInt(1)); + assertTrue(rs.wasNull()); + assertTrue(rs.next()); + assertEquals(3, rs.getInt(1)); + assertFalse(rs.next()); + } finally { + if (htable != null) htable.close(); + admin.disableTable(htableName); + admin.deleteTable(htableName); + admin.close(); + } + } +}