PHOENIX-4257 Breakup GroupByIT,MutableQueryIT, and QueryIT into several integration tests so as not to create too many tables in one test
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/7a332ced Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/7a332ced Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/7a332ced Branch: refs/heads/4.x-HBase-0.98 Commit: 7a332cedeb69f0b01092f934e24fd11a2e8a51fa Parents: c14e15d Author: James Taylor <[email protected]> Authored: Fri Sep 29 11:27:43 2017 -0700 Committer: James Taylor <[email protected]> Committed: Fri Sep 29 11:31:01 2017 -0700 ---------------------------------------------------------------------- .../phoenix/end2end/AggregateQueryIT.java | 2 +- .../org/apache/phoenix/end2end/BaseQueryIT.java | 23 -- .../apache/phoenix/end2end/CaseStatementIT.java | 33 +- .../phoenix/end2end/DateArithmeticIT.java | 52 +-- .../org/apache/phoenix/end2end/DateTimeIT.java | 68 ++++ .../org/apache/phoenix/end2end/GroupByIT.java | 67 ++- .../apache/phoenix/end2end/MutableQueryIT.java | 405 ------------------- .../java/org/apache/phoenix/end2end/NullIT.java | 158 ++++++++ .../phoenix/end2end/PointInTimeQueryIT.java | 236 +++++++++++ .../org/apache/phoenix/end2end/QueryIT.java | 115 ------ .../org/apache/phoenix/end2end/UngroupedIT.java | 44 +- .../java/org/apache/phoenix/util/TestUtil.java | 6 +- 12 files changed, 635 insertions(+), 574 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateQueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateQueryIT.java index 3dc0184..f85e274 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateQueryIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateQueryIT.java @@ -96,7 +96,7 @@ public class AggregateQueryIT extends BaseQueryIT { HTable htable = (HTable) conn.unwrap(PhoenixConnection.class).getQueryServices().getTable(tableNameBytes); htable.clearRegionCache(); int nRegions = htable.getRegionLocations().size(); - admin.split(tableNameBytes, ByteUtil.concat(Bytes.toBytes(tenantId), Bytes.toBytes("00A" + Character.valueOf((char) ('3' + nextRunCount()))))); // vary split point with test run + admin.split(tableNameBytes, ByteUtil.concat(Bytes.toBytes(tenantId), Bytes.toBytes("00A3"))); int retryCount = 0; do { Thread.sleep(2000); http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseQueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseQueryIT.java index 15560cd..7f7416b 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseQueryIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseQueryIT.java @@ -19,21 +19,14 @@ package org.apache.phoenix.end2end; import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; -import java.io.IOException; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; -import java.sql.SQLException; import java.util.Collection; import java.util.List; import java.util.Properties; -import java.util.concurrent.atomic.AtomicInteger; -import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp; -import org.apache.hadoop.hbase.io.ImmutableBytesWritable; -import org.apache.hadoop.hbase.util.Bytes; import org.apache.phoenix.schema.PTableImpl; -import org.apache.phoenix.util.ByteUtil; import org.apache.phoenix.util.PropertiesUtil; import org.junit.runner.RunWith; import org.junit.runners.Parameterized; @@ -136,20 +129,4 @@ public abstract class BaseQueryIT extends ParallelStatsDisabledIT { } return testCases; } - - protected static boolean compare(CompareOp op, ImmutableBytesWritable lhsOutPtr, ImmutableBytesWritable rhsOutPtr) { - int compareResult = Bytes.compareTo(lhsOutPtr.get(), lhsOutPtr.getOffset(), lhsOutPtr.getLength(), rhsOutPtr.get(), rhsOutPtr.getOffset(), rhsOutPtr.getLength()); - return ByteUtil.compare(op, compareResult); - } - - protected static void analyzeTable(Connection conn, String tableName) throws IOException, SQLException { - String query = "UPDATE STATISTICS " + tableName; - conn.createStatement().execute(query); - } - - private static AtomicInteger runCount = new AtomicInteger(0); - protected static int nextRunCount() { - return runCount.getAndAdd(1); - } - } http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/CaseStatementIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CaseStatementIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CaseStatementIT.java index 4e78fdc..a1cb05f 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CaseStatementIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CaseStatementIT.java @@ -42,7 +42,6 @@ import java.util.Collection; import java.util.List; import java.util.Properties; -import org.apache.phoenix.util.PhoenixRuntime; import org.apache.phoenix.util.PropertiesUtil; import org.junit.Test; import org.junit.runner.RunWith; @@ -248,4 +247,36 @@ public class CaseStatementIT extends BaseQueryIT { } } + @Test + public void testUnfoundSingleColumnCaseStatement() throws Exception { + String query = "SELECT entity_id, b_string FROM " + tableName + " WHERE organization_id=? and CASE WHEN a_integer = 0 or a_integer != 0 THEN 1 ELSE 0 END = 0"; + String url = getUrl(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(url, props); + // Set ROW5.A_INTEGER to null so that we have one row + // where the else clause of the CASE statement will + // fire. + Connection upsertConn = DriverManager.getConnection(url, props); + String upsertStmt = + "upsert into " + tableName + + " (" + + " ENTITY_ID, " + + " ORGANIZATION_ID, " + + " A_INTEGER) " + + "VALUES ('" + ROW5 + "','" + tenantId + "', null)"; + upsertConn.setAutoCommit(true); // Test auto commit + // Insert all rows at ts + PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt); + stmt.execute(); // should commit too + upsertConn.close(); + + PreparedStatement statement = conn.prepareStatement(query); + statement.setString(1, tenantId); + ResultSet rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(ROW5, rs.getString(1)); + assertFalse(rs.next()); + conn.close(); + } + } http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateArithmeticIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateArithmeticIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateArithmeticIT.java index eb31ab4..795876d 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateArithmeticIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateArithmeticIT.java @@ -47,15 +47,17 @@ import org.junit.Test; import com.google.common.collect.Lists; -public class DateArithmeticIT extends BaseQueryIT { - - public DateArithmeticIT(String idxDdl, boolean mutable, boolean columnEncoded, boolean keepDeletedCells) - throws Exception { - super(idxDdl, mutable, columnEncoded, keepDeletedCells); - } - +public class DateArithmeticIT extends ParallelStatsDisabledIT { + @Test public void testValidArithmetic() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Date date = new Date(System.currentTimeMillis()); + Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName = + initATableValues(generateUniqueName(), getOrganizationId(), getDefaultSplits(getOrganizationId()), + date, null, getUrl(), "COLUMN_ENCODED_BYTES=0"); + String[] queries = new String[] { "SELECT entity_id,organization_id FROM " + tableName + " where (A_DATE - A_DATE) * 5 < 0", "SELECT entity_id,organization_id FROM " + tableName + " where 1 + A_DATE < A_DATE", @@ -63,24 +65,21 @@ public class DateArithmeticIT extends BaseQueryIT { }; for (String query : queries) { - Properties props = new Properties(); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { PreparedStatement statement = conn.prepareStatement(query); statement.executeQuery(); - } - finally { - conn.close(); - } } + conn.close(); } @Test public void testDateAdd() throws Exception { - String query = "SELECT entity_id, b_string FROM " + tableName + " WHERE a_date + CAST(0.5 AS DOUBLE) < ?"; - String url = getUrl(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(url, props); + Date date = new Date(System.currentTimeMillis()); + Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName = + initATableValues(generateUniqueName(), getOrganizationId(), getDefaultSplits(getOrganizationId()), + date, null, getUrl(), "COLUMN_ENCODED_BYTES=0"); + String query = "SELECT entity_id, b_string FROM " + tableName + " WHERE a_date + CAST(0.5 AS DOUBLE) < ?"; try { PreparedStatement statement = conn.prepareStatement(query); statement.setDate(1, new Date(System.currentTimeMillis() + MILLIS_IN_DAY)); @@ -98,10 +97,13 @@ public class DateArithmeticIT extends BaseQueryIT { @Test public void testDateSubtract() throws Exception { - String query = "SELECT entity_id, b_string FROM " + tableName + " WHERE a_date - CAST(0.5 AS DOUBLE) > ?"; - String url = getUrl(); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(url, props); + Date date = new Date(System.currentTimeMillis()); + Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName = + initATableValues(generateUniqueName(), getOrganizationId(), getDefaultSplits(getOrganizationId()), + date, null, getUrl(), "COLUMN_ENCODED_BYTES=0"); + String query = "SELECT entity_id, b_string FROM " + tableName + " WHERE a_date - CAST(0.5 AS DOUBLE) > ?"; try { PreparedStatement statement = conn.prepareStatement(query); statement.setDate(1, new Date(System.currentTimeMillis() + MILLIS_IN_DAY)); @@ -119,11 +121,12 @@ public class DateArithmeticIT extends BaseQueryIT { @Test public void testDateDateSubtract() throws Exception { - String url; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - - url = getUrl(); - Connection conn = DriverManager.getConnection(url, props); + Date date = new Date(System.currentTimeMillis()); + Connection conn = DriverManager.getConnection(getUrl(), props); + String tableName = + initATableValues(generateUniqueName(), getOrganizationId(), getDefaultSplits(getOrganizationId()), + date, null, getUrl(), "COLUMN_ENCODED_BYTES=0"); PreparedStatement statement = conn.prepareStatement("UPSERT INTO " + tableName + " (organization_id,entity_id,a_time) VALUES(?,?,?)"); statement.setString(1, getOrganizationId()); statement.setString(2, ROW2); @@ -303,5 +306,4 @@ public class DateArithmeticIT extends BaseQueryIT { assertTrue(rs.next()); assertEquals("1995-05-01",rs.getDate(1).toString()); } - } http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java index e9a98e3..c976114 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java @@ -55,12 +55,16 @@ import java.util.Calendar; import java.util.GregorianCalendar; import java.util.Properties; +import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp; +import org.apache.hadoop.hbase.io.ImmutableBytesWritable; import org.apache.hadoop.hbase.util.Bytes; import org.apache.phoenix.query.QueryConstants; import org.apache.phoenix.schema.types.PDate; +import org.apache.phoenix.schema.types.PTimestamp; import org.apache.phoenix.util.ByteUtil; import org.apache.phoenix.util.DateUtil; import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.TestUtil; import org.junit.After; import org.junit.Before; import org.junit.Test; @@ -1812,4 +1816,68 @@ public class DateTimeIT extends ParallelStatsDisabledIT { } } + + @Test + public void testTimestamp() throws Exception { + String updateStmt = + "upsert into " + tableName + + " (" + + " ORGANIZATION_ID, " + + " ENTITY_ID, " + + " A_TIMESTAMP) " + + "VALUES (?, ?, ?)"; + // Override value that was set at creation time + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection upsertConn = DriverManager.getConnection(url, props); + upsertConn.setAutoCommit(true); // Test auto commit + PreparedStatement stmt = upsertConn.prepareStatement(updateStmt); + stmt.setString(1, tenantId); + stmt.setString(2, ROW4); + Timestamp tsValue1 = new Timestamp(5000); + byte[] ts1 = PTimestamp.INSTANCE.toBytes(tsValue1); + stmt.setTimestamp(3, tsValue1); + stmt.execute(); + + Connection conn1 = DriverManager.getConnection(url, props); + TestUtil.analyzeTable(conn1, tableName); + conn1.close(); + + updateStmt = + "upsert into " + tableName + + " (" + + " ORGANIZATION_ID, " + + " ENTITY_ID, " + + " A_TIMESTAMP," + + " A_TIME) " + + "VALUES (?, ?, ?, ?)"; + stmt = upsertConn.prepareStatement(updateStmt); + stmt.setString(1, tenantId); + stmt.setString(2, ROW5); + Timestamp tsValue2 = new Timestamp(5000); + tsValue2.setNanos(200); + byte[] ts2 = PTimestamp.INSTANCE.toBytes(tsValue2); + stmt.setTimestamp(3, tsValue2); + stmt.setTime(4, new Time(tsValue2.getTime())); + stmt.execute(); + upsertConn.close(); + + assertTrue(TestUtil.compare(CompareOp.GREATER, new ImmutableBytesWritable(ts2), new ImmutableBytesWritable(ts1))); + assertFalse(TestUtil.compare(CompareOp.GREATER, new ImmutableBytesWritable(ts1), new ImmutableBytesWritable(ts1))); + + String query = "SELECT entity_id, a_timestamp, a_time FROM " + tableName + " WHERE organization_id=? and a_timestamp > ?"; + Connection conn = DriverManager.getConnection(url, props); + try { + PreparedStatement statement = conn.prepareStatement(query); + statement.setString(1, tenantId); + statement.setTimestamp(2, new Timestamp(5000)); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW5); + assertEquals(rs.getTimestamp("A_TIMESTAMP"), tsValue2); + assertEquals(rs.getTime("A_TIME"), new Time(tsValue2.getTime())); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByIT.java index f4e10e3..2f2bf1d 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByIT.java @@ -21,7 +21,9 @@ import static org.apache.phoenix.util.TestUtil.A_VALUE; import static org.apache.phoenix.util.TestUtil.B_VALUE; import static org.apache.phoenix.util.TestUtil.C_VALUE; import static org.apache.phoenix.util.TestUtil.E_VALUE; +import static org.apache.phoenix.util.TestUtil.ROW3; import static org.apache.phoenix.util.TestUtil.ROW5; +import static org.apache.phoenix.util.TestUtil.ROW6; import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; @@ -32,7 +34,9 @@ import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; +import java.util.Arrays; import java.util.Collection; +import java.util.List; import java.util.Properties; import org.apache.phoenix.util.PhoenixRuntime; @@ -42,12 +46,14 @@ import org.junit.runner.RunWith; import org.junit.runners.Parameterized; import org.junit.runners.Parameterized.Parameters; +import com.google.common.collect.Lists; + @RunWith(Parameterized.class) public class GroupByIT extends BaseQueryIT { public GroupByIT(String indexDDL, boolean mutable, boolean columnEncoded) throws Exception { - super(indexDDL, mutable, columnEncoded, true); + super(indexDDL, mutable, columnEncoded, false); } @Parameters(name="GroupByIT_{index}") // name is used by failsafe as file name in reports @@ -315,4 +321,63 @@ public class GroupByIT extends BaseQueryIT { conn.close(); } + @SuppressWarnings("unchecked") + @Test + public void testGroupByCondition() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + PreparedStatement statement = conn.prepareStatement("SELECT count(*) FROM " + tableName + " WHERE organization_id=? GROUP BY a_integer=6"); + statement.setString(1, tenantId); + ResultSet rs = statement.executeQuery(); + assertValueEqualsResultSet(rs, Arrays.<Object>asList(1L,8L)); + try { + statement = conn.prepareStatement("SELECT count(*),a_integer=6 FROM " + tableName + " WHERE organization_id=? and (a_integer IN (5,6) or a_integer is null) GROUP BY a_integer=6"); + statement.setString(1, tenantId); + rs = statement.executeQuery(); + List<List<Object>> expectedResults = Lists.newArrayList( + Arrays.<Object>asList(1L,false), + Arrays.<Object>asList(1L,true)); + assertValuesEqualsResultSet(rs, expectedResults); + } finally { + conn.close(); + } + + conn = DriverManager.getConnection(getUrl(), props); + try { + statement = conn.prepareStatement("UPSERT into " + tableName + " (organization_id,entity_id,a_integer) values(?,?,null)"); + statement.setString(1, tenantId); + statement.setString(2, ROW3); + statement.executeUpdate(); + conn.commit(); + } finally { + conn.close(); + } + conn = DriverManager.getConnection(getUrl(), props); + statement = conn.prepareStatement("SELECT count(*) FROM " + tableName + " WHERE organization_id=? GROUP BY a_integer=6"); + statement.setString(1, tenantId); + rs = statement.executeQuery(); + assertValueEqualsResultSet(rs, Arrays.<Object>asList(1L,1L,7L)); + statement = conn.prepareStatement("SELECT a_integer, entity_id FROM " + tableName + " WHERE organization_id=? and (a_integer IN (5,6) or a_integer is null)"); + statement.setString(1, tenantId); + rs = statement.executeQuery(); + List<List<Object>> expectedResults = Lists.newArrayList( + Arrays.<Object>asList(null,ROW3), + Arrays.<Object>asList(5,ROW5), + Arrays.<Object>asList(6,ROW6)); + assertValuesEqualsResultSet(rs, expectedResults); + try { + statement = conn.prepareStatement("SELECT count(*),a_integer=6 FROM " + tableName + " WHERE organization_id=? and (a_integer IN (5,6) or a_integer is null) GROUP BY a_integer=6"); + statement.setString(1, tenantId); + rs = statement.executeQuery(); + expectedResults = Lists.newArrayList( + Arrays.<Object>asList(1L,null), + Arrays.<Object>asList(1L,false), + Arrays.<Object>asList(1L,true)); + assertValuesEqualsResultSet(rs, expectedResults); + } finally { + conn.close(); + } + } + + } http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableQueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableQueryIT.java deleted file mode 100644 index 6a8ce53..0000000 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/MutableQueryIT.java +++ /dev/null @@ -1,405 +0,0 @@ -/* - * 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.A_VALUE; -import static org.apache.phoenix.util.TestUtil.B_VALUE; -import static org.apache.phoenix.util.TestUtil.C_VALUE; -import static org.apache.phoenix.util.TestUtil.E_VALUE; -import static org.apache.phoenix.util.TestUtil.ROW1; -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.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.DriverManager; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.util.Arrays; -import java.util.Collection; -import java.util.List; -import java.util.Properties; - -import org.apache.phoenix.util.PhoenixRuntime; -import org.apache.phoenix.util.PropertiesUtil; -import org.junit.Test; -import org.junit.runners.Parameterized.Parameters; - -import com.google.common.collect.Lists; - -public class MutableQueryIT extends BaseQueryIT { - - @Parameters(name="indexDDL={0},mutable={1},columnEncoded={2}") - @Shadower(classBeingShadowed = BaseQueryIT.class) - public static Collection<Object> data() { - List<Object> testCases = Lists.newArrayList(); - for (String indexDDL : INDEX_DDLS) { - for (boolean columnEncoded : new boolean[]{false,true}) { - testCases.add(new Object[] { indexDDL, true, columnEncoded }); - } - } - return testCases; - } - - public MutableQueryIT(String indexDDL, boolean mutable, boolean columnEncoded) throws Exception { - super(indexDDL, mutable, columnEncoded, true); - } - - @Test - public void testSumOverNullIntegerColumn() throws Exception { - String query = "SELECT sum(a_integer) FROM " + tableName + " a"; - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - conn.setAutoCommit(true); - conn.createStatement().execute("UPSERT INTO " + tableName + " (organization_id,entity_id,a_integer) VALUES('" + getOrganizationId() + "','" + ROW3 + "',NULL)"); - Connection conn1 = DriverManager.getConnection(getUrl(), props); - analyzeTable(conn1, tableName); - conn1.close(); - conn = DriverManager.getConnection(getUrl(), props); - try { - PreparedStatement statement = conn.prepareStatement(query); - ResultSet rs = statement.executeQuery(); - assertTrue (rs.next()); - assertEquals(42, rs.getInt(1)); - assertFalse(rs.next()); - } finally { - conn.close(); - } - conn = DriverManager.getConnection(getUrl(), props); - conn.setAutoCommit(true); - conn.createStatement().execute("UPSERT INTO " + tableName + " (organization_id,entity_id,a_integer) SELECT organization_id, entity_id, CAST(null AS integer) FROM " + tableName); - - conn = DriverManager.getConnection(getUrl(), props); - try { - PreparedStatement statement = conn.prepareStatement(query); - ResultSet rs = statement.executeQuery(); - assertTrue (rs.next()); - assertEquals(0, rs.getInt(1)); - assertTrue(rs.wasNull()); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - private void testNoStringValue(String value) throws Exception { - String url = getUrl(); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection upsertConn = DriverManager.getConnection(url, props); - upsertConn.setAutoCommit(true); // Test auto commit - // Insert all rows at ts - PreparedStatement stmt = upsertConn.prepareStatement( - "upsert into " + tableName + " VALUES (?, ?, ?)"); // without specifying columns - stmt.setString(1, tenantId); - stmt.setString(2, ROW5); - stmt.setString(3, value); - stmt.execute(); // should commit too - upsertConn.close(); - - Connection conn1 = DriverManager.getConnection(getUrl(), props); - analyzeTable(conn1, tableName); - conn1.close(); - - String query = "SELECT a_string, b_string FROM " + tableName + " WHERE organization_id=? and a_integer = 5"; - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - PreparedStatement statement = conn.prepareStatement(query); - statement.setString(1, tenantId); - ResultSet rs = statement.executeQuery(); - assertTrue (rs.next()); - assertEquals(null, rs.getString(1)); - assertTrue(rs.wasNull()); - assertEquals(C_VALUE, rs.getString("B_string")); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @Test - public void testNullStringValue() throws Exception { - testNoStringValue(null); - } - - @Test - public void testEmptyStringValue() throws Exception { - testNoStringValue(""); - } - - @Test - public void testUnfoundSingleColumnCaseStatement() throws Exception { - String query = "SELECT entity_id, b_string FROM " + tableName + " WHERE organization_id=? and CASE WHEN a_integer = 0 or a_integer != 0 THEN 1 ELSE 0 END = 0"; - String url = getUrl(); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(url, props); - // Set ROW5.A_INTEGER to null so that we have one row - // where the else clause of the CASE statement will - // fire. - Connection upsertConn = DriverManager.getConnection(url, props); - String upsertStmt = - "upsert into " + tableName + - " (" + - " ENTITY_ID, " + - " ORGANIZATION_ID, " + - " A_INTEGER) " + - "VALUES ('" + ROW5 + "','" + tenantId + "', null)"; - upsertConn.setAutoCommit(true); // Test auto commit - // Insert all rows at ts - PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt); - stmt.execute(); // should commit too - upsertConn.close(); - - PreparedStatement statement = conn.prepareStatement(query); - statement.setString(1, tenantId); - ResultSet rs = statement.executeQuery(); - assertTrue(rs.next()); - assertEquals(ROW5, rs.getString(1)); - assertFalse(rs.next()); - conn.close(); - } - - @SuppressWarnings("unchecked") - @Test - public void testGroupByCondition() throws Exception { - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - PreparedStatement statement = conn.prepareStatement("SELECT count(*) FROM " + tableName + " WHERE organization_id=? GROUP BY a_integer=6"); - statement.setString(1, tenantId); - ResultSet rs = statement.executeQuery(); - assertValueEqualsResultSet(rs, Arrays.<Object>asList(1L,8L)); - try { - statement = conn.prepareStatement("SELECT count(*),a_integer=6 FROM " + tableName + " WHERE organization_id=? and (a_integer IN (5,6) or a_integer is null) GROUP BY a_integer=6"); - statement.setString(1, tenantId); - rs = statement.executeQuery(); - List<List<Object>> expectedResults = Lists.newArrayList( - Arrays.<Object>asList(1L,false), - Arrays.<Object>asList(1L,true)); - assertValuesEqualsResultSet(rs, expectedResults); - } finally { - conn.close(); - } - - conn = DriverManager.getConnection(getUrl(), props); - try { - statement = conn.prepareStatement("UPSERT into " + tableName + " (organization_id,entity_id,a_integer) values(?,?,null)"); - statement.setString(1, tenantId); - statement.setString(2, ROW3); - statement.executeUpdate(); - conn.commit(); - } finally { - conn.close(); - } - conn = DriverManager.getConnection(getUrl(), props); - statement = conn.prepareStatement("SELECT count(*) FROM " + tableName + " WHERE organization_id=? GROUP BY a_integer=6"); - statement.setString(1, tenantId); - rs = statement.executeQuery(); - assertValueEqualsResultSet(rs, Arrays.<Object>asList(1L,1L,7L)); - statement = conn.prepareStatement("SELECT a_integer, entity_id FROM " + tableName + " WHERE organization_id=? and (a_integer IN (5,6) or a_integer is null)"); - statement.setString(1, tenantId); - rs = statement.executeQuery(); - List<List<Object>> expectedResults = Lists.newArrayList( - Arrays.<Object>asList(null,ROW3), - Arrays.<Object>asList(5,ROW5), - Arrays.<Object>asList(6,ROW6)); - assertValuesEqualsResultSet(rs, expectedResults); - try { - statement = conn.prepareStatement("SELECT count(*),a_integer=6 FROM " + tableName + " WHERE organization_id=? and (a_integer IN (5,6) or a_integer is null) GROUP BY a_integer=6"); - statement.setString(1, tenantId); - rs = statement.executeQuery(); - expectedResults = Lists.newArrayList( - Arrays.<Object>asList(1L,null), - Arrays.<Object>asList(1L,false), - Arrays.<Object>asList(1L,true)); - assertValuesEqualsResultSet(rs, expectedResults); - } finally { - conn.close(); - } - } - - @Test - public void testPointInTimeDeleteUngroupedAggregation() throws Exception { - String updateStmt = - "upsert into " + tableName + - " (" + - " ORGANIZATION_ID, " + - " ENTITY_ID, " + - " A_STRING) " + - "VALUES (?, ?, ?)"; - - // Override value that was set at creation time - String url = getUrl(); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - - // Remove column value at ts + 1 (i.e. equivalent to setting the value to null) - Connection conn = DriverManager.getConnection(url, props); - PreparedStatement stmt = conn.prepareStatement(updateStmt); - stmt.setString(1, tenantId); - stmt.setString(2, ROW7); - stmt.setString(3, null); - stmt.execute(); - - // Delete row - stmt = conn.prepareStatement("delete from " + tableName + " where organization_id=? and entity_id=?"); - stmt.setString(1, tenantId); - stmt.setString(2, ROW5); - stmt.execute(); - conn.commit(); - conn.close(); - long firstDeleteTime = System.currentTimeMillis(); - long timeDelta = 100; - Thread.sleep(timeDelta); - - // Delete row at timestamp 3. This should not be seen by the query executing - // Remove column value at ts + 1 (i.e. equivalent to setting the value to null) - Connection futureConn = DriverManager.getConnection(getUrl()); - stmt = futureConn.prepareStatement("delete from " + tableName + " where organization_id=? and entity_id=?"); - stmt.setString(1, tenantId); - stmt.setString(2, ROW6); - stmt.execute(); - futureConn.commit(); - futureConn.close(); - - // query at a time which is beyong deleteTime1 but before the time at which above delete - // happened - long queryTime = firstDeleteTime + timeDelta / 2; - - String query = "SELECT count(1) FROM " + tableName + " WHERE organization_id=? and a_string = ?"; - props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(queryTime)); // Execute at timestamp 2 - conn = DriverManager.getConnection(getUrl(), props); - PreparedStatement statement = conn.prepareStatement(query); - statement.setString(1, tenantId); - statement.setString(2, B_VALUE); - ResultSet rs = statement.executeQuery(); - assertTrue(rs.next()); - assertEquals(2, rs.getLong(1)); - assertFalse(rs.next()); - conn.close(); - } - - @Test - public void testPointInTimeScan() throws Exception { - // Override value that was set at creation time - String url = getUrl(); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection upsertConn = DriverManager.getConnection(url, props); - String upsertStmt = - "upsert into " + tableName + - " (" + - " ORGANIZATION_ID, " + - " ENTITY_ID, " + - " A_INTEGER) " + - "VALUES (?, ?, ?)"; - upsertConn.setAutoCommit(true); // Test auto commit - PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt); - stmt.setString(1, tenantId); - stmt.setString(2, ROW4); - stmt.setInt(3, 5); - stmt.execute(); // should commit too - upsertConn.close(); - long upsert1Time = System.currentTimeMillis(); - long timeDelta = 100; - Thread.sleep(timeDelta); - - upsertConn = DriverManager.getConnection(url, props); - upsertConn.setAutoCommit(true); // Test auto commit - stmt = upsertConn.prepareStatement(upsertStmt); - stmt.setString(1, tenantId); - stmt.setString(2, ROW4); - stmt.setInt(3, 9); - stmt.execute(); // should commit too - upsertConn.close(); - - long queryTime = upsert1Time + timeDelta / 2; - String query = "SELECT organization_id, a_string AS a FROM " + tableName + " WHERE organization_id=? and a_integer = 5"; - props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(queryTime)); - Connection conn = DriverManager.getConnection(getUrl(), props); - PreparedStatement statement = conn.prepareStatement(query); - statement.setString(1, tenantId); - ResultSet rs = statement.executeQuery(); - assertTrue(rs.next()); - assertEquals(tenantId, rs.getString(1)); - assertEquals(A_VALUE, rs.getString("a")); - assertTrue(rs.next()); - assertEquals(tenantId, rs.getString(1)); - assertEquals(B_VALUE, rs.getString(2)); - assertFalse(rs.next()); - conn.close(); - } - - @SuppressWarnings("unchecked") - @Test - public void testPointInTimeLimitedScan() throws Exception { - // Override value that was set at creation time - String url = getUrl(); - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection upsertConn = DriverManager.getConnection(url, props); - String upsertStmt = - "upsert into " + tableName + - " (" + - " ORGANIZATION_ID, " + - " ENTITY_ID, " + - " A_INTEGER) " + - "VALUES (?, ?, ?)"; - upsertConn.setAutoCommit(true); // Test auto commit - // Insert all rows at ts - PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt); - stmt.setString(1, tenantId); - stmt.setString(2, ROW1); - stmt.setInt(3, 6); - stmt.execute(); // should commit too - upsertConn.close(); - long upsert1Time = System.currentTimeMillis(); - long timeDelta = 100; - Thread.sleep(timeDelta); - - url = getUrl(); - upsertConn = DriverManager.getConnection(url, props); - upsertConn.setAutoCommit(true); // Test auto commit - // Insert all rows at ts - stmt = upsertConn.prepareStatement(upsertStmt); - stmt.setString(1, tenantId); - stmt.setString(2, ROW1); - stmt.setInt(3, 0); - stmt.execute(); // should commit too - upsertConn.close(); - - long queryTime = upsert1Time + timeDelta / 2; - String query = "SELECT a_integer,b_string FROM " + tableName + " WHERE organization_id=? and a_integer <= 5 limit 2"; - props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(queryTime)); - Connection conn = DriverManager.getConnection(getUrl(), props); - PreparedStatement statement = conn.prepareStatement(query); - statement.setString(1, tenantId); - ResultSet rs = statement.executeQuery(); - List<List<Object>> expectedResultsA = Lists.newArrayList( - Arrays.<Object>asList(2, C_VALUE), - Arrays.<Object>asList( 3, E_VALUE)); - List<List<Object>> expectedResultsB = Lists.newArrayList( - Arrays.<Object>asList( 5, C_VALUE), - Arrays.<Object>asList(4, B_VALUE)); - // Since we're not ordering and we may be using a descending index, we don't - // know which rows we'll get back. - assertOneOfValuesEqualsResultSet(rs, expectedResultsA,expectedResultsB); - conn.close(); - } -} http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/NullIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/NullIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/NullIT.java new file mode 100644 index 0000000..ffe0b0d --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/NullIT.java @@ -0,0 +1,158 @@ +/* + * 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.C_VALUE; +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.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.util.Collection; +import java.util.List; +import java.util.Properties; + +import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.TestUtil; +import org.junit.Test; +import org.junit.runners.Parameterized.Parameters; + +import com.google.common.collect.Lists; + +public class NullIT extends BaseQueryIT { + + @Parameters(name="indexDDL={0},mutable={1},columnEncoded={2}") + @Shadower(classBeingShadowed = BaseQueryIT.class) + public static Collection<Object> data() { + List<Object> testCases = Lists.newArrayList(); + for (String indexDDL : INDEX_DDLS) { + for (boolean columnEncoded : new boolean[]{false,true}) { + testCases.add(new Object[] { indexDDL, true, columnEncoded }); + } + } + return testCases; + } + + public NullIT(String indexDDL, boolean mutable, boolean columnEncoded) throws Exception { + super(indexDDL, mutable, columnEncoded, true); + } + + private void testNoStringValue(String value) throws Exception { + String url = getUrl(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection upsertConn = DriverManager.getConnection(url, props); + upsertConn.setAutoCommit(true); // Test auto commit + // Insert all rows at ts + PreparedStatement stmt = upsertConn.prepareStatement( + "upsert into " + tableName + " VALUES (?, ?, ?)"); // without specifying columns + stmt.setString(1, tenantId); + stmt.setString(2, ROW5); + stmt.setString(3, value); + stmt.execute(); // should commit too + upsertConn.close(); + + Connection conn1 = DriverManager.getConnection(getUrl(), props); + TestUtil.analyzeTable(conn1, tableName); + conn1.close(); + + String query = "SELECT a_string, b_string FROM " + tableName + " WHERE organization_id=? and a_integer = 5"; + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + PreparedStatement statement = conn.prepareStatement(query); + statement.setString(1, tenantId); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(null, rs.getString(1)); + assertTrue(rs.wasNull()); + assertEquals(C_VALUE, rs.getString("B_string")); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + + @Test + public void testNullStringValue() throws Exception { + testNoStringValue(null); + } + + @Test + public void testEmptyStringValue() throws Exception { + testNoStringValue(""); + } + + @Test + public void testIsNull() throws Exception { + String query = "SELECT entity_id FROM " + tableName + " WHERE X_DECIMAL is null"; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW1); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW2); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW3); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW4); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW5); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW6); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + + @Test + public void testIsNotNull() throws Exception { + String query = "SELECT entity_id FROM " + tableName + " WHERE X_DECIMAL is not null"; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW7); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW8); + assertTrue (rs.next()); + assertEquals(rs.getString(1), ROW9); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/PointInTimeQueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/PointInTimeQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/PointInTimeQueryIT.java new file mode 100644 index 0000000..b08fb25 --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/PointInTimeQueryIT.java @@ -0,0 +1,236 @@ +/* + * 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.A_VALUE; +import static org.apache.phoenix.util.TestUtil.B_VALUE; +import static org.apache.phoenix.util.TestUtil.C_VALUE; +import static org.apache.phoenix.util.TestUtil.E_VALUE; +import static org.apache.phoenix.util.TestUtil.ROW1; +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.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.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.util.Arrays; +import java.util.Collection; +import java.util.List; +import java.util.Properties; + +import org.apache.phoenix.util.PhoenixRuntime; +import org.apache.phoenix.util.PropertiesUtil; +import org.junit.Test; +import org.junit.runners.Parameterized.Parameters; + +import com.google.common.collect.Lists; + +public class PointInTimeQueryIT extends BaseQueryIT { + + @Parameters(name="PointInTimeQueryIT_{index},mutable={1},columnEncoded={2}") + @Shadower(classBeingShadowed = BaseQueryIT.class) + public static Collection<Object> data() { + List<Object> testCases = Lists.newArrayList(); + for (String indexDDL : INDEX_DDLS) { + for (boolean columnEncoded : new boolean[]{false,true}) { + testCases.add(new Object[] { indexDDL, true, columnEncoded }); + } + } + return testCases; + } + + public PointInTimeQueryIT(String idxDdl, boolean mutable, boolean columnEncoded, boolean keepDeletedCells) + throws Exception { + // These queries fail without KEEP_DELETED_CELLS=true + super(idxDdl, mutable, columnEncoded, true); + } + + @Test + public void testPointInTimeDeleteUngroupedAggregation() throws Exception { + String updateStmt = + "upsert into " + tableName + + " (" + + " ORGANIZATION_ID, " + + " ENTITY_ID, " + + " A_STRING) " + + "VALUES (?, ?, ?)"; + + // Override value that was set at creation time + String url = getUrl(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + + // Remove column value at ts + 1 (i.e. equivalent to setting the value to null) + Connection conn = DriverManager.getConnection(url, props); + PreparedStatement stmt = conn.prepareStatement(updateStmt); + stmt.setString(1, tenantId); + stmt.setString(2, ROW7); + stmt.setString(3, null); + stmt.execute(); + + // Delete row + stmt = conn.prepareStatement("delete from " + tableName + " where organization_id=? and entity_id=?"); + stmt.setString(1, tenantId); + stmt.setString(2, ROW5); + stmt.execute(); + conn.commit(); + conn.close(); + long firstDeleteTime = System.currentTimeMillis(); + long timeDelta = 100; + Thread.sleep(timeDelta); + + // Delete row at timestamp 3. This should not be seen by the query executing + // Remove column value at ts + 1 (i.e. equivalent to setting the value to null) + Connection futureConn = DriverManager.getConnection(getUrl()); + stmt = futureConn.prepareStatement("delete from " + tableName + " where organization_id=? and entity_id=?"); + stmt.setString(1, tenantId); + stmt.setString(2, ROW6); + stmt.execute(); + futureConn.commit(); + futureConn.close(); + + // query at a time which is beyong deleteTime1 but before the time at which above delete + // happened + long queryTime = firstDeleteTime + timeDelta / 2; + + String query = "SELECT count(1) FROM " + tableName + " WHERE organization_id=? and a_string = ?"; + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(queryTime)); // Execute at timestamp 2 + conn = DriverManager.getConnection(getUrl(), props); + PreparedStatement statement = conn.prepareStatement(query); + statement.setString(1, tenantId); + statement.setString(2, B_VALUE); + ResultSet rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(2, rs.getLong(1)); + assertFalse(rs.next()); + conn.close(); + } + + @Test + public void testPointInTimeScan() throws Exception { + // Override value that was set at creation time + String url = getUrl(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection upsertConn = DriverManager.getConnection(url, props); + String upsertStmt = + "upsert into " + tableName + + " (" + + " ORGANIZATION_ID, " + + " ENTITY_ID, " + + " A_INTEGER) " + + "VALUES (?, ?, ?)"; + upsertConn.setAutoCommit(true); // Test auto commit + PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt); + stmt.setString(1, tenantId); + stmt.setString(2, ROW4); + stmt.setInt(3, 5); + stmt.execute(); // should commit too + upsertConn.close(); + long upsert1Time = System.currentTimeMillis(); + long timeDelta = 100; + Thread.sleep(timeDelta); + + upsertConn = DriverManager.getConnection(url, props); + upsertConn.setAutoCommit(true); // Test auto commit + stmt = upsertConn.prepareStatement(upsertStmt); + stmt.setString(1, tenantId); + stmt.setString(2, ROW4); + stmt.setInt(3, 9); + stmt.execute(); // should commit too + upsertConn.close(); + + long queryTime = upsert1Time + timeDelta / 2; + String query = "SELECT organization_id, a_string AS a FROM " + tableName + " WHERE organization_id=? and a_integer = 5"; + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(queryTime)); + Connection conn = DriverManager.getConnection(getUrl(), props); + PreparedStatement statement = conn.prepareStatement(query); + statement.setString(1, tenantId); + ResultSet rs = statement.executeQuery(); + assertTrue(rs.next()); + assertEquals(tenantId, rs.getString(1)); + assertEquals(A_VALUE, rs.getString("a")); + assertTrue(rs.next()); + assertEquals(tenantId, rs.getString(1)); + assertEquals(B_VALUE, rs.getString(2)); + assertFalse(rs.next()); + conn.close(); + } + + @SuppressWarnings("unchecked") + @Test + public void testPointInTimeLimitedScan() throws Exception { + // Override value that was set at creation time + String url = getUrl(); + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection upsertConn = DriverManager.getConnection(url, props); + String upsertStmt = + "upsert into " + tableName + + " (" + + " ORGANIZATION_ID, " + + " ENTITY_ID, " + + " A_INTEGER) " + + "VALUES (?, ?, ?)"; + upsertConn.setAutoCommit(true); // Test auto commit + // Insert all rows at ts + PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt); + stmt.setString(1, tenantId); + stmt.setString(2, ROW1); + stmt.setInt(3, 6); + stmt.execute(); // should commit too + upsertConn.close(); + long upsert1Time = System.currentTimeMillis(); + long timeDelta = 100; + Thread.sleep(timeDelta); + + url = getUrl(); + upsertConn = DriverManager.getConnection(url, props); + upsertConn.setAutoCommit(true); // Test auto commit + // Insert all rows at ts + stmt = upsertConn.prepareStatement(upsertStmt); + stmt.setString(1, tenantId); + stmt.setString(2, ROW1); + stmt.setInt(3, 0); + stmt.execute(); // should commit too + upsertConn.close(); + + long queryTime = upsert1Time + timeDelta / 2; + String query = "SELECT a_integer,b_string FROM " + tableName + " WHERE organization_id=? and a_integer <= 5 limit 2"; + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(queryTime)); + Connection conn = DriverManager.getConnection(getUrl(), props); + PreparedStatement statement = conn.prepareStatement(query); + statement.setString(1, tenantId); + ResultSet rs = statement.executeQuery(); + List<List<Object>> expectedResultsA = Lists.newArrayList( + Arrays.<Object>asList(2, C_VALUE), + Arrays.<Object>asList( 3, E_VALUE)); + List<List<Object>> expectedResultsB = Lists.newArrayList( + Arrays.<Object>asList( 5, C_VALUE), + Arrays.<Object>asList(4, B_VALUE)); + // Since we're not ordering and we may be using a descending index, we don't + // know which rows we'll get back. + assertOneOfValuesEqualsResultSet(rs, expectedResultsA,expectedResultsB); + conn.close(); + } + +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java index c5283cd..a6d8a21 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryIT.java @@ -41,15 +41,10 @@ import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Time; -import java.sql.Timestamp; import java.util.Arrays; import java.util.Properties; -import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp; -import org.apache.hadoop.hbase.io.ImmutableBytesWritable; import org.apache.phoenix.exception.SQLExceptionCode; -import org.apache.phoenix.schema.types.PTimestamp; import org.apache.phoenix.util.PropertiesUtil; import org.junit.Test; @@ -168,70 +163,6 @@ public class QueryIT extends BaseQueryIT { } @Test - public void testTimestamp() throws Exception { - String updateStmt = - "upsert into " + tableName + - " (" + - " ORGANIZATION_ID, " + - " ENTITY_ID, " + - " A_TIMESTAMP) " + - "VALUES (?, ?, ?)"; - // Override value that was set at creation time - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection upsertConn = DriverManager.getConnection(url, props); - upsertConn.setAutoCommit(true); // Test auto commit - PreparedStatement stmt = upsertConn.prepareStatement(updateStmt); - stmt.setString(1, tenantId); - stmt.setString(2, ROW4); - Timestamp tsValue1 = new Timestamp(5000); - byte[] ts1 = PTimestamp.INSTANCE.toBytes(tsValue1); - stmt.setTimestamp(3, tsValue1); - stmt.execute(); - - Connection conn1 = DriverManager.getConnection(url, props); - analyzeTable(conn1, tableName); - conn1.close(); - - updateStmt = - "upsert into " + tableName + - " (" + - " ORGANIZATION_ID, " + - " ENTITY_ID, " + - " A_TIMESTAMP," + - " A_TIME) " + - "VALUES (?, ?, ?, ?)"; - stmt = upsertConn.prepareStatement(updateStmt); - stmt.setString(1, tenantId); - stmt.setString(2, ROW5); - Timestamp tsValue2 = new Timestamp(5000); - tsValue2.setNanos(200); - byte[] ts2 = PTimestamp.INSTANCE.toBytes(tsValue2); - stmt.setTimestamp(3, tsValue2); - stmt.setTime(4, new Time(tsValue2.getTime())); - stmt.execute(); - upsertConn.close(); - - assertTrue(compare(CompareOp.GREATER, new ImmutableBytesWritable(ts2), new ImmutableBytesWritable(ts1))); - assertFalse(compare(CompareOp.GREATER, new ImmutableBytesWritable(ts1), new ImmutableBytesWritable(ts1))); - - String query = "SELECT entity_id, a_timestamp, a_time FROM " + tableName + " WHERE organization_id=? and a_timestamp > ?"; - Connection conn = DriverManager.getConnection(url, props); - try { - PreparedStatement statement = conn.prepareStatement(query); - statement.setString(1, tenantId); - statement.setTimestamp(2, new Timestamp(5000)); - ResultSet rs = statement.executeQuery(); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW5); - assertEquals(rs.getTimestamp("A_TIMESTAMP"), tsValue2); - assertEquals(rs.getTime("A_TIME"), new Time(tsValue2.getTime())); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @Test public void testSimpleInListStatement() throws Exception { String query = "SELECT entity_id FROM " + tableName + " WHERE organization_id=? AND a_integer IN (2,4)"; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); @@ -322,52 +253,6 @@ public class QueryIT extends BaseQueryIT { } @Test - public void testIsNull() throws Exception { - String query = "SELECT entity_id FROM " + tableName + " WHERE X_DECIMAL is null"; - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - PreparedStatement statement = conn.prepareStatement(query); - ResultSet rs = statement.executeQuery(); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW1); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW2); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW3); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW4); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW5); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW6); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @Test - public void testIsNotNull() throws Exception { - String query = "SELECT entity_id FROM " + tableName + " WHERE X_DECIMAL is not null"; - Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); - Connection conn = DriverManager.getConnection(getUrl(), props); - try { - PreparedStatement statement = conn.prepareStatement(query); - ResultSet rs = statement.executeQuery(); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW7); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW8); - assertTrue (rs.next()); - assertEquals(rs.getString(1), ROW9); - assertFalse(rs.next()); - } finally { - conn.close(); - } - } - - @Test public void testValidStringConcatExpression() throws Exception {//test fails with stack overflow wee int counter=0; String[] answers = new String[]{"00D300000000XHP5bar","a5bar","15bar","5bar","5bar"}; http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/it/java/org/apache/phoenix/end2end/UngroupedIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UngroupedIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UngroupedIT.java index c1c6d31..e562501 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UngroupedIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UngroupedIT.java @@ -20,6 +20,7 @@ package org.apache.phoenix.end2end; import static org.apache.phoenix.util.TestUtil.B_VALUE; import static org.apache.phoenix.util.TestUtil.C_VALUE; import static org.apache.phoenix.util.TestUtil.E_VALUE; +import static org.apache.phoenix.util.TestUtil.ROW3; import static org.apache.phoenix.util.TestUtil.ROW5; import static org.apache.phoenix.util.TestUtil.ROW6; import static org.apache.phoenix.util.TestUtil.ROW7; @@ -37,14 +38,15 @@ import java.util.Properties; import org.apache.phoenix.util.PhoenixRuntime; import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.TestUtil; import org.junit.Test; import org.junit.runners.Parameterized.Parameters; public class UngroupedIT extends BaseQueryIT { - public UngroupedIT(String idxDdl, boolean mutable, boolean columnEncoded, boolean keepDeletedCells) + public UngroupedIT(String idxDdl, boolean mutable, boolean columnEncoded) throws Exception { - super(idxDdl, mutable, columnEncoded, keepDeletedCells); + super(idxDdl, mutable, columnEncoded, false); } @Parameters(name="UngroupedIT_{index}") // name is used by failsafe as file name in reports @@ -201,4 +203,42 @@ public class UngroupedIT extends BaseQueryIT { assertFalse(rs.next()); conn.close(); } + + @Test + public void testSumOverNullIntegerColumn() throws Exception { + String query = "SELECT sum(a_integer) FROM " + tableName + " a"; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + conn.setAutoCommit(true); + conn.createStatement().execute("UPSERT INTO " + tableName + " (organization_id,entity_id,a_integer) VALUES('" + getOrganizationId() + "','" + ROW3 + "',NULL)"); + Connection conn1 = DriverManager.getConnection(getUrl(), props); + TestUtil.analyzeTable(conn1, tableName); + conn1.close(); + conn = DriverManager.getConnection(getUrl(), props); + try { + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(42, rs.getInt(1)); + assertFalse(rs.next()); + } finally { + conn.close(); + } + conn = DriverManager.getConnection(getUrl(), props); + conn.setAutoCommit(true); + conn.createStatement().execute("UPSERT INTO " + tableName + " (organization_id,entity_id,a_integer) SELECT organization_id, entity_id, CAST(null AS integer) FROM " + tableName); + + conn = DriverManager.getConnection(getUrl(), props); + try { + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(0, rs.getInt(1)); + assertTrue(rs.wasNull()); + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + } http://git-wip-us.apache.org/repos/asf/phoenix/blob/7a332ced/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java index 765d76d..3fae790 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java @@ -1013,6 +1013,10 @@ public class TestUtil { } } } - + + public static boolean compare(CompareOp op, ImmutableBytesWritable lhsOutPtr, ImmutableBytesWritable rhsOutPtr) { + int compareResult = Bytes.compareTo(lhsOutPtr.get(), lhsOutPtr.getOffset(), lhsOutPtr.getLength(), rhsOutPtr.get(), rhsOutPtr.getOffset(), rhsOutPtr.getLength()); + return ByteUtil.compare(op, compareResult); + } }
