Problem is in invalid case for schema's IGNORECASE=true;DATABASE_TO_UPPER=false

[sberdachuk-epam]

IGNORECASE property doesn't work for schemas

I'm created database with parameters:
MODE=MYSQL;DB_CLOSE_DELAY=-1;IGNORECASE=true;DATABASE_TO_UPPER=false

In common everything work fine except working with external DB tools like DBeaver.

Problem is in invalid case for schema's.
It can not populate 'public' schema data like SELECT * FROM public.

It is works only with upper case: 'PUBLIC'
SELECT * FROM PUBLIC.

Same problem with information_schema

[pintiliea]

I just noticed the same on my machine.

H2 version:
1.4.196 (h2-1.4.196.jar)

This is the URL I use:
jdbc:h2:mem:tests;IGNORECASE=TRUE;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;TRACE_LEVEL_SYSTEM_OUT=3

The statement that get's executed:
/**/PreparedStatement prep10 = conn7.prepareStatement("SELECT TABLE_CATALOG TABLE_CAT, TABLE_SCHEMA TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, TYPE_NAME TYPE_CAT, TYPE_NAME TYPE_SCHEM, TYPE_NAME, TYPE_NAME SELF_REFERENCING_COL_NAME, TYPE_NAME REF_GENERATION, SQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG LIKE ? ESCAPE ? AND TABLE_SCHEMA LIKE ? ESCAPE ? AND TABLE_NAME LIKE ? ESCAPE ? AND (TRUE) ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");

The values:

• catalogPattern: null
• schemaPattern: public
• tableNamePattern: MY_TABLE
• escape: \\

which results in this:

SELECT 
    TABLE_CATALOG TABLE_CAT, 
	TABLE_SCHEMA TABLE_SCHEM, 
	TABLE_NAME, 
	TABLE_TYPE, 
	REMARKS, 
	TYPE_NAME TYPE_CAT, 
	TYPE_NAME TYPE_SCHEM, 
	TYPE_NAME, 
	TYPE_NAME SELF_REFERENCING_COL_NAME, 
	TYPE_NAME REF_GENERATION, 
	SQL 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
    TABLE_CATALOG LIKE '%'
    AND TABLE_SCHEMA LIKE 'public'
    AND TABLE_NAME LIKE 'MY_TABLE'
    AND (TRUE) ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME

(I removed ESCAPE because it didn't work in the H2-CONSOLE)

The SQL above returns 0 rows.

When I do this -- AND TABLE_SCHEMA LIKE 'public' it returns 1 row with MY_TABLE.

Uppercase PUBLIC also returns 1 row: AND TABLE_SCHEMA LIKE 'PUBLIC'

Which means IGNORECASE=TRUE doesn't work for the column TABLE_SCHEMA in INFORMATION_SCHEMA.TABLES

The relevant class is
org.h2.jdbc.JdbcDatabaseMetaData

    /**
     * Gets the list of tables in the database. The result set is sorted by
     * TABLE_TYPE, TABLE_SCHEM, and TABLE_NAME.
     *
     * <ul>
     * <li>1 TABLE_CAT (String) table catalog </li>
     * <li>2 TABLE_SCHEM (String) table schema </li>
     * <li>3 TABLE_NAME (String) table name </li>
     * <li>4 TABLE_TYPE (String) table type </li>
     * <li>5 REMARKS (String) comment </li>
     * <li>6 TYPE_CAT (String) always null </li>
     * <li>7 TYPE_SCHEM (String) always null </li>
     * <li>8 TYPE_NAME (String) always null </li>
     * <li>9 SELF_REFERENCING_COL_NAME (String) always null </li>
     * <li>10 REF_GENERATION (String) always null </li>
     * <li>11 SQL (String) the create table statement or NULL for systems tables
     * </li>
     * </ul>
     *
     * @param catalogPattern null (to get all objects) or the catalog name
     * @param schemaPattern null (to get all objects) or a schema name
     *            (uppercase for unquoted names)
     * @param tableNamePattern null (to get all objects) or a table name
     *            (uppercase for unquoted names)
     * @param types null or a list of table types
     * @return the list of columns
     * @throws SQLException if the connection is closed
     */
    @Override
    public ResultSet getTables(String catalogPattern, String schemaPattern,
            String tableNamePattern, String[] types) throws SQLException {
        try {
            if (isDebugEnabled()) {
                debugCode("getTables(" + quote(catalogPattern) + ", " +
                        quote(schemaPattern) + ", " + quote(tableNamePattern) +
                        ", " + quoteArray(types) + ");");
            }
            checkClosed();
            String tableType;
            if (types != null && types.length > 0) {
                StatementBuilder buff = new StatementBuilder("TABLE_TYPE IN(");
                for (int i = 0; i < types.length; i++) {
                    buff.appendExceptFirst(", ");
                    buff.append('?');
                }
                tableType = buff.append(')').toString();
            } else {
                tableType = "TRUE";
            }
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "TABLE_TYPE, "
                    + "REMARKS, "
                    + "TYPE_NAME TYPE_CAT, "
                    + "TYPE_NAME TYPE_SCHEM, "
                    + "TYPE_NAME, "
                    + "TYPE_NAME SELF_REFERENCING_COL_NAME, "
                    + "TYPE_NAME REF_GENERATION, "
                    + "SQL "
                    + "FROM INFORMATION_SCHEMA.TABLES "
                    + "WHERE TABLE_CATALOG LIKE ? ESCAPE ? "
                    + "AND TABLE_SCHEMA LIKE ? ESCAPE ? "
                    + "AND TABLE_NAME LIKE ? ESCAPE ? "
                    + "AND (" + tableType + ") "
                    + "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");
            prep.setString(1, getCatalogPattern(catalogPattern));
            prep.setString(2, "\\");
            prep.setString(3, getSchemaPattern(schemaPattern));
            prep.setString(4, "\\");
            prep.setString(5, getPattern(tableNamePattern));
            prep.setString(6, "\\");
            for (int i = 0; types != null && i < types.length; i++) {
                prep.setString(7 + i, types[i]);
            }
            return prep.executeQuery();
        } catch (Exception e) {
            throw logAndConvert(e);
        }
    }

My workaround for now is to just use PUBLIC instead of public

[sberdachuk-epam]

Hi, main problem that db access tools like Dbeaver can't populate database schema properly in this case.

[katzyn]

In the next version of H2 DATABASE_TO_LOWER=TRUE can be used instead.