date:20200302

[jira] [Updated] (PHOENIX-5065) Inconsistent treatment of NULL and empty string

2020-03-02 Thread Richard Antal (Jira)



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

Richard Antal updated PHOENIX-5065:
---
Attachment: PHOENIX-5065.master.v3.patch

> Inconsistent treatment of NULL and empty string
> ---
>
> Key: PHOENIX-5065
> URL: https://issues.apache.org/jira/browse/PHOENIX-5065
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.14.1
>Reporter: Geoffrey Jacoby
>Priority: Major
> Attachments: PHOENIX-5065.master.v1.patch, 
> PHOENIX-5065.master.v2.patch, PHOENIX-5065.master.v3.patch
>
>
> Phoenix doesn't handle NULLs consistently with other SQL dialects, and it 
> doesn't handle them consistently internally either. 
> In PHOENIX-2422, [~jamestaylor] mentioned that Phoenix's intended behavior is 
> for empty string and NULL to be equivalent. That's inconsistent with other 
> SQL dialects (in which NULL is never equal to anything, including itself), 
> but if that's our documented behavior, then that's fine unless PHOENIX-2422 
> to change it is ever worked. 
> But consider the following queries:
> {code:java}
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '';
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IS NULL;
> -- Returns some number of rows. Call it N
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('');
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('', 'FOO');
> -- Returns N rows. Note that FOO does not exist, and is just a nonsense string
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '' OR TENANT_ID = 'FOO'
> --Returns 0 rows, but slowly
> {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

2020-03-02 Thread 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

2020-03-02 Thread 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-5718) GetTable builds a table excluding the given clientTimeStamp

2020-03-02 Thread Sandeep Guggilam (Jira)



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

Sandeep Guggilam updated PHOENIX-5718:
--
Attachment: PHOENIX-5718.master.v2.patch

> GetTable builds a table excluding the given clientTimeStamp
> ---
>
> Key: PHOENIX-5718
> URL: https://issues.apache.org/jira/browse/PHOENIX-5718
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.16.0
>Reporter: Sandeep Guggilam
>Assignee: Sandeep Guggilam
>Priority: Major
> Fix For: 4.16.0
>
> Attachments: PHOENIX-5718.4.x-HBase-1.3.v1.patch, 
> PHOENIX-5718.4.x-HBase-1.3.v2.patch, PHOENIX-5718.master.v1.patch, 
> PHOENIX-5718.master.v2.patch
>
>
> Here is the scenario tested:
>  # Brought up a server with 4.16 where new columns are added but not added as 
> part of upgrade path
>  # Connect  with 4.14 client
>  # Connect with a 4.16 client - this will throw an exception as the new 
> columns added as part of 4.16 were not added as part of upgrade path
>  # Now the code will force update the cache in 
> PhoenixStatement#executeQuery() method
>  # Now the buildTable is removing even the columns added as part of 4.15 , 
> the reason being we are passing the clientTimeStamp to build table ( say 29 
> is the timestamp for column added for 4.15) but the table is scanning rows 
> EXCLUDING the passed clientTimeSTamp as the Scan#setTimeRange method excludes 
> the end time stamp
> The passing of clientTimeStamp to build table is in 
> MetaDataEndPointImpl#doGetTable method



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Re: [DISCUSS] Unifying the 4.x branches

2020-03-02 Thread Josh Elser

Left a couple of minor requests to fix (no consequence changes -- 
comments, whitespace), which you can just fix on commit. Great work, Istvan.


Any of y'all want to look before Istvan merges it?

On 2/27/20 4:58 AM, Istvan Toth wrote:

Thanks Andrew, I like that solution.

I've added the "final" PRs and patches to
https://issues.apache.org/jira/browse/PHOENIX-5721 ,
they are ready for review.

On Wed, Feb 19, 2020 at 9:01 PM Andrew Purtell 
wrote:


Since you have already done this great work, and 1.3 isn’t dead yet, and
won’t be “this year”, and it serves as an example of how to bring in entire
new features on later code lines, perhaps it should go in. Just my 0.02.


On Feb 19, 2020, at 10:39 AM, Istvan Toth  wrote:

Geoffrey,

Absolutely.
80% of this patch is dealing with 1.3.
1.4 vs 1.5 affects two or three java files.

My game plan is to submit two different patches, a small one for 1.4 and
1.5 support, and a bigger one other that adds 1.3, so that it can be
reverted easily after 1.3 is dropped.

I think that maintaining a separate 1.3 branch is the least desirable
outcome, as we'd still have two 4.x branches to maintain, with different
jenkins jobs, and slightly different jar names and maven artifact

structure.


Of course the easiest route is to just drop 1.3 support ASAP, and then
unify 1.4 and 1.5 .

If we are not ready to drop 1.3 very soon, I'd vote for unifying with
1.3 included (I've finished the hard part), and then reverting a lot of
the compatibility cruft when we drop 1.3 support.

As for the 4.15 maintenance releases, those could stay on the current
branches, as we probably want point releases to be drop-in compatible,
and the unified version changes the maven artifact versioning, and some
jar filenames. This would be one way to extend some support for 1.3

regards
István




On 2020. 02. 19. 19:11, Geoffrey Jacoby wrote:
Istvan,

The HBase community has been on the verge of EOLing 1.3 for some time
now -- are there significant gains or simplification if we either end
1.3 support in Phoenix before PHOENIX-5721 goes in, or alternately,
don't include it in the unified profile since it will be EOLed in the
not-too-distant-future even if we don't do it now?

Geoffrey

On Wed, Feb 19, 2020 at 5:36 AM Istvan Toth mailto:st...@apache.org>> wrote:

Now I have an unpolished version of the unified 4.x branch at
https://github.com/stoty/phoenix/tree/PHOENIX-5721

It takes the same approach as the  master branch, though there are a

bit

more differences to hide in the versions.

I need to finish the assembly stuff, go over once more the changes,
and run
full ITs on each profile, but I expect that
the Java code will mostly see whitespace changes, so you can check

the

logic behind the changes.

The HBase metrics stuff in particular is interesting, because the

whole

feature is missing from 1.3, so it can be used as an example on how
we can
adopt new HBase features later.

On Mon, Feb 10, 2020 at 9:01 AM Istvan Toth mailto:st...@apache.org>> wrote:


Created PHOENIX-5721

 to

track this.

On Mon, Feb 10, 2020 at 8:21 AM István Tóth 
> wrote:



Thanks for the feedback, Geoffrey.

I took the lazy option of just creating compatibility methods to

paper

over the HBase API changes (emulate the latest version) when we

are calling

into HBase.

For the APIs implemented by Phoenix, I added compatibility

superclasses.

So I expect that we will be able to add a dummy

RegionObserver.preWALAppend

to the compatibility coprocessor superclass(es), so that the same

code

compiles for older versions as well.

Of course if other code paths depend on having that

functionality, those

should also be gated by similar compatibility shims/version checks.

My current approach is a quick fix, and does not preclude (in fact it
enables) later efforts at refactoring/cleanup.

On Fri, Feb 7, 2020 at 7:31 PM Geoffrey Jacoby

mailto:gjac...@apache.org>>

wrote:


If unification could be done, that would be great. (I apologize

that I

haven't had the bandwidth over the past week or two to take a

close look

at
the work Istvan has been doing to unify the 5.x branches -- as

one who

spends too much time cherry-picking I very much appreciate this

effort!

:-)
)

I still think the hardest part, as I think I mentioned in some

previous

thread, is what to do when the HBase coprocs themselves diverge

between

minor versions, as they can do. How do you handle the fact that
RegionObserver.preWALAppend exists in 1.5 but not 1.3 and 1.4,

and will

exist in 2.3 but not 2.2 and 2.1? And that therefore any

features that

depend on preWALAppend existing (none yet, but they're coming

later this

year) will work in a 1.5 or 2.3 environment, but not a 1.4 or

2.2 one?


Since our coprocessors tend to be giant monoliths,

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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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-5636) Improve the error message when client connects to server with higher major version

2020-03-02 Thread Chinmay Kulkarni (Jira)



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

Chinmay Kulkarni updated PHOENIX-5636:
--
Fix Version/s: (was: 4.15.1)
   4.16.0
   5.1.0

> Improve the error message when client connects to server with higher major 
> version
> --
>
> Key: PHOENIX-5636
> URL: https://issues.apache.org/jira/browse/PHOENIX-5636
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.15.0
>Reporter: Sandeep Guggilam
>Assignee: Christine Feng
>Priority: Minor
>  Labels: beginner, newbie
> Fix For: 5.1.0, 4.16.0
>
> Attachments: PHOENIX-5636.4.x-HBase-1.3.v2.patch, 
> PHOENIX-5636.4.x-HBase-1.3.v3.patch, PHOENIX-5636.4.x-Hbase-1.3.v1.patch, 
> PHOENIX-5636.master.v1.patch, PHOENIX-5636.master.v10.patch, 
> PHOENIX-5636.master.v2.patch, PHOENIX-5636.master.v3.patch, 
> PHOENIX-5636.master.v4.patch, PHOENIX-5636.master.v5.patch, 
> PHOENIX-5636.master.v6.patch, PHOENIX-5636.master.v7.patch, 
> PHOENIX-5636.master.v8.patch, PHOENIX-5636.master.v9.patch
>
>  Time Spent: 2h 40m
>  Remaining Estimate: 0h
>
> When a 4.14 client connects to a 5.0 server, it errors out saying " Outdated 
> jars. Newer Phoenix clients can't communicate with older Phoenix servers"
> It should probably error out with "Major version of client is less than that 
> of the server"



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (PHOENIX-5751) Optimize localCache utilization in IndexUtil#isGlobalIndexCheckEnabled()

2020-03-02 Thread Swaroopa Kadam (Jira)



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

Swaroopa Kadam updated PHOENIX-5751:

Summary: Optimize localCache utilization in 
IndexUtil#isGlobalIndexCheckEnabled()  (was: Optimize localCache utilitzation 
in IndexUtil#isGlobalIndexCheckEnabled())

> Optimize localCache utilization in IndexUtil#isGlobalIndexCheckEnabled()
> 
>
> Key: PHOENIX-5751
> URL: https://issues.apache.org/jira/browse/PHOENIX-5751
> Project: Phoenix
>  Issue Type: Improvement
>Affects Versions: 4.15.0, 4.14.3
>Reporter: Swaroopa Kadam
>Assignee: Swaroopa Kadam
>Priority: Major
> Fix For: 5.1.0, 4.15.1
>
>
> We don't need to add and check if globalIndexChecker is enabled on data table.



--
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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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

2020-03-02 Thread 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-5065) Inconsistent treatment of NULL and empty string

2020-03-02 Thread Richard Antal (Jira)



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

Richard Antal updated PHOENIX-5065:
---
Attachment: PHOENIX-5065.master.v2.patch

> Inconsistent treatment of NULL and empty string
> ---
>
> Key: PHOENIX-5065
> URL: https://issues.apache.org/jira/browse/PHOENIX-5065
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.14.1
>Reporter: Geoffrey Jacoby
>Priority: Major
> Attachments: PHOENIX-5065.master.v1.patch, 
> PHOENIX-5065.master.v2.patch
>
>
> Phoenix doesn't handle NULLs consistently with other SQL dialects, and it 
> doesn't handle them consistently internally either. 
> In PHOENIX-2422, [~jamestaylor] mentioned that Phoenix's intended behavior is 
> for empty string and NULL to be equivalent. That's inconsistent with other 
> SQL dialects (in which NULL is never equal to anything, including itself), 
> but if that's our documented behavior, then that's fine unless PHOENIX-2422 
> to change it is ever worked. 
> But consider the following queries:
> {code:java}
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '';
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IS NULL;
> -- Returns some number of rows. Call it N
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('');
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('', 'FOO');
> -- Returns N rows. Note that FOO does not exist, and is just a nonsense string
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '' OR TENANT_ID = 'FOO'
> --Returns 0 rows, but slowly
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (PHOENIX-5065) Inconsistent treatment of NULL and empty string

2020-03-02 Thread Richard Antal (Jira)



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

Richard Antal updated PHOENIX-5065:
---
Attachment: PHOENIX-5065.master.v1.patch

> Inconsistent treatment of NULL and empty string
> ---
>
> Key: PHOENIX-5065
> URL: https://issues.apache.org/jira/browse/PHOENIX-5065
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.14.1
>Reporter: Geoffrey Jacoby
>Priority: Major
> Attachments: PHOENIX-5065.master.v1.patch
>
>
> Phoenix doesn't handle NULLs consistently with other SQL dialects, and it 
> doesn't handle them consistently internally either. 
> In PHOENIX-2422, [~jamestaylor] mentioned that Phoenix's intended behavior is 
> for empty string and NULL to be equivalent. That's inconsistent with other 
> SQL dialects (in which NULL is never equal to anything, including itself), 
> but if that's our documented behavior, then that's fine unless PHOENIX-2422 
> to change it is ever worked. 
> But consider the following queries:
> {code:java}
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '';
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IS NULL;
> -- Returns some number of rows. Call it N
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('');
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('', 'FOO');
> -- Returns N rows. Note that FOO does not exist, and is just a nonsense string
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '' OR TENANT_ID = 'FOO'
> --Returns 0 rows, but slowly
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (PHOENIX-5065) Inconsistent treatment of NULL and empty string

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

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

[jira] [Updated] (PHOENIX-5718) GetTable builds a table excluding the given clientTimeStamp

Re: [DISCUSS] Unifying the 4.x branches

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

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

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

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

[jira] [Updated] (PHOENIX-5636) Improve the error message when client connects to server with higher major version

[jira] [Updated] (PHOENIX-5751) Optimize localCache utilization in IndexUtil#isGlobalIndexCheckEnabled()

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

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

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

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

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

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

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

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

[jira] [Updated] (PHOENIX-5065) Inconsistent treatment of NULL and empty string

[jira] [Updated] (PHOENIX-5065) Inconsistent treatment of NULL and empty string

21 matches

Site Navigation

Mail list logo

Footer information