Is it possible to use GROUP BY with bind variables?

I want to issue a query like the following

select max(col1), f(:1, col2) from t group by f(:1, col2)

where :1 is a bind variable. Using PreparedStatement, if I say

connection.prepareStatement
  ("select max(col1), f(?, col2) from t group by f(?, col2)")

I get an error from the DBMS complaining that f(?, col2) is not a GROUP BY expression.

How does one normally solve this in JDBC?

We can try re-writing the statement so that there is only one bind argument. This approach is kind of ugly. but returns the result set:

select max(col1) 
     , f_col2
  from (
         select col1
              , f(? ,col2) as f_col2 
           from t
       )
 group
    by f_col2

This re-written statement has a reference to only a single bind argument, so now the DBMS sees the expressions in the GROUP BY clause and the SELECT list are identical.

HTH

[EDIT]

(I wish there were a prettier way, this is why I prefer the named bind argument approach that Oracle uses. With the Perl DBI driver, positional arguments are converted to named arguments in the statement actually sent to Oracle.)

I didn't see the problem at first, I didn't understand the original question. (Apparently, several other people missed it too.) But after running some test cases, it dawned on me what the problem was, what the question was working.

Let me see if I can state the problem: how to get two separate (positional) bind arguments to be treated (by the DBMS) as if it were two references to the same (named) bind argument.

The DBMS is expecting the expression in the GROUP BY to match the expression in the SELECT list. But the two expressions are considered DIFFERENT even when the expressions are identical, when the only difference is that each expression references a different bind variable. (We can demonstrate some test cases that at least some DBMS will allow, but there are more general cases that will raise an exception.)

At this point the short answer is, that's got me stumped. The suggestion I have (which may not be an actual answer to the original question) is to restructure the query.

[/EDIT]

We can dive deeper into details if this approach doesn't work, we can figure out the deeper problem. Particular concern with "clever" SQL like this is (as always) performance trade-offs. (We can see the optimizer choosing a different plan for the re-written query, even though it returns the specified result set. For further testing, we'd really need to know what DBMS, what driver, statistics, etc.)

EDIT (eight and a half years later)

Another attempt at a query rewrite. Again, the only solution I come up with is a query with one bind placeholder. This time, we stick it into an inline view that returns a single row, and join that to t. I can see what it's doing; I'm not sure how the Oracle optimizer will see this. We may want (or need) to do an explicit conversion e.g. TO_NUMBER(?) AS param, TO_DATE(?,'...') AS param, TO_CHAR(?) AS param, depending on the datatype of the bind parameter, and the datatype we want to be returned as from the view.)

This is how I would do it in MySQL. The original query in my answer does the join operation inside the inline view (MySQL derived table). And we want to avoid materializing a hughjass derived table if we can avoid it. Then again, MySQL would probably let the original query slide as long as sql_mode doesn't include ONLY_FULL_GROUP_BY. MySQL would also let us drop the FROM DUAL)

  SELECT MAX(t.col1)
       , f( v.param ,t.col2)
    FROM t
   CROSS
    JOIN ( SELECT ? AS param FROM DUAL) v
   GROUP
      BY f( v.param ,t.col2)

According to the answer from MadusankaD, within the past eight years, Oracle has added support for reusing the same named bind parameters in the JDBC driver, and retaining equivalence. (I haven't tested that, but if that works now, then great.)