[
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Daniel Wong updated PHOENIX-5698:
---------------------------------
Description:
n the code below ideally we'd expect a SINGLE ROW DELETE plan client side.
However, this generates an incorrect scan with range ['tenant1
0CY005xx000001Sv6o'). If the order of the RVCs is changed to row key order
Phoenix correctly generates a SINGLE ROW SCAN. As we provide the full PK this
we expect a either tightly bounded range scan or a client side delete. Instead
we get a range scan on composite leading edge TENANT_ID,KEY_PREFIX,ID1.
{code:java}
@Test
public void testInListExpressionWithDescAgain() throws Exception {
String fullTableName = generateUniqueName();
String fullViewName = generateUniqueName();
String tenantView = generateUniqueName();
// create base table and global view using global connection
try (Connection conn = DriverManager.getConnection(getUrl()))
{ conn.setAutoCommit(true); Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15) NOT
NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" + " CONSTRAINT PK PRIMARY KEY (\n"
+ " TENANT_ID," + " KEY_PREFIX" + ")) MULTI_TENANT=TRUE"); stmt.execute("CREATE
VIEW " + fullViewName + "(\n" + " ID1 VARCHAR NOT NULL,\n" + " ID2 VARCHAR NOT
NULL,\n" + " EVENT_DATE DATE NOT NULL,\n" + " CONSTRAINT PKVIEW PRIMARY KEY\n"
+ " (\n" + " ID1, ID2 DESC, EVENT_DATE DESC\n" + ")) AS SELECT * FROM " +
fullTableName + " WHERE KEY_PREFIX = '0CY'"); }
// create and use a tenant specific view to write data
try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) )
{
viewConn.setAutoCommit(true); //need autocommit for serverside deletion
Statement stmt = viewConn.createStatement();
stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * FROM "
+ fullViewName );
viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000300', 1532458230000)");
viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000400', 1532458240000)");
viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000500', 1532458250000)");
viewConn.commit();
ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " +
tenantView );
printResultSet(rs);
System.out.println("Delete Start");
rs = stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + " WHERE (ID1,
EVENT_DATE, ID2) IN (('005xx000001Sv6o', 1532458240000,
'000000000000400'),('005xx000001Sv6o', 1532458230000, '000000000000300'))");
printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN
(('005xx000001Sv6o', 1532458240000, '000000000000400'),('005xx000001Sv6o',
1532458230000, '000000000000300'))");
viewConn.commit();
System.out.println("Delete End");
rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " + tenantView );
printResultSet(rs);
}
}
private void printResultSet(ResultSet rs) throws SQLException {
StringBuilder builder = new StringBuilder();
while(rs.next()) {
for(int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
Object col = rs.getObject(i + 1);
if(col == null)
{ builder.append("null"); }
else {
if(col instanceof Date)
{ DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
builder.append(df.format(col)); }
else {
builder.append(col.toString());
}
}
builder.append(",");
}
builder.append("\n");
}
System.out.println(builder.toString());
}
{code}
was:
n the code below ideally we'd expect a SINGLE ROW DELETE plan client side.
However, this generates an incorrect scan with range ['tenant1
0CY005xx000001Sv6o'). If the order of the RVCs is changed to row key order
Phoenix correctly generates a SINGLE ROW SCAN.
{code:java}
@Test
public void testInListExpressionWithDescAgain() throws Exception {
String fullTableName = generateUniqueName();
String fullViewName = generateUniqueName();
String tenantView = generateUniqueName();
// create base table and global view using global connection
try (Connection conn = DriverManager.getConnection(getUrl()))
{ conn.setAutoCommit(true); Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15) NOT
NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" + " CONSTRAINT PK PRIMARY KEY (\n"
+ " TENANT_ID," + " KEY_PREFIX" + ")) MULTI_TENANT=TRUE"); stmt.execute("CREATE
VIEW " + fullViewName + "(\n" + " ID1 VARCHAR NOT NULL,\n" + " ID2 VARCHAR NOT
NULL,\n" + " EVENT_DATE DATE NOT NULL,\n" + " CONSTRAINT PKVIEW PRIMARY KEY\n"
+ " (\n" + " ID1, ID2 DESC, EVENT_DATE DESC\n" + ")) AS SELECT * FROM " +
fullTableName + " WHERE KEY_PREFIX = '0CY'"); }
// create and use a tenant specific view to write data
try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1) )
{
viewConn.setAutoCommit(true); //need autocommit for serverside deletion
Statement stmt = viewConn.createStatement();
stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * FROM "
+ fullViewName );
viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000300', 1532458230000)");
viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000400', 1532458240000)");
viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000500', 1532458250000)");
viewConn.commit();
ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " +
tenantView );
printResultSet(rs);
System.out.println("Delete Start");
rs = stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + " WHERE (ID1,
EVENT_DATE, ID2) IN (('005xx000001Sv6o', 1532458240000,
'000000000000400'),('005xx000001Sv6o', 1532458230000, '000000000000300'))");
printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN
(('005xx000001Sv6o', 1532458240000, '000000000000400'),('005xx000001Sv6o',
1532458230000, '000000000000300'))");
viewConn.commit();
System.out.println("Delete End");
rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " + tenantView );
printResultSet(rs);
}
}
private void printResultSet(ResultSet rs) throws SQLException {
StringBuilder builder = new StringBuilder();
while(rs.next()) {
for(int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
Object col = rs.getObject(i + 1);
if(col == null)
{ builder.append("null"); }
else {
if(col instanceof Date)
{ DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
builder.append(df.format(col)); }
else {
builder.append(col.toString());
}
}
builder.append(",");
}
builder.append("\n");
}
System.out.println(builder.toString());
}
{code}
> Phoenix Query with RVC IN list expression generates wrong scan with non-pk
> ordered pks
> --------------------------------------------------------------------------------------
>
> Key: PHOENIX-5698
> URL: https://issues.apache.org/jira/browse/PHOENIX-5698
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.15.0, 4.14.3
> Reporter: Daniel Wong
> Priority: Major
> Labels: DESC
>
> n the code below ideally we'd expect a SINGLE ROW DELETE plan client side.
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx000001Sv6o'). If the order of the RVCs is changed to row key order
> Phoenix correctly generates a SINGLE ROW SCAN. As we provide the full PK
> this we expect a either tightly bounded range scan or a client side delete.
> Instead we get a range scan on composite leading edge
> TENANT_ID,KEY_PREFIX,ID1.
>
> {code:java}
> @Test
> public void testInListExpressionWithDescAgain() throws Exception {
> String fullTableName = generateUniqueName();
> String fullViewName = generateUniqueName();
> String tenantView = generateUniqueName();
> // create base table and global view using global connection
> try (Connection conn = DriverManager.getConnection(getUrl()))
> { conn.setAutoCommit(true); Statement stmt = conn.createStatement();
> stmt.execute("CREATE TABLE " + fullTableName + "(\n" + " TENANT_ID CHAR(15)
> NOT NULL,\n" + " KEY_PREFIX CHAR(3) NOT NULL,\n" + " CONSTRAINT PK PRIMARY
> KEY (\n" + " TENANT_ID," + " KEY_PREFIX" + ")) MULTI_TENANT=TRUE");
> stmt.execute("CREATE VIEW " + fullViewName + "(\n" + " ID1 VARCHAR NOT
> NULL,\n" + " ID2 VARCHAR NOT NULL,\n" + " EVENT_DATE DATE NOT NULL,\n" + "
> CONSTRAINT PKVIEW PRIMARY KEY\n" + " (\n" + " ID1, ID2 DESC, EVENT_DATE
> DESC\n" + ")) AS SELECT * FROM " + fullTableName + " WHERE KEY_PREFIX =
> '0CY'"); }
> // create and use a tenant specific view to write data
> try (Connection viewConn = DriverManager.getConnection(TENANT_SPECIFIC_URL1)
> ) {
> viewConn.setAutoCommit(true); //need autocommit for serverside deletion
> Statement stmt = viewConn.createStatement();
> stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS SELECT * FROM
> " + fullViewName );
> viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
> EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000300', 1532458230000)");
> viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
> EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000400', 1532458240000)");
> viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2,
> EVENT_DATE) VALUES ('005xx000001Sv6o', '000000000000500', 1532458250000)");
> viewConn.commit();
> ResultSet rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " +
> tenantView );
> printResultSet(rs);
> System.out.println("Delete Start");
> rs = stmt.executeQuery("EXPLAIN DELETE FROM " + tenantView + " WHERE (ID1,
> EVENT_DATE, ID2) IN (('005xx000001Sv6o', 1532458240000,
> '000000000000400'),('005xx000001Sv6o', 1532458230000, '000000000000300'))");
> printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN
> (('005xx000001Sv6o', 1532458240000, '000000000000400'),('005xx000001Sv6o',
> 1532458230000, '000000000000300'))");
> viewConn.commit();
> System.out.println("Delete End");
> rs = stmt.executeQuery("SELECT ID1, ID2, EVENT_DATE FROM " + tenantView );
> printResultSet(rs);
> }
> }
> private void printResultSet(ResultSet rs) throws SQLException {
> StringBuilder builder = new StringBuilder();
> while(rs.next()) {
> for(int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
> Object col = rs.getObject(i + 1);
> if(col == null)
> { builder.append("null"); }
> else {
> if(col instanceof Date)
> { DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> builder.append(df.format(col)); }
> else {
> builder.append(col.toString());
> }
> }
> builder.append(",");
> }
> builder.append("\n");
> }
> System.out.println(builder.toString());
> }
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
