#1762 Collation does not work with LIKE clause having wildcard in the middle

[Martin Desruisseaux]

Issue https://sourceforge.net/p/hsqldb/bugs/1516/ suggests that collations should work with LIKE clauses. But issue #1516 tests only with a wildcard in the end of the pattern, which works. Testing with the same pattern but a wildcard before the end does not work.

The following code reproduces the problem. It works for all tested cases except "LIKE With wildcard in the middle".

import java.sql.*;

class SpatialMetadataTest {
    public static void main(String[] args) throws SQLException {
        new SpatialMetadataTest().testCollates();
    }

    private Statement stmt;

    private void testCollates() throws SQLException {
        final var simple = new org.hsqldb.jdbc.JDBCDataSource();
        simple.setURL("jdbc:hsqldb:mem:Test");
        try (Connection connection = simple.getConnection()) {
            try (Statement stmt = connection.createStatement()) {
                stmt.execute("CREATE TABLE datum (name VARCHAR(255) COLLATE \"French 0\")");
                stmt.execute("INSERT INTO datum values('Nouvelle Triangulation Française'), ('Nouvelle Triangulation Française (Paris))')");
                this.stmt = stmt;
                printQuery("With =",                             " = 'Nouvelle Triangulation Francaise'");
                printQuery("LIKE without wildcard",            "LIKE 'Nouvelle Triangulation Francaise'");
                printQuery("LIKE With wildcard in the middle", "LIKE 'Nouvelle%Triangulation Francaise'");
                printQuery("LIKE With wildcard at the end",    "LIKE 'Nouvelle Triangulation Francaise%'");
            }
        }
    }

    private void printQuery(final String title, final String sqlFragment) throws SQLException {
        System.out.println(title);
        try (ResultSet r = stmt.executeQuery("SELECT * FROM datum WHERE name " + sqlFragment)) {
            while (r.next()) {
                System.out.println("    " + r.getString(1));
            }
        }
    }
}