phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jan Fernando (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work
Date Thu, 19 Oct 2017 19:42:00 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16211612#comment-16211612
] 

Jan Fernando commented on PHOENIX-4292:
---------------------------------------

I am not suggesting we test everything. I was just concerned when I looked at the review that
there was only 1 test. This is the normal kind of feedback I'd given any developer who's code
I was reviewing. Seems to me this bug might warrant more than 1 test, I think it's a fair
question to ask any developer and if 1 test makes sense they should be able to argue for that.


My concern, came when the answer to my question was that there might be other cases that could
still not be addressed and that would be handled by filing another JIRA. If the other cases
are not pertinent to this particular issue and we feel we have locked this one down then great
and let's state that. I don't believe asking community members to submit their own tests is
the right response here. Everyone committing code should care about quality holistically.
I was merely trying to ask questions to think about things from that perspective.  

I'm simply responding to thread on the dev list a few weeks back about quality principles
[~lhofhansl] brought up, my take away from that was try and engage more with these kind of
questions when opportunities arose. 

Apologies if this was not how my comments sounded.  This was my 2 cents and I appreciate the
fast turn around on the fix. I'm happy to defer to you guys as you are in the code everyday
and I am not these days.


> 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', '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)

Mime
View raw message