[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383 and PHOENIX-4841, when we
clip the {{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line
260 in {{WhereOptimizer.pushKeyExpressionsToScan}} , {{(pk3,pk4) < (5,7)}} is
clipped to {{pk3 <= 5}} and {{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we could simply skip this RVC.
was:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383 and PHOENIX-4841, when we
clip the {{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line
260 in {{WhereOptimizer.pushKeyExpressionsToScan}} , {{(pk3,pk4) < (5,7)}} is
clipped to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383 and PHOENIX-4841, when we
clip the {{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line
260 in {{WhereOptimizer.pushKeyExpressionsToScan}} , {{(pk3,pk4) < (5,7)}} is
clipped to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we could simply skip this RVC.
was:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383 and PHOENIX-4841, when we
clip the {{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line
260 in {{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is
clipped to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we could simply skip this RVC.
was:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383 and PHOENIX-4841, when we
clip the {{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line
260 in {{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is
clipped to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we could simply skip this RVC.
was:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and
[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks
[
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
[
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-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Fix Version/s: 4.16.0
> Fix erroneous query result when RVC is clipped with desc column
> ---
>
> Key: PHOENIX-5753
> URL: https://issues.apache.org/jira/browse/PHOENIX-5753
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 5.0.0, 4.15.0
>Reporter: chenglei
>Priority: Major
> Labels: DESC
> Fix For: 4.16.0
>
>
> Given following table and data:
> {code:java}
>CREATE TABLE test
>(
> pk1 INTEGER NOT NULL ,
> pk2 INTEGER NOT NULL,
> pk3 INTEGER NOT NULL,
> pk4 INTEGER NOT NULL,
> v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
>)
> {code}
> Noticed pk3 is DESC.
> {code:java}
>UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
> {code}
> If we execute the following sql:
> {code:java}
> select * from test
> where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
> {code}
> the returned result is empty, but obviously, the above inserted row
> (1,3,4,10,1) should be returned.
> I think this problem is introduced by PHOENIX-3383, when we clip the
> {{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
> {{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is
> clipped to {{pk3 <= 5}} and
> {{pk4 < 7}} .
> {code:java}
> 257List leftRanges = clipLeft(schema,
> slot.getPKPosition()
> 258+ slotOffset - clipLeftSpan, clipLeftSpan,
> keyRanges, ptr);
> 259keyRanges =
> 260clipRight(schema, slot.getPKPosition() +
> slotOffset - 1, keyRanges,
> 261leftRanges, ptr);
> 262if (prevSortOrder == SortOrder.DESC) {
> 263leftRanges = invertKeyRanges(leftRanges);
> 264}
> 265slotSpanArray[cnf.size()] = clipLeftSpan-1;
> 266cnf.add(leftRanges);
> 267clipLeftSpan = 0;
> 268prevSortOrder = sortOrder;
> 269// since we have to clip the portion with the same
> sort order, we can no longer
> 270// extract the nodes from the where clause
> 271// for eg. for the schema A VARCHAR DESC, B VARCHAR
> ASC and query
> 272// WHERE (A,B) < ('a','b')
> 273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
> 274// so we still need to filter on A,B
> 275stopExtracting = true;
> 276}
> {code}
> Eventually after we completed the
> {{WhereOptimizer.pushKeyExpressionsToScan}}, the result
> {{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
> {{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
> [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
> (1,3,4,10,1) could not be retrieved.
> But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
> 5}} and {{pk4 < 7}} , we could only have
> {{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) <
> (5,7)}} to {{pk3 <= 5}} , we should stop traverse the rowKey column further.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty, but obviously, the above inserted row
(1,3,4,10,1) should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we should stop traverse the rowKey column further.
was:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is DESC.
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we should stop traverse the rowKey column further.
was:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is desc,
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test (
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
}
{code}
Noticed pk3 is desc,
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we should stop traverse the rowKey column further.
was:
Given following table and data:
{code:java}
CREATE TABLE test (
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
}
{code:java}
Noticed pk3 is desc,
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code:java}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code:java}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test
(
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
)
{code}
Noticed pk3 is desc,
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we should stop traverse the rowKey column further.
was:
Given following table and data:
{code:java}
CREATE TABLE test (
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
}
{code}
Noticed pk3 is desc,
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[
https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5753:
--
Description:
Given following table and data:
{code:java}
CREATE TABLE test (
pk1 INTEGER NOT NULL ,
pk2 INTEGER NOT NULL,
pk3 INTEGER NOT NULL,
pk4 INTEGER NOT NULL,
v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
}
{code:java}
Noticed pk3 is desc,
{code:java}
UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
{code:java}
If we execute the following sql:
{code:java}
select * from test
where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
{code:java}
the returned result is empty,but obviously, the above inserted row (1,3,4,10,1)
should be returned.
I think this problem is introduced by PHOENIX-3383, when we clip the
{{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
{{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is clipped
to {{pk3 <= 5}} and
{{pk4 < 7}} .
{code:java}
257List leftRanges = clipLeft(schema,
slot.getPKPosition()
258+ slotOffset - clipLeftSpan, clipLeftSpan,
keyRanges, ptr);
259keyRanges =
260clipRight(schema, slot.getPKPosition() +
slotOffset - 1, keyRanges,
261leftRanges, ptr);
262if (prevSortOrder == SortOrder.DESC) {
263leftRanges = invertKeyRanges(leftRanges);
264}
265slotSpanArray[cnf.size()] = clipLeftSpan-1;
266cnf.add(leftRanges);
267clipLeftSpan = 0;
268prevSortOrder = sortOrder;
269// since we have to clip the portion with the same sort
order, we can no longer
270// extract the nodes from the where clause
271// for eg. for the schema A VARCHAR DESC, B VARCHAR ASC
and query
272// WHERE (A,B) < ('a','b')
273// the range (* - a\xFFb) is converted to (~a-*)(*-b)
274// so we still need to filter on A,B
275stopExtracting = true;
276}
{code:java}
Eventually after we completed the {{WhereOptimizer.pushKeyExpressionsToScan}},
the result
{{ScanRanges.ranges}} is [[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]],
{{ScanRanges.useSkipScanFilter}} is {{true}} and {{SkipScanFilter}} is also
[[[1 - 2]], [[3 - 4]], [[~5 - *)], [(* - 7)]], so the the above inserted row
(1,3,4,10,1) could not be retrieved.
But as we know, {{(pk3,pk4) < (5,7)}} is not semantically equals to {{pk3 <=
5}} and {{pk4 < 7}} , we could only have
{{pk3 <= 5}} but not {{pk4 < 7}}, so when we clipped {{(pk3,pk4) < (5,7)}}
to {{pk3 <= 5}} , we should stop traverse the rowKey column further.
was:Consider
> Fix erroneous query result when RVC is clipped with desc column
> ---
>
> Key: PHOENIX-5753
> URL: https://issues.apache.org/jira/browse/PHOENIX-5753
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 5.0.0, 4.15.0
>Reporter: chenglei
>Priority: Major
> Labels: DESC
>
> Given following table and data:
> {code:java}
> CREATE TABLE test (
> pk1 INTEGER NOT NULL ,
> pk2 INTEGER NOT NULL,
> pk3 INTEGER NOT NULL,
> pk4 INTEGER NOT NULL,
> v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
> }
> {code:java}
> Noticed pk3 is desc,
> {code:java}
>UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
> {code:java}
> If we execute the following sql:
> {code:java}
> select * from test
> where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
> {code:java}
> the returned result is empty,but obviously, the above inserted row
> (1,3,4,10,1) should be returned.
> I think this problem is introduced by PHOENIX-3383, when we clip the
> {{(pk3,pk4) < (5,7)}} because {{pk3}} is {{DESC}} by following line 260 in
> {{WhereOptimizer.pushKeyExpressionsToScan}}, {{(pk3,pk4) < (5,7)}} is
> clipped to {{pk3 <= 5}} and
> {{pk4 < 7}} .
> {code:java}
> 257List leftRanges = clipLeft(schema,
> slot.getPKPosition()
> 258+ slotOffset - clipLeftSpan, clipLeftSpan,
> keyRanges, ptr);
> 259keyRanges =
> 260clipRight(schema, slot.getPKPosition() +
> slotOffset - 1, keyRanges,
> 261leftRanges, ptr);
> 262if (prevSortOrder == SortOrder.DESC) {
> 263
[jira] [Updated] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
[ https://issues.apache.org/jira/browse/PHOENIX-5753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] chenglei updated PHOENIX-5753: -- Description: Consider > Fix erroneous query result when RVC is clipped with desc column > --- > > Key: PHOENIX-5753 > URL: https://issues.apache.org/jira/browse/PHOENIX-5753 > Project: Phoenix > Issue Type: Bug >Affects Versions: 5.0.0, 4.15.0 >Reporter: chenglei >Priority: Major > Labels: DESC > > Consider -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (PHOENIX-5753) Fix erroneous query result when RVC is clipped with desc column
chenglei created PHOENIX-5753: - Summary: Fix erroneous query result when RVC is clipped with desc column Key: PHOENIX-5753 URL: https://issues.apache.org/jira/browse/PHOENIX-5753 Project: Phoenix Issue Type: Bug Affects Versions: 4.15.0, 5.0.0 Reporter: chenglei -- 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
[
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
[
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-5317) Upserting rows into child views with pk fails when the base view has an index on it.
[
https://issues.apache.org/jira/browse/PHOENIX-5317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sandeep Guggilam updated PHOENIX-5317:
--
Attachment: PHOENIX-5317.4.x-HBase-1.3.v1.patch
> Upserting rows into child views with pk fails when the base view has an index
> on it.
>
>
> Key: PHOENIX-5317
> URL: https://issues.apache.org/jira/browse/PHOENIX-5317
> Project: Phoenix
> Issue Type: Sub-task
>Affects Versions: 4.13.0, 4.14.1
>Reporter: Jacob Isaac
>Assignee: Sandeep Guggilam
>Priority: Major
> Attachments: PHOENIX-5137-TestFailure.txt,
> PHOENIX-5317.4.x-HBase-1.3.v1.patch, PHOENIX-5317.4.x-HBase-1.3.v1.patch
>
>
> Steps to reproduce -
> 1 Create Base Table, Base/Global View and Index using non tenanted connection.
> CREATE TABLE IF NOT EXISTS TEST.BASETABLE (
> TENANT_ID CHAR(15) NOT NULL,
> KEY_PREFIX CHAR(3) NOT NULL,
> CREATED_DATE DATE,
> CREATED_BY CHAR(15),
> SYSTEM_MODSTAMP DATE
> CONSTRAINT PK PRIMARY KEY (
> TENANT_ID,
> KEY_PREFIX
> )
> ) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1;
> CREATE VIEW IF NOT EXISTS TEST.MY_GLOBAL_VIEW (
> TEXT1 VARCHAR NOT NULL,
> INT1 BIGINT NOT NULL,
> DOUBLE1 DECIMAL(12, 3),
> IS_BOOLEAN BOOLEAN,
> RELATIONSHIP_ID CHAR(15),
> TEXT_READ_ONLY VARCHAR,
> DATE_TIME1 DATE,
> JSON1 VARCHAR,
> IP_START_ADDRESS VARCHAR
> CONSTRAINT PKVIEW PRIMARY KEY
> (
> TEXT1, INT1
> )
> )
> AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = '0CY';
> CREATE INDEX IF NOT EXISTS TEST_MY_GLOBAL_VIEW_SEC_INDEX
> ON TEST.MY_GLOBAL_VIEW (TEXT1, INT1)
> INCLUDE (CREATED_BY, RELATIONSHIP_ID, JSON1, DOUBLE1, IS_BOOLEAN,
> IP_START_ADDRESS, CREATED_DATE, SYSTEM_MODSTAMP, TEXT_READ_ONLY);
> 2. Create child view using an tenant-owned connection
> CREATE VIEW IF NOT EXISTS TEST."z01" (COL1 VARCHAR, COL2 VARCHAR, COL3
> VARCHAR, COL4 VARCHAR CONSTRAINT PK PRIMARY KEY (COL1, COL2, COL3, COL4)) AS
> SELECT * FROM TEST.MY_GLOBAL_VIEW;
> 3. Upsert into child view
> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1, COL1, COL2, COL3, COL4)
> VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10, 'z',
> '8', 'z', 'z', 'z');
> Following exception is thrown -
> 0: jdbc:phoenix:localhost> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1,
> COL1, COL2, COL3, COL4) VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd
> HH:mm:ss'), 10, 'z', '8', 'z', 'z', 'z');
> java.lang.IllegalArgumentException
> at
> com.google.common.base.Preconditions.checkArgument(Preconditions.java:76)
> at
> com.google.common.collect.Lists.computeArrayListCapacity(Lists.java:105)
> at
> com.google.common.collect.Lists.newArrayListWithExpectedSize(Lists.java:195)
> at
> org.apache.phoenix.index.IndexMaintainer.(IndexMaintainer.java:424)
> at
> org.apache.phoenix.index.IndexMaintainer.create(IndexMaintainer.java:143)
> at
> org.apache.phoenix.schema.PTableImpl.getIndexMaintainer(PTableImpl.java:1176)
> at
> org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:303)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:519)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:501)
> at org.apache.phoenix.execute.MutationState.send(MutationState.java:941)
> at
> org.apache.phoenix.execute.MutationState.send(MutationState.java:1387)
> at
> org.apache.phoenix.execute.MutationState.commit(MutationState.java:1228)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:666)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:662)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:662)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:399)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:379)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:366)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1775)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:807)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
--
This message was sent by
[jira] [Updated] (PHOENIX-5317) Upserting rows into child views with pk fails when the base view has an index on it.
[
https://issues.apache.org/jira/browse/PHOENIX-5317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sandeep Guggilam updated PHOENIX-5317:
--
Attachment: (was: PHOENIX-5317.4.x-HBase-1.3.v1.patch)
> Upserting rows into child views with pk fails when the base view has an index
> on it.
>
>
> Key: PHOENIX-5317
> URL: https://issues.apache.org/jira/browse/PHOENIX-5317
> Project: Phoenix
> Issue Type: Sub-task
>Affects Versions: 4.13.0, 4.14.1
>Reporter: Jacob Isaac
>Assignee: Sandeep Guggilam
>Priority: Major
> Attachments: PHOENIX-5137-TestFailure.txt,
> PHOENIX-5317.4.x-HBase-1.3.v1.patch
>
>
> Steps to reproduce -
> 1 Create Base Table, Base/Global View and Index using non tenanted connection.
> CREATE TABLE IF NOT EXISTS TEST.BASETABLE (
> TENANT_ID CHAR(15) NOT NULL,
> KEY_PREFIX CHAR(3) NOT NULL,
> CREATED_DATE DATE,
> CREATED_BY CHAR(15),
> SYSTEM_MODSTAMP DATE
> CONSTRAINT PK PRIMARY KEY (
> TENANT_ID,
> KEY_PREFIX
> )
> ) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1;
> CREATE VIEW IF NOT EXISTS TEST.MY_GLOBAL_VIEW (
> TEXT1 VARCHAR NOT NULL,
> INT1 BIGINT NOT NULL,
> DOUBLE1 DECIMAL(12, 3),
> IS_BOOLEAN BOOLEAN,
> RELATIONSHIP_ID CHAR(15),
> TEXT_READ_ONLY VARCHAR,
> DATE_TIME1 DATE,
> JSON1 VARCHAR,
> IP_START_ADDRESS VARCHAR
> CONSTRAINT PKVIEW PRIMARY KEY
> (
> TEXT1, INT1
> )
> )
> AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = '0CY';
> CREATE INDEX IF NOT EXISTS TEST_MY_GLOBAL_VIEW_SEC_INDEX
> ON TEST.MY_GLOBAL_VIEW (TEXT1, INT1)
> INCLUDE (CREATED_BY, RELATIONSHIP_ID, JSON1, DOUBLE1, IS_BOOLEAN,
> IP_START_ADDRESS, CREATED_DATE, SYSTEM_MODSTAMP, TEXT_READ_ONLY);
> 2. Create child view using an tenant-owned connection
> CREATE VIEW IF NOT EXISTS TEST."z01" (COL1 VARCHAR, COL2 VARCHAR, COL3
> VARCHAR, COL4 VARCHAR CONSTRAINT PK PRIMARY KEY (COL1, COL2, COL3, COL4)) AS
> SELECT * FROM TEST.MY_GLOBAL_VIEW;
> 3. Upsert into child view
> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1, COL1, COL2, COL3, COL4)
> VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10, 'z',
> '8', 'z', 'z', 'z');
> Following exception is thrown -
> 0: jdbc:phoenix:localhost> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1,
> COL1, COL2, COL3, COL4) VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd
> HH:mm:ss'), 10, 'z', '8', 'z', 'z', 'z');
> java.lang.IllegalArgumentException
> at
> com.google.common.base.Preconditions.checkArgument(Preconditions.java:76)
> at
> com.google.common.collect.Lists.computeArrayListCapacity(Lists.java:105)
> at
> com.google.common.collect.Lists.newArrayListWithExpectedSize(Lists.java:195)
> at
> org.apache.phoenix.index.IndexMaintainer.(IndexMaintainer.java:424)
> at
> org.apache.phoenix.index.IndexMaintainer.create(IndexMaintainer.java:143)
> at
> org.apache.phoenix.schema.PTableImpl.getIndexMaintainer(PTableImpl.java:1176)
> at
> org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:303)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:519)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:501)
> at org.apache.phoenix.execute.MutationState.send(MutationState.java:941)
> at
> org.apache.phoenix.execute.MutationState.send(MutationState.java:1387)
> at
> org.apache.phoenix.execute.MutationState.commit(MutationState.java:1228)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:666)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:662)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:662)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:399)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:379)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:366)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1775)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:807)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
--
This message was sent by Atlassian Jira
[jira] [Updated] (PHOENIX-5698) Phoenix Query with RVC IN list expression generates wrong scan with non-pk ordered pks
[
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
[
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
[
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
[
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
[
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
[
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
[
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
[
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
[
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-5317) Upserting rows into child views with pk fails when the base view has an index on it.
[
https://issues.apache.org/jira/browse/PHOENIX-5317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sandeep Guggilam updated PHOENIX-5317:
--
Attachment: (was: PHOENIX-5317.4.x-HBase-1.3.v1.patch)
> Upserting rows into child views with pk fails when the base view has an index
> on it.
>
>
> Key: PHOENIX-5317
> URL: https://issues.apache.org/jira/browse/PHOENIX-5317
> Project: Phoenix
> Issue Type: Sub-task
>Affects Versions: 4.13.0, 4.14.1
>Reporter: Jacob Isaac
>Assignee: Sandeep Guggilam
>Priority: Major
> Attachments: PHOENIX-5137-TestFailure.txt,
> PHOENIX-5317.4.x-HBase-1.3.v1.patch, PHOENIX-5317.4.x-HBase-1.3.v1.patch
>
>
> Steps to reproduce -
> 1 Create Base Table, Base/Global View and Index using non tenanted connection.
> CREATE TABLE IF NOT EXISTS TEST.BASETABLE (
> TENANT_ID CHAR(15) NOT NULL,
> KEY_PREFIX CHAR(3) NOT NULL,
> CREATED_DATE DATE,
> CREATED_BY CHAR(15),
> SYSTEM_MODSTAMP DATE
> CONSTRAINT PK PRIMARY KEY (
> TENANT_ID,
> KEY_PREFIX
> )
> ) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1;
> CREATE VIEW IF NOT EXISTS TEST.MY_GLOBAL_VIEW (
> TEXT1 VARCHAR NOT NULL,
> INT1 BIGINT NOT NULL,
> DOUBLE1 DECIMAL(12, 3),
> IS_BOOLEAN BOOLEAN,
> RELATIONSHIP_ID CHAR(15),
> TEXT_READ_ONLY VARCHAR,
> DATE_TIME1 DATE,
> JSON1 VARCHAR,
> IP_START_ADDRESS VARCHAR
> CONSTRAINT PKVIEW PRIMARY KEY
> (
> TEXT1, INT1
> )
> )
> AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = '0CY';
> CREATE INDEX IF NOT EXISTS TEST_MY_GLOBAL_VIEW_SEC_INDEX
> ON TEST.MY_GLOBAL_VIEW (TEXT1, INT1)
> INCLUDE (CREATED_BY, RELATIONSHIP_ID, JSON1, DOUBLE1, IS_BOOLEAN,
> IP_START_ADDRESS, CREATED_DATE, SYSTEM_MODSTAMP, TEXT_READ_ONLY);
> 2. Create child view using an tenant-owned connection
> CREATE VIEW IF NOT EXISTS TEST."z01" (COL1 VARCHAR, COL2 VARCHAR, COL3
> VARCHAR, COL4 VARCHAR CONSTRAINT PK PRIMARY KEY (COL1, COL2, COL3, COL4)) AS
> SELECT * FROM TEST.MY_GLOBAL_VIEW;
> 3. Upsert into child view
> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1, COL1, COL2, COL3, COL4)
> VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10, 'z',
> '8', 'z', 'z', 'z');
> Following exception is thrown -
> 0: jdbc:phoenix:localhost> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1,
> COL1, COL2, COL3, COL4) VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd
> HH:mm:ss'), 10, 'z', '8', 'z', 'z', 'z');
> java.lang.IllegalArgumentException
> at
> com.google.common.base.Preconditions.checkArgument(Preconditions.java:76)
> at
> com.google.common.collect.Lists.computeArrayListCapacity(Lists.java:105)
> at
> com.google.common.collect.Lists.newArrayListWithExpectedSize(Lists.java:195)
> at
> org.apache.phoenix.index.IndexMaintainer.(IndexMaintainer.java:424)
> at
> org.apache.phoenix.index.IndexMaintainer.create(IndexMaintainer.java:143)
> at
> org.apache.phoenix.schema.PTableImpl.getIndexMaintainer(PTableImpl.java:1176)
> at
> org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:303)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:519)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:501)
> at org.apache.phoenix.execute.MutationState.send(MutationState.java:941)
> at
> org.apache.phoenix.execute.MutationState.send(MutationState.java:1387)
> at
> org.apache.phoenix.execute.MutationState.commit(MutationState.java:1228)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:666)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:662)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:662)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:399)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:379)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:366)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1775)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:807)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
--
This message
[jira] [Updated] (PHOENIX-5317) Upserting rows into child views with pk fails when the base view has an index on it.
[
https://issues.apache.org/jira/browse/PHOENIX-5317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sandeep Guggilam updated PHOENIX-5317:
--
Attachment: PHOENIX-5317.4.x-HBase-1.3.v1.patch
> Upserting rows into child views with pk fails when the base view has an index
> on it.
>
>
> Key: PHOENIX-5317
> URL: https://issues.apache.org/jira/browse/PHOENIX-5317
> Project: Phoenix
> Issue Type: Sub-task
>Affects Versions: 4.13.0, 4.14.1
>Reporter: Jacob Isaac
>Assignee: Sandeep Guggilam
>Priority: Major
> Attachments: PHOENIX-5137-TestFailure.txt,
> PHOENIX-5317.4.x-HBase-1.3.v1.patch, PHOENIX-5317.4.x-HBase-1.3.v1.patch
>
>
> Steps to reproduce -
> 1 Create Base Table, Base/Global View and Index using non tenanted connection.
> CREATE TABLE IF NOT EXISTS TEST.BASETABLE (
> TENANT_ID CHAR(15) NOT NULL,
> KEY_PREFIX CHAR(3) NOT NULL,
> CREATED_DATE DATE,
> CREATED_BY CHAR(15),
> SYSTEM_MODSTAMP DATE
> CONSTRAINT PK PRIMARY KEY (
> TENANT_ID,
> KEY_PREFIX
> )
> ) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1;
> CREATE VIEW IF NOT EXISTS TEST.MY_GLOBAL_VIEW (
> TEXT1 VARCHAR NOT NULL,
> INT1 BIGINT NOT NULL,
> DOUBLE1 DECIMAL(12, 3),
> IS_BOOLEAN BOOLEAN,
> RELATIONSHIP_ID CHAR(15),
> TEXT_READ_ONLY VARCHAR,
> DATE_TIME1 DATE,
> JSON1 VARCHAR,
> IP_START_ADDRESS VARCHAR
> CONSTRAINT PKVIEW PRIMARY KEY
> (
> TEXT1, INT1
> )
> )
> AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = '0CY';
> CREATE INDEX IF NOT EXISTS TEST_MY_GLOBAL_VIEW_SEC_INDEX
> ON TEST.MY_GLOBAL_VIEW (TEXT1, INT1)
> INCLUDE (CREATED_BY, RELATIONSHIP_ID, JSON1, DOUBLE1, IS_BOOLEAN,
> IP_START_ADDRESS, CREATED_DATE, SYSTEM_MODSTAMP, TEXT_READ_ONLY);
> 2. Create child view using an tenant-owned connection
> CREATE VIEW IF NOT EXISTS TEST."z01" (COL1 VARCHAR, COL2 VARCHAR, COL3
> VARCHAR, COL4 VARCHAR CONSTRAINT PK PRIMARY KEY (COL1, COL2, COL3, COL4)) AS
> SELECT * FROM TEST.MY_GLOBAL_VIEW;
> 3. Upsert into child view
> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1, COL1, COL2, COL3, COL4)
> VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10, 'z',
> '8', 'z', 'z', 'z');
> Following exception is thrown -
> 0: jdbc:phoenix:localhost> UPSERT INTO TEST."z01" (DATE_TIME1, INT1, TEXT1,
> COL1, COL2, COL3, COL4) VALUES (TO_DATE('2017-10-16 22:00:00', '-MM-dd
> HH:mm:ss'), 10, 'z', '8', 'z', 'z', 'z');
> java.lang.IllegalArgumentException
> at
> com.google.common.base.Preconditions.checkArgument(Preconditions.java:76)
> at
> com.google.common.collect.Lists.computeArrayListCapacity(Lists.java:105)
> at
> com.google.common.collect.Lists.newArrayListWithExpectedSize(Lists.java:195)
> at
> org.apache.phoenix.index.IndexMaintainer.(IndexMaintainer.java:424)
> at
> org.apache.phoenix.index.IndexMaintainer.create(IndexMaintainer.java:143)
> at
> org.apache.phoenix.schema.PTableImpl.getIndexMaintainer(PTableImpl.java:1176)
> at
> org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:303)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:519)
> at
> org.apache.phoenix.execute.MutationState$1.next(MutationState.java:501)
> at org.apache.phoenix.execute.MutationState.send(MutationState.java:941)
> at
> org.apache.phoenix.execute.MutationState.send(MutationState.java:1387)
> at
> org.apache.phoenix.execute.MutationState.commit(MutationState.java:1228)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:666)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:662)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:662)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:399)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:379)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:366)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1775)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:807)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
--
This message was sent by
[jira] [Assigned] (PHOENIX-5629) Phoenix Function to Return HBase row timestamp
[ https://issues.apache.org/jira/browse/PHOENIX-5629?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jacob Isaac reassigned PHOENIX-5629: Assignee: Jacob Isaac (was: Tanuj Khurana) > Phoenix Function to Return HBase row timestamp > -- > > Key: PHOENIX-5629 > URL: https://issues.apache.org/jira/browse/PHOENIX-5629 > Project: Phoenix > Issue Type: New Feature >Reporter: Geoffrey Jacoby >Assignee: Jacob Isaac >Priority: Major > Fix For: 5.1.0, 4.16.0 > > Attachments: PHOENIX-5629.4.x-HBase-1.3.v1.patch, > PHOENIX-5629.4.x-HBase-1.3.v2.patch, PHOENIX-5629.master.v1.patch, > PHOENIX-5629.master.v2.patch > > Time Spent: 1h 40m > Remaining Estimate: 0h > > t's occasionally useful when diagnosing an issue with Phoenix to be able to > easily look up the HBase timestamp of the HBase Cell returned by a Phoenix > query. > For example: > SELECT ROW_TIMESTAMP(Column1) FROM Table1 WHERE Column1 = 'SomeValue' -- 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
[ https://issues.apache.org/jira/browse/PHOENIX-5636?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Christine Feng updated PHOENIX-5636: Attachment: (was: PHOENIX-5636.4.x-HBase-1.3.v3.patch) > 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: 4.15.1 > > 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-5752) Phoenix RVC InListExpression generates wrong DESC order column scan
[
https://issues.apache.org/jira/browse/PHOENIX-5752?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-5752:
--
Labels: DESC (was: )
> Phoenix RVC InListExpression generates wrong DESC order column scan
> ---
>
> Key: PHOENIX-5752
> URL: https://issues.apache.org/jira/browse/PHOENIX-5752
> Project: Phoenix
> Issue Type: Improvement
>Reporter: Xinyi Yan
>Assignee: Xinyi Yan
>Priority: Major
> Labels: DESC
>
> {code:java}
> @Test
> public void testInListExpressionWithDescOrderWithRightQueryPlan() throws
> Exception {
> String fullTableName = generateUniqueName();
> String fullViewName = generateUniqueName();
> String tenantView = generateUniqueName();
> String TENANT_SPECIFIC_URL1 = getUrl() + ';' + TENANT_ID_ATTRIB +
> "=tenant1";
> try (Connection conn = DriverManager.getConnection(getUrl())) {
> conn.setAutoCommit(true);
> try (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" + " ID3 BIGINT, ID4 BIGINT
> \n" +
> " CONSTRAINT PKVIEW PRIMARY KEY\n" + " (\n" + " ID1, ID2
> DESC\n" + ")) " +
> "AS SELECT * FROM " + fullTableName + " WHERE KEY_PREFIX
> = '0CY'");
> }
> }
> try (Connection viewConn =
> DriverManager.getConnection(TENANT_SPECIFIC_URL1)) {
> viewConn.setAutoCommit(true);
> try (Statement stmt = viewConn.createStatement()) {
> stmt.execute("CREATE VIEW IF NOT EXISTS " + tenantView + " AS
> SELECT * FROM " + fullViewName);
> try (PreparedStatement preparedStmt = viewConn.prepareStatement(
> "SELECT * FROM " + tenantView + " WHERE (ID1, ID2) " +
> "IN (('me', '500')," +
> "('bar', '400')," +
> "('foo', '300'))")) {
> QueryPlan queryPlan =
> PhoenixRuntime.getOptimizedQueryPlan(preparedStmt);
>
> assertTrue(queryPlan.getExplainPlan().toString().contains("POINT LOOKUP ON
> "));
> }
> }
> }
> }
> {code}
> InListExpression generates a range scan instead of a point lookup. This might
> result in an extremely bad performance for the DELETE and SELECT query. From
> my perspective, it might be the point that we need to refactor and/or rewrite
> query optimization code so that we don't need to have two code paths for
> InListExpresson. We can simply do the query rewrite to ((ID1=? AND ID2=?) or
> (ID1=? AND ID2=?)) as the right approach.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
