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

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

 [] 

• see comment 6

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

• Update and add new query hint wiki at http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Query_Hints
• Verify named parameter support using ":"
• Verify escaping of "%" in the parameter name is handled - http://old.nabble.com/Issue-with-namedQuery-setParameter-and-string-which-contains-%22'%22-(single-quote)-td19606841.html

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.

Open Issues

Issue #	Owner	Description / Notes
I1	mobrien	-

Decisions

Issue #	Description / Notes	Decision

References

• EclipseLink Fisheye Revision pending