[ 
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', 'yyyy-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', 'yyyy-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', 'yyyy-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', 'yyyy-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', 'yyyy-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', 'yyyy-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', 'yyyy-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', 'yyyy-MM-dd HH:mm:ss'), 10); 
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testa', 'teste', 
> TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10); 
> upsert into TEST."ab2" (pk1, pk2, col1, col3) VALUES ('testb', 'testa', 
> TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 10); 
> SELECT * FROM TEST."ab2" WHERE pk1 = 'testa'; -- This query returns 4 records 
> as expected
> SELECT * FROM TEST."ab2"; -- Returns 5 rows as expected
> SELECT * FROM TEST."ab2" WHERE pk1 >= 'testa'; -- Returns 5 rows as expected
> SELECT * FROM TEST."ab2" WHERE pk1 <= 'testa'; -- Returns 4 rows as expected
> SELECT * FROM TEST."ab2" WHERE pk1 > 'testa'; -- Returns 1 row as expected
> SELECT * FROM TEST."ab2" WHERE pk1 < 'testa'; -- Returns 0 rows as expected
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH MULTIPLE DATE PK 
> DESC:View with composite PK with multiple Date PK values DESC
> upsert into TEST."ab3" (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:00:00', 'yyyy-MM-dd 
> HH:mm:ss'), 'txt1', 10); 
> upsert into TEST."ab3" (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:01:00', 'yyyy-MM-dd 
> HH:mm:ss'), 'txt1', 10); 
> upsert into TEST."ab3" (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:02:00', 'yyyy-MM-dd 
> HH:mm:ss'), 'txt1', 10); 
> upsert into TEST."ab3" (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:03:00', 'yyyy-MM-dd 
> HH:mm:ss'), 'txt1', 10); 
> upsert into TEST."ab3" (pk1, pk2, col1, col3) VALUES (TO_DATE('2017-10-16 
> 23:00:00', 'yyyy-MM-dd HH:mm:ss'), TO_DATE('2017-10-16 21:04:00', 'yyyy-MM-dd 
> HH:mm:ss'), 'txt1', 10); 
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(pk2, 'yyyy-MM-dd 
> HH:mm:ss'), col1, col3 FROM TEST."ab3" WHERE pk1 = TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- This query returns 4 records as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(pk2, 'yyyy-MM-dd 
> HH:mm:ss'), col1, col3 FROM TEST."ab3"; -- Returns 5 rows as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(pk2, 'yyyy-MM-dd 
> HH:mm:ss'), col1, col3 FROM TEST."ab3" WHERE pk1 >= TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- Returns 5 rows as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(pk2, 'yyyy-MM-dd 
> HH:mm:ss'), col1, col3 FROM TEST."ab3" WHERE pk1 <= TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- Returns 4 rows as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(pk2, 'yyyy-MM-dd 
> HH:mm:ss'), col1, col3 FROM TEST."ab3" WHERE pk1 > TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- Returns 1 row as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), TO_CHAR(pk2, 'yyyy-MM-dd 
> HH:mm:ss'), col1, col3 FROM TEST."ab3" WHERE pk1 < TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- Returns 0 rows as expected
> -- DEMONOSTRATE NO QUERY PROBLEMS WHEN HAVE VIEW WITH MULTIPLE DATA TYPE 
> DESC: View with composite PK with multiple Data Type PK values DESC
> upsert into TEST."ab4" (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 1, 'txt1', 10); 
> upsert into TEST."ab4" (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 2, 'txt2', 10); 
> upsert into TEST."ab4" (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 3, 'txt3', 10); 
> upsert into TEST."ab4" (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 
> 22:00:00', 'yyyy-MM-dd HH:mm:ss'), 4, 'txt4', 10); 
> upsert into TEST."ab4" (pk1, pk2, pk3, col3) VALUES (TO_DATE('2017-10-16 
> 23:00:00', 'yyyy-MM-dd HH:mm:ss'), 1, 'txt1', 10); 
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4" 
> WHERE pk1 = TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- This 
> query returns 4 records as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4" 
> WHERE pk1 = TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss') AND pk2 = 
> 2; -- This query returns 1 records as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4" 
> WHERE pk1 = TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss') AND pk2 > 
> 2; -- This query returns 2 records as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4"; 
> -- Returns 5 rows as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4" 
> WHERE pk1 >= TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- 
> Returns 5 rows as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4" 
> WHERE pk1 <= TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- 
> Returns 4 rows as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4" 
> WHERE pk1 > TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- Returns 
> 1 row as expected
> SELECT TO_CHAR(pk1, 'yyyy-MM-dd HH:mm:ss'), pk2, pk3, col3 FROM TEST."ab4" 
> WHERE pk1 < TO_DATE('2017-10-16 22:00:00', 'yyyy-MM-dd HH:mm:ss'); -- Returns 
> 0 rows as expected



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to