LIKE expressions with initial and ending % do not work on char columns

[tdcmeehan]

Your Environment

Local Mac

Expected Behavior

LIKE '%somevalue%' should work over varchar and char columns.

Current Behavior

Returns an error over char: Unexpected parameters (char(15), varchar, bigint) for function split. Expected: split(varchar(x), varchar(y), bigint) , split(varchar(x), varchar(y))

Possible Solution

See discussion in #20436.

Simplest solution, as advocated there, is to remove the optimization found in that PR over char columns, since it doesn't work.

Alternatively, we could try to create the function that's missing.

Finally, we could revert #20436.

Steps to Reproduce

1. Run the MemoryQueryRunner in IntelliJ
2. From the CLI: create table orders_char as select CAST(orderpriority AS CHAR(15)) AS orderpriority from orders;
3. From the CLI: select * from orders_char WHERE orderpriority LIKE '%1-URGENT%';
4. Observe the failure

Screenshots (if appropriate)

Context

This has been present since 0.284, so we'll need to create patch fixes for 0.284 and 0.285. We'll also need to hold up 0.286 so this goes in.

[kaikalur]

I prefer adding the missing SPLIT function overload for char type - varchar vs char is too subtle for users.

So I suggest for now, removing the CharType as suggsted on the original PR and open a separate issue to add SPLIT for CHAR type so we can make it more robust.

We are definitely not reverting the optimization.

[kaikalur]

presto:di> select SPLIT(CAST('abc' AS CHAR(3)), 'a');
Query 20240229_013122_09457_ph8x5 failed: line 1:8: Unexpected parameters (char(3), varchar(1)) for function split. Expected: split(varchar(x), varchar(y), bigint) , split(varchar(x), varchar(y))
select SPLIT(CAST('abc' AS CHAR(3)), 'a')

[tdcmeehan]

I prefer adding the missing SPLIT function overload for char type - varchar vs char is too subtle for users.

So I suggest for now, removing the CharType as suggsted on the original PR and open a separate issue to add SPLIT for CHAR type so we can make it more robust.

We are definitely not reverting the optimization.

Agreed on this path forward. Would you like to raise a PR?