[jira] [Commented] (PHOENIX-118) Type mismatch error on upsert for unsigned_date and date

["James Taylor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13923327#comment-13923327 ] 

James Taylor commented on PHOENIX-118:
--------------------------------------

I'm having a hard time reproducing this. Here's my test (that passes). I have a local change list - not sure if that's affecting or not. Do you see any difference with your case?
{code}
    private static Format DATE_FORMAT = DateUtil.getDateParser(DateUtil.DEFAULT_DATE_FORMAT);
    
    private static Date toDate(String dateString) {
        try {
            return (Date)DATE_FORMAT.parseObject(dateString);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }
    
    @Test
    public void testUpsertDateIntoUnsignedDate() throws Exception {
        long ts = nextTimestamp();
        Properties props = new Properties();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager.getConnection(getUrl(), props);
            stmt = conn.prepareStatement("create table UpsertTimestamp (k varchar, v unsigned_date not null, constraint pk primary key (k,v))");
            stmt.execute();
        } finally {
            closeStmtAndConn(stmt, conn);
        }
        
        String dateStr = "2013-01-01 04:00:00";
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2));
        try {
            conn = DriverManager.getConnection(getUrl(), props);
            stmt = conn.prepareStatement("upsert into UpsertTimestamp(k,v) values ('a', to_date(?))");
            stmt.setString(1, dateStr);
            stmt.executeUpdate();
            conn.commit();
        } finally {
             closeStmtAndConn(stmt, conn);
        }
        
        Date date = toDate(dateStr);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4));
        try {
            conn = DriverManager.getConnection(getUrl(), props);
            stmt = conn.prepareStatement("select * from UpsertTimestamp");
            ResultSet rs = stmt.executeQuery();
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals(date, rs.getDate(2));
            assertFalse(rs.next());
        } finally {
             closeStmtAndConn(stmt, conn);
        }
    }
{code}

> Type mismatch error on upsert for unsigned_date and date
> --------------------------------------------------------
>
>                 Key: PHOENIX-118
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-118
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 3.0.0
>            Reporter: Mujtaba Chohan
>            Assignee: James Taylor
>             Fix For: 3.0.0
>
>
> Following upsert statement fails for unsigned_date
> upsert into TEST values (TO_DATE('2013-01-04 01:01:01'));
> Error:ERROR 203 (22005): Type mismatch. DATE and UNSIGNED_DATE for expression: '2013-01-04 01:01:01.000' in column CREATED_DATE (state=22005,code=203)
> Uncovered during 2.x > 3.0 upgrade when date type is changed to unsigned_date type.



--
This message was sent by Atlassian JIRA
(v6.2#6252)