Is it possible to use GROUP BY with bind variables?

Question

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?

Answer 1

I suggest 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]

I can provide more details if this approach doesn't work, or if you have some other problem figuring it out. Or if there's a problem with performance (I 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.)

Answer 2

Even though you have issued a query through JDBC driver(using PreparedStatement) like this:

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

At last JDBC driver replaces these like below query before parsing to the database , even though you have used the same binding variable name in the both places.

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

But in oracle this will not be recognized as a valid group by clause. And also normal JDBC driver doesn't support named bind variables.

For that you can use OraclePreparedStatement class for you connection. That means it is oracle JDBC. Then you can use named bind variables. It will solve your issue.

Starting from Oracle Database 10g JDBC drivers, bind by name is supported using the setXXXAtName methods.

http://docs.oracle.com/cd/E24693_01/java.11203/e16548/apxref.htm#autoId20