search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
8.84k stars 1.05k forks source link

[YCQL] Support JSONB and collection columns as included columns in secondary index #4458

Open m-iancu opened 4 years ago

m-iancu commented 4 years ago

Jira Link: DB-4664 This is disabled in pt_column_definition.cc by this code:

  if (!QLType::IsValidPrimaryType(ql_type()->main()) || ql_type()->main() == DataType::FROZEN) {
    return sem_context->Error(coldef_, "Unsupported index datatype",
                              ErrorCode::SQL_STATEMENT_INVALID);
  }

But in theory, included columns correspond to value columns in the index so they could be allowed. We should investigate if that's correct and, if so, enable including columns of all supported types.

jameshartig commented 3 years ago

This would be very useful for us. We have collection fields that we'd like to include in the WHERE along with that WHERE using a secondary index and in order for the query to be index-only it can only look at covered columns. We'd be comfortable converting collection columns to JSONB if those were easier to include in secondary indexes.

The only way around this at the moment is for us to do a query without the collection filter so that it still uses the index and then we look up each row again using the table's primary key and check the value of that collection column.