[SPARK-48172][SQL] Fix escaping issues in JDBC Dialects

[mihailom-db]

What changes were proposed in this pull request?

Special case escaping for MySQL and fix issues with redundant escaping for ' character.

Why are the changes needed?

When pushing down startsWith, endsWith and contains they are converted to LIKE. This requires addition of escape characters for these expressions. Unfortunately, MySQL uses ESCAPE '\' syntax instead of ESCAPE '' which would cause errors when trying to push down.

Does this PR introduce any user-facing change?

Yes

How was this patch tested?

Tests for each existing dialect.

Was this patch authored or co-authored using generative AI tooling?

No.

[yaooqinn]

Existing tests.

I believe that it cannot be included under the current coverage. Can we add some new tests?

[mihailom-db]

@cloud-fan Could you take a look at this fix?

[beliefer]

LGTM except @cloud-fan 's comment.

[cloud-fan]

merged to master/3.5/3.4!

[panbingkun]

@mihailom-db Is the GA failure related to this pr? https://github.com/panbingkun/spark/actions/runs/9086615518/job/24972712440

[panbingkun]

From the GA's results, it seems that only MySQL is good, and everything else has problems, not only the support of data type LONGTEXT cc @beliefer @yaooqinn @HyukjinKwon @cloud-fan

[panbingkun]

When I change the data type from LONGTEXT to varchar(100), it seems that there are different problems in MsSqlServerIntegrationSuite, DB2IntegrationSuite, PostgresIntegrationSuite , eg:

[yaooqinn]

Thank @panbingkun，I reverted this in https://github.com/apache/spark/commit/4ff5ca81cffbd1940c864144ca8fbba54b605e4e

[panbingkun]

Thank @panbingkun，I reverted this in 4ff5ca8

Thanks! @yaooqinn

@mihailom-db After fix it, you can resubmit :)