Java Code Examples for org.apache.phoenix.query.QueryConstants#MILLIS_IN_DAY

@Test
public void testEqualsRound() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and trunc(\"DATE\",'DAY')=?";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date equalDate = new Date((startDate.getTime() + 2 * QueryConstants.MILLIS_IN_DAY)/ QueryConstants.MILLIS_IN_DAY*QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate, 1.0);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, equalDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("C", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateAddCompareDate() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and date + 1 >= ?";
    Connection conn = DriverManager.getConnection(url);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 8 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("E", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("F", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testEqualsRound() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and trunc(date,'DAY')=?"; 
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date equalDate = new Date((startDate.getTime() + 2 * QueryConstants.MILLIS_IN_DAY)/ QueryConstants.MILLIS_IN_DAY*QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tenantId, getSplits(tenantId), ts, startDate, 1.0);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, equalDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("C", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateSubtractionCompareNumber() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and ? - date > 3"; 
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 6 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tenantId, getSplits(tenantId), ts, startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("B", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateSubtractionCompareDate() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and date - 1 >= ?";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("F", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateSubtractionCompareDate() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and date - 1 >= ?"; 
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tenantId, getSplits(tenantId), ts, startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("F", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateAddCompareDate() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and date + 1 >= ?"; 
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 8 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tenantId, getSplits(tenantId), ts, startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("E", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("F", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateSubtractionLongToDecimalCompareNumber() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and ? - \"DATE\" - 1.5 > 3";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("B", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("C", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateSubtractionCompareNumber() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and ? - \"DATE\" > 3";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 6 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("B", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
    long finalResult=0;
    
    for(int i=0;i<children.size();i++) {
        if (!children.get(i).evaluate(tuple, ptr) || ptr.getLength() == 0) {
            return false;
        }
        long value;
        PDataType type = children.get(i).getDataType();
        SortOrder sortOrder = children.get(i).getSortOrder();
        if (type == PDecimal.INSTANCE) {
            BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, type, sortOrder);
            value = bd.multiply(BD_MILLIS_IN_DAY).longValue();
        } else if (type.isCoercibleTo(PLong.INSTANCE)) {
            value = type.getCodec().decodeLong(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY;
        } else if (type.isCoercibleTo(PDouble.INSTANCE)) {
            value = (long)(type.getCodec().decodeDouble(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY);
        } else {
            value = type.getCodec().decodeLong(ptr, sortOrder);
        }
        if (i == 0) {
            finalResult = value;
        } else {
            finalResult -= value;
        }
    }
    byte[] resultPtr=new byte[getDataType().getByteSize()];
    getDataType().getCodec().encodeLong(finalResult, resultPtr, 0);
    ptr.set(resultPtr);
    return true;
}
 

@Test
public void testGetWallClockTimeFromCellTimeStamp() {
    long ts = System.currentTimeMillis();
    assertEquals(ts, PhoenixRuntime.getWallClockTimeFromCellTimeStamp(ts));
    long nanoTs = TransactionUtil.convertToNanoseconds(ts);
    assertEquals(ts, PhoenixRuntime.getWallClockTimeFromCellTimeStamp(nanoTs));
    long skewedTs = ts + QueryConstants.MILLIS_IN_DAY; // skew of a day
    // Even with a day of skew, we won't consider the ts a nanos timestamp
    assertEquals(skewedTs, PhoenixRuntime.getWallClockTimeFromCellTimeStamp(skewedTs));
}
 

@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
	long finalResult=0;
	
	for(int i=0;i<children.size();i++) {
	    Expression child = children.get(i);
           if (!child.evaluate(tuple, ptr) || ptr.getLength() == 0) {
               return false;
           }
           PDataType childType = child.getDataType();
           boolean isDate = childType.isCoercibleTo(PDate.INSTANCE);
           long childvalue = childType.getCodec().decodeLong(ptr, child.getSortOrder());
           if (i == 0) {
               finalResult = childvalue;
           } else {
               finalResult -= childvalue;
               /*
                * Special case for date subtraction - note that only first two expression may be dates.
                * We need to convert the date to a unit of "days" because that's what sql expects.
                */
               if (isDate) {
                   finalResult /= QueryConstants.MILLIS_IN_DAY;
               }
           }
	}
	byte[] resultPtr=new byte[getDataType().getByteSize()];
	getDataType().getCodec().encodeLong(finalResult, resultPtr, 0);
	ptr.set(resultPtr);
	return true;
}
 

@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
    long finalResult=0;
    
    for(int i=0;i<children.size();i++) {
        if (!children.get(i).evaluate(tuple, ptr)) {
            return false;
        }
        if (ptr.getLength() == 0) {
            return true;
        }
        long value;
        PDataType type = children.get(i).getDataType();
        SortOrder sortOrder = children.get(i).getSortOrder();
        if (type == PDecimal.INSTANCE) {
            BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, type, sortOrder);
            value = bd.multiply(BD_MILLIS_IN_DAY).longValue();
        } else if (type.isCoercibleTo(PLong.INSTANCE)) {
            value = type.getCodec().decodeLong(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY;
        } else if (type.isCoercibleTo(PDouble.INSTANCE)) {
            value = (long)(type.getCodec().decodeDouble(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY);
        } else {
            value = type.getCodec().decodeLong(ptr, sortOrder);
        }
        finalResult += value;
    }
    byte[] resultPtr = new byte[getDataType().getByteSize()];
    getDataType().getCodec().encodeLong(finalResult, resultPtr, 0);
    ptr.set(resultPtr);
    return true;
}
 

@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
    long finalResult=0;
    
    for(int i=0;i<children.size();i++) {
        if (!children.get(i).evaluate(tuple, ptr) || ptr.getLength() == 0) {
            return false;
        }
        long value;
        PDataType type = children.get(i).getDataType();
        SortOrder sortOrder = children.get(i).getSortOrder();
        if (type == PDecimal.INSTANCE) {
            BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, sortOrder);
            value = bd.multiply(BD_MILLIS_IN_DAY).longValue();
        } else if (type.isCoercibleTo(PLong.INSTANCE)) {
            value = type.getCodec().decodeLong(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY;
        } else if (type.isCoercibleTo(PDouble.INSTANCE)) {
            value = (long)(type.getCodec().decodeDouble(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY);
        } else {
            value = type.getCodec().decodeLong(ptr, sortOrder);
        }
        if (i == 0) {
            finalResult = value;
        } else {
            finalResult -= value;
        }
    }
    byte[] resultPtr=new byte[getDataType().getByteSize()];
    getDataType().getCodec().encodeLong(finalResult, resultPtr, 0);
    ptr.set(resultPtr);
    return true;
}
 

@Test
public void testDateSubtractionLongToDecimalCompareNumber() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature FROM PRODUCT_METRICS WHERE organization_id = ? and ? - date - 1.5 > 3"; 
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        Date startDate = new Date(System.currentTimeMillis());
        Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
        initDateTableValues(tenantId, getSplits(tenantId), ts, startDate);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setDate(2, endDate);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("B", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("C", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
	long finalResult=0;
	
	for(int i=0;i<children.size();i++) {
	    Expression child = children.get(i);
           if (!child.evaluate(tuple, ptr) || ptr.getLength() == 0) {
               return false;
           }
           PDataType childType = child.getDataType();
           boolean isDate = childType.isCoercibleTo(PDate.INSTANCE);
           long childvalue = childType.getCodec().decodeLong(ptr, child.getSortOrder());
           if (i == 0) {
               finalResult = childvalue;
           } else {
               finalResult -= childvalue;
               /*
                * Special case for date subtraction - note that only first two expression may be dates.
                * We need to convert the date to a unit of "days" because that's what sql expects.
                */
               if (isDate) {
                   finalResult /= QueryConstants.MILLIS_IN_DAY;
               }
           }
	}
	byte[] resultPtr=new byte[getDataType().getByteSize()];
	getDataType().getCodec().encodeLong(finalResult, resultPtr, 0);
	ptr.set(resultPtr);
	return true;
}
 

@Override
public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
    long finalResult=0;
    
    for(int i=0;i<children.size();i++) {
        if (!children.get(i).evaluate(tuple, ptr)) {
            return false;
        }
        if (ptr.getLength() == 0) {
            return true;
        }
        long value;
        PDataType type = children.get(i).getDataType();
        SortOrder sortOrder = children.get(i).getSortOrder();
        if (type == PDecimal.INSTANCE) {
            BigDecimal bd = (BigDecimal) PDecimal.INSTANCE.toObject(ptr, sortOrder);
            value = bd.multiply(BD_MILLIS_IN_DAY).longValue();
        } else if (type.isCoercibleTo(PLong.INSTANCE)) {
            value = type.getCodec().decodeLong(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY;
        } else if (type.isCoercibleTo(PDouble.INSTANCE)) {
            value = (long)(type.getCodec().decodeDouble(ptr, sortOrder) * QueryConstants.MILLIS_IN_DAY);
        } else {
            value = type.getCodec().decodeLong(ptr, sortOrder);
        }
        finalResult += value;
    }
    byte[] resultPtr = new byte[getDataType().getByteSize()];
    getDataType().getCodec().encodeLong(finalResult, resultPtr, 0);
    ptr.set(resultPtr);
    return true;
}
 

@Test
public void testExecuteStatements() throws Exception {
    String tenantId = getOrganizationId();
    String tableName = initATableValues(tenantId, getDefaultSplits(tenantId), getUrl());
    String ptsdbTableName = generateUniqueName();
    String statements = 
        "create table if not exists " + tableName + // 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 " + ptsdbTableName +
        "   (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 ('a','j','s');\n" +
        "alter table " + ptsdbTableName + " add if not exists val decimal;\n" +  // Shouldn't error out b/c of if not exists clause
        "alter table " + ptsdbTableName + " 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 " + ptsdbTableName + "(\"DATE\", val, host) " +
        "    SELECT current_date(), x_integer+2, entity_id FROM " + tableName + " WHERE a_integer >= ?;" +
        "UPSERT INTO " + ptsdbTableName + "(\"DATE\", val, inst)\n" +
        "    SELECT \"DATE\"+1, val*10, host FROM " + ptsdbTableName + ";";
    
    Date now = new Date(System.currentTimeMillis());
    Connection conn = DriverManager.getConnection(getUrl());
    conn.setAutoCommit(true);
    List<Object> binds = Arrays.<Object>asList(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 " + ptsdbTableName + " 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 testExecuteStatements() throws Exception {
    String tenantId = getOrganizationId();
    initATableValues(tenantId, getDefaultSplits(tenantId), getUrl());
    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();
}