[jira] [Updated] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work
[
https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Daniel Wong updated PHOENIX-4292:
-
Labels: DESC (was: )
> Filters on Tables and Views with composite PK of VARCHAR fields with sort
> direction DESC do not work
>
>
> Key: PHOENIX-4292
> URL: https://issues.apache.org/jira/browse/PHOENIX-4292
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.10.0
>Reporter: Jan Fernando
>Assignee: Thomas D'Silva
>Priority: Major
> Labels: DESC
> Fix For: 4.13.0
>
> Attachments: PHOENIX-4292-addendum.patch, PHOENIX-4292.patch
>
>
> We noticed that in certain instances on tables and views that were defined
> with a Composite PK and where the elements of the PK were all DESC that
> queries exhibited strange behavior and did not return results when expected.
> A simple query on the first element of the PK returned 0 results e.g SELECT *
> FROM MY_TABLE WHERE PK1 = 'myvaluethatexists' would return 0 results.
> After some investigation it appears that querying tables and views with a
> Composite PK that :
> a) have multiple VARCHAR columns in the PK
> b) the sort direction of all the VARCHAR columns is defined as DESC
> does not work correctly and the filters are not honored and SQL appears
> broken to the end user.
> Detailed repro steps:
> ---
> -- 1. Create Global Base Table
> 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
> -- 2. Create various TENANT SPECIFIC VIEWS i.e. use a tenant specific
> connection
> CREATE VIEW IF NOT EXISTS TEST."abc" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'abc';
> CREATE VIEW IF NOT EXISTS TEST."ab2" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 ASC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab2';
> CREATE VIEW IF NOT EXISTS TEST."ab3" (pk1 DATE(10) NOT NULL, pk2 DATE(10) NOT
> NULL, col1 VARCHAR(10), col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC,
> pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab3';
> CREATE VIEW IF NOT EXISTS TEST."ab4" (pk1 DATE(10) NOT NULL, pk2 DECIMAL NOT
> NULL, pk3 VARCHAR(10) NOT NULL, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC, pk3 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX =
> 'ab4';
> -- 3. Test cases that exhibit this issues
> -- SIMULATE EQUALITY QUERY PROBLEMS: View with composite PK with multiple PK
> values of VARCHAR values DESC
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testd',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'teste',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testb', 'testa',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> SELECT * FROM TEST."abc" WHERE pk1 = 'testa'; -- INCORRECT RESULT: This query
> returns no records, expected to return 4
> SELECT * FROM TEST."abc"; -- Returns 5 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 >= 'testa'; -- INCORRECT RESULT: This
> query returns 1 record, expected to return 5
> SELECT * FROM TEST."abc" WHERE pk1 <= 'testa'; -- Returns 4 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 > 'testa'; -- Returns 1 row as expected
> SELECT * FROM TEST."abc" WHERE pk1 < 'testa'; -- INCORRECT RESULT: This query
> returns 1 record, expected to return 0
> -- The following are cases where everything works as expected and which don't
> have composite VARCHAR PKs
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH SINGLE DESC TEXT PK:
> View with composite PK with single pk value DESC
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert
[jira] [Updated] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work
[
https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor updated PHOENIX-4292:
--
Fix Version/s: (was: 4.12.1)
> Filters on Tables and Views with composite PK of VARCHAR fields with sort
> direction DESC do not work
>
>
> Key: PHOENIX-4292
> URL: https://issues.apache.org/jira/browse/PHOENIX-4292
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.10.0
>Reporter: Jan Fernando
>Assignee: Thomas D'Silva
>Priority: Major
> Fix For: 4.13.0
>
> Attachments: PHOENIX-4292-addendum.patch, PHOENIX-4292.patch
>
>
> We noticed that in certain instances on tables and views that were defined
> with a Composite PK and where the elements of the PK were all DESC that
> queries exhibited strange behavior and did not return results when expected.
> A simple query on the first element of the PK returned 0 results e.g SELECT *
> FROM MY_TABLE WHERE PK1 = 'myvaluethatexists' would return 0 results.
> After some investigation it appears that querying tables and views with a
> Composite PK that :
> a) have multiple VARCHAR columns in the PK
> b) the sort direction of all the VARCHAR columns is defined as DESC
> does not work correctly and the filters are not honored and SQL appears
> broken to the end user.
> Detailed repro steps:
> ---
> -- 1. Create Global Base Table
> 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
> -- 2. Create various TENANT SPECIFIC VIEWS i.e. use a tenant specific
> connection
> CREATE VIEW IF NOT EXISTS TEST."abc" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'abc';
> CREATE VIEW IF NOT EXISTS TEST."ab2" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 ASC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab2';
> CREATE VIEW IF NOT EXISTS TEST."ab3" (pk1 DATE(10) NOT NULL, pk2 DATE(10) NOT
> NULL, col1 VARCHAR(10), col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC,
> pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab3';
> CREATE VIEW IF NOT EXISTS TEST."ab4" (pk1 DATE(10) NOT NULL, pk2 DECIMAL NOT
> NULL, pk3 VARCHAR(10) NOT NULL, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC, pk3 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX =
> 'ab4';
> -- 3. Test cases that exhibit this issues
> -- SIMULATE EQUALITY QUERY PROBLEMS: View with composite PK with multiple PK
> values of VARCHAR values DESC
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testd',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'teste',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testb', 'testa',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> SELECT * FROM TEST."abc" WHERE pk1 = 'testa'; -- INCORRECT RESULT: This query
> returns no records, expected to return 4
> SELECT * FROM TEST."abc"; -- Returns 5 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 >= 'testa'; -- INCORRECT RESULT: This
> query returns 1 record, expected to return 5
> SELECT * FROM TEST."abc" WHERE pk1 <= 'testa'; -- Returns 4 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 > 'testa'; -- Returns 1 row as expected
> SELECT * FROM TEST."abc" WHERE pk1 < 'testa'; -- INCORRECT RESULT: This query
> returns 1 record, expected to return 0
> -- The following are cases where everything works as expected and which don't
> have composite VARCHAR PKs
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH SINGLE DESC TEXT PK:
> View with composite PK with single pk value DESC
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."ab2"
[jira] [Updated] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work
[
https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas D'Silva updated PHOENIX-4292:
Attachment: PHOENIX-4292-addendum.patch
Attaching addendum that also tests views that pk formats that were working
correctly.
> Filters on Tables and Views with composite PK of VARCHAR fields with sort
> direction DESC do not work
>
>
> Key: PHOENIX-4292
> URL: https://issues.apache.org/jira/browse/PHOENIX-4292
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.10.0
>Reporter: Jan Fernando
>Assignee: Thomas D'Silva
> Fix For: 4.13.0, 4.12.1
>
> Attachments: PHOENIX-4292-addendum.patch, PHOENIX-4292.patch
>
>
> We noticed that in certain instances on tables and views that were defined
> with a Composite PK and where the elements of the PK were all DESC that
> queries exhibited strange behavior and did not return results when expected.
> A simple query on the first element of the PK returned 0 results e.g SELECT *
> FROM MY_TABLE WHERE PK1 = 'myvaluethatexists' would return 0 results.
> After some investigation it appears that querying tables and views with a
> Composite PK that :
> a) have multiple VARCHAR columns in the PK
> b) the sort direction of all the VARCHAR columns is defined as DESC
> does not work correctly and the filters are not honored and SQL appears
> broken to the end user.
> Detailed repro steps:
> ---
> -- 1. Create Global Base Table
> 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
> -- 2. Create various TENANT SPECIFIC VIEWS i.e. use a tenant specific
> connection
> CREATE VIEW IF NOT EXISTS TEST."abc" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'abc';
> CREATE VIEW IF NOT EXISTS TEST."ab2" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 ASC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab2';
> CREATE VIEW IF NOT EXISTS TEST."ab3" (pk1 DATE(10) NOT NULL, pk2 DATE(10) NOT
> NULL, col1 VARCHAR(10), col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC,
> pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab3';
> CREATE VIEW IF NOT EXISTS TEST."ab4" (pk1 DATE(10) NOT NULL, pk2 DECIMAL NOT
> NULL, pk3 VARCHAR(10) NOT NULL, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC, pk3 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX =
> 'ab4';
> -- 3. Test cases that exhibit this issues
> -- SIMULATE EQUALITY QUERY PROBLEMS: View with composite PK with multiple PK
> values of VARCHAR values DESC
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testd',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'teste',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testb', 'testa',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> SELECT * FROM TEST."abc" WHERE pk1 = 'testa'; -- INCORRECT RESULT: This query
> returns no records, expected to return 4
> SELECT * FROM TEST."abc"; -- Returns 5 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 >= 'testa'; -- INCORRECT RESULT: This
> query returns 1 record, expected to return 5
> SELECT * FROM TEST."abc" WHERE pk1 <= 'testa'; -- Returns 4 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 > 'testa'; -- Returns 1 row as expected
> SELECT * FROM TEST."abc" WHERE pk1 < 'testa'; -- INCORRECT RESULT: This query
> returns 1 record, expected to return 0
> -- The following are cases where everything works as expected and which don't
> have composite VARCHAR PKs
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH SINGLE DESC TEXT PK:
> View with composite PK with single pk value DESC
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
>
[jira] [Updated] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work
[
https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas D'Silva updated PHOENIX-4292:
Attachment: PHOENIX-4292.patch
[~jamestaylor]
I thinks its a different issue. The test passes if I change
{{ValueSchema.Field}} constructor to set the sort order correctly instead of
always using SortOrder.ASC. I have attached a patch.
> Filters on Tables and Views with composite PK of VARCHAR fields with sort
> direction DESC do not work
>
>
> Key: PHOENIX-4292
> URL: https://issues.apache.org/jira/browse/PHOENIX-4292
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.10.0
>Reporter: Jan Fernando
>Assignee: Thomas D'Silva
> Fix For: 4.13.0, 4.12.1
>
> Attachments: PHOENIX-4292.patch
>
>
> We noticed that in certain instances on tables and views that were defined
> with a Composite PK and where the elements of the PK were all DESC that
> queries exhibited strange behavior and did not return results when expected.
> A simple query on the first element of the PK returned 0 results e.g SELECT *
> FROM MY_TABLE WHERE PK1 = 'myvaluethatexists' would return 0 results.
> After some investigation it appears that querying tables and views with a
> Composite PK that :
> a) have multiple VARCHAR columns in the PK
> b) the sort direction of all the VARCHAR columns is defined as DESC
> does not work correctly and the filters are not honored and SQL appears
> broken to the end user.
> Detailed repro steps:
> ---
> -- 1. Create Global Base Table
> 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
> -- 2. Create various TENANT SPECIFIC VIEWS i.e. use a tenant specific
> connection
> CREATE VIEW IF NOT EXISTS TEST."abc" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'abc';
> CREATE VIEW IF NOT EXISTS TEST."ab2" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 ASC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab2';
> CREATE VIEW IF NOT EXISTS TEST."ab3" (pk1 DATE(10) NOT NULL, pk2 DATE(10) NOT
> NULL, col1 VARCHAR(10), col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC,
> pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab3';
> CREATE VIEW IF NOT EXISTS TEST."ab4" (pk1 DATE(10) NOT NULL, pk2 DECIMAL NOT
> NULL, pk3 VARCHAR(10) NOT NULL, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC, pk3 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX =
> 'ab4';
> -- 3. Test cases that exhibit this issues
> -- SIMULATE EQUALITY QUERY PROBLEMS: View with composite PK with multiple PK
> values of VARCHAR values DESC
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testd',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'teste',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testb', 'testa',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> SELECT * FROM TEST."abc" WHERE pk1 = 'testa'; -- INCORRECT RESULT: This query
> returns no records, expected to return 4
> SELECT * FROM TEST."abc"; -- Returns 5 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 >= 'testa'; -- INCORRECT RESULT: This
> query returns 1 record, expected to return 5
> SELECT * FROM TEST."abc" WHERE pk1 <= 'testa'; -- Returns 4 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 > 'testa'; -- Returns 1 row as expected
> SELECT * FROM TEST."abc" WHERE pk1 < 'testa'; -- INCORRECT RESULT: This query
> returns 1 record, expected to return 0
> -- The following are cases where everything works as expected and which don't
> have composite VARCHAR PKs
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH SINGLE DESC TEXT PK:
> View with composite PK with single pk value DESC
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
>
[jira] [Updated] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work
[
https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas D'Silva updated PHOENIX-4292:
Summary: Filters on Tables and Views with composite PK of VARCHAR fields
with sort direction DESC do not work (was: SOQL Filters on Tables and Views
with composite PK of VARCHAR fields with sort direction DESC do not work)
> Filters on Tables and Views with composite PK of VARCHAR fields with sort
> direction DESC do not work
>
>
> Key: PHOENIX-4292
> URL: https://issues.apache.org/jira/browse/PHOENIX-4292
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.10.0
>Reporter: Jan Fernando
>Assignee: Thomas D'Silva
> Fix For: 4.13.0, 4.12.1
>
>
> We noticed that in certain instances on tables and views that were defined
> with a Composite PK and where the elements of the PK were all DESC that
> queries exhibited strange behavior and did not return results when expected.
> A simple query on the first element of the PK returned 0 results e.g SELECT *
> FROM MY_TABLE WHERE PK1 = 'myvaluethatexists' would return 0 results.
> After some investigation it appears that querying tables and views with a
> Composite PK that :
> a) have multiple VARCHAR columns in the PK
> b) the sort direction of all the VARCHAR columns is defined as DESC
> does not work correctly and the filters are not honored and SQL appears
> broken to the end user.
> Detailed repro steps:
> ---
> -- 1. Create Global Base Table
> 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
> -- 2. Create various TENANT SPECIFIC VIEWS i.e. use a tenant specific
> connection
> CREATE VIEW IF NOT EXISTS TEST."abc" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'abc';
> CREATE VIEW IF NOT EXISTS TEST."ab2" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 ASC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab2';
> CREATE VIEW IF NOT EXISTS TEST."ab3" (pk1 DATE(10) NOT NULL, pk2 DATE(10) NOT
> NULL, col1 VARCHAR(10), col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC,
> pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab3';
> CREATE VIEW IF NOT EXISTS TEST."ab4" (pk1 DATE(10) NOT NULL, pk2 DECIMAL NOT
> NULL, pk3 VARCHAR(10) NOT NULL, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC, pk3 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX =
> 'ab4';
> -- 3. Test cases that exhibit this issues
> -- SIMULATE EQUALITY QUERY PROBLEMS: View with composite PK with multiple PK
> values of VARCHAR values DESC
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testd',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'teste',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testb', 'testa',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> SELECT * FROM TEST."abc" WHERE pk1 = 'testa'; -- INCORRECT RESULT: This query
> returns no records, expected to return 4
> SELECT * FROM TEST."abc"; -- Returns 5 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 >= 'testa'; -- INCORRECT RESULT: This
> query returns 1 record, expected to return 5
> SELECT * FROM TEST."abc" WHERE pk1 <= 'testa'; -- Returns 4 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 > 'testa'; -- Returns 1 row as expected
> SELECT * FROM TEST."abc" WHERE pk1 < 'testa'; -- INCORRECT RESULT: This query
> returns 1 record, expected to return 0
> -- The following are cases where everything works as expected and which don't
> have composite VARCHAR PKs
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH SINGLE DESC TEXT PK:
> View with composite PK with single pk value DESC
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES
[jira] [Updated] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work
[
https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas D'Silva updated PHOENIX-4292:
Fix Version/s: 4.13.0
> Filters on Tables and Views with composite PK of VARCHAR fields with sort
> direction DESC do not work
>
>
> Key: PHOENIX-4292
> URL: https://issues.apache.org/jira/browse/PHOENIX-4292
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.10.0
>Reporter: Jan Fernando
>Assignee: Thomas D'Silva
> Fix For: 4.13.0, 4.12.1
>
>
> We noticed that in certain instances on tables and views that were defined
> with a Composite PK and where the elements of the PK were all DESC that
> queries exhibited strange behavior and did not return results when expected.
> A simple query on the first element of the PK returned 0 results e.g SELECT *
> FROM MY_TABLE WHERE PK1 = 'myvaluethatexists' would return 0 results.
> After some investigation it appears that querying tables and views with a
> Composite PK that :
> a) have multiple VARCHAR columns in the PK
> b) the sort direction of all the VARCHAR columns is defined as DESC
> does not work correctly and the filters are not honored and SQL appears
> broken to the end user.
> Detailed repro steps:
> ---
> -- 1. Create Global Base Table
> 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
> -- 2. Create various TENANT SPECIFIC VIEWS i.e. use a tenant specific
> connection
> CREATE VIEW IF NOT EXISTS TEST."abc" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'abc';
> CREATE VIEW IF NOT EXISTS TEST."ab2" (pk1 VARCHAR(10) NOT NULL, pk2
> VARCHAR(10) NOT NULL, col1 DATE, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 ASC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab2';
> CREATE VIEW IF NOT EXISTS TEST."ab3" (pk1 DATE(10) NOT NULL, pk2 DATE(10) NOT
> NULL, col1 VARCHAR(10), col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1 DESC,
> pk2 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX = 'ab3';
> CREATE VIEW IF NOT EXISTS TEST."ab4" (pk1 DATE(10) NOT NULL, pk2 DECIMAL NOT
> NULL, pk3 VARCHAR(10) NOT NULL, col3 DECIMAL CONSTRAINT PK PRIMARY KEY (pk1
> DESC, pk2 DESC, pk3 DESC)) AS SELECT * FROM TEST.BASETABLE WHERE KEY_PREFIX =
> 'ab4';
> -- 3. Test cases that exhibit this issues
> -- SIMULATE EQUALITY QUERY PROBLEMS: View with composite PK with multiple PK
> values of VARCHAR values DESC
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'testd',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testa', 'teste',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."abc" (pk1, pk2, col1, col3) VALUES ('testb', 'testa',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> SELECT * FROM TEST."abc" WHERE pk1 = 'testa'; -- INCORRECT RESULT: This query
> returns no records, expected to return 4
> SELECT * FROM TEST."abc"; -- Returns 5 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 >= 'testa'; -- INCORRECT RESULT: This
> query returns 1 record, expected to return 5
> SELECT * FROM TEST."abc" WHERE pk1 <= 'testa'; -- Returns 4 rows as expected
> SELECT * FROM TEST."abc" WHERE pk1 > 'testa'; -- Returns 1 row as expected
> SELECT * FROM TEST."abc" WHERE pk1 < 'testa'; -- INCORRECT RESULT: This query
> returns 1 record, expected to return 0
> -- The following are cases where everything works as expected and which don't
> have composite VARCHAR PKs
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH SINGLE DESC TEXT PK:
> View with composite PK with single pk value DESC
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testb',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testc',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd HH:mm:ss'), 10);
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'testd',
> TO_DATE('2017-10-16 22:00:00', '-MM-dd
