@Test
  public void testUpsertValuesWithExpression() throws Exception {
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
    Properties props = new Properties();
    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
    PreparedStatement upsertStmt = conn.prepareStatement(upsert);
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
    upsertStmt = conn.prepareStatement(upsert);
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();
    conn.close();

    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
    conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String select = "SELECT i FROM IntKeyTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(-1, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(3, rs.getInt(1));
    assertFalse(rs.next());
  }
  @Test
  public void testUpsertDateValues() throws Exception {
    long ts = nextTimestamp();
    Date now = new Date(System.currentTimeMillis());
    ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
    Properties props = new Properties();
    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String dateString = "1999-01-01 02:00:00";
    PreparedStatement upsertStmt =
        conn.prepareStatement(
            "upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    upsertStmt =
        conn.prepareStatement(
            "upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();

    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
    conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String select = "SELECT date,current_date() FROM ptsdb";
    ResultSet rs = conn.createStatement().executeQuery(select);
    Date then = new Date(System.currentTimeMillis());
    assertTrue(rs.next());
    Date date = DateUtil.parseDate(dateString);
    assertEquals(date, rs.getDate(1));
    assertTrue(rs.next());
    assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
    assertFalse(rs.next());
  }
  protected static void initTableValues(byte[][] splits, long ts) throws Exception {
    ensureTableCreated(getUrl(), "LongInKeyTest", splits, ts - 2);

    // Insert all rows at ts
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
    Connection conn = DriverManager.getConnection(url);
    conn.setAutoCommit(true);
    PreparedStatement stmt = conn.prepareStatement("upsert into " + "LongInKeyTest VALUES(?)");
    stmt.setLong(1, 2);
    stmt.execute();
    conn.close();
  }
  @Test
  public void testIntegerPK() throws Exception {
    int[] testNumbers = {
      Integer.MIN_VALUE,
      Integer.MIN_VALUE + 1,
      -2,
      -1,
      0,
      1,
      2,
      Integer.MAX_VALUE - 1,
      Integer.MAX_VALUE
    };
    ensureTableCreated(getUrl(), "PKIntValueTest");
    Properties props = new Properties();
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO PKIntValueTest VALUES(?)";
    PreparedStatement stmt = conn.prepareStatement(upsert);
    for (int i = 0; i < testNumbers.length; i++) {
      stmt.setInt(1, testNumbers[i]);
      stmt.execute();
    }
    conn.commit();
    conn.close();

    String select = "SELECT COUNT(*) from PKIntValueTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());

    select = "SELECT count(*) FROM PKIntValueTest where pk >= " + Integer.MIN_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk >= "
            + Integer.MIN_VALUE
            + " GROUP BY pk ORDER BY pk ASC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    // NOTE: This case currently fails with an error message:
    // "Overflow trying to get next key for [-1, -1, -1, -1]"
    select = "SELECT count(*) FROM PKIntValueTest where pk <= " + Integer.MAX_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk <= "
            + Integer.MAX_VALUE
            + " GROUP BY pk ORDER BY pk DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    // NOTE: This case currently fails since it is not retrieving the negative values.
    select = "SELECT count(*) FROM PKIntValueTest where pk >= " + INTEGER_MIN_MINUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk >= "
            + INTEGER_MIN_MINUS_ONE
            + " GROUP BY pk ORDER BY pk ASC NULLS LAST ";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    // NOTE: This test case fails because it is not retrieving positive values.
    select = "SELECT count(*) FROM PKIntValueTest where pk <= " + INTEGER_MAX_PLUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT pk FROM PKIntValueTest where pk <= "
            + INTEGER_MAX_PLUS_ONE
            + " GROUP BY pk ORDER BY pk DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());
  }
  @Test
  public void testBigIntKV() throws Exception {
    // NOTE: Due to how we parse negative long, -9223372036854775808L, the minimum value of
    // bigint is not recognizable in the current version. As a result, we start with
    // Long.MIN_VALUE+1 as the smallest value.
    long[] testNumbers = {
      Long.MIN_VALUE + 1,
      Long.MIN_VALUE + 2,
      -2L,
      -1L,
      0L,
      1L,
      2L,
      Long.MAX_VALUE - 1,
      Long.MAX_VALUE
    };
    ensureTableCreated(getUrl(), "KVBigIntValueTest");
    Properties props = new Properties();
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO KVBigIntValueTest VALUES(?,?)";
    PreparedStatement stmt = conn.prepareStatement(upsert);
    for (int i = 0; i < testNumbers.length; i++) {
      stmt.setLong(1, i);
      stmt.setLong(2, testNumbers[i]);
      stmt.execute();
    }
    conn.commit();
    conn.close();

    String select = "SELECT COUNT(*) from KVBigIntValueTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + (Long.MIN_VALUE + 1);
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVBigIntValueTest WHERE kv >= "
            + (Long.MIN_VALUE + 1)
            + " GROUP BY kv ORDER BY kv ASC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + Long.MAX_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVBigIntValueTest WHERE kv <= "
            + Long.MAX_VALUE
            + " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getLong(1));
    }
    assertFalse(rs.next());

    /* NOTE: This section currently fails due to the fact that we cannot parse literal values
       that are bigger than Long.MAX_VALUE and Long.MIN_VALUE. We will need to fix the parse
       before enabling this section of the test.
    select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + LONG_MIN_MINUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT kv FROM KVBigIntValueTest WHERE kv >= " + LONG_MIN_MINUS_ONE +
            " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + LONG_MAX_PLUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select = "SELECT kv FROM KVBigIntValueTest WHERE kv <= " + LONG_MAX_PLUS_ONE +
            " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length-1; i >= 0; i--) {
        assertTrue(rs.next());
        assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());
    */
  }
  @Test
  public void testIntegerKV() throws Exception {
    int[] testNumbers = {
      Integer.MIN_VALUE,
      Integer.MIN_VALUE + 1,
      -2,
      -1,
      0,
      1,
      2,
      Integer.MAX_VALUE - 1,
      Integer.MAX_VALUE
    };
    ensureTableCreated(getUrl(), "KVIntValueTest");
    Properties props = new Properties();
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO KVIntValueTest VALUES(?, ?)";
    PreparedStatement stmt = conn.prepareStatement(upsert);
    for (int i = 0; i < testNumbers.length; i++) {
      stmt.setInt(1, i);
      stmt.setInt(2, testNumbers[i]);
      stmt.execute();
    }
    conn.commit();
    conn.close();

    String select = "SELECT COUNT(*) from KVIntValueTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv >= " + Integer.MIN_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv >= "
            + Integer.MIN_VALUE
            + " GROUP BY kv ORDER BY kv ASC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv <= " + Integer.MAX_VALUE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv <= "
            + Integer.MAX_VALUE
            + " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv >= " + INTEGER_MIN_MINUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv >= "
            + INTEGER_MIN_MINUS_ONE
            + " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
    rs = conn.createStatement().executeQuery(select);
    for (int i = 0; i < testNumbers.length; i++) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());

    select = "SELECT count(*) FROM KVIntValueTest where kv <= " + INTEGER_MAX_PLUS_ONE;
    rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(testNumbers.length, rs.getInt(1));
    assertFalse(rs.next());
    select =
        "SELECT kv FROM KVIntValueTest WHERE kv <= "
            + INTEGER_MAX_PLUS_ONE
            + " GROUP BY kv ORDER BY kv DESC NULLS LAST";
    rs = conn.createStatement().executeQuery(select);
    for (int i = testNumbers.length - 1; i >= 0; i--) {
      assertTrue(rs.next());
      assertEquals(testNumbers[i], rs.getInt(1));
    }
    assertFalse(rs.next());
  }
Beispiel #7
0
 private long createTable() throws Exception {
   long ts = nextTimestamp();
   ensureTableCreated(getUrl(), GROUPBYTEST_NAME, null, ts - 2);
   return ts;
 }