You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Samarth Jain (JIRA)" <ji...@apache.org> on 2014/10/15 23:44:34 UTC
[jira] [Updated] (PHOENIX-1354) Upsert select not inserting all the
rows when using row value constructors
[ https://issues.apache.org/jira/browse/PHOENIX-1354?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Samarth Jain updated PHOENIX-1354:
----------------------------------
Description:
{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}
was:
{code}
@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 cursorid from cursor_storage");
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
System.out.println(Base64.encodeBytes(rs.getBytes(1)));
}
}
{code}
> 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)