Jump to: navigation, search

EclipseLink/Bugs/325167

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

  • In the constructor

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
    • native query fragment
        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 change the default bind parameter hash char

  • See 20101029 SVN Patch
  • 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

  • Essentially, this change involves making the currently hardcoded # delimiter - user definable into something like % for example.
  • I will need to familiarize myself with query hint usage and how it relates to our parameter processing first.
  • The user will be able to mix both our # hash parameter delimiter and use # exclusively for temporary table delimiters by substituting % for # where they actually want parameter binding to still occur.
    • SQL = UPDATE ... WHERE id=% AND (id IN (SELECT ... FROM #TEMP_TABLE))
    • converted after parameter binding to..
    • SQL = UPDATE ... WHERE id=32 AND (id IN (SELECT ... FROM #TEMP_TABLE))
    • not
    • SQL = UPDATE ... WHERE id=32 AND (id IN (SELECT ... FROM ?)


  • We need to add the query hint to EclipseLink
QueryHintsHandler.java (JPA)
QueryHints (Core)
  • The hint needs to be in the HashMap on jpa.QueryHintsHandler.mainMap if we wish to use it on our native query
aQuery = entityManager.createNativeQuery("update ...");
aQuery.setHint(QueryHints.PARAMETER_DELIMITER, "%");

Results

query	DataReadQuery  (id=157)	
	parameterDelimiter	"%" (id=153)	

Work Items

References

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)#Using_Named_Parameters_in_a_Native_Query

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. We could use the persistence unit level property or the QueryHint at the query level.
  • 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