[jira] [Commented] (PHOENIX-3371) Aggregate Function is broken if secondary index exists
[ https://issues.apache.org/jira/browse/PHOENIX-3371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15573816#comment-15573816 ] Hudson commented on PHOENIX-3371: - FAILURE: Integrated in Jenkins build Phoenix-master #1440 (See [https://builds.apache.org/job/Phoenix-master/1440/]) PHOENIX-3371 Aggregate Function is broken if secondary index exists (jamestaylor: rev 04f5370d7b5a6297f4577a4463dd101a52abc7a7) * (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ViewIndexIT.java * (edit) phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java > Aggregate Function is broken if secondary index exists > -- > > Key: PHOENIX-3371 > URL: https://issues.apache.org/jira/browse/PHOENIX-3371 > Project: Phoenix > Issue Type: Bug >Affects Versions: 4.8.1 >Reporter: Jiping Zhou > Attachments: PHOENIX-3371.patch, PHOENIX-3371_v2.patch > > > 1. Create a global table > CREATE TABLE IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_TABLE ( > ORGANIZATION_ID CHAR(15) NOT NULL, > KEY_PREFIX CHAR(3) NOT NULL, > CREATED_DATE DATE, > CREATED_BY CHAR(15), > CONSTRAINT PK PRIMARY KEY ( > ORGANIZATION_ID, > KEY_PREFIX > ) > ) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true; > 2. Create a global view on base global table with where clause > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW ( > INT1 BIGINT NOT NULL, > DOUBLE1 DECIMAL(12, 3), > IS_BOOLEAN BOOLEAN, > TEXT1 VARCHAR, > CONSTRAINT PKVIEW PRIMARY KEY > ( > INT1 > ) > ) > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE KEY_PREFIX = '123'; > 3. Create gloabl index on the global view > CREATE INDEX IF NOT EXISTS GLOBAL_INDEX > ON PLATFORM_ENTITY.GLOBAL_VIEW (TEXT1 DESC, INT1) > INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE); > 4. Create tenant specific view on top of global view > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.TENANT_VIEW > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_VIEW; > 5. Insert 10 record in tenant view > 6. Remove any record in tenant view > 7. Query in tenant view > SELECT * FROM PLATFORM_ENTITY.TENANT_VIEW; > 9 records are returned > SELECT COUNT(*) FROM PLATFORM_ENTITY.TENANT_VIEW; > 10 was returned. > I guess the issue was secondary index is not updated when delete or update > happens. And the aggregate function is using the secondary index. The problem > does not happen in 4.8.0-1.0.4 > It is a regression -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (PHOENIX-3371) Aggregate Function is broken if secondary index exists
[
https://issues.apache.org/jira/browse/PHOENIX-3371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15573033#comment-15573033
]
James Taylor commented on PHOENIX-3371:
---
How are you tearing-down the test after it runs the first time, [~JipingZhou]?
I suspect the way in which you're re-initializing or tearing-down the test
violates our contract. A DELETE statement will only maintain the index if it's
issued on the view that has the index. For example, if you're reseting your
state by issuing a command like this, that wouldn't be valid:
{code}
DELETE * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE ORGANIZATION_ID = ?
{code}
The index will get out of sync if you issue any DELETE command on the base
table.
Also, we don't support DROP VIEW CASCADE, so the following wouldn't drop the
tenant-specific view causing state to be left behind:
- DROP VIEW PLATFORM_ENTITY.TENANT_VIEW CASCADE
I've filed PHOENIX-3377 to support this in the future.
To cleanup the state you can do this:
- DROP TABLE PLATFORM_ENTITY.GLOBAL_TABLE CASCADE
Or another alternative would be to create new Phoenix tables with generated
tables on each table run.
> Aggregate Function is broken if secondary index exists
> --
>
> Key: PHOENIX-3371
> URL: https://issues.apache.org/jira/browse/PHOENIX-3371
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.8.1
>Reporter: Jiping Zhou
> Attachments: PHOENIX-3371.patch, PHOENIX-3371_v2.patch
>
>
> 1. Create a global table
> CREATE TABLE IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_TABLE (
> ORGANIZATION_ID CHAR(15) NOT NULL,
> KEY_PREFIX CHAR(3) NOT NULL,
> CREATED_DATE DATE,
> CREATED_BY CHAR(15),
> CONSTRAINT PK PRIMARY KEY (
> ORGANIZATION_ID,
> KEY_PREFIX
> )
> ) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true;
> 2. Create a global view on base global table with where clause
> CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW (
> INT1 BIGINT NOT NULL,
> DOUBLE1 DECIMAL(12, 3),
> IS_BOOLEAN BOOLEAN,
> TEXT1 VARCHAR,
> CONSTRAINT PKVIEW PRIMARY KEY
> (
> INT1
> )
> )
> AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE KEY_PREFIX = '123';
> 3. Create gloabl index on the global view
> CREATE INDEX IF NOT EXISTS GLOBAL_INDEX
> ON PLATFORM_ENTITY.GLOBAL_VIEW (TEXT1 DESC, INT1)
> INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE);
> 4. Create tenant specific view on top of global view
> CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.TENANT_VIEW
> AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_VIEW;
> 5. Insert 10 record in tenant view
> 6. Remove any record in tenant view
> 7. Query in tenant view
> SELECT * FROM PLATFORM_ENTITY.TENANT_VIEW;
> 9 records are returned
> SELECT COUNT(*) FROM PLATFORM_ENTITY.TENANT_VIEW;
> 10 was returned.
> I guess the issue was secondary index is not updated when delete or update
> happens. And the aggregate function is using the secondary index. The problem
> does not happen in 4.8.0-1.0.4
> It is a regression
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3371) Aggregate Function is broken if secondary index exists
[ https://issues.apache.org/jira/browse/PHOENIX-3371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15572850#comment-15572850 ] Jiping Zhou commented on PHOENIX-3371: -- Here is the tests which can reproduce the issue https://gus.my.salesforce.com/apex/adm_bugdetail?id=a07B002RkM6IAK&sfdc.override=1 The test will pass the first time but fail since second time. > Aggregate Function is broken if secondary index exists > -- > > Key: PHOENIX-3371 > URL: https://issues.apache.org/jira/browse/PHOENIX-3371 > Project: Phoenix > Issue Type: Bug >Affects Versions: 4.8.1 >Reporter: Jiping Zhou > Attachments: PHOENIX-3371.patch, PHOENIX-3371_v2.patch > > > 1. Create a global table > CREATE TABLE IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_TABLE ( > ORGANIZATION_ID CHAR(15) NOT NULL, > KEY_PREFIX CHAR(3) NOT NULL, > CREATED_DATE DATE, > CREATED_BY CHAR(15), > CONSTRAINT PK PRIMARY KEY ( > ORGANIZATION_ID, > KEY_PREFIX > ) > ) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true; > 2. Create a global view on base global table with where clause > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW ( > INT1 BIGINT NOT NULL, > DOUBLE1 DECIMAL(12, 3), > IS_BOOLEAN BOOLEAN, > TEXT1 VARCHAR, > CONSTRAINT PKVIEW PRIMARY KEY > ( > INT1 > ) > ) > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE KEY_PREFIX = '123'; > 3. Create gloabl index on the global view > CREATE INDEX IF NOT EXISTS GLOBAL_INDEX > ON PLATFORM_ENTITY.GLOBAL_VIEW (TEXT1 DESC, INT1) > INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE); > 4. Create tenant specific view on top of global view > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.TENANT_VIEW > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_VIEW; > 5. Insert 10 record in tenant view > 6. Remove any record in tenant view > 7. Query in tenant view > SELECT * FROM PLATFORM_ENTITY.TENANT_VIEW; > 9 records are returned > SELECT COUNT(*) FROM PLATFORM_ENTITY.TENANT_VIEW; > 10 was returned. > I guess the issue was secondary index is not updated when delete or update > happens. And the aggregate function is using the secondary index. The problem > does not happen in 4.8.0-1.0.4 > It is a regression -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (PHOENIX-3371) Aggregate Function is broken if secondary index exists
[ https://issues.apache.org/jira/browse/PHOENIX-3371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15572846#comment-15572846 ] Jiping Zhou commented on PHOENIX-3371: -- Here is the core tests which can reproduce the issue pliny.db.query.PlinySOQLPlatformImmutableTestCases/testSum pliny.db.query.PlinySOQLPlatformImmutableTestCases/testCountAll The test will pass the first time but fail since second time. > Aggregate Function is broken if secondary index exists > -- > > Key: PHOENIX-3371 > URL: https://issues.apache.org/jira/browse/PHOENIX-3371 > Project: Phoenix > Issue Type: Bug >Affects Versions: 4.8.1 >Reporter: Jiping Zhou > Attachments: PHOENIX-3371.patch, PHOENIX-3371_v2.patch > > > 1. Create a global table > CREATE TABLE IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_TABLE ( > ORGANIZATION_ID CHAR(15) NOT NULL, > KEY_PREFIX CHAR(3) NOT NULL, > CREATED_DATE DATE, > CREATED_BY CHAR(15), > CONSTRAINT PK PRIMARY KEY ( > ORGANIZATION_ID, > KEY_PREFIX > ) > ) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true; > 2. Create a global view on base global table with where clause > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW ( > INT1 BIGINT NOT NULL, > DOUBLE1 DECIMAL(12, 3), > IS_BOOLEAN BOOLEAN, > TEXT1 VARCHAR, > CONSTRAINT PKVIEW PRIMARY KEY > ( > INT1 > ) > ) > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE KEY_PREFIX = '123'; > 3. Create gloabl index on the global view > CREATE INDEX IF NOT EXISTS GLOBAL_INDEX > ON PLATFORM_ENTITY.GLOBAL_VIEW (TEXT1 DESC, INT1) > INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE); > 4. Create tenant specific view on top of global view > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.TENANT_VIEW > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_VIEW; > 5. Insert 10 record in tenant view > 6. Remove any record in tenant view > 7. Query in tenant view > SELECT * FROM PLATFORM_ENTITY.TENANT_VIEW; > 9 records are returned > SELECT COUNT(*) FROM PLATFORM_ENTITY.TENANT_VIEW; > 10 was returned. > I guess the issue was secondary index is not updated when delete or update > happens. And the aggregate function is using the secondary index. The problem > does not happen in 4.8.0-1.0.4 > It is a regression -- This message was sent by Atlassian JIRA (v6.3.4#6332)
