Lift input parameter restrictions

[lukasj]

I just came across JPA spec 4.6.4 and I was shocked. Is there any reason to limit input parameters to WHERE and HAVING clauses?

[lukasj]

@glassfishrobot Commented
Reported by c.beikov

[lukasj]

@glassfishrobot Commented
This issue was imported from java.net JIRA JPA_SPEC-103

[lukasj]

• Issue Imported From: https://github.com/javaee/jpa-spec/issues/103
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @ldemichiel

[gavinking]

So @beikov, to my way of thinking, this restriction is actually reasonably well-motivated.

1. As you well know, some databases are extremely finicky about where they allow parameters in a SQL statement. And I bet that even now there are some platforms that disallow them in order by. (But you probably know better than me.)
2. We don't want to allow parameters in the select clause in a completely unrestricted way, because then queries like select title, ?1 from Book can't be typed without having an argument to ?1. So we would have to figure out what the restrictions are.
3. I suppose parameters in on restrictions are OK, though the truth is I simply don't know. I don't think they're at all common.
4. I suppose parameters in group by are mostly OK, though they don't seem terribly useful to me. I doubt they're common.

The thing is that actually existing JPA implementations don't (I guess) impose enforce this restriction. Rather, they let you use parameters wherever you like, and then blow up if the database doesn't like it. For the spec to impose a blanket requirement that parameters are allowed anywhere, and for the TCK to test that, might require providers having to implement really nasty and impractical workarounds. (Remember, we certify on Derby, which is hot garbage in this area.)

The spec is saying what providers have to support. It's not limiting what they can support.

So honestly I think this is basically fine. However, I would suggest a slight change in wording, from:

Input parameters can only be used in the WHERE clause or HAVING clause of a query or as the new value for an update item in the SET clause of an update statement.

To:

A provider is only required to support Input parameters in the WHERE clause or HAVING clause of a query or as the new value for an update item in the SET clause of an update statement.

WDYT?

[gavinking]

P.S. If someone can promise me that neither Derby nor any major RDBMS has any problem dealing with parameters in group by, I guess it would be fine to also whitelist group by.

[gavinking]

WDYT?

please see #492