CVE-2023-25157 (Java, published 2023-02-21T22:15Z)
Description: value: GeoServer is an open source software server written in Java that allows users to share and edit geospatial data. GeoServer includes support for the OGC Filter expression language and the OGC Common Query Language (CQL) as part of the Web Feature Service (WFS) and Web Map Service (WMS) protocols.  CQL is also supported through the Web Coverage Service (WCS) protocol for ImageMosaic coverages. Users are advised to upgrade to either version 2.21.4, or version 2.22.2 to resolve this issue. Users unable to upgrade should disable the PostGIS Datastore *encode functions* setting to mitigate ``strEndsWith``, ``strStartsWith`` and ``PropertyIsLike `` misuse and enable the PostGIS DataStore *preparedStatements* setting to mitigate the ``FeatureId`` misuse.
Severity: nan (CVSS v3 base score: 9.8)
Weakness: CWE-89 \u2014 Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
Repository: https://github.com/geoserver/geoserver
Files changed:
src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/ConfigDatabase.java
src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/Dialect.java
src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/FilterToCatalogSQL.java
src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/OracleDialect.java
src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/QueryBuilder.java
src/community/jdbcconfig/src/main/java/org/geoserver/jdbcloader/JDBCLoaderProperties.java
src/community/jdbcconfig/src/main/resources/applicationContext.xml
src/community/jdbcconfig/src/main/resources/jdbcconfig.properties
src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.h2
src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.postgres
src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/JDBCConfigTestSupport.java
src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/internal/QueryBuilderTest.java
Commit message: Merge pull request from GHSA-7g5f-wrx8-5ccf

* [GEOS-10839] Add JDBC Configuration parameter to disable SQL comments and pretty-printing

* [GEOS-10842] Escape user inputs in SQL queries

* [GEOS-10842] Applied PR feedback

Vulnerable code:
    public ConfigDatabase(final DataSource dataSource, final XStreamInfoSerialBinding binding) {
        this(dataSource, binding, null);

            this.dialect = Dialect.detect(dataSource);
        final StringBuilder sql = sqlBuilder.build();
            count = template.queryForObject(sql.toString(), namedParameters, Integer.class);
        final StringBuilder sql = sqlBuilder.build();
                            sql.toString(),
        final StringBuilder sql = sqlBuilder.build();
                        sql.toString(),
    public static Dialect detect(DataSource dataSource) {
    public FilterToCatalogSQL(Class<?> queryType, DbMappings dbMappings) {
        append(extraData, "0 = 1 /* EXCLUDE */\n");
        return extraData;
        append(extraData, "1 = 1 /* INCLUDE */\n");
        return extraData;
            StringBuilder builder;

                    builder =
                            append(
                                    extraData,
                                    "oid NOT IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
                                    "AND o1.property_type IN (:",
                                    propertyTypesParam1,
                                    ") ",
                                    "AND o2.property_type IN (:",
                                    propertyTypesParam2,
                                    ") ",
                                    "AND ",
                                    valueCol1,
                                    " != ",
                                    valueCol2,
                                    ") /* ",
                                    filter.toString(),
                                    " */\n");
                    builder =
                            append(
                                    extraData,
                                    "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
                                    "AND o1.property_type IN (:",
                                    propertyTypesParam1,
                                    ") ",
                                    "AND o2.property_type IN (:",
                                    propertyTypesParam2,
                                    ") ",
                                    "AND ",
                                    valueCol1,
                                    " = ",
                                    valueCol2,
                                    ") /* ",
                                    filter.toString(),
                                    " */\n");
                    builder =
                            append(
                                    extraData,
                                    "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
                                    "AND o1.property_type IN (:",
                                    propertyTypesParam1,
                                    ") ",
                                    "AND o2.property_type IN (:",
                                    propertyTypesParam2,
                                    ") ",
                                    "AND ",
                                    valueCol1,
                                    " = ",
                                    valueCol2,
                                    " GROUP BY (oid) HAVING COUNT(oid) = 1) /* ",
                                    filter.toString(),
                                    "/* ",
                                    filter.toString(),
                                    " */\n");

            return builder;

                StringBuilder builder =
                        append(extraData, handleInstanceOf((IsInstanceOf) filter.getExpression1()));
                return builder;

            StringBuilder builder;
                    builder =
                            append(
                                    extraData,
                                    "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
                                    propertyTypesParam,
                                    ") AND ",
                                    valueCol,
                                    " != :",
                                    valueParam,
                                    ") /* ",
                                    filter.toString(),
                                    " */\n");
                    builder =
                            append(
                                    extraData,
                                    "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                                    propertyTypesParam,
                                    ") AND ",
                                    valueCol,
                                    " = :",
                                    valueParam,
                                    ") /* ",
                                    filter.toString(),
                                    " */\n");
                    builder =
                            append(
                                    extraData,
                                    "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                                    propertyTypesParam,
                                    ") AND ",
                                    valueCol,
                                    " = :",
                                    valueParam,
                                    " GROUP BY (oid) HAVING COUNT(oid) = 1) /* ",
                                    filter.toString(),
                                    " */\n");

            return builder;
    private String handleInstanceOf(IsInstanceOf instanceOf) {

        if (clazz == null || dbMappings.getTypeId(clazz) == null) {
            return "0 = 1 /* EXCLUDE */\n";
        }


        return "type_id = " + typeId + " /* isInstanceOf " + clazz.getName() + " */\n";
        // TODO: check for indexed property name
                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal);
        StringBuilder builder;

                builder =
                        append(
                                extraData,
                                "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
                                propertyTypesParam,
                                ") AND ",
                                valueCol,
                                " NOT LIKE '",
                                pattern,
                                "') /* ",
                                filter.toString(),
                                " */\n");
                builder =
                        append(
                                extraData,
                                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                                propertyTypesParam,
                                ") AND ",
                                valueCol,
                                " LIKE '",
                                pattern,
                                "') /* ",
                                filter.toString(),
                                " */\n");
                builder =
                        append(
                                extraData,
                                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                                propertyTypesParam,
                                ") AND ",
                                valueCol,
                                " LIKE '",
                                pattern,
                                "' ",
                                "GROUP BY (oid) HAVING COUNT(oid) = 1) /* ",
                                filter.toString(),
                                " */\n");

        return builder;
        StringBuilder sql = (StringBuilder) extraData;

        List<Filter> children = filter.getChildren();
        checkArgument(children.size() > 0);
        sql.append("(\n    ");

        for (Iterator<Filter> it = children.iterator(); it.hasNext(); ) {
            Filter child = it.next();
            sql = (StringBuilder) child.accept(this, sql);
            if (it.hasNext()) {
                sql = append(extraData, "    AND\n    ");
            }
        }
        sql.append(")");
        return sql;
        StringBuilder sql = (StringBuilder) extraData;
        sql.append("(");
            Filter child = it.next();
            sql = (StringBuilder) child.accept(this, sql);
                sql = append(extraData, "    OR\n    ");
        sql.append(")");
        return sql;
        filter.getFilter().accept(this, append(extraData, "NOT ("));
        return append(extraData, ")");
        StringBuilder builder =
                append(
                        extraData,
                        "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                        propertyTypesParam,
                        ") AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                                + propertyTypesParam
                                + "))) /* ",
                        filter.toString(),
                        " */\n");
        return builder;
        StringBuilder builder =
                append(
                        extraData,
                        "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                        propertyTypesParam,
                        ") AND value IS NULL) /* ",
                        filter.toString(),
                        " */\n");
        return builder;
            sql.insert(0, "SELECT * FROM (SELECT query.*, rownum rnum FROM (\n");
            sql.append(") query\n");
            sql.append("WHERE rownum <= ").append(limit).append(")\n");
    private void querySortBy(StringBuilder query, StringBuilder whereClause, SortBy[] orders) {
        query.append("SELECT id FROM ");

        query.append("\n    (SELECT oid, id FROM object WHERE ");
        if (queryType != null) {
            query.append("type_id IN (:types) /* ")
                    .append(queryType.getCanonicalName())
                    .append(" */\n      AND ");
        query.append(whereClause).append(") object");
            query.append("\n  LEFT JOIN");
            query.append("\n    (SELECT oid, value ")
                    .append(attributeName)
                    .append(" FROM \n      object_property WHERE property_type IN (:")

            query.append(" /* ")
                    .append(order.getPropertyName().getPropertyName())
                    .append(" ")
                    .append(ascDesc(order))
                    .append(" */");

            query.append("\n  ON object.oid = ").append(subSelectName).append(".oid");

        query.append("\n  ").append(orderBy);
    private StringBuilder buildWhereClause() {

        this.predicateBuilder = new FilterToCatalogSQL(this.queryType, this.dbMappings);

        return (StringBuilder) this.supportedFilter.accept(predicateBuilder, whereClause);
    public StringBuilder build() {
        StringBuilder whereClause = buildWhereClause();
            if (Filter.INCLUDE.equals(this.originalFilter)) {
                query.append("SELECT COUNT(oid) FROM object WHERE type_id IN (:types)");
            } else {
                query.append("SELECT COUNT(oid) FROM object WHERE type_id IN (:types) AND (\n");
                query.append(whereClause).append(")");
            SortBy[] orders = this.sortOrder;
            if (orders == null) {
                query.append("SELECT id FROM object WHERE type_id IN (:types) AND (\n");
                query.append(whereClause).append(")");
                query.append(" ORDER BY oid");
                querySortBy(query, whereClause, orders);
        return query;
                    dataSource(), new XStreamInfoSerialBinding(new XStreamPersisterFactory()));
import static org.hamcrest.CoreMatchers.containsString;
import static org.hamcrest.MatcherAssert.assertThat;
        dbMappings = new DbMappings(dialect);
    public void testQueryAll() {
        Filter filter = Predicates.equal("name", "ws1");
        StringBuilder build =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
                        .filter(filter)
                        .build();
    public void testSort1() {
        Filter filter = Predicates.acceptAll();
        StringBuilder build =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
                        .filter(filter)
                        .sortOrder(Predicates.asc("foo"))
                        .build();
    public void testSort2() {
        Filter filter = Predicates.acceptAll();
        StringBuilder build =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
                        .filter(filter)
                        .sortOrder(Predicates.asc("foo"), Predicates.desc("bar"))
                        .build();
    public void testSort3() {
        Filter filter = Predicates.acceptAll();
        StringBuilder build =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
                        .filter(filter)
                        .sortOrder(
                                Predicates.asc("foo"),
                                Predicates.desc("bar"),
                                Predicates.asc("baz"))
                        .build();
    public void testSort3WithFilter() {
        Filter filter = Predicates.equal("name", "quux");
        StringBuilder build =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
                        .filter(filter)
                        .sortOrder(
                                Predicates.asc("foo"),
                                Predicates.desc("bar"),
                                Predicates.asc("baz"))
                        .build();
    public void testNotEquals() {
        // Create the filter
        Filter filter = Predicates.notEqual("name", "quux");
        // Build it
        StringBuilder build =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
                        .filter(filter)
                        .build();
        String sql = build.toString();
        // Ensure the following sql is present
        assertThat(
                sql,
                containsString(
                        "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:ptype0) AND UPPER(value) = :value0)"));
    public void testIsInstanceOf() {
        // Create the filter
        Filter filter = Predicates.isInstanceOf(LayerInfo.class);
        // Build it
        StringBuilder build =
                        .filter(filter)
        String sql = build.toString();
        // Ensure the following sql is present
        assertThat(sql, containsString("type_id = " + dbMappings.getTypeId(LayerInfo.class)));
    public void testIsNull() {
        // Create the filter
        Filter filter = Predicates.isNull("name");
        // Build it
        StringBuilder build =
                        .filter(filter)
        String sql = build.toString();
        String sqlNull =
                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0))";
        // Ensure the following sql is present
        assertThat(sql, containsString(sqlNull));
    public void testIsNil() {
        // Create the filter
        Filter filter = Predicates.isNull("name");
        // Build it
        StringBuilder build =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
                        .filter(filter)
                        .build();
        String sql = build.toString();
        String sqlNil =
                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL)";
        // Ensure the following sql is present
        assertThat(sql, containsString(sqlNil));

Fixed code:
import org.geoserver.jdbcloader.JDBCLoaderProperties;
    private JDBCLoaderProperties properties;

    public ConfigDatabase(
            JDBCLoaderProperties properties,
            DataSource dataSource,
            XStreamInfoSerialBinding binding) {
        this(properties, dataSource, binding, null);
            JDBCLoaderProperties properties,
        this.properties = properties;
            this.dialect = Dialect.detect(dataSource, properties.isDebugMode());
        final String sql = sqlBuilder.build();
            count = template.queryForObject(sql, namedParameters, Integer.class);
        final String sql = sqlBuilder.build();
                            sql,
        final String sql = sqlBuilder.build();
                        sql,
import java.util.regex.Pattern;
    // see https://github.com/hibernate/hibernate-orm/commit/59fede7acaaa1579b561407aefa582311f7ebe78
    private static final Pattern ESCAPE_CLOSING_COMMENT_PATTERN = Pattern.compile("\\*/");
    private static final Pattern ESCAPE_OPENING_COMMENT_PATTERN = Pattern.compile("/\\*");

    private boolean debugMode;

    public static Dialect detect(DataSource dataSource, boolean debugMode) {
        dialect.setDebugMode(debugMode);
    public boolean isDebugMode() {
        return debugMode;
    }

    public void setDebugMode(boolean debugMode) {
        this.debugMode = debugMode;
    }

    /** Escapes the contents of the SQL comment to prevent SQL injection. */
    public String escapeComment(String comment) {
        String escaped = ESCAPE_CLOSING_COMMENT_PATTERN.matcher(comment).replaceAll("*\\\\/");
        return ESCAPE_OPENING_COMMENT_PATTERN.matcher(escaped).replaceAll("/\\\\*");
    }

    /** Appends the objects to the SQL in a comment if debug mode is enabled. */
    public StringBuilder appendComment(StringBuilder sql, Object... objects) {
        if (!debugMode) {
            return sql;
        }
        sql.append(" /* ");
        for (Object object : objects) {
            sql.append(escapeComment(String.valueOf(object)));
        }
        return sql.append(" */\n");
    }

    /** Appends the objects to the SQL in an comment if debug mode is enabled. */
    public StringBuilder appendComment(Object sql, Object... objects) {
        return appendComment((StringBuilder) sql, objects);
    }

    /** Appends one of the strings to the SQL depending on whether debug mode is enabled. */
    public StringBuilder appendIfDebug(StringBuilder sql, String ifEnabled, String ifDisabled) {
        return sql.append(debugMode ? ifEnabled : ifDisabled);
    }

import org.opengis.filter.BinaryLogicOperator;
    private final Dialect dialect;

    public FilterToCatalogSQL(Dialect dialect, Class<?> queryType, DbMappings dbMappings) {
        this.dialect = dialect;
        return dialect.appendComment(append(extraData, "0 = 1"), "EXCLUDE");
        return dialect.appendComment(append(extraData, "1 = 1"), "INCLUDE");
                    append(
                            extraData,
                            "oid NOT IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
                            "AND o1.property_type IN (:",
                            propertyTypesParam1,
                            ") AND o2.property_type IN (:",
                            propertyTypesParam2,
                            ") AND ",
                            valueCol1,
                            " != ",
                            valueCol2,
                            ")");
                    append(
                            extraData,
                            "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
                            "AND o1.property_type IN (:",
                            propertyTypesParam1,
                            ") AND o2.property_type IN (:",
                            propertyTypesParam2,
                            ") AND ",
                            valueCol1,
                            " = ",
                            valueCol2,
                            ")");
                    append(
                            extraData,
                            "oid IN (SELECT o1.oid FROM object_property o1, object_property o2 WHERE o1.oid = o2.oid ",
                            "AND o1.property_type IN (:",
                            propertyTypesParam1,
                            ") AND o2.property_type IN (:",
                            propertyTypesParam2,
                            ") AND ",
                            valueCol1,
                            " = ",
                            valueCol2,
                            " GROUP BY (oid) HAVING COUNT(oid) = 1)");
                return handleInstanceOf((IsInstanceOf) filter.getExpression1(), extraData);
                    append(
                            extraData,
                            "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
                            propertyTypesParam,
                            ") AND ",
                            valueCol,
                            " != :",
                            valueParam,
                            ")");
                    append(
                            extraData,
                            "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                            propertyTypesParam,
                            ") AND ",
                            valueCol,
                            " = :",
                            valueParam,
                            ")");
                    append(
                            extraData,
                            "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                            propertyTypesParam,
                            ") AND ",
                            valueCol,
                            " = :",
                            valueParam,
                            " GROUP BY (oid) HAVING COUNT(oid) = 1)");
        return dialect.appendComment(extraData, filter);
    private Object handleInstanceOf(IsInstanceOf instanceOf, Object extraData) {
        if (typeId == null) {
            return visit(Filter.EXCLUDE, extraData);
        }
        append(extraData, "type_id = ", typeId.toString());
        return dialect.appendComment(extraData, "isInstanceOf ", clazz.getName());
                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal, false);
        String valueParam = newParam("value", pattern);
                append(
                        extraData,
                        "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
                        propertyTypesParam,
                        ") AND ",
                        valueCol,
                        " NOT LIKE :",
                        valueParam,
                        ")");
                append(
                        extraData,
                        "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                        propertyTypesParam,
                        ") AND ",
                        valueCol,
                        " LIKE :",
                        valueParam,
                        ")");
                append(
                        extraData,
                        "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                        propertyTypesParam,
                        ") AND ",
                        valueCol,
                        " LIKE :",
                        valueParam,
                        " GROUP BY (oid) HAVING COUNT(oid) = 1)");
        return dialect.appendComment(extraData, filter);
        return visit(filter, "AND", extraData);
        return visit(filter, "OR", extraData);
    }
    protected Object visit(BinaryLogicOperator filter, String type, Object extraData) {
        StringBuilder sql = (StringBuilder) extraData;
        sql.append('(');
        dialect.appendIfDebug(sql, "\n    ", "");
            it.next().accept(this, sql);
                dialect.appendIfDebug(sql, "    ", " ");
                sql.append(type);
                dialect.appendIfDebug(sql, "\n    ", " ");
        return sql.append(')');
        Filter child = filter.getFilter();
        // these filter types are already enclosed in parentheses
        boolean extraParens =
                !(child instanceof And || child instanceof Or || child instanceof PropertyIsNull);
        append(extraData, "NOT ", extraParens ? "(" : "");
        child.accept(this, extraData);
        return append(extraData, extraParens ? ")" : "");
        append(
                extraData,
                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                propertyTypesParam,
                ") AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
                propertyTypesParam,
                ")))");
        return dialect.appendComment(extraData, filter);
        append(
                extraData,
                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
                propertyTypesParam,
                ") AND value IS NULL)");
        return dialect.appendComment(extraData, filter);
            sql.insert(
                    0,
                    "SELECT * FROM (SELECT query.*, rownum rnum FROM ("
                            + (isDebugMode() ? "\n" : ""));
            sql.append(") query");
            appendIfDebug(sql, "\n", " ");
            sql.append("WHERE rownum <= ").append(limit).append(")");
            appendIfDebug(sql, "\n", " ");
    private void querySortBy(StringBuilder query, String whereClause, SortBy[] orders) {
        query.append("SELECT id FROM");
        dialect.appendIfDebug(query, "\n    ", " ");
        query.append("(SELECT oid, id FROM object WHERE type_id IN (:types)");
        dialect.appendComment(query, queryType.getName());
        if (whereClause != null) {
            dialect.appendIfDebug(query, "      ", " ");
            query.append("AND ").append(whereClause);
        query.append(") object");
            dialect.appendIfDebug(query, "\n  ", " ");
            query.append("LEFT JOIN");
            dialect.appendIfDebug(query, "\n    ", " ");
            query.append("(SELECT oid, value ").append(attributeName).append(" FROM");
            dialect.appendIfDebug(query, "\n      ", " ");
            query.append("object_property WHERE property_type IN (:")
            dialect.appendComment(
                    query, order.getPropertyName().getPropertyName(), " ", ascDesc(order));
            dialect.appendIfDebug(query, "  ", " ");
            query.append("ON object.oid = ").append(subSelectName).append(".oid");
        dialect.appendIfDebug(query, "\n  ", " ");
        query.append(orderBy);
    private String buildWhereClause() {
        this.predicateBuilder =
                new FilterToCatalogSQL(this.dialect, this.queryType, this.dbMappings);
        if (Filter.INCLUDE.equals(this.originalFilter)) {
            return null;
        }
        if (Filter.INCLUDE.equals(this.supportedFilter)) {
            return null;
        }
        return this.supportedFilter.accept(predicateBuilder, whereClause).toString();
    public String build() {
        String whereClause = buildWhereClause();
            query.append("SELECT COUNT(oid) FROM object WHERE type_id IN (:types)");
            dialect.appendComment(query, queryType.getName());
            if (whereClause != null) {
                dialect.appendIfDebug(query, "", " ");
                query.append("AND ").append(whereClause);
            if (sortOrder != null) {
                querySortBy(query, whereClause, sortOrder);
                query.append("SELECT id FROM object WHERE type_id IN (:types)");
                dialect.appendComment(query, queryType.getName());
                dialect.appendIfDebug(query, "", " ");
                if (whereClause != null) {
                    query.append("AND ").append(whereClause);
                    dialect.appendIfDebug(query, whereClause.endsWith("\n") ? "" : " ", " ");
                }
                query.append("ORDER BY oid");
        return query.toString().trim();

    public boolean isDebugMode() {
        return Boolean.parseBoolean(getProperty("debugMode", "false"));
    }

    public void setDebugMode(boolean debugMode) {
        setProperty("debugMode", String.valueOf(debugMode));
    }
    <constructor-arg ref="jdbcConfigProperties" />
# Flag controlling whether to add comments and some pretty-printing to SQL queries to
# assist with debugging.  Setting this to false may allow the module to better take
# advantage of prepared statement caching.
debugMode=false

# Flag controlling whether to add comments and some pretty-printing to SQL queries to
# assist with debugging.  Setting this to false may allow the module to better take
# advantage of prepared statement caching.
debugMode=false

# Flag controlling whether to add comments and some pretty-printing to SQL queries to
# assist with debugging.  Setting this to false may allow the module to better take
# advantage of prepared statement caching.
debugMode=false

import org.geoserver.jdbcloader.JDBCLoaderProperties;
                    new JDBCLoaderProperties(null),
                    dataSource(),
                    new XStreamInfoSerialBinding(new XStreamPersisterFactory()));
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotEquals;
import static org.junit.Assert.assertTrue;
import org.opengis.filter.FilterFactory;
import org.opengis.filter.sort.SortBy;
    private static final FilterFactory FACTORY = Predicates.factory;

    public void testForIdsSort1DebugDisabled() {
        String expected =
                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types)) object "
                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
                        + "ORDER BY prop0 ASC";
        verifyForIds(expected, false, Predicates.acceptAll(), Predicates.asc("foo"));
    public void testForIdsSort1DebugEnabled() {
        String expected =
                "SELECT id FROM"
                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\n) object"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop0 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
                        + "\n  ON object.oid = subSelect0.oid"
                        + "\n  ORDER BY prop0 ASC";
        verifyForIds(expected, true, Predicates.acceptAll(), Predicates.asc("foo"));
    public void testForIdsSort2DebugDisabled() {
        String expected =
                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types)) object "
                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
                        + "LEFT JOIN (SELECT oid, value prop1 FROM object_property "
                        + "WHERE property_type IN (:sortProperty1)) subSelect1 ON object.oid = subSelect1.oid "
                        + "ORDER BY prop0 ASC, prop1 DESC";
        verifyForIds(
                expected,
                false,
                Predicates.acceptAll(),
                Predicates.asc("foo"),
                Predicates.desc("bar"));
    public void testForIdsSort2DebugEnabled() {
        String expected =
                "SELECT id FROM"
                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\n) object"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop0 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
                        + "\n  ON object.oid = subSelect0.oid"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop1 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty1)) subSelect1 /* bar DESC */"
                        + "\n  ON object.oid = subSelect1.oid"
                        + "\n  ORDER BY prop0 ASC, prop1 DESC";
        verifyForIds(
                expected,
                true,
                Predicates.acceptAll(),
                Predicates.asc("foo"),
                Predicates.desc("bar"));
    public void testForIdsSort3DebugDisabled() {
        String expected =
                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types)) object "
                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
                        + "LEFT JOIN (SELECT oid, value prop1 FROM object_property "
                        + "WHERE property_type IN (:sortProperty1)) subSelect1 ON object.oid = subSelect1.oid "
                        + "LEFT JOIN (SELECT oid, value prop2 FROM object_property "
                        + "WHERE property_type IN (:sortProperty2)) subSelect2 ON object.oid = subSelect2.oid "
                        + "ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
        verifyForIds(
                expected,
                false,
                Predicates.acceptAll(),
                Predicates.asc("foo"),
                Predicates.desc("bar"),
                Predicates.asc("baz"));
    public void testForIdsSort3DebugEnabled() {
        String expected =
                "SELECT id FROM"
                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\n) object"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop0 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
                        + "\n  ON object.oid = subSelect0.oid"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop1 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty1)) subSelect1 /* bar DESC */"
                        + "\n  ON object.oid = subSelect1.oid"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop2 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty2)) subSelect2 /* baz ASC */"
                        + "\n  ON object.oid = subSelect2.oid"
                        + "\n  ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
        verifyForIds(
                expected,
                true,
                Predicates.acceptAll(),
                Predicates.asc("foo"),
                Predicates.desc("bar"),
                Predicates.asc("baz"));
    }

    @Test
    public void testForIdsSort3WithFilterDebugDisabled() {
        String expected =
                "SELECT id FROM (SELECT oid, id FROM object WHERE type_id IN (:types) AND oid IN "
                        + "(SELECT oid FROM object_property WHERE property_type IN (:ptype0) AND value = :value0)) object "
                        + "LEFT JOIN (SELECT oid, value prop0 FROM object_property "
                        + "WHERE property_type IN (:sortProperty0)) subSelect0 ON object.oid = subSelect0.oid "
                        + "LEFT JOIN (SELECT oid, value prop1 FROM object_property "
                        + "WHERE property_type IN (:sortProperty1)) subSelect1 ON object.oid = subSelect1.oid "
                        + "LEFT JOIN (SELECT oid, value prop2 FROM object_property "
                        + "WHERE property_type IN (:sortProperty2)) subSelect2 ON object.oid = subSelect2.oid "
                        + "ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
        verifyForIds(
                expected,
                false,
                Predicates.equal("name", "quux"),
                Predicates.asc("foo"),
                Predicates.desc("bar"),
                Predicates.asc("baz"));
    }

    @Test
    public void testForIdsSort3WithFilterDebugEnabled() {
        String expected =
                "SELECT id FROM"
                        + "\n    (SELECT oid, id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\n      AND oid IN (SELECT oid FROM object_property WHERE property_type IN (:ptype0) AND value = :value0) /* [ name = quux ] */"
                        + "\n) object"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop0 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty0)) subSelect0 /* foo ASC */"
                        + "\n  ON object.oid = subSelect0.oid"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop1 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty1)) subSelect1 /* bar DESC */"
                        + "\n  ON object.oid = subSelect1.oid"
                        + "\n  LEFT JOIN"
                        + "\n    (SELECT oid, value prop2 FROM"
                        + "\n      object_property WHERE property_type IN (:sortProperty2)) subSelect2 /* baz ASC */"
                        + "\n  ON object.oid = subSelect2.oid"
                        + "\n  ORDER BY prop0 ASC, prop1 DESC, prop2 ASC";
        verifyForIds(
                expected,
                true,
                Predicates.equal("name", "quux"),
                Predicates.asc("foo"),
                Predicates.desc("bar"),
                Predicates.asc("baz"));
    }

    @Test
    public void testForCountUnknownProperty() {
        dialect.setDebugMode(false);
        String expected = "SELECT COUNT(oid) FROM object WHERE type_id IN (:types)";
        Filter filter = Predicates.equal("foo.bar.baz", "quux");
        QueryBuilder<?> builder =
                QueryBuilder.forCount(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
        String actual = builder.build();
        assertEquals(expected, actual);
        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
        assertEquals(filter, builder.getUnsupportedFilter());
        assertFalse(builder.isOffsetLimitApplied());
        assertEquals(1, builder.getNamedParameters().size());
    }

    @Test
    public void testForIdsUnknownProperty() {
        dialect.setDebugMode(false);
        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid";
        Filter filter = Predicates.equal("foo.bar.baz", "quux");
        QueryBuilder<?> builder =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
        String actual = builder.build();
        assertEquals(expected, actual);
        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
        assertEquals(filter, builder.getUnsupportedFilter());
        assertFalse(builder.isOffsetLimitApplied());
        assertEquals(1, builder.getNamedParameters().size());
    }

    @Test
    public void testForCountSimplifiedInclude() {
        dialect.setDebugMode(false);
        String expected = "SELECT COUNT(oid) FROM object WHERE type_id IN (:types)";
        Filter filter = Predicates.and(Predicates.acceptAll(), Predicates.acceptAll());
        QueryBuilder<?> builder =
                QueryBuilder.forCount(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
        String actual = builder.build();
        assertEquals(expected, actual);
        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
        assertEquals(Filter.INCLUDE, builder.getUnsupportedFilter());
        assertFalse(builder.isOffsetLimitApplied());
        assertEquals(1, builder.getNamedParameters().size());
    }

    @Test
    public void testForIdsSimplifiedInclude() {
        dialect.setDebugMode(false);
        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid";
        Filter filter = Predicates.and(Predicates.acceptAll(), Predicates.acceptAll());
        QueryBuilder<?> builder =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).filter(filter);
        String actual = builder.build();
        assertEquals(expected, actual);
        assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
        assertEquals(Filter.INCLUDE, builder.getUnsupportedFilter());
        assertTrue(builder.isOffsetLimitApplied());
        assertEquals(1, builder.getNamedParameters().size());
    public void testForIdsIncludeWithOffSetDebugDisabled() {
        dialect.setDebugMode(false);
        String expected =
                "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid LIMIT 2147483647 OFFSET 5";
        String actual =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).offset(5).build();
        assertEquals(expected, actual);
    }

    @Test
    public void testForIdsIncludeWithLimitDebugDisabled() {
        dialect.setDebugMode(false);
        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid LIMIT 10";
        String actual =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).limit(10).build();
        assertEquals(expected, actual);
    }

    @Test
    public void testForIdsIncludeWithOffSetAndLimitDebugDisabled() {
        dialect.setDebugMode(false);
        String expected =
                "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid LIMIT 10 OFFSET 5";
        String actual =
                        .offset(5)
                        .limit(10)
        assertEquals(expected, actual);
    }

    @Test
    public void testForIdsIncludeWithOffSetDebugEnabled() {
        dialect.setDebugMode(true);
        String expected =
                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\nORDER BY oid LIMIT 2147483647 OFFSET 5";
        String actual =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).offset(5).build();
        assertEquals(expected, actual);
    }

    @Test
    public void testForIdsIncludeWithLimitDebugEnabled() {
        dialect.setDebugMode(true);
        String expected =
                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\nORDER BY oid LIMIT 10";
        String actual =
                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings).limit(10).build();
        assertEquals(expected, actual);
    public void testForIdsIncludeWithOffSetAndLimitDebugEnabled() {
        dialect.setDebugMode(true);
        String expected =
                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\nORDER BY oid LIMIT 10 OFFSET 5";
        String actual =
                        .offset(5)
                        .limit(10)
        assertEquals(expected, actual);
    }

    @Test
    public void testForCountIncludeDebugDisabled() {
        String expected = "SELECT COUNT(oid) FROM object WHERE type_id IN (:types)";
        verifyForCount(expected, false, Predicates.acceptAll());
    }

    @Test
    public void testForCountIncludeDebugEnabled() {
        String expected =
                "SELECT COUNT(oid) FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */";
        verifyForCount(expected, true, Predicates.acceptAll());
    }

    @Test
    public void testForIdsIncludeDebugDisabled() {
        String expected = "SELECT id FROM object WHERE type_id IN (:types) ORDER BY oid";
        verifyForIds(expected, false, Predicates.acceptAll());
    }

    @Test
    public void testForIdsIncludeDebugEnabled() {
        String expected =
                "SELECT id FROM object WHERE type_id IN (:types) /* org.geoserver.catalog.WorkspaceInfo */"
                        + "\nORDER BY oid";
        verifyForIds(expected, true, Predicates.acceptAll());
    }

    @Test
    public void testForCountAndIsInstanceofDebugDisabled() {
        String expected = "(type_id = 14 AND 0 = 1)";
        Filter filter =
                Predicates.and(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountAndIsInstanceofDebugEnabled() {
        String expected =
                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
                        + "\n    AND\n    0 = 1 /* EXCLUDE */\n)";
        Filter filter =
                Predicates.and(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForCount(expected, true, filter);
    }
    @Test
    public void testForIdsAndIsInstanceofDebugDisabled() {
        String expected = "(type_id = 14 AND 0 = 1) ";
        Filter filter =
                Predicates.and(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsAndIsInstanceofDebugEnabled() {
        String expected =
                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
                        + "\n    AND\n    0 = 1 /* EXCLUDE */\n) ";
        Filter filter =
                Predicates.and(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountOrIsInstanceofDebugDisabled() {
        String expected = "(type_id = 14 OR 0 = 1)";
        Filter filter =
                Predicates.or(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountOrIsInstanceofDebugEnabled() {
        String expected =
                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
                        + "\n    OR\n    0 = 1 /* EXCLUDE */\n)";
        Filter filter =
                Predicates.or(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForIdsOrIsInstanceofDebugDisabled() {
        String expected = "(type_id = 14 OR 0 = 1) ";
        Filter filter =
                Predicates.or(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsOrIsInstanceofDebugEnabled() {
        String expected =
                "(\n    type_id = 14 /* isInstanceOf org.geoserver.catalog.LayerInfo */"
                        + "\n    OR\n    0 = 1 /* EXCLUDE */\n) ";
        Filter filter =
                Predicates.or(
                        Predicates.isInstanceOf(LayerInfo.class),
                        Predicates.isInstanceOf(String.class));
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountIsEqualToSensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0)";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsEqualToSensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsEqualToSensitiveDebugEnabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForIdsIsEqualToSensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) ";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsEqualToSensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */\n";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsEqualToSensitiveDebugEnabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */\n";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountIsEqualToInsensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0)";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsEqualToInsensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsEqualToInsensitiveDebugEnabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForIdsIsEqualToInsensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) ";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsEqualToInsensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */\n";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsEqualToInsensitiveDebugEnabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */\n";
        Filter filter = FACTORY.equal(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountIsNotEqualToSensitiveDebugDisabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0))";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsNotEqualToSensitiveDebugEnabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */\n)";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsNotEqualToSensitiveDebugEnabledEscaping() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */\n)";
        Filter filter =
                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForIdsIsNotEqualToSensitiveDebugDisabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0)) ";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsNotEqualToSensitiveDebugEnabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = quux ] */\n) ";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), true);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsNotEqualToSensitiveDebugEnabledEscaping() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value = :value0) /* [ name = FOO*\\/BAR ] */\n) ";
        Filter filter =
                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), true);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountIsNotEqualToInsensitiveDebugDisabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0))";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsNotEqualToInsensitiveDebugEnabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */\n)";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsNotEqualToInsensitiveDebugEnabledEscaping() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */\n)";
        Filter filter =
                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForIdsIsNotEqualToInsensitiveDebugDisabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0)) ";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsNotEqualToInsensitiveDebugEnabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = quux ] */\n) ";
        Filter filter = FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("quux"), false);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsNotEqualToInsensitiveDebugEnabledEscaping() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) = :value0) /* [ name = FOO*\\/BAR ] */\n) ";
        Filter filter =
                FACTORY.notEqual(FACTORY.property("name"), FACTORY.literal("FOO*/BAR"), false);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountIsLikeSensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0)";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsLikeSensitiveDebugDisabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0)";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsLikeSensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %quux% ] */";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsLikeSensitiveDebugEnabledEscaping1() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %\\'FOO% ] */";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsLikeSensitiveDebugEnabledEscaping2() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", true);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForIdsIsLikeSensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) ";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsLikeSensitiveDebugDisabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) ";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsLikeSensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %quux% ] */\n";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", true);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsLikeSensitiveDebugEnabledEscaping1() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %\\'FOO% ] */\n";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", true);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsLikeSensitiveDebugEnabledEscaping2() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND value LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */\n";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", true);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountIsLikeInsensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0)";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsLikeInsensitiveDebugDisabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0)";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
        verifyForCount(expected, false, filter);
    }

    @Test
    public void testForCountIsLikeInsensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %quux% ] */";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsLikeInsensitiveDebugEnabledEscaping1() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %\\'FOO% ] */";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForCountIsLikeInsensitiveDebugEnabledEscaping2() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", false);
        verifyForCount(expected, true, filter);
    }

    @Test
    public void testForIdsIsLikeInsensitiveDebugDisabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) ";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsLikeInsensitiveDebugDisabledEscaping() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) ";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
        verifyForIds(expected, false, filter);
    }

    @Test
    public void testForIdsIsLikeInsensitiveDebugEnabled() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %quux% ] */\n";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%quux%", "%", "_", "\\", false);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsLikeInsensitiveDebugEnabledEscaping1() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %\\'FOO% ] */\n";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%\\'FOO%", "%", "_", "\\", false);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForIdsIsLikeInsensitiveDebugEnabledEscaping2() {
        String expected =
                "oid IN (SELECT oid FROM object_property WHERE property_type "
                        + "IN (:ptype0) AND UPPER(value) LIKE :value0) /* [ name is like %FOO*\\/BAR% ] */\n";
        Filter filter = FACTORY.like(FACTORY.property("name"), "%FOO*/BAR%", "%", "_", "\\", false);
        verifyForIds(expected, true, filter);
    }

    @Test
    public void testForCountIsNullDebugDisabled() {
        String expected =
                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0)))";
        verifyForCount(expected, false, Predicates.isNull("name"));
    public void testForCountIsNullDebugEnabled() {
        String expected =
                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0))) /* [ name IS NULL ] */";
        verifyForCount(expected, true, Predicates.isNull("name"));
    }

    @Test
    public void testForIdsIsNullDebugDisabled() {
        String expected =
                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0))) ";
        verifyForIds(expected, false, Predicates.isNull("name"));
    }

    @Test
    public void testForIdsIsNullDebugEnabled() {
        String expected =
                "(oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0))) /* [ name IS NULL ] */\n";
        verifyForIds(expected, true, Predicates.isNull("name"));
    }

    @Test
    public void testForCountIsNotNullDebugDisabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0)))";
        verifyForCount(expected, false, Predicates.not(Predicates.isNull("name")));
    }

    @Test
    public void testForCountIsNotNullDebugEnabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0))) /* [ name IS NULL ] */";
        verifyForCount(expected, true, Predicates.not(Predicates.isNull("name")));
    }

    @Test
    public void testForIdsIsNotNullDebugDisabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0))) ";
        verifyForIds(expected, false, Predicates.not(Predicates.isNull("name")));
    }

    @Test
    public void testForIdsIsNotNullDebugEnabled() {
        String expected =
                "NOT (oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
                        + "ptype0))) /* [ name IS NULL ] */\n";
        verifyForIds(expected, true, Predicates.not(Predicates.isNull("name")));
    }

    private void verifyForCount(String expectedSQL, boolean debugMode, Filter filter) {
        String expected = expectedSQL;
        if (!expected.startsWith("SELECT")) {
            expected =
                    "SELECT COUNT(oid) FROM object WHERE type_id IN (:types) "
                            + (debugMode ? "/* org.geoserver.catalog.WorkspaceInfo */\n" : "")
                            + "AND "
                            + expectedSQL;
        }
        QueryBuilder<?> builder = QueryBuilder.forCount(dialect, WorkspaceInfo.class, dbMappings);
        verifyQuery(builder, expected, debugMode, filter);
    }

    private void verifyForIds(
            String expectedSQL, boolean debugMode, Filter filter, SortBy... order) {
        String expected = expectedSQL;
        if (!expected.startsWith("SELECT")) {
            expected =
                    "SELECT id FROM object WHERE type_id IN (:types) "
                            + (debugMode ? "/* org.geoserver.catalog.WorkspaceInfo */\n" : "")
                            + "AND "
                            + expectedSQL
                            + "ORDER BY oid";
        }
        QueryBuilder<?> builder = QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings);
        verifyQuery(builder, expected, debugMode, filter, order);
    }
    private void verifyQuery(
            QueryBuilder<?> builder,
            String expected,
            boolean debugMode,
            Filter filter,
            SortBy... order) {
        dialect.setDebugMode(debugMode);
        String actual = builder.filter(filter).sortOrder(order).build();
        assertEquals(expected, actual);
        if (Filter.INCLUDE.equals(filter)) {
            assertEquals(Filter.INCLUDE, builder.getSupportedFilter());
        } else {
            assertNotEquals(Filter.INCLUDE, builder.getSupportedFilter());
        }
        assertEquals(Filter.INCLUDE, builder.getUnsupportedFilter());
        assertEquals(!actual.startsWith("SELECT COUNT"), builder.isOffsetLimitApplied());
        assertFalse(builder.getNamedParameters().isEmpty());