Your Grails SQL Query Might Be Lying to You

Introduction

If you've worked with Grails long enough, you've probably reached for both db.rows() and domain.executeQuery() at different times. One gives you raw SQL speed. The other gives you Hibernate's safety net. Both get the job done — until they don't.

This article is a deep dive into how these two SQL execution methods work under the hood, when to use each, and a critical bug in Groovy SQL's named parameter handling that can silently corrupt your query results without throwing a single error.

Whether you're building reports, writing service-layer logic, or debugging a query that "should work but doesn't," this comparison will save you hours.

The Two Approaches at a Glance

db.rows() — Groovy SQL

Groovy's groovy.sql.Sql class gives you direct SQL execution. You write the query, bind the parameters, and get back lightweight result objects — essentially Maps.

def db = new Sql(dataSource)
def results = db.rows("SELECT * FROM users WHERE id = :id", [id: 123])

The dataSource is typically injected as a Spring bean. The SQL hits the database directly — no ORM, no object mapping, no overhead.

What you get: Speed, simplicity, and full control over the SQL.

domain.executeQuery() — Hibernate/GORM

Grails' GORM layer uses Hibernate under the hood. You write HQL (Hibernate Query Language), and Hibernate handles parameter binding, type conversion, and returns fully hydrated domain objects.

def results = Users.executeQuery("FROM Users WHERE id = :id", [id: 123])

What you get: Type safety, caching, lazy loading, and domain objects.

How Named Parameters Work — And Where They Diverge

Both methods support named parameters with the :paramName syntax. For single values, they behave identically. The critical difference is how they handle collections.

Groovy SQL's Approach

Groovy SQL parses the SQL string, finds :paramName patterns, replaces each with a single ? placeholder, and calls PreparedStatement.setObject() with the value.

def results = db.rows(
    "SELECT * FROM users WHERE name = :name AND age > :age",
    [name: 'John', age: 25]
)
// Generated SQL: SELECT * FROM users WHERE name = ? AND age > ?
// Binds: ['John', 25]

For single values — perfect. No issues.

Hibernate's Approach

Hibernate parses HQL, converts it to SQL, and uses its own parameter binding engine. When it encounters a collection, it calls setParameterList() — a method specifically designed to expand collections into multiple placeholders.

def results = Users.executeQuery(
    "FROM Users WHERE id IN (:ids)",
    [ids: [1, 2, 3]]
)
// Generated SQL: SELECT * FROM users WHERE id IN (?, ?, ?)
// Binds: [1, 2, 3]

So far, so similar. Now here's where things break.

The Silent Bug: Collection Serialization in Groovy SQL

This is the critical finding. In Groovy SQL (tested on version 2.1.9), passing any collection type — List, Set, Array — as a named parameter causes JDBC to serialize the entire Java object into binary.

Your query doesn't fail. It doesn't throw an error. It silently compares your database column against a blob of serialized Java bytes — and returns wrong results.

What You Write

def results = db.rows(
    "SELECT * FROM users WHERE id IN (:ids)",
    [ids: [1, 2, 3]]
)

What the Database Actually Receives

SELECT * FROM users WHERE id IN (
    _binary'��\0sr\0java.util.ArrayListx����a�\0I\0sizexp...'
)

That's not a typo. Groovy SQL calls PreparedStatement.setObject() with the entire collection as a single object. JDBC doesn't know how to expand it into individual values, so it falls back to Java serialization and sends the raw bytes.

The query executes. No exception. The database compares your column against binary garbage, matches nothing (or worse, matches the wrong rows), and returns silently.

Why This Happens

Internally, Groovy SQL's rows() method follows this chain:

rows(String sql, Map params)
  → expand(sql, params)
    → replaceParameters()
      → setObject(PreparedStatement, index, value)

When the value is a collection, setObject() treats it as a single opaque object. There's no expansion logic, no iteration over elements. JDBC serializes it.

Hibernate, by contrast, detects collections and calls setParameterList(), which counts the elements, generates the correct number of ? placeholders, and binds each value individually. For empty collections, it generates WHERE 1=0 — returning an empty result set safely.

The Behavior Matrix

Key takeaway: Named parameters in db.rows() do not work for any collection type in IN/NOT IN clauses. This is not a Set-specific issue — it affects Lists and Arrays equally.

How This Plays Out in Production

To illustrate the real impact, consider a common pattern in enterprise applications: a service query that filters records based on a configurable list of account types.

The Pattern

// A configurable value, split into a collection
Set typeFilter = (configValue?.split(',')?.collect { it.trim() } as Set) ?: new HashSet()

// Used in a query with named parameters
def results = db.rows(
    "SELECT * FROM accounts WHERE type NOT IN (:typeFilter)",
    [typeFilter: typeFilter]
)

This looks perfectly reasonable. It passed code review. It worked in testing (because the config value was always populated in test environments). Then it hit production.

What Happened Across Two Releases

Release A used named parameters. Every scenario failed silently:

The database query log showed binary blobs where strings should have been. But the application never threw an error. Users received incomplete or incorrect data with no indication anything was wrong.

Release B switched to dynamic positional parameters:

def sql = "...type NOT IN (${typeFilter.collect{'?'}.join(',')})..."

This was progress — two out of three cases worked, and the failure was loud instead of silent. The empty collection case broke with a clear syntax error, which led to the bug being discovered and properly fixed.

Why the Bug Surfaced

The configuration field was always populated in earlier environments. After a production change, some configurations started returning null or empty values. Release A silently returned wrong data. Release B threw a visible error — which is how the team traced it back to the collection parameter handling.

A loud failure is always better than a silent one.

The Fix: Three Approaches

Approach 1: Guard Against Empty Collections (Recommended for db.rows())

Always check for empty collections and build placeholders dynamically:

List types = configValue?.split(',')?.collect { it.trim() } ?: []

String sql
def params

if (types) {
    String placeholders = types.collect { '?' }.join(',')
    sql = "SELECT * FROM accounts WHERE type NOT IN (${placeholders})"
    params = types
} else {
    sql = "SELECT * FROM accounts"  // Omit the clause entirely
    params = []
}

def results = db.rows(sql, params)

Approach 2: Switch to domain.executeQuery()

If you're working with domain classes, Hibernate handles everything correctly:

def results = Accounts.executeQuery(
    "FROM Accounts WHERE accountType NOT IN (:types)",
    [types: typeFilter]
)
// Handles List, Set, empty collections — all correctly

Approach 3: Positional Parameters with Dynamic Placeholders

For cases where you need db.rows() and the collection is guaranteed non-empty:

List ids = [1, 2, 3]
String placeholders = ids.collect { '?' }.join(',')
String sql = "SELECT * FROM users WHERE id IN (${placeholders})"
def results = db.rows(sql, ids)

Performance: Is the Speed Difference Worth the Risk?

db.rows() is roughly 2–3x faster for simple reads. But that advantage disappears when:

• Your query involves collection parameters (you'll need extra guard logic)
• You need the results as domain objects anyway (you'll hydrate manually)
• The query is complex enough that ORM overhead is negligible

The performance win is real but narrow. For most application logic, domain.executeQuery() is the safer default.

When to Use Each

Use db.rows() when:

• You need raw performance for simple SELECTs
• You're running reports or aggregations (COUNT, SUM, GROUP BY)
• You're querying non-domain tables
• Your parameters are single values (not collections)
• You've properly handled collection parameters with positional placeholders

Use domain.executeQuery() when:

• You're working with domain objects and relationships
• You need lazy loading or query caching
• Your queries involve collection parameters (IN/NOT IN)
• You want empty collections handled gracefully
• You need transaction management

Common Pitfalls

1. Silent Binary Serialization

//  Looks correct, fails silently
db.rows("SELECT * FROM users WHERE id IN (:ids)", [ids: [1, 2, 3]])

//  Use positional parameters
List ids = [1, 2, 3]
db.rows("SELECT * FROM users WHERE id IN (${ids.collect{'?'}.join(',')})", ids)

2. SQL Injection via String Interpolation

//  Vulnerable — never interpolate user input directly
db.rows("SELECT * FROM users WHERE name = '${userInput}'")

//  Always parameterize
db.rows("SELECT * FROM users WHERE name = :name", [name: userInput])

3. N+1 Queries with Hibernate

//  Each .accounts access triggers a separate query
def users = Users.executeQuery("FROM Users")
users.each { println it.accounts.size() }

//  Fetch join loads everything in one query
def users = Users.executeQuery("FROM Users u LEFT JOIN FETCH u.accounts")
users.each { println it.accounts.size() }

4. Unclosed Connections

def db = new Sql(dataSource)
try {
    return db.rows(sql, params)
} finally {
    db.close()
}

How to Detect This in Your Codebase

If you suspect this bug might exist in your application:

1. Search for the pattern — look for db.rows calls that pass collections into IN or NOT IN clauses with named parameters.

2. Enable database query logging — check the raw SQL hitting your database. If you see _binary'...' where you expected string or numeric values, you've found it.

3. Enable application-level SQL logging — configure your logging framework to output the SQL and bound parameters from both Groovy SQL and Hibernate.

4. Test with empty collections — if a configuration value or user input can be empty, make sure your queries handle that case explicitly.

Quick Reference

Key Takeaways

1. db.rows() is faster but unsafe with collections. Named parameters serialize any collection type as binary in older Groovy SQL versions. This is a silent failure — no errors, just wrong data.

2. domain.executeQuery() is safer by default. Hibernate's setParameterList() correctly expands collections and handles empty ones gracefully.

3. If you must use db.rows() with collections, use positional parameters with dynamically generated placeholders, and always guard against empty collections.

4. Silent failures are worse than loud ones. A SQL syntax error tells you something's broken. A binary serialization bug returns data that looks right but isn't.

5. Default to domain.executeQuery() unless you have a specific performance reason to use raw SQL — and even then, validate your parameter binding.

If you're maintaining a Grails application, it's worth searching for db.rows() calls that pass Lists or Sets into IN clauses. You might have a silent bug waiting to surface.