[PHOENIX-2194] order by should not require all PK fields with = constraint

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.5.0
• Fix Version/s: 4.6.0, 4.5.2
• Labels: AtMention SFDC

Environment

linux

• Assignee: James R. Taylor
• Reporter: Gary Horen
• Created: 21/Aug/15 00:01
• Updated: 14/Dec/15 20:58
• Resolved: 09/Sep/15 00:02

Description

Here is a table:
CREATE TABLE IF NOT EXISTS FEEDS.STUFF
(
STUFF CHAR(15) NOT NULL,
NONSENSE CHAR(15) NOT NULL
CONSTRAINT PK PRIMARY KEY
(
STUFF,
NONSENSE

)
) VERSIONS=1,MULTI_TENANT=TRUE,REPLICATION_SCOPE=1

Here is a query:
explain SELECT * FROM feeds.stuff
where stuff = ' '
and nonsense > ' '
order by nonsense

Here is the plan:
CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN
SERVER FILTER BY FIRST KEY ONLY
SERVER TOP 100 ROWS SORTED BY [NONSE
CLIENT MERGE SORT

If I change to ORDER BY STUFF, NONSENSE I get:
CLIENT 1-CHUNK SERIAL 1-WAY RANGE SCAN O
SERVER FILTER BY FIRST KEY ONLY AND
SERVER 100 ROW LIMIT
CLIENT 100 ROW LIMIT

Since the leading constraint is =, ORDER BY will be unaffected by it, so ORDER BY should not need the leading constraint; it should only require the columns whose values would vary (which, since they are ordered by the key, should (and do) result in the client side sort being optimized out.) Having to include the leading = constraints in the ORDER BY clause is very counter-intuitive.

Issue Links

• is related to: PHOENIX-2455 Partial results for a query when PHOENIX-2194 is applied
• relates to: CALCITE-873 Prevent sort when ORDER BY not necessary due to equality constraints