Bug Analysis Document: 325167: Native Query Containing Temporary Table # Identifier is Translated to ? SP Parameter
Document History
Date
|
Author
|
Version Description & Notes
|
20100914
|
Michael O'Brien
|
1.0 Initial reproduction use cases
|
20100927
|
Michael O'Brien
|
Temporarily off project for 2 weeks - out of office
|
20101022
|
Michael O'Brien
|
Return to analysis - recommend Alternative #10
|
Overview
Problem Description
- Problem: When users do a native query on temporary tables that involve the # hash prefix - we break because we process the user defined query as if it involves parameters.
- Required fix: It would be nice if we could not process # into ? parameter markers in this case only
Constraints
- C1: There is a historical API that provides for EclipseLink native query users to use the # hash mark as a parameter marker. We may need to deprecate or remove this unpublished API.
- C2: Need to handle multiple # where
# = temporary table
## = global temporary table
### = OUT parameter type
#### = INOUT parameter type
Concurrency and Thread Safety
Analysis
Problem Details
Debug and Stacktrace
- The following debug stacktrace illustrates the point that the following custom query hash (#) is converted to a (?) parameter in error.
- from
- UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = 47) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM #
- to
- UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = 47) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM ?
DatasourceCall.java:563
/**
* INTERNAL:
* All values are printed as ? to allow for parameter binding or translation during the execute of the call.
*/
public void appendIn(Writer writer, DatabaseField field) {
try {
writer.write(argumentMarker());
this SQLCall (id=116)
hasCustomSQLArguments true
shouldProcessTokenInQuotes false
sqlString "UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = 47) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM #DPAR_SCPROCUNIT)))" (id=135)
field DatabaseField (id=146)
name "DPAR_SCPROCUNIT" (id=145)
qualifiedName "DPAR_SCPROCUNIT" (id=145)
GridClient [Java Application]
org.eclipse.persistence.example.dataparallel.GridClient at localhost:63971
Thread [Main Thread] (Suspended)
SQLCall(DatasourceCall).appendIn(Writer, DatabaseField) line: 567
SQLCall(DatasourceCall).translateCustomQuery() line: 422
SQLCall.translateCustomQuery() line: 283
SQLCall.prepareInternal(AbstractSession) line: 149
SQLCall(DatabaseCall).prepare(AbstractSession) line: 546
CallQueryMechanism.prepareCall() line: 132
CallQueryMechanism(DatasourceCallQueryMechanism).prepareExecuteNoSelect() line: 490
DataModifyQuery.prepare() line: 103
DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord, boolean) line: 548
DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord) line: 506
DataModifyQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 714
DataModifyQuery(DatabaseQuery).executeInUnitOfWork(UnitOfWorkImpl, AbstractRecord) line: 682
RepeatableWriteUnitOfWork(UnitOfWorkImpl).internalExecuteQuery(DatabaseQuery, AbstractRecord) line: 2960
RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord, int) line: 1303
RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord) line: 1285
RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, List) line: 1259
EJBQueryImpl<X>.executeUpdate() line: 534
GridClient.nativeQuery() line: 110
GridClient.main(String[]) line: 344
C:\opt\wls10340_0818\jrockit_160_20_D1.1.0-2119\bin\javaw.exe (2010-10-27 3:35:28 PM)
Design
Alternative 1: Disable select Query userDefined=True flags at Query Construction
Alternative 2: Disable userDefined=True by searching queryString for [from #]
Alternative 3: Subclass SQLCall for special temporary table query
Alternative 4: Extend translation code handling of #
- This seems hacky and a little dangerous because of the optimization for # delimiter handling.
- The following experimental code only handles "from #" and not the multitude of other use cases possible using temporary tables. It would be better to use a lexical parser.
DatabaseQuery:1322
public void checkPrepare(AbstractSession session, AbstractRecord translationRow, boolean force) {
if ((isReadQuery() || isDataModifyQuery()) && isCallQuery() && (getQueryMechanism() instanceof CallQueryMechanism)
...
&& ((translationRow == null) || translationRow.isEmpty())) {
//&& !isTemporaryTableQuery()) {
...
// 325167: experimental code to be discarded/refactored
public boolean isTemporaryTableQuery() {
boolean result = false; // explicit
if (isCallQuery() && (getQueryMechanism() instanceof CallQueryMechanism)) {
String queryString = ((CallQueryMechanism)getQueryMechanism()).getCall().getQueryString();
if(null != queryString) {
Pattern pattern = Pattern.compile("[^ (]*[ ]*[(]*[fF][rR][oO][mM][ ]+#[^ ]*");
Matcher matcher = pattern.matcher(queryString);
result = matcher.find();
}
}
return result;
}
Alternative 5: Delimit temporary table with Square brackets like 260263
- This fix involves using square brackets like what was initially done for stored procedure parameters in bug # 260263 where we ended up using the @ delimiter in the final patch
[]
Analysis 5
- square delimiters does not currently work like it did for stored procedures
aBuffer.append("(SELECT PROCUNIT_ID FROM [#DPAR_SCPROCUNIT])))")
- still gets converted to ? parameters by CalQueryMechanism with a hanging beginning square bracket
(SELECT PROCUNIT_ID FROM [?)))
Alternative 6: Use @variable for #temporary table name
- We would need to create something like stored procedure code and set @table = #temporary_table
- Later we would reference it like execute("...from " + @table)
- This may not be feasible and/or supported on all database platforms - some of which do not execute code. However those that support temporary tables (derby/H2 only support global ## temporary tables) should also support procedures
Alternative 7: Use a query hint to disable the userDefined flag
- This procedure (after a group discussion) would allow the user to not have the native query treated as a Call - where (1-n) # delimiters are converted to ? parameters
- 20101029: Discussion with Chris and Andrei
- Allow user to use an alternate char like % for our # hash symbol used in EclipseLink
- We then will not process the # as a bound parameter
Analysis
Alternative 8: use standard backslash escaping
- This may not be feasible - but I will test escaping the #
Alternative 9: Use boolean flag similar to shouldProcessTokenInQuotes from 217745
- See similar shouldProcessTokenInQuotes which surrounds shouldBindAddParameters in DatasourceCall
- See similar fix for 284884 : Quoted '?' symbol in expression literal causes ArrayIndexOutOfBoundsException in DatasourceCall.translateQueryString() in SVN rev # 4764
Alternative 10: Use existing eclipselink.jdbc.bind-parameters property to stop #temp_table parameter processing
- There is already an existing property that we can check that will allow us to not process the #temporary_table custom SQL if the user sets this property.
- Note: this fix will not automatically determine whether or not to process the # delimiter as a parameter - but it will allow the user to disable it for custom native queries in this case.
<property name="eclipselink.jdbc.bind-parameters" value="False"/> <!-- default is True -->
SQLCall.java:137
protected void prepareInternal(AbstractSession session) {
//if (hasCustomSQLArguments()) {
if (hasCustomSQLArguments() && session.getPlatform().shouldBindAllParameters()) { // 325167: do not process possible #temp table name
...
translateCustomQuery();
...
super.prepareInternal(session);
Analysis
- We need to verify that override functionality via shouldIgnoreBindAllParameters() is preserved if the Query overrides the Session.
Decision
Pending code-review of Alternative #10 or #7.
Logs
Implementation
Logging
Testing
API
GUI
Config files
Documentation
Open Issues
Issue #
|
Owner
|
Description / Notes
|
I1
|
mobrien
|
-
|
Decisions
Issue #
|
Description / Notes
|
Decision
|
|
|
|
Future Considerations
References