Passed-in parameter used in GROUP BY still returns 1 row?

Question

I stumbled on this behavior and now I'm curious what's actually going on. If I try to bind a parameter to a column name in the GROUP BY clause, data is actually returned, but only one row. I know you're not allowed to use table or column names as parameters, but I'm wondering what's causing this to happen behind the scenes. This was buried deep in my (amateur) code and it was hard to troubleshoot, because it didn't throw an error, and it actually returned data. I would love more insight into this!

Sample table:

| artist          | album          | label       |
|-----------------|----------------|-------------|
| John Coltrane   | A Love Supreme | MCA Records |
| John McLaughlin | Extrapolation  | Marmalade   |
| John Coltrane   | A Love Supreme | Impulse!    |
| John McLaughlin | Extrapolation  | Polydor     |

Example code:

$field = 'artist';
$artist = '%john%';
$sql = 'SELECT artist, album
    FROM record_collection
    WHERE artist LIKE :artist
    GROUP BY :field';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':artist', $artist);
$stmt->bindParam(':field', $field);
$stmt->execute();
echo 'Row count: '. $stmt->rowCount();

This returns: "Row count: 1"

I noticed:

• Using "GROUP BY artist" instead, and commenting out the bindParam line for :field, I get the expected "Row count: 2". This is the right way to do it.
• Using the parameter :field in the WHERE clause as a column name (e.g., "WHERE :field LIKE :artist") gives you "Row count: 0".

So what I'm wondering is:

1. What is SQL/PDO doing beind the scenes that's causing 1 row to get returned?
2. If parameters in the GROUP BY aren't supported, why not return nothing, or better yet, throw an error? I assume not, but is there any legitimate use for passing a parameter into GROUP BY?

Answer 1

When you pass :field in, then you are passing in a string value. So, the result is group by <constant>, which returns one row.

You cannot parameterize the name of a column, so you have to put it directly into the SQL statement:

$sql = 'SELECT artist, album
        FROM record_collection
        WHERE artist LIKE :artist
        GROUP BY '.$field'

:artist is fine because it is a value, not an identifier in SQL.