[YCQL] Unable to use range query on a column which is clustered in the index but a partition column in the primary key

[kmuthukk]

Test Case:

CREATE KEYSPACE k;
USE k;

CREATE TABLE test (
  id int,
  scope text,
  metadata map<text, text>,
  PRIMARY KEY (id)
) WITH default_time_to_live = 0
  AND transactions = {'enabled': 'true'};

CREATE INDEX test_index ON test (scope, id)
    WITH CLUSTERING ORDER BY (id ASC)
    AND transactions = {'enabled': 'true'};

INSERT into test (id, scope, metadata) VALUES (1, 'test', {'a' : '1', 'b' : '1'});
INSERT into test (id, scope, metadata) VALUES (2, 'test', {'a' : '2', 'b' : '2'});
INSERT into test (id, scope, metadata) VALUES (3, 'test', {'a' : '3', 'b' : '3'});
INSERT into test (id, scope, metadata) VALUES (4, 'test', {'a' : '4', 'b' : '4'});
INSERT into test (id, scope, metadata) VALUES (5, 'test', {'a' : '5', 'b' : '5'});

Now, a query of this form is expected to use the index because scope is provided, and we should be able to do a range scan on the id which is a clustering column in the index. However, we run into this error message:

ycqlsh:k> SELECT id FROM test WHERE scope = 'test' AND id > 2;
SyntaxException: Invalid CQL Statement. Partition column cannot be used in this expression
SELECT id FROM test WHERE scope = 'test' AND id > 2;
                                             ^^
 (ql error -12)

[kmuthukk]

A kludgy stop-gap workaround until we can roll out the fix/enhancement to support this, would be to have a duplicate column id2 whose contents are the same as id column. But the index is built on (scope, id2) instead of (scope, id).

CREATE TABLE test (
  id int,
  id2 int,
  scope text,
  metadata map<text, text>,
  PRIMARY KEY (id)
) WITH default_time_to_live = 0
  AND transactions = {'enabled': 'false'};

CREATE INDEX test_index ON test (scope, id2)
    WITH CLUSTERING ORDER BY (id2 ASC)
    AND transactions = {'enabled': 'false', 'consistency_level': 'user_enforced'};


INSERT into test (id, id2, scope, metadata) VALUES (1, 1, 'test', {'a' : '1', 'b' : '1'});
INSERT into test (id, id2, scope, metadata) VALUES (2, 2, 'test', {'a' : '2', 'b' : '2'});
INSERT into test (id, id2, scope, metadata) VALUES (3, 3, 'test', {'a' : '3', 'b' : '3'});
INSERT into test (id, id2, scope, metadata) VALUES (4, 4, 'test', {'a' : '4', 'b' : '4'});
INSERT into test (id, id2, scope, metadata) VALUES (5, 5, 'test', {'a' : '5', 'b' : '5'});

ycqlsh:k1> explain SELECT id FROM test WHERE scope = 'test' AND id2 > 2 limit 2;

QUERY PLAN
------------------------------------------------
 Index Only Scan using k1.test_index on k1.test
   Key Conditions: (scope = 'test')
   Filter: (id2 > 2)

ycqlsh:k1> SELECT id FROM test WHERE scope = 'test' AND id2 > 2 limit 2;

id
----
  3
  4 

[tedyu]

Looks like the original query would work when "Partition column cannot be used in this expression" error is dropped from WhereExprState::AnalyzeColumnOp().

Maybe there is some consideration for prohibiting partition column from appearing in inequality conditions.

[tedyu]

I tried to refine the condition where the error is raised.

      if (col_desc->is_hash() && !sem_context->selecting_from_index()) {
        return sem_context->Error(expr, "Partition column cannot be used in this expression",
            ErrorCode::CQL_STATEMENT_INVALID);

For the given example, sem_context->selecting_from_index() is false and the error is still raised.

I will keep investigating.

[tedyu]

Looking at PTSelectStmt::Analyze(), this is the current order:

  RETURN_NOT_OK(AnalyzeWhereClause(sem_context));
...
    RETURN_NOT_OK(AnalyzeIndexes(sem_context));
    if (child_select_ && child_select_->covers_fully_) {
      return Status::OK();

Effort so far has been on AnalyzeWhereClause(). However, the child select is determined later, in AnalyzeIndexes().
One possibility is to extract hash column check to after AnalyzeIndexes() returns.

[tedyu]

cql-clustered-child.txt