是否可以将 GROUP BY 与绑定变量一起使用？

发布于 2024-07-23 05:02:36 字数 372 浏览 7 评论 0原文

我想发出如下查询，

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 中通常如何解决这个问题？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

通知家属抬走 2024-07-30 05:02:36

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

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

这个重写的语句仅引用单个绑定参数，因此现在 DBMS 看到 GROUP BY 子句和 SELECT 列表中的表达式是相同的。

HTH

[编辑]

方法。使用 Perl DBI 驱动程序，位置参数将转换为实际发送到 Oracle 的语句中的命名参数。）

（我希望有一种更漂亮的方法，这就是为什么我更喜欢 Oracle 使用的命名绑定参数一开始没看到问题，没明白原来的问题。（显然，其他几个人也错过了。）但是在运行了一些测试用例之后，我意识到问题是什么，问题在起作用。

让我看看是否可以说明问题：如何让两个单独的（位置）绑定参数（由 DBMS）处理为对同一（命名）绑定参数的两个引用。

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

在这一点上，简短的答案是，这让我感到困惑。我的建议（可能不是原始问题的实际答案）是重构查询。

[/编辑]

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

编辑（八年半后）

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

这就是我在 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 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.)

回复收藏 0 原文

下雨或天晴 2024-07-30 05:02:36

即使您已经通过 JDBC 驱动程序（使用 PreparedStatement）发出了这样的查询：

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

最后 JDBC 驱动程序在解析到数据库之前会替换这些查询，即使您在中使用了相同的绑定变量名称这两个地方。

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

但在 Oracle 中，这不会被识别为有效的 group by 子句。
而且普通的 JDBC 驱动程序也不支持命名绑定变量。

为此，您可以使用 OraclePreparedStatement 类进行连接。这意味着它是oracle JDBC。然后您可以使用命名绑定变量。它会解决你的问题。

从 Oracle Database 10g JDBC 驱动程序开始，使用 setXXXAtName 方法支持按名称绑定。

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

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

回复收藏 0 原文

心如狂蝶 2024-07-30 05:02:36

您是否尝试使用 ? 而不是命名绑定变量？另外，您使用哪个驱动程序？我使用瘦驱动程序尝试了这个简单的示例，它似乎工作正常：

PreparedStatement ps = con.prepareStatement("SELECT COUNT(*), TO_CHAR(SYSDATE, ?) FROM DUAL GROUP BY TO_CHAR(SYSDATE, ?)");
ps.setString(1, "YYYY");
ps.setString(2, "YYYY");
ps.executeQuery();

Did you try using ? rather than the named bind variables? As well, which driver are you using? I tried this trivial example using the thin driver, and it seemed to work fine:

PreparedStatement ps = con.prepareStatement("SELECT COUNT(*), TO_CHAR(SYSDATE, ?) FROM DUAL GROUP BY TO_CHAR(SYSDATE, ?)");
ps.setString(1, "YYYY");
ps.setString(2, "YYYY");
ps.executeQuery();

回复收藏 0 原文