babelfish-for-postgresql / babelfish_extensions / 15180060239

77%

push

github

Use Index Scan for EXACT and PREFIX pattern matches for LIKE for Const node inside CollateExpr (#3770) (#3776)

With this commit: e119968, babelfish now use Index Scans for the following cases:

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

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

However if there is a Collate clause with the right operand :

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

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

then Babelfish does NOT use Index Scan.

This commit enables Index scan for such cases. We do this by removing CollateExpr on both operands as those are
redundant and the OpExpr (LIKE) operator is already aware of the collation it needs to use.

(cherry picked from commit 431bf0be0)

Task: BABEL-5077
Signed-off-by: Shameem Ahmed <shmeeh@amazon.com>

31 of 34 new or added lines in 1 file covered. (91.18%)

48609 of 64485 relevant lines covered (75.38%)

306388.7 hits per line