CVE-2023-25157: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
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.
CWE description: 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.
CVSS v3 base score: 9.8
Published: 2023-02-21T22:15Z
Repository: https://github.com/geoserver/geoserver
Language: Java
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 date: 2023-02-13 09:43:43 -0800
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
Diff: diff --git a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/ConfigDatabase.java b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/ConfigDatabase.java
index 58a7fbdd07..8c38e3925b 100644
--- a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/ConfigDatabase.java
+++ b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/ConfigDatabase.java
@@ -101,10 +101,11 @@
 import org.geoserver.config.ServiceInfo;
 import org.geoserver.config.SettingsInfo;
 import org.geoserver.config.impl.CoverageAccessInfoImpl;
 import org.geoserver.config.impl.GeoServerInfoImpl;
 import org.geoserver.config.impl.JAIInfoImpl;
+import org.geoserver.jdbcloader.JDBCLoaderProperties;
 import org.geoserver.ows.util.OwsUtils;
 import org.geoserver.platform.ExtensionPriority;
 import org.geoserver.platform.resource.Resource;
 import org.geoserver.util.CacheProvider;
 import org.geoserver.util.DefaultCacheProvider;
@@ -158,10 +159,12 @@ public class ConfigDatabase implements ApplicationContextAware {
 
     private static final int LOCK_TIMEOUT_SECONDS = 60;
 
     private Dialect dialect;
 
+    private JDBCLoaderProperties properties;
+
     private DataSource dataSource;
 
     private DbMappings dbMappings;
 
     private CatalogImpl catalog;
@@ -196,19 +199,23 @@ public class ConfigDatabase implements ApplicationContextAware {
     /** Protected default constructor needed by spring-jdbc instrumentation */
     protected ConfigDatabase() {
         //
     }
 
-    public ConfigDatabase(final DataSource dataSource, final XStreamInfoSerialBinding binding) {
-        this(dataSource, binding, null);
+    public ConfigDatabase(
+            JDBCLoaderProperties properties,
+            DataSource dataSource,
+            XStreamInfoSerialBinding binding) {
+        this(properties, dataSource, binding, null);
     }
 
     public ConfigDatabase(
+            JDBCLoaderProperties properties,
             final DataSource dataSource,
             final XStreamInfoSerialBinding binding,
             CacheProvider cacheProvider) {
-
+        this.properties = properties;
         this.binding = binding;
         this.template = new NamedParameterJdbcTemplate(dataSource);
         // cannot use dataSource at this point due to spring context config hack
         // in place to support tx during testing
         this.dataSource = dataSource;
@@ -225,11 +232,11 @@ public ConfigDatabase(
         locks = new ConcurrentHashMap<>();
     }
 
     private Dialect dialect() {
         if (dialect == null) {
-            this.dialect = Dialect.detect(dataSource);
+            this.dialect = Dialect.detect(dataSource, properties.isDebugMode());
         }
         return dialect;
     }
 
     @Transactional(
@@ -294,11 +301,11 @@ public GeoServer getGeoServer() {
 
     public <T extends CatalogInfo> int count(final Class<T> of, final Filter filter) {
 
         QueryBuilder<T> sqlBuilder = QueryBuilder.forCount(dialect, of, dbMappings).filter(filter);
 
-        final StringBuilder sql = sqlBuilder.build();
+        final String sql = sqlBuilder.build();
         final Filter unsupportedFilter = sqlBuilder.getUnsupportedFilter();
         final boolean fullySupported = Filter.INCLUDE.equals(unsupportedFilter);
         if (LOGGER.isLoggable(Level.FINER)) {
             LOGGER.finer("Original filter: " + filter);
             LOGGER.finer("Supported filter: " + sqlBuilder.getSupportedFilter());
@@ -307,11 +314,11 @@ public <T extends CatalogInfo> int count(final Class<T> of, final Filter filter)
         final int count;
         if (fullySupported) {
             final Map<String, Object> namedParameters = sqlBuilder.getNamedParameters();
             logStatement(sql, namedParameters);
 
-            count = template.queryForObject(sql.toString(), namedParameters, Integer.class);
+            count = template.queryForObject(sql, namedParameters, Integer.class);
         } else {
             LOGGER.fine(
                     "Filter is not fully supported, doing scan of supported part to return the number of matches");
             // going the expensive route, filtering as much as possible
             CloseableIterator<T> iterator = query(of, filter, null, null, (SortBy) null);
@@ -361,11 +368,11 @@ public <T extends Info> CloseableIterator<T> query(
                 QueryBuilder.forIds(dialect, of, dbMappings)
                         .filter(filter)
                         .offset(offset)
                         .limit(limit)
                         .sortOrder(sortOrder);
-        final StringBuilder sql = sqlBuilder.build();
+        final String sql = sqlBuilder.build();
 
         List<String> ids = null;
 
         final SimplifyingFilterVisitor filterSimplifier = new SimplifyingFilterVisitor();
         final Filter simplifiedFilter =
@@ -404,11 +411,11 @@ public <T extends Info> CloseableIterator<T> query(
             Stopwatch sw = Stopwatch.createStarted();
             // the oracle offset/limit implementation returns a two column result set
             // with rownum in the 2nd - queryForList will throw an exception
             ids =
                     template.query(
-                            sql.toString(),
+                            sql,
                             namedParameters,
                             new RowMapper<String>() {
                                 @Override
                                 public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                                     return rs.getString(1);
@@ -466,11 +473,11 @@ public <T extends Info> CloseableIterator<String> queryIds(
         checkNotNull(of);
         checkNotNull(filter);
 
         QueryBuilder<T> sqlBuilder = QueryBuilder.forIds(dialect, of, dbMappings).filter(filter);
 
-        final StringBuilder sql = sqlBuilder.build();
+        final String sql = sqlBuilder.build();
         final Map<String, Object> namedParameters = sqlBuilder.getNamedParameters();
         final Filter unsupportedFilter = sqlBuilder.getUnsupportedFilter();
         final boolean fullySupported = Filter.INCLUDE.equals(unsupportedFilter);
 
         if (LOGGER.isLoggable(Level.FINER)) {
@@ -483,11 +490,11 @@ public <T extends Info> CloseableIterator<String> queryIds(
         Stopwatch sw = Stopwatch.createStarted();
         // the oracle offset/limit implementation returns a two column result set
         // with rownum in the 2nd - queryForList will throw an exception
         List<String> ids =
                 template.query(
-                        sql.toString(),
+                        sql,
                         namedParameters,
                         new RowMapper<String>() {
                             @Override
                             public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                                 return rs.getString(1);
diff --git a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/Dialect.java b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/Dialect.java
index d269969609..84b09e817a 100644
--- a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/Dialect.java
+++ b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/Dialect.java
@@ -6,16 +6,23 @@
 package org.geoserver.jdbcconfig.internal;
 
 import com.google.common.base.Joiner;
 import java.sql.Connection;
 import java.sql.SQLException;
+import java.util.regex.Pattern;
 import javax.annotation.Nullable;
 import javax.sql.DataSource;
 
 public class Dialect {
 
-    public static Dialect detect(DataSource dataSource) {
+    // 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 dialect;
         try {
             Connection conn = dataSource.getConnection();
             String driver = conn.getMetaData().getDriverName();
             if (driver.contains("Oracle")) {
@@ -25,13 +32,50 @@ public static Dialect detect(DataSource dataSource) {
             }
             conn.close();
         } catch (SQLException ex) {
             throw new RuntimeException(ex);
         }
+        dialect.setDebugMode(debugMode);
         return dialect;
     }
 
+    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);
+    }
+
     public void applyOffsetLimit(
             StringBuilder sql, @Nullable Integer offset, @Nullable Integer limit) {
         // some db's require limit to be present of offset is
         if (offset != null && limit == null) {
             limit = Integer.MAX_VALUE;
diff --git a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/FilterToCatalogSQL.java b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/FilterToCatalogSQL.java
index 9605932ddb..199f14f2e4 100644
--- a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/FilterToCatalogSQL.java
+++ b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/FilterToCatalogSQL.java
@@ -17,10 +17,11 @@
 import org.geoserver.catalog.Predicates;
 import org.geoserver.function.IsInstanceOf;
 import org.geotools.filter.Capabilities;
 import org.geotools.filter.LikeFilterImpl;
 import org.opengis.filter.And;
+import org.opengis.filter.BinaryLogicOperator;
 import org.opengis.filter.ExcludeFilter;
 import org.opengis.filter.Filter;
 import org.opengis.filter.FilterFactory;
 import org.opengis.filter.FilterVisitor;
 import org.opengis.filter.Id;
@@ -92,17 +93,20 @@ public class FilterToCatalogSQL implements FilterVisitor, ExpressionVisitor {
         builder.addName(IsInstanceOf.NAME.getName());
 
         CAPABILITIES = builder.getContents();
     }
 
+    private final Dialect dialect;
+
     private final Class<?> queryType;
 
     private final DbMappings dbMappings;
 
     private final Map<String, Object> namedParams = new LinkedHashMap<>();
 
-    public FilterToCatalogSQL(Class<?> queryType, DbMappings dbMappings) {
+    public FilterToCatalogSQL(Dialect dialect, Class<?> queryType, DbMappings dbMappings) {
+        this.dialect = dialect;
         this.queryType = queryType;
         this.dbMappings = dbMappings;
         List<Integer> concreteQueryTypes = dbMappings.getConcreteQueryTypes(queryType);
         namedParams.put("types", concreteQueryTypes);
     }
@@ -130,22 +134,20 @@ public Object visitNullFilter(Object extraData) {
      * @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.ExcludeFilter,
      *     java.lang.Object)
      */
     @Override
     public Object visit(ExcludeFilter filter, Object extraData) {
-        append(extraData, "0 = 1 /* EXCLUDE */\n");
-        return extraData;
+        return dialect.appendComment(append(extraData, "0 = 1"), "EXCLUDE");
     }
 
     /**
      * @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.IncludeFilter,
      *     java.lang.Object)
      */
     @Override
     public Object visit(IncludeFilter filter, Object extraData) {
-        append(extraData, "1 = 1 /* INCLUDE */\n");
-        return extraData;
+        return dialect.appendComment(append(extraData, "1 = 1"), "INCLUDE");
     }
 
     /**
      * @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.PropertyIsEqualTo,
      *     java.lang.Object)
@@ -169,85 +171,61 @@ public Object visit(PropertyIsEqualTo filter, Object extraData) {
 
             // respect matchCase
             String valueCol1 = matchingCase ? "o1.value" : "UPPER(o1.value)";
             String valueCol2 = matchingCase ? "o2.value" : "UPPER(o2.value)";
 
-            StringBuilder builder;
-
             switch (matchAction) {
                     // respect matchaction
                 case ALL: // all = another value for the property may not occur
-                    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");
+                    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,
+                            ")");
                     break;
                 case ANY: // any = the value for the property must occur at least once
-                    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");
+                    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,
+                            ")");
                     break;
                 case ONE: // one = the value for the property must occur exactly once
-                    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");
+                    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)");
                     break;
                 default:
                     throw new IllegalArgumentException("MatchAction: " + matchAction);
             }
-
-            return builder;
-
         } else {
 
             if (filter.getExpression1() instanceof IsInstanceOf) {
-                StringBuilder builder =
-                        append(extraData, handleInstanceOf((IsInstanceOf) filter.getExpression1()));
-                return builder;
+                return handleInstanceOf((IsInstanceOf) filter.getExpression1(), extraData);
             }
 
             // comparing a literal with a field
 
             PropertyName expression1;
@@ -269,88 +247,73 @@ public Object visit(PropertyIsEqualTo filter, Object extraData) {
             String expectedValue = expression2.evaluate(null, String.class);
             if (!matchingCase) {
                 expectedValue = expectedValue.toUpperCase();
             }
             String valueParam = newParam("value", expectedValue);
-
-            StringBuilder builder;
             String valueCol = matchingCase ? "value" : "UPPER(value)";
 
             switch (matchAction) {
                     // respect match action
                 case ALL: // all = another value for the property may not occur
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                    propertyTypesParam,
-                                    ") AND ",
-                                    valueCol,
-                                    " != :",
-                                    valueParam,
-                                    ") /* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                            propertyTypesParam,
+                            ") AND ",
+                            valueCol,
+                            " != :",
+                            valueParam,
+                            ")");
                     break;
                 case ANY: // any = the value for the property must occur at least once
-                    builder =
-                            append(
-                                    extraData,
-                                    "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                    propertyTypesParam,
-                                    ") AND ",
-                                    valueCol,
-                                    " = :",
-                                    valueParam,
-                                    ") /* ",
-                                    filter.toString(),
-                                    " */\n");
+                    append(
+                            extraData,
+                            "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                            propertyTypesParam,
+                            ") AND ",
+                            valueCol,
+                            " = :",
+                            valueParam,
+                            ")");
                     break;
                 case ONE: // one = the value for the property must occur exactly once
-                    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");
+                    append(
+                            extraData,
+                            "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                            propertyTypesParam,
+                            ") AND ",
+                            valueCol,
+                            " = :",
+                            valueParam,
+                            " GROUP BY (oid) HAVING COUNT(oid) = 1)");
                     break;
                 default:
                     throw new IllegalArgumentException("MatchAction: " + matchAction);
             }
-
-            return builder;
         }
+        return dialect.appendComment(extraData, filter);
     }
 
-    private String handleInstanceOf(IsInstanceOf instanceOf) {
+    private Object handleInstanceOf(IsInstanceOf instanceOf, Object extraData) {
         Expression expression1 = instanceOf.getParameters().get(0);
 
         Class<?> clazz = expression1.evaluate(null, Class.class);
-
-        if (clazz == null || dbMappings.getTypeId(clazz) == null) {
-            return "0 = 1 /* EXCLUDE */\n";
-        }
-
         Integer typeId = dbMappings.getTypeId(clazz);
-
-        return "type_id = " + typeId + " /* isInstanceOf " + clazz.getName() + " */\n";
+        if (typeId == null) {
+            return visit(Filter.EXCLUDE, extraData);
+        }
+        append(extraData, "type_id = ", typeId.toString());
+        return dialect.appendComment(extraData, "isInstanceOf ", clazz.getName());
     }
 
     /**
      * @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.PropertyIsLike,
      *     java.lang.Object)
      */
     @Override
     public Object visit(PropertyIsLike filter, Object extraData) {
         final PropertyName expression1 = (PropertyName) filter.getExpression();
-        // TODO: check for indexed property name
 
         final String propertyTypesParam = propertyTypesParam(expression1);
 
         final String literal = filter.getLiteral();
         final MatchAction matchAction = filter.getMatchAction();
@@ -358,67 +321,55 @@ public Object visit(PropertyIsLike filter, Object extraData) {
         final char multi = filter.getWildCard().charAt(0);
         final char single = filter.getSingleChar().charAt(0);
         final boolean matchCase = filter.isMatchingCase();
 
         final String pattern =
-                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal);
+                LikeFilterImpl.convertToSQL92(esc, multi, single, matchCase, literal, false);
 
         // respect match case
+        String valueParam = newParam("value", pattern);
         String valueCol = matchCase ? "value" : "UPPER(value)";
 
-        StringBuilder builder;
-
         switch (matchAction) {
                 // respect match action
             case ALL: // all = another value for the property may not occur
-                builder =
-                        append(
-                                extraData,
-                                "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                propertyTypesParam,
-                                ") AND ",
-                                valueCol,
-                                " NOT LIKE '",
-                                pattern,
-                                "') /* ",
-                                filter.toString(),
-                                " */\n");
+                append(
+                        extraData,
+                        "oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                        propertyTypesParam,
+                        ") AND ",
+                        valueCol,
+                        " NOT LIKE :",
+                        valueParam,
+                        ")");
                 break;
             case ANY: // any = the value for the property must occur at least once
-                builder =
-                        append(
-                                extraData,
-                                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
-                                propertyTypesParam,
-                                ") AND ",
-                                valueCol,
-                                " LIKE '",
-                                pattern,
-                                "') /* ",
-                                filter.toString(),
-                                " */\n");
+                append(
+                        extraData,
+                        "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                        propertyTypesParam,
+                        ") AND ",
+                        valueCol,
+                        " LIKE :",
+                        valueParam,
+                        ")");
                 break;
             case ONE: // one = the value for the property must occur exactly once
-                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");
+                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)");
                 break;
             default:
                 throw new IllegalArgumentException("MatchAction: " + matchAction);
         }
-
-        return builder;
+        return dialect.appendComment(extraData, filter);
     }
 
     private String propertyTypesParam(final PropertyName property) {
 
         final String propertyTypesParam;
@@ -475,44 +426,34 @@ public Object visit(PropertyIsNotEqualTo filter, Object extraData) {
     }
 
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.And, java.lang.Object) */
     @Override
     public Object visit(And filter, Object extraData) {
-        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;
+        return visit(filter, "AND", extraData);
     }
 
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.Or, java.lang.Object) */
     @Override
     public Object visit(Or filter, Object extraData) {
-        StringBuilder sql = (StringBuilder) extraData;
+        return visit(filter, "OR", extraData);
+    }
 
+    protected Object visit(BinaryLogicOperator filter, String type, Object extraData) {
+        StringBuilder sql = (StringBuilder) extraData;
         List<Filter> children = filter.getChildren();
         checkArgument(children.size() > 0);
-        sql.append("(");
+        sql.append('(');
+        dialect.appendIfDebug(sql, "\n    ", "");
         for (Iterator<Filter> it = children.iterator(); it.hasNext(); ) {
-            Filter child = it.next();
-            sql = (StringBuilder) child.accept(this, sql);
+            it.next().accept(this, sql);
             if (it.hasNext()) {
-                sql = append(extraData, "    OR\n    ");
+                dialect.appendIfDebug(sql, "    ", " ");
+                sql.append(type);
+                dialect.appendIfDebug(sql, "\n    ", " ");
             }
         }
-        sql.append(")");
-        return sql;
+        return sql.append(')');
     }
 
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.Id, java.lang.Object) */
     @Override
     public Object visit(Id filter, Object extraData) {
@@ -521,12 +462,17 @@ public Object visit(Id filter, Object extraData) {
     }
 
     /** @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.Not, java.lang.Object) */
     @Override
     public Object visit(Not filter, Object extraData) {
-        filter.getFilter().accept(this, append(extraData, "NOT ("));
-        return append(extraData, ")");
+        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 ? ")" : "");
     }
 
     /**
      * @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.PropertyIsBetween,
      *     java.lang.Object)
@@ -585,21 +531,18 @@ public Object visit(PropertyIsLessThanOrEqualTo filter, Object extraData) {
     @Override
     public Object visit(PropertyIsNull filter, Object extraData) {
         final PropertyName propertyName = (PropertyName) filter.getExpression();
         final String propertyTypesParam = propertyTypesParam(propertyName);
 
-        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;
+        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);
     }
 
     /**
      * @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.PropertyIsNil,
      *     java.lang.Object)
@@ -607,19 +550,16 @@ public Object visit(PropertyIsNull filter, Object extraData) {
     @Override
     public Object visit(PropertyIsNil filter, Object extraData) {
         final PropertyName propertyName = (PropertyName) filter.getExpression();
         final String propertyTypesParam = propertyTypesParam(propertyName);
 
-        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;
+        append(
+                extraData,
+                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:",
+                propertyTypesParam,
+                ") AND value IS NULL)");
+        return dialect.appendComment(extraData, filter);
     }
 
     /**
      * @see org.opengis.filter.FilterVisitor#visit(org.opengis.filter.spatial.BBOX,
      *     java.lang.Object)
diff --git a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/OracleDialect.java b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/OracleDialect.java
index a184f99a88..56108ffc6c 100644
--- a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/OracleDialect.java
+++ b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/OracleDialect.java
@@ -19,16 +19,21 @@ public void applyOffsetLimit(
         if (limit != null && offset == null) {
             offset = 0;
             limit += 1; // not zero-based
         }
         if (offset != null && limit != null) {
-            sql.insert(0, "SELECT * FROM (SELECT query.*, rownum rnum FROM (\n");
-            sql.append(") query\n");
+            sql.insert(
+                    0,
+                    "SELECT * FROM (SELECT query.*, rownum rnum FROM ("
+                            + (isDebugMode() ? "\n" : ""));
+            sql.append(") query");
+            appendIfDebug(sql, "\n", " ");
             if (limit != Integer.MAX_VALUE) {
                 limit = offset + limit;
             }
-            sql.append("WHERE rownum <= ").append(limit).append(")\n");
+            sql.append("WHERE rownum <= ").append(limit).append(")");
+            appendIfDebug(sql, "\n", " ");
             sql.append("WHERE rnum > ").append(offset);
         }
     }
 
     @Override
diff --git a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/QueryBuilder.java b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/QueryBuilder.java
index 2e252b483d..56456be19b 100644
--- a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/QueryBuilder.java
+++ b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcconfig/internal/QueryBuilder.java
@@ -126,11 +126,11 @@ public QueryBuilder<T> sortOrder(SortBy... order) {
     public QueryBuilder<T> filter(Filter filter) {
         this.originalFilter = filter;
         return this;
     }
 
-    private void querySortBy(StringBuilder query, StringBuilder whereClause, SortBy[] orders) {
+    private void querySortBy(StringBuilder query, String whereClause, SortBy[] orders) {
 
         /*
          * Start with the oid and id from the object table selecting for type and the filter.
          *
          * Then left join on oid for each property to sort by to turn it into an attribute.
@@ -142,19 +142,19 @@ private void querySortBy(StringBuilder query, StringBuilder whereClause, SortBy[
         StringBuilder orderBy = new StringBuilder();
         orderBy.append("ORDER BY ");
 
         int i = 0;
 
-        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("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(whereClause).append(") object");
+        query.append(") object");
 
         for (SortBy order : orders) {
             final String sortProperty = order.getPropertyName().getPropertyName();
             final String subSelectName = "subSelect" + i;
             final String attributeName = "prop" + i;
@@ -165,39 +165,40 @@ private void querySortBy(StringBuilder query, StringBuilder whereClause, SortBy[
 
             // Store the property type ID as a named parameter
             Map<String, Object> namedParameters = getNamedParameters();
             namedParameters.put(propertyParamName, sortPropertyTypeIds);
 
-            query.append("\n  LEFT JOIN");
-            query.append("\n    (SELECT oid, value ")
-                    .append(attributeName)
-                    .append(" FROM \n      object_property WHERE property_type IN (:")
+            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 (:")
                     .append(propertyParamName)
                     .append(")) ")
                     .append(subSelectName);
-
-            query.append(" /* ")
-                    .append(order.getPropertyName().getPropertyName())
-                    .append(" ")
-                    .append(ascDesc(order))
-                    .append(" */");
-
-            query.append("\n  ON object.oid = ").append(subSelectName).append(".oid");
+            dialect.appendComment(
+                    query, order.getPropertyName().getPropertyName(), " ", ascDesc(order));
+            dialect.appendIfDebug(query, "  ", " ");
+            query.append("ON object.oid = ").append(subSelectName).append(".oid");
             // Update the ORDER BY clause to be added later
             if (i > 0) orderBy.append(", ");
             orderBy.append(attributeName).append(" ").append(ascDesc(order));
 
             i++;
         }
-
-        query.append("\n  ").append(orderBy);
+        dialect.appendIfDebug(query, "\n  ", " ");
+        query.append(orderBy);
     }
 
-    private StringBuilder buildWhereClause() {
+    private String buildWhereClause() {
+        this.predicateBuilder =
+                new FilterToCatalogSQL(this.dialect, this.queryType, this.dbMappings);
+        if (Filter.INCLUDE.equals(this.originalFilter)) {
+            return null;
+        }
         final SimplifyingFilterVisitor filterSimplifier = new SimplifyingFilterVisitor();
-
-        this.predicateBuilder = new FilterToCatalogSQL(this.queryType, this.dbMappings);
         Capabilities fcs = new Capabilities(FilterToCatalogSQL.CAPABILITIES);
         FeatureType parent = null;
         // use this to instruct the filter splitter which filters can be encoded depending on
         // whether a db mapping for a given property name exists
         ClientTransactionAccessor transactionAccessor =
@@ -236,40 +237,46 @@ public Filter getDeleteFilter() {
         Filter unsupported = filterSplitter.getFilterPost();
         Filter demultipliedFilter =
                 (Filter) supported.accept(new LiteralDemultiplyingFilterVisitor(), null);
         this.supportedFilter = (Filter) demultipliedFilter.accept(filterSimplifier, null);
         this.unsupportedFilter = (Filter) unsupported.accept(filterSimplifier, null);
-
+        if (Filter.INCLUDE.equals(this.supportedFilter)) {
+            return null;
+        }
         StringBuilder whereClause = new StringBuilder();
-        return (StringBuilder) this.supportedFilter.accept(predicateBuilder, whereClause);
+        return this.supportedFilter.accept(predicateBuilder, whereClause).toString();
     }
 
-    public StringBuilder build() {
+    public String build() {
 
-        StringBuilder whereClause = buildWhereClause();
+        String whereClause = buildWhereClause();
 
         StringBuilder query = new StringBuilder();
         if (isCountQuery) {
-            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(")");
+            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);
             }
         } else {
-            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");
+            if (sortOrder != null) {
+                querySortBy(query, whereClause, sortOrder);
             } else {
-                querySortBy(query, whereClause, orders);
+                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");
             }
             applyOffsetLimit(query);
         }
 
-        return query;
+        return query.toString().trim();
     }
 
     /** When the query was built, were the offset and limit included. */
     public boolean isOffsetLimitApplied() {
         return offsetLimitApplied;
diff --git a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcloader/JDBCLoaderProperties.java b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcloader/JDBCLoaderProperties.java
index a5b05a1736..117122bd55 100644
--- a/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcloader/JDBCLoaderProperties.java
+++ b/src/community/jdbcconfig/src/main/java/org/geoserver/jdbcloader/JDBCLoaderProperties.java
@@ -105,6 +105,14 @@ public String getDatasourceId() {
     }
 
     public void setDatasourceId(String datasourceId) {
         this.datasourceId = datasourceId;
     }
+
+    public boolean isDebugMode() {
+        return Boolean.parseBoolean(getProperty("debugMode", "false"));
+    }
+
+    public void setDebugMode(boolean debugMode) {
+        setProperty("debugMode", String.valueOf(debugMode));
+    }
 }
diff --git a/src/community/jdbcconfig/src/main/resources/applicationContext.xml b/src/community/jdbcconfig/src/main/resources/applicationContext.xml
index f07277368d..39a1c1d56a 100644
--- a/src/community/jdbcconfig/src/main/resources/applicationContext.xml
+++ b/src/community/jdbcconfig/src/main/resources/applicationContext.xml
@@ -31,10 +31,11 @@
   <bean id="jdbcPersistenceBinding" class="org.geoserver.jdbcconfig.internal.XStreamInfoSerialBinding">
     <constructor-arg ref="xstreamPersisterFactory" />
   </bean>
 
   <bean id="JDBCConfigDB" class="org.geoserver.jdbcconfig.internal.ConfigDatabase">
+    <constructor-arg ref="jdbcConfigProperties" />
     <constructor-arg ref="jdbcConfigDataSource" />
     <constructor-arg ref="jdbcPersistenceBinding" />
   </bean>
 
   <bean id="JDBCCatalogFacade" class="org.geoserver.jdbcconfig.catalog.JDBCCatalogFacade">
diff --git a/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties b/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties
index 9d3377f894..4e4be0febb 100644
--- a/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties
+++ b/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties
@@ -8,10 +8,15 @@ initdb=true
 initScript=jdbcconfig/scripts/initdb.postgres.sql
 
 # flag controlling whether the existing (non-db) catalog should be imported
 import=true
 
+# 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
+
 # database connection info
 jdbcUrl=jdbc:postgresql://localhost:5432/gscatalog
 driverClassName=org.postgresql.Driver
 username=postgres
 password=
diff --git a/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.h2 b/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.h2
index 690b66fa9a..b6d116a861 100644
--- a/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.h2
+++ b/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.h2
@@ -12,10 +12,15 @@ enabled=true
 
 initdb=true
 initScript=jdbcconfig/scripts/initdb.h2.sql
 import=true
 
+# 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
+
 jdbcUrl=jdbc:h2:file:${GEOSERVER_DATA_DIR}/jdbcconfig/catalog;AUTO_SERVER=TRUE
 driverClassName=org.h2.Driver
 username=sa
 password=
 
diff --git a/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.postgres b/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.postgres
index bf2669196b..c2638ce891 100644
--- a/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.postgres
+++ b/src/community/jdbcconfig/src/main/resources/jdbcconfig.properties.postgres
@@ -29,10 +29,15 @@ initScript=jdbcconfig/scripts/initdb.postgres.sql
 #
 # Import the data directory into a new database.  Should only be used on the first run.
 #
 import=true
 
+# 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
+
 #
 # The JNDI name for the data source.  Uncomment to use JNDI.
 #
 #jndiName=java\:/comp/env/jdbc/gsconfig
 
diff --git a/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/JDBCConfigTestSupport.java b/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/JDBCConfigTestSupport.java
index 7c6fe44496..ad618c62a1 100644
--- a/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/JDBCConfigTestSupport.java
+++ b/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/JDBCConfigTestSupport.java
@@ -30,10 +30,11 @@
 import org.geoserver.jdbcconfig.internal.DbMappings;
 import org.geoserver.jdbcconfig.internal.JDBCConfigProperties;
 import org.geoserver.jdbcconfig.internal.JDBCConfigXStreamPersisterInitializer;
 import org.geoserver.jdbcconfig.internal.Util;
 import org.geoserver.jdbcconfig.internal.XStreamInfoSerialBinding;
+import org.geoserver.jdbcloader.JDBCLoaderProperties;
 import org.geoserver.platform.GeoServerExtensionsHelper;
 import org.geoserver.platform.GeoServerResourceLoader;
 import org.springframework.context.annotation.AnnotationConfigApplicationContext;
 import org.springframework.context.annotation.Bean;
 import org.springframework.context.annotation.Configuration;
@@ -416,11 +417,13 @@ public PlatformTransactionManager jdbcConfigTransactionManager() {
         }
 
         @Bean
         public ConfigDatabase configDatabase() {
             return new ConfigDatabase(
-                    dataSource(), new XStreamInfoSerialBinding(new XStreamPersisterFactory()));
+                    new JDBCLoaderProperties(null),
+                    dataSource(),
+                    new XStreamInfoSerialBinding(new XStreamPersisterFactory()));
         }
 
         @Bean
         public DataSource dataSource() {
             return lazy;
diff --git a/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/internal/QueryBuilderTest.java b/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/internal/QueryBuilderTest.java
index 7a5c09e800..93a3a197d5 100644
--- a/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/internal/QueryBuilderTest.java
+++ b/src/community/jdbcconfig/src/test/java/org/geoserver/jdbcconfig/internal/QueryBuilderTest.java
@@ -14,38 +14,43 @@
  *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  *    Lesser General Public License for more details.
  */
 package org.geoserver.jdbcconfig.internal;
 
-import static org.hamcrest.CoreMatchers.containsString;
-import static org.hamcrest.MatcherAssert.assertThat;
+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.geoserver.catalog.LayerInfo;
 import org.geoserver.catalog.Predicates;
 import org.geoserver.catalog.WorkspaceInfo;
 import org.geoserver.jdbcconfig.JDBCConfigTestSupport;
 import org.junit.After;
 import org.junit.Before;
 import org.junit.Test;
 import org.opengis.filter.Filter;
+import org.opengis.filter.FilterFactory;
+import org.opengis.filter.sort.SortBy;
 
 /**
  * @author groldan
  * @author Kevin Smith, OpenGeo
  */
 public class QueryBuilderTest {
 
+    private static final FilterFactory FACTORY = Predicates.factory;
+
     private JDBCConfigTestSupport testSupport;
 
     private DbMappings dbMappings;
 
     Dialect dialect;
 
     @Before
     public void setUp() throws Exception {
         dialect = new Dialect();
-        dbMappings = new DbMappings(dialect);
         testSupport =
                 new JDBCConfigTestSupport(
                         (JDBCConfigTestSupport.DBConfig)
                                 JDBCConfigTestSupport.parameterizedDBConfigs().get(0)[0]);
         testSupport.setUp();
@@ -56,127 +61,928 @@ public void setUp() throws Exception {
     public void tearDown() throws Exception {
         testSupport.tearDown();
     }
 
     @Test
-    public void testQueryAll() {
-        Filter filter = Predicates.equal("name", "ws1");
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .build();
+    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"));
     }
 
     @Test
-    public void testSort1() {
-        Filter filter = Predicates.acceptAll();
-        StringBuilder build =
-                QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
-                        .sortOrder(Predicates.asc("foo"))
-                        .build();
+    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"));
     }
 
     @Test
-    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 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"));
     }
 
     @Test
-    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 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"));
     }
 
     @Test
-    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 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"));
     }
 
     @Test
-    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 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());
     }
 
     @Test
-    public void testIsInstanceOf() {
-        // Create the filter
-        Filter filter = Predicates.isInstanceOf(LayerInfo.class);
-        // Build it
-        StringBuilder build =
+    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 =
                 QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
+                        .offset(5)
+                        .limit(10)
                         .build();
-        String sql = build.toString();
-        // Ensure the following sql is present
-        assertThat(sql, containsString("type_id = " + dbMappings.getTypeId(LayerInfo.class)));
+        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);
     }
 
     @Test
-    public void testIsNull() {
-        // Create the filter
-        Filter filter = Predicates.isNull("name");
-        // Build it
-        StringBuilder build =
+    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 =
                 QueryBuilder.forIds(dialect, WorkspaceInfo.class, dbMappings)
-                        .filter(filter)
+                        .offset(5)
+                        .limit(10)
                         .build();
-        String sql = build.toString();
+        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);
+    }
 
-        String sqlNull =
-                "oid IN (SELECT oid FROM object_property WHERE property_type IN (:"
+    @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) AND value IS NULL) OR oid NOT IN (SELECT oid FROM object_property WHERE property_type IN (:"
-                        + "ptype0))";
-        // Ensure the following sql is present
-        assertThat(sql, containsString(sqlNull));
+                        + "ptype0)))";
+        verifyForCount(expected, false, Predicates.isNull("name"));
     }
 
     @Test
-    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();
+    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);
+    }
 
-        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));
+    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());
     }
 }

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());