[
https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16211698#comment-16211698
]
Andrew Purtell commented on PHOENIX-4292:
-----------------------------------------
[~jfernando_sfdc] I'm not arguing with you either about this bug report, and
the specific tests for it. Your comment about insufficient quality/testing in
Phoenix derailed us from that narrow focus into a larger discussion. You don't
define what you mean by "adequate" when you say this:
{quote}
It's great we address bugs quickly, don't get me wrong and that's always
appreciated. However, If we find a bug that exposes holes that have been there
from the very beginning, I believe part of fixing that bug is exploring where
else that bug might have implications. As part of this we should make sure
there is adequate test coverage. That way we really solve the problem and have
confidence in the quality of what we are shipping. Otherwise we are just
plugging leaks as they are found. As Phoenix matures and used in more
production environments I believe this kind of approach is becoming even more
important.
{quote}
As a Phoenix committer and community member it's on you as much as everyone
else to define what 'adequate' means and your thoughts are more than welcome.
Later, when you said this:
{quote}
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.
{quote}
I do think you are exactly backwards about what we should ask our community
members, and seem to suggest that committers are the responsible ones for test
coverage, which is a misunderstanding of the committer role at Apache in my
opinion. However as I said we have been derailed a bit from the issue at hand.
If more needs to be said or hashed out probably best to start a discussion on
dev@ or continue in that thread that Lars started a couple of weeks ago.
> 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)
