是否可以将GROUP BY与绑定变量一起使用? [英] Is it possible to use GROUP BY with bind variables?

问题描述

我想发出如下查询

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

其中，:1是绑定变量.如果我说

，请使用PreparedStatement

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

我从DBMS收到一个错误，抱怨说f(?, col2)不是GROUP BY表达式.

通常如何在JDBC中解决这个问题?

解决方案

我建议重新编写该语句，以便只有一个绑定参数. 这种方法有点丑陋，但会返回结果集:

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

此重写语句仅引用了一个绑定参数，因此现在DBMS看到GROUP BY子句中的表达式与SELECT列表相同.

HTH

(我希望有一个更漂亮的方法，这就是为什么我更喜欢Oracle使用的命名绑定参数方法.借助Perl DBI驱动程序，在实际发送给Oracle的语句中，位置参数被转换为命名参数.)

起初我没有看到问题，我不明白最初的问题. (显然，其他几个人也错过了它.)但是在运行了一些测试用例之后，我突然意识到问题出在哪里，问题出在什么地方.

让我看看是否可以说明问题:如何获取两个单独的(位置)绑定参数(由DBMS来对待)，就像两个对相同(命名)绑定参数的引用一样.

DBMS期望GROUP BY中的表达式与SELECT列表中的表达式匹配.但是，即使两个表达式都相同，但唯一的区别是每个表达式都引用了一个不同的绑定变量，这两个表达式仍被认为是不同的. (我们可以演示至少一些DBMS允许的一些测试用例，但是还有一些更常见的情况会引发异常.)

这时的简短答案是，这让我感到困惑.我的建议(可能不是对原始问题的实际答案)是重组查询.

[/EDIT]

如果此方法不起作用，或者您在解决该问题时遇到其他问题，我可以提供更多详细信息.或者，如果性能存在问题(我可以看到优化器为重写的查询选择了不同的计划，即使它返回了指定的结果集.对于进一步的测试，我们真的需要知道什么DBMS，什么驱动程序，统计信息等)

编辑(八年半后)

另一次尝试重写查询.同样，我想出的唯一解决方案是带有一个绑定占位符的查询.这次，我们将其粘贴到一个内联视图中，该视图返回单行，并将其连接到t.我可以看到它在做什么；我不确定Oracle优化器如何看待这一点.我们可能希望(或需要)进行明确的转换，例如TO_NUMBER(?) AS param，TO_DATE(?,'...') AS param，TO_CHAR(?) AS param，具体取决于bind参数的数据类型以及我们要从视图返回的数据类型.)

这就是我在MySQL中要做的事情.我的答案中的原始查询在内联视图(MySQL 派生表)中执行联接操作.如果可以避免的话，我们希望避免实例化hughjass派生表.再说一次，只要sql_mode不包含ONLY_FULL_GROUP_BY，MySQL可能会让原始查询滑动. MySQL还可以让我们删除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)

根据MadusankaD的回答，在过去八年中，Oracle增加了对在JDBC驱动程序中重用相同的命名绑定参数并保持等效性的支持. (我尚未测试过，但是如果现在可以正常工作，那就太好了.)

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?

解决方案

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.)

这篇关于是否可以将GROUP BY与绑定变量一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！