[jira] [Commented] (PHOENIX-1354) Upsert select not inserting all the rows when using row value constructors

["Samarth Jain (JIRA)" <ji...@apache.org>]

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

Samarth Jain commented on PHOENIX-1354:
---------------------------------------

The row value constructor isn't getting evaluated for each row that is getting upserted. 

> Upsert select not inserting all the rows when using row value constructors
> --------------------------------------------------------------------------
>
>                 Key: PHOENIX-1354
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-1354
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 5.0.0, 4.2
>            Reporter: Samarth Jain
>            Assignee: James Taylor
>            Priority: Critical
>
> {code}
> @Test
>     @Test
>     public void testUpsertSelectRVC() throws Exception {
>         String cursorTableDDL = "CREATE TABLE IF NOT EXISTS " + 
>                 "CURSOR_STORAGE" +  " (\n" +  
>                 "TENANT_ID VARCHAR(15) NOT NULL\n," +  
>                 "QUERY_ID VARCHAR(15) NOT NULL,\n" +
>                 "CURSOR_ORDER BIGINT NOT NULL,\n" + 
>                 "CURSORID VARBINARY " + 
>                 "CONSTRAINT CURSOR_TABLE_PK PRIMARY KEY (TENANT_ID, QUERY_ID, CURSOR_ORDER)) "+
>                 "SALT_BUCKETS = 4, TTL=86400";
>         String baseDataTableDDL = "CREATE TABLE IF NOT EXISTS " +
>                 "HISTORY_TABLE" + " (\n" + 
>                 "TENANT_ID CHAR(15) NOT NULL,\n" +
>                 "PARENT_ID CHAR(15) NOT NULL,\n" + 
>                 "CREATED_DATE DATE NOT NULL,\n" + 
>                 "ENTITY_HISTORY_ID CHAR(15) NOT NULL,\n" + 
>                 "DATA_TYPE VARCHAR,\n" + 
>                 "OLDVAL_STRING VARCHAR,\n" + 
>                 "NEWVAL_STRING VARCHAR\n" + 
>                 "CONSTRAINT PK PRIMARY KEY(TENANT_ID, PARENT_ID, CREATED_DATE DESC, ENTITY_HISTORY_ID)) " + 
>                 "VERSIONS = 1";
>         Properties props = new Properties();
>         Connection conn = DriverManager.getConnection(getUrl(), props);
>         conn.createStatement().execute(cursorTableDDL);
>         conn.createStatement().execute(baseDataTableDDL);
>         
>         String tenantId = "00DxxxxxxxxxABC";
>         String dml = "UPSERT INTO HISTORY_TABLE VALUES (?, ?, ?, ?, ?, ?, ?)";
>         PreparedStatement stmt = conn.prepareStatement(dml);
>         for (int i = 1; i <=5; i++) {
>             stmt.setString(1, tenantId);
>             stmt.setString(2, "00Pxxxxxxxxxxx" + i);
>             stmt.setDate(3, new Date(i));
>             stmt.setString(4, "eidXXXXXXXXXXX" + i);
>             stmt.setString(5, "datatype");
>             stmt.setString(6, "oldv");
>             stmt.setString(7, "newv");
>             stmt.executeUpdate();
>         }
>         conn.commit();
>         
>         String sequenceName = "SEQU";
>         conn.createStatement().execute("CREATE SEQUENCE " + sequenceName + " CACHE " + Long.MAX_VALUE);
>         conn.setAutoCommit(true);
>         String upsertSelect = "UPSERT INTO CURSOR_STORAGE (tenant_id, query_id, cursor_order, cursorId) SELECT ?, ?, NEXT VALUE FOR " + sequenceName + ", (TENANT_ID, PARENT_ID, CREATED_DATE, ENTITY_HISTORY_ID) FROM HISTORY_TABLE";
>         stmt = conn.prepareStatement(upsertSelect);
>         stmt.setString(1, tenantId);
>         stmt.setString(2, "cursorqueryid");
>         stmt.executeUpdate();
>         
>         stmt = conn.prepareStatement("select cursor_order, cursorid from cursor_storage");
>         ResultSet rs = stmt.executeQuery();
>         int i = 1;
>         String previousCursorId = null;
>         while(rs.next()) {
>             assertEquals("Cursor order didn't match", i, rs.getInt(1));
>             assertFalse("Cursor ids should be unequal. Failed for i = " + i, Base64.encodeBytes(rs.getBytes(2)).equals(previousCursorId));
>             previousCursorId = Base64.encodeBytes(rs.getBytes(2));
>             i++;
>         }
>     }
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)