babelfish-for-postgresql / babelfish_extensions / 13828310282

77%

push

github

Update logic for LIKE operator for Case Insensitive collation (#3439)

We have supported LIKE from TSQL side for case insensitive collation by transforming LIKE to ILIKE (this is a PG
operator which sorts strings case insensitively based on active locale). We have also introduced optimisation in place
for the case when the right operand of the LIKE operator is a Const node and has a prefix. Eg:

-- CASE 1
SELECT COL FROM TAB WHERE COL LIKE 'ab'

-- CASE 2
SELECT COL FROM TAB WHERE COL LIKE 'a%'

This is the optimisation that we follow:

expression LIKE pattern

-- will become if pattern has exact match
expression = pattern

-- will become if pattern has prefix match
expression ILIKE pattern COLLATE cs_as AND 
expression BETWEEN patternConstPrefix AND (patternConstPrefix || E'\uFFFF)

E’\uFFFF is the highest sort key. We use the BETWEEN operator as in PG we can NOT use index scan for LIKE/ILIKE
operator. Whereas introducing BETWEEN will enable us to do so. And the CS_AS collation is used to store the locale
info for ILIKE.

For supporting case insensitive accent insensitive collations like latin1_general_ci_ai , we transform the node firstly by
adding a function expression (remove_accents_internal) which removes the accent and then we apply the above logic.

Currently we have observed that we do not comply with the transformation above. Rather than COLLATE CS_AS, we
observe COLLATE “default” in query plan. Consider the following query:

-- query (column a is collated with latin1_genetal_ci_as)
select a from varchar_tst where a collate chinese_prc_ci_as LIKE 'ab%'

-- query plan
Filter: (((a)::text ~~* 'ab%'::text COLLATE "default") 
AND ((a)::text >= 'ab'::text COLLATE "default") AND ((a)::text < 'ab?'::text))

Even though we observe COLLATE "default" in the query plan, it picks up the correct collation oid during execution.
However, if we update the collation oid in typeTypeCollation function to pickup database collation oid for string literals
in par... (continued)

21 of 21 new or added lines in 1 file covered. (100.0%)

47372 of 63138 relevant lines covered (75.03%)

187983.29 hits per line