[CASSANDRA-18493] SAI - LIKE prefix/suffix support

• Type: Epic
• Status: Open
• Priority: Normal
• Resolution: Unresolved
• Fix Version/s: 5.x
• Component/s: Feature/SAI
• Change Category: Operability
• Complexity: Byzantine
• Platform: All
• Assignee: Mike Adamson
• Reporter: Mike Adamson

Authors

Mike Adamson

• Created: 02/May/23 17:46
• Updated: 29/Oct/24 15:20

Description

This should provide the following functionality:

• LIKE abc% - prefix support
• LIKE %bcd - suffix support
• LIKE ab%cd - prefix/suffix support

Out of scope:

• LIKE %abc% - contains support

The index support for this can broken down as follows (general ideas that are open to suggestions):

• Prefix support. This can currently be achieved with the existing trie index but this needs work to make it more performant in coalescing postings. An alternative approach could be to modify the block balanced tree to support variable length datatypes. This would make general range queries possible on variable length types as well as prefix queries. These would benefit from the auxilary postings present in the balanced tree.
• Suffix support. This will need a reverse index on the values. This allows a search of the suffix to operate in the same way as a prefix query. There is no reason why suffix index cannot be built on top of the prefix index with separate postings for prefix and suffix. We would need to look at the byte comparable code in order to produce reverse values efficiently that sort correctly. 
• Prefix/Suffix support. This would require separate prefix and suffix index searches and an intersection on the resulting postings.

Issue Links

• Parent Feature: CASSANDRA-19224 Storage Attached Indexes (Phase 3)
• relates to: CASSANDRA-19224 Storage Attached Indexes (Phase 3)
• relates to: CASSANDRA-19574 LIKE_MATCHES should be an equality
• relates to: CASSANDRA-17198 Allow to filter using LIKE predicates