Jan Fernando created PHOENIX-4292:
-------------------------------------
Summary: SOQL Filters on Tables and Filters with composite PK of
VARCHAR fields with sort direction DESC does 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
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
(v6.4.14#64029)
