[YCQL] Unable to do explicit ORDER BY sort on clustered columns of index

[dwheeler]

When creating a keyspace with the following schema

CREATE TABLE test_table (
	a text,
	b text,
	c int,
	PRIMARY KEY (a, b))
WITH CLUSTERING ORDER BY (b ASC) AND default_time_to_live = 0;

CREATE INDEX test_table_index ON test_table (b, c) INCLUDE (a) 
WITH CLUSTERING ORDER BY (c DESC) 
  AND transactions = {'enabled' : FALSE, 'consistency_level' : 'user_enforced'};

Queries that use the secondary index and provide an order by will fail with the error:

SELECT * FROM test_table WHERE b = 'b2'  ORDER BY c DESC;
ERROR: Invalid Arguments. All hash columns must be set if order by clause is present.

Leaving the order clause out succeeds

SELECT * FROM test_table WHERE b = 'b2';

[kmuthukk]

Thx @dwheeler for reporting.
@nocaway - could you take an initial look of what's involved to address this?

[nocaway]

This is a bug in YugaByte semantic analysis.

• The error check was processing SELECT statement against the table description without considering the secondary indexes of the table.
• The decision on which index to use for a query is determined at a later time.

Implementation.

• Currently, when WHERE clause is processed, values of columns are divided to list<hash_column>, list<range_column>, and list<regular_column> according to the table description. These lists are then used to check against ONLY the PRIMARY index. Afterward, YugaByte walks the lists to decide which INDEX to use, but by that time, the error check already rejects the requested query.

• The fix would be changing the list to array< list >. Each entry of the array would be associated with an index. PRIMARY index is associated with array[0]. Index id 1 would be associated with array[1], and so on. The array can then be used to analyze the query against each INDEX.

[nocaway]

This has been fixed by commit 592fcfa

Instead of making copies of column_list (schema) for the indexes as suggested in my previous comment, the existing code made one new copy for the entire SELECT statement, replace the target-table with its chosen table_index, and rerun analysis on the entire SELECT against the chosen table_index. I decided to keep this design of copying the entire SELECT statement in this bug fix as changing the current design would be beyond the scope of this bug fix.

To fix the issue, the analysis for ORDER BY clause is now done after the aforementioned copy of original SELECT statement is created and being analyzed. As a result, the error-check is run against the index schema instead of the schema of the target-table.