Expr. simplification / rewrite: regex `.*foo.*`

[crepererum]

Is your feature request related to a problem or challenge?

SQL is a complex beast and sometimes users don't use the best tool for the job. A good example is:

SELECT *
FROM t
WHERE c ~ '.*foo.*';

This regex will even get pushed down to the Parquet file filtering. However it effectively only means "c contains 'foo'".

Describe the solution you'd like

Extend the regex simplification and the respective tests to rewrite .*needle.* to contains.

Describe alternatives you've considered

We could also rewrite this to %foo% (i.e. a LIKE statement)

Additional context

-

[petern48]

take