[BUG] The `like` function is case insensitive in PPL but it should be case sensitive

[LantaoJin]

What is the bug?
Following PPL queries on Clickbench have the different results with Spark SQL. They caused by the like function in PPL is case insensitive but Spark's like is case sensitive (ilike is case insensitive)
Q21 and its plan
Q22 and its plan
Q23 and its plan
Q24 and its plan

[LantaoJin]

cc @penghuo since this change introduced by #1160

The like function in SparkSQL and SPL are all case-sensitive. For case-insensitive, Spark uses ilike or like(upper(field), 'PATTERN')

like function in MySQL is case-insensitive by default, but can be case-sensitive by changing the charset:

mysql> SELECT 'abc' LIKE 'ABC';
    -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
    -> 0

[penghuo]

Agree, we should change behaviour of like to case sensitive. can we use plugins.ppl.syntax.legacy.preferred setting to handle breaking change?

Question, can like support all string field type, 1) text, 2) keyword, 3) _source (index disable). If not, what is limiatation?

[LantaoJin]

Agree, we should change behaviour of like to case sensitive. can we use plugins.ppl.syntax.legacy.preferred setting to handle breaking change?

if we want to leverage plugins.ppl.syntax.legacy.preferred to handle behavior, we'd better have an option or argument for like function to control the default value correspondingly. For example: like(field, PATTERN, [caseSensitive]).

Question, can like support all string field type, 1) text, 2) keyword, 3) _source (index disable). If not, what is limiatation?

Yes we 've already support all string field type in v3. For non-keyword, we execute it via script pushdown.