[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Lars Hofhansl (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Lars Hofhansl updated PHOENIX-5698:
---
Fix Version/s: 5.1.0

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Fix For: 5.1.0, 4.16.0
>
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-4.x.v4.patch, 
> PHOENIX-5698-4.x.v5.patch, PHOENIX-5698-4.x.v6.patch, 
> PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 8h
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Chinmay Kulkarni (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Chinmay Kulkarni updated PHOENIX-5698:
--
Fix Version/s: 4.16.0

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Fix For: 4.16.0
>
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-4.x.v4.patch, 
> PHOENIX-5698-4.x.v5.patch, PHOENIX-5698-4.x.v6.patch, 
> PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 8h
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x.v6.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-4.x.v4.patch, 
> PHOENIX-5698-4.x.v5.patch, PHOENIX-5698-4.x.v6.patch, 
> PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 7h 50m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.x.v6.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-4.x.v4.patch, 
> PHOENIX-5698-4.x.v5.patch, PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 7h 50m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x.v6.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-4.x.v4.patch, 
> PHOENIX-5698-4.x.v5.patch, PHOENIX-5698-4.x.v6.patch, 
> PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 7h 50m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x.v5.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-4.x.v4.patch, 
> PHOENIX-5698-4.x.v5.patch, PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 7h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x.v4.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-4.x.v4.patch, 
> PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 5h 20m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x.v3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-4.x.v3.patch, PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 5h 20m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-4.x.patch, 
> PHOENIX-5698-master.v2.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 50m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-master.v2.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-master.v2.patch, 
> PHOENIX-5698.patch
>
>  Time Spent: 4h 50m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-master.v2.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-master.v2.patch, 
> PHOENIX-5698.patch
>
>  Time Spent: 4h 50m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-master.v2.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698-master.v2.patch, 
> PHOENIX-5698.patch
>
>  Time Spent: 4h 50m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.x-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.x-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.x-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.x-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.14-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: (was: PHOENIX-5698-4.x-HBase-1.3.patch)

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch, PHOENIX-5698.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.14-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.14-HBase-1.3.patch, 
> PHOENIX-5698-4.x-HBase-1.3.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Xinyi Yan (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Xinyi Yan updated PHOENIX-5698:
---
Attachment: PHOENIX-5698-4.x-HBase-1.3.patch

> 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
>Assignee: Xinyi Yan
>Priority: Major
>  Labels: DESC
> Attachments: PHOENIX-5698-4.x-HBase-1.3.patch
>
>  Time Spent: 4h 40m
>  Remaining Estimate: 0h
>
> In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
> However, this generates an incorrect scan with range ['tenant1
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Chinmay Kulkarni (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Chinmay Kulkarni updated PHOENIX-5698:
--
Description: 
In the code below ideally we'd expect a SINGLE ROW DELETE plan client side. 
However, this generates an incorrect scan with range ['tenant1
0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
 viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
 viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
 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 (('005xx01Sv6o', 153245824, 
'400'),('005xx01Sv6o', 153245823, '300'))");
 printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
(('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
153245823, '300'))");
 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("-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
0CY005xx01Sv6o'). 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 

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Daniel Wong (Jira)]

 [ 
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
0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
 viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
 viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
 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 (('005xx01Sv6o', 153245824, 
'400'),('005xx01Sv6o', 153245823, '300'))");
 printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
(('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
153245823, '300'))");
 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("-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
0CY005xx01Sv6o'). 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 

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Daniel Wong (Jira)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-5698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Daniel Wong updated PHOENIX-5698:
-
Labels: DESC  (was: )

> 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
> 0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
>  viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
> EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
>  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 (('005xx01Sv6o', 153245824, 
> '400'),('005xx01Sv6o', 153245823, '300'))");
>  printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
> stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
> (('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
> 153245823, '300'))");
>  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("-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)

[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks

[Daniel Wong (Jira)]

 [ 
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
0CY005xx01Sv6o'). 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 ('005xx01Sv6o', '300', 153245823)");
 viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
EVENT_DATE) VALUES ('005xx01Sv6o', '400', 153245824)");
 viewConn.createStatement().execute("UPSERT INTO " + tenantView + "(ID1, ID2, 
EVENT_DATE) VALUES ('005xx01Sv6o', '500', 153245825)");
 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 (('005xx01Sv6o', 153245824, 
'400'),('005xx01Sv6o', 153245823, '300'))");
 printResultSet(rs); // THIS SHOULD BE A SINGLE ROW SCAN
stmt.execute("DELETE FROM " + tenantView + " WHERE (ID1, EVENT_DATE, ID2) IN 
(('005xx01Sv6o', 153245824, '400'),('005xx01Sv6o', 
153245823, '300'))");
 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("-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
0CY005xx01Sv6o'). If the order of the RVCs is changed to row key order 
Phoenix correctly generates a SINGLE ROW SCAN.


@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" +
"