Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 2C84F200D23 for ; Thu, 19 Oct 2017 19:20:07 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 2AE4A1609EE; Thu, 19 Oct 2017 17:20:07 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 495CF1609D7 for ; Thu, 19 Oct 2017 19:20:06 +0200 (CEST) Received: (qmail 8109 invoked by uid 500); 19 Oct 2017 17:20:05 -0000 Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list dev@phoenix.apache.org Received: (qmail 8034 invoked by uid 99); 19 Oct 2017 17:20:05 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 19 Oct 2017 17:20:05 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 941D11807DC for ; Thu, 19 Oct 2017 17:20:04 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -99.202 X-Spam-Level: X-Spam-Status: No, score=-99.202 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, RP_MATCHES_RCVD=-0.001, SPF_PASS=-0.001, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id dYaCpFGxX_MH for ; Thu, 19 Oct 2017 17:20:02 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id 6A17E5FD8E for ; Thu, 19 Oct 2017 17:20:01 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id DC693E0F3B for ; Thu, 19 Oct 2017 17:20:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 3BF4621EE9 for ; Thu, 19 Oct 2017 17:20:00 +0000 (UTC) Date: Thu, 19 Oct 2017 17:20:00 +0000 (UTC) From: "Thomas D'Silva (JIRA)" To: dev@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (PHOENIX-4292) Filters on Tables and Views with composite PK of VARCHAR fields with sort direction DESC do not work MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Thu, 19 Oct 2017 17:20:07 -0000 [ https://issues.apache.org/jira/browse/PHOENIX-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16211378#comment-16211378 ] Thomas D'Silva commented on PHOENIX-4292: ----------------------------------------- Its possible, I filed PHOENIX-3383 to add more tests. > 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)