Comment 1 Michael OBrien 2010-09-14 10:45:37 EDT

>debugged in SE and reproduced the issue
>We should not be adding the #temporary table as a parameter on the query at
org.eclipse.persistence.example.dataparallel.GridClient at localhost:56800	
	Thread [main] (Suspended)	
		SQLCall(DatasourceCall).appendIn(Writer, DatabaseField) line: 568	
		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: 541	
		DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord) line: 502	
		DataModifyQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 703	
		DataModifyQuery(DatabaseQuery).executeInUnitOfWork(UnitOfWorkImpl, AbstractRecord) line: 675	
		RepeatableWriteUnitOfWork(UnitOfWorkImpl).internalExecuteQuery(DatabaseQuery, AbstractRecord) line: 2914	
		RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord, int) line: 1301	
		RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord) line: 1283	
		RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, List) line: 1257	
		EJBQueryImpl<X>.executeUpdate() line: 534	
		GridClient.nativeQuery() line: 107	
		GridClient.main(String[]) line: 329	

>DatasourceCall.java:422
    public void translateCustomQuery() {
...
        try {
            // ** This method is heavily optimized do not touch anything unless you "know" what your doing.
            while (lastIndex != -1) {
                int poundIndex = queryString.indexOf('#', lastIndex);
...
                if (poundIndex != -1) {
...
                    // Check for ## which means field from modify row.
                    if (queryString.charAt(poundIndex + 1) == '#') {
...
                    } else {
                        String fieldName = queryString.substring(poundIndex + 1, wordEndIndex);
                        DatabaseField field = createField(fieldName);
>                       appendIn(writer, field);


this	SQLCall  (id=73)	
	parameters	NonSynchronizedVector  (id=97)	
		elementData	Object[10]  (id=120)	
>			[0]	DatabaseField  (id=92)	
				qualifiedName	"DPAR_SPROCUNIT" (id=91)	
	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_SPROCUNIT)))" (id=78)	



DatabaseQuery.java:535
    public void checkPrepare(AbstractSession session, AbstractRecord translationRow, boolean force) {
        try {
            // This query is first prepared for global common state, this must be synced.
            if (!this.isPrepared) {// Avoid the monitor is already prepare, must
...
                synchronized (this) {
                    if (!isPrepared()) {
                        if ((isReadQuery() || isDataModifyQuery()) && isCallQuery() && (getQueryMechanism() instanceof CallQueryMechanism) && ((translationRow == null) || translationRow.isEmpty())) {
                            if (isReadObjectQuery() || isUserDefined()) {
>                                ((CallQueryMechanism) getQueryMechanism()).setCallHasCustomSQLArguments();

>We then write a "?" in place of this parameter into the CharArrayWriter("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 ?")

GridClient [Java Application]	
	org.eclipse.persistence.example.dataparallel.GridClient at localhost:56800	
		Thread [main] (Suspended (breakpoint at line 393 in DatasourceCall))	
			SQLCall(DatasourceCall).translateCustomQuery() line: 393	
			SQLCall.translateCustomQuery() line: 283	

>And set the queryString
queryString	"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 ?)))" (id=143)

Comment 2 Michael OBrien 2010-09-14 11:16:13 EDT

>The preliminary fix is to not do processing for custom SQL arguments in this case
>SQLCall.java:138
    protected void prepareInternal(AbstractSession session) {
        if (hasCustomSQLArguments()) {
            ...
            translateCustomQuery();

>needs to be false so we do not run translateCustomQuery()
this	SQLCall  (id=74)	
>	hasCustomSQLArguments	true	(should be 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_SPROCUNIT)))" (id=79)	

>The flag is set by ((CallQueryMechanism) getQueryMechanism()).setCallHasCustomSQLArguments() in checkPrepare
if it is a readObjectQuery or is user defined
?    if (isReadObjectQuery() || isUserDefined()) {

>GridClient [Java Application]	
	org.eclipse.persistence.example.dataparallel.GridClient at localhost:57176	
		Thread [main] (Suspended (breakpoint at line 164 in SQLCall))	
			SQLCall.setHasCustomSQLArguments(boolean) line: 164	
			CallQueryMechanism.setCallHasCustomSQLArguments() line: 346	
			DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord, boolean) line: 535	
			DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord) line: 502	
			DataModifyQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 703	


>Therefore the fix is not to set the userDefined field to [True] in this case
>Setting the isSQLCall to false would not make sense for setCallHasCustomSQLArguments()



>The preliminary fix is to not do parameter processing for custom SQL arguments in this case
>SQLCall.java:138
    protected void prepareInternal(AbstractSession session) {
        if (hasCustomSQLArguments()) {
            ...
            translateCustomQuery();

>needs to be false so we do not run translateCustomQuery()
this	SQLCall  (id=74)	
>	hasCustomSQLArguments	true	(should be 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_SPROCUNIT)))" (id=79)	

>The flag is set by ((CallQueryMechanism) getQueryMechanism()).setCallHasCustomSQLArguments() in checkPrepare
if it is a readObjectQuery or is user defined
?    if (isReadObjectQuery() || isUserDefined()) {

>GridClient [Java Application]	
	org.eclipse.persistence.example.dataparallel.GridClient at localhost:57176	
		Thread [main] (Suspended (breakpoint at line 164 in SQLCall))	
			SQLCall.setHasCustomSQLArguments(boolean) line: 164	
			CallQueryMechanism.setCallHasCustomSQLArguments() line: 346	
			DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord, boolean) line: 535	
			DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord) line: 502	
			DataModifyQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 703	


>Therefore the fix is not to set the userDefined field to [True] in this case
>Setting the isSQLCall to false would not make sense for setCallHasCustomSQLArguments()
There are 28 references to DatabaseQuery.setIsUserDefined()
We need a way to figure out the difference between an #F_NAME parameter and a #EMPLOYEE temporary table and not set the flag in the temporary table case
update EMPLOYEE set F_NAME to #F_NAME 
from
update EMPLOYEE set F_NAME to 'test' where ((EMP_ID = 1) and (EMP_ID in (select EMP_ID from #EMPLOYEE))

>What about a mixed query that has both parameters and custom tables?
update EMPLOYEE set F_NAME to #F_NAME where ((EMP_ID = 1) and (EMP_ID in (select EMP_ID from #EMPLOYEE))

Comment 3 Michael OBrien 2010-09-14 17:28:10 EDT

>Issue: SQLCall needs a possible subclass or a release from being classified as a "Call" in the temporary table case
Since the call on the CallQueryMechanism is an SQLCall - by definition "A call is an SQL string with parameters." it has parameters in the SQL string - but this one does not.
Therefore we need an SQLCall without parameters - as the translation code assumes that # is only for stored procedures

[EL Warning]: 2010-09-14 14:51:03.222--ClientSession(20337504)--Thread(Thread[main,5,main])--named_argument_not_found_in_query_parameters (There is no English translation for this message.)
<fixed message for bug# 325170 below>
[EL Warning]: 2010-09-14 15:16:02.052--ClientSession(20796783)--Thread(Thread[main,5,main])--Missing Query parameter for named argument: DPAR_SPROCUNIT null will be substituted.
SQLCall(DatasourceCall).getValueForInParameter(Object, AbstractRecord, AbstractRecord, AbstractSession, boolean) line: 791	
>or the translation code needs to work with a navigable tree and not just character based indexes so we will know if the # is in the context of a "from" subquery
update
 +-- in
     +-- select from #

>Here is where the ? is written
Thread [main] (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: 541	
	DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord) line: 502	
	DataModifyQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 703	
	DataModifyQuery(DatabaseQuery).executeInUnitOfWork(UnitOfWorkImpl, AbstractRecord) line: 675	
	RepeatableWriteUnitOfWork(UnitOfWorkImpl).internalExecuteQuery(DatabaseQuery, AbstractRecord) line: 2914	
	RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord, int) line: 1301	
	RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord) line: 1283	
	RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, List) line: 1257	
	EJBQueryImpl<X>.executeUpdate() line: 534	
	GridClient.nativeQuery() line: 107	
	GridClient.main(String[]) line: 329	


>Experimental fix result
>before
Query: DataModifyQuery(sql="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 ?)))")
>after
Query: DataModifyQuery(sql="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_SPROCUNIT)))")

>using new
   // 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;
    }

>and modified checkPrepare
    if ((isReadQuery() || isDataModifyQuery()) && isCallQuery() && (getQueryMechanism() instanceof CallQueryMechanism) 
       && ((translationRow == null) || translationRow.isEmpty())
       && !isTemporaryTableQuery()) {
       // Must check for read object queries as the row will be
       // empty until the prepare.
       if (isReadObjectQuery() || isUserDefined()) {
            ((CallQueryMechanism) getQueryMechanism()).setCallHasCustomSQLArguments();
       }
    } else if (isCallQuery() && (getQueryMechanism() instanceof CallQueryMechanism) 
       && !isTemporaryTableQuery()) {
       ((CallQueryMechanism) getQueryMechanism()).setCallHasCustomSQLArguments();
    }

Comment 4 Michael OBrien 2010-09-14 17:32:07 EDT

Created attachment 178884 [details]
experimental non-call pattern-matching only for "from #" case so far, to preempt userDefined being set to true

Comment 5 Michael OBrien 2010-09-23 07:29:31 EDT

See also square bracket [] fix for bug # 260265 (a child of bug # 299418) for stored procedure parameters - which is related
See additional forum post on this temporary table issue
http://www.dotnetspark.com/links/15292-error-when-using-sql-server-temporary-tables.aspx

Comment 6 Michael OBrien 2010-09-23 07:43:51 EDT

>directly related MSDN post to be answered when this is fixed
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/40bb4a45-af38-4aba-92e2-00da9616d35b
>Alternate fix is to delimit with [] brackets - just as in one of the fixes for bug# 260263 where we ended up using @
>see comment 6
https://bugs.eclipse.org/bugs/show_bug.cgi?id=260263#c6

this is Alt # 5
http://wiki.eclipse.org/EclipseLink/Bugs/325167#Alternative_5:_Delimit_temporary_table_with_Square_brackets_like_260263

Comment 7 Michael OBrien 2010-09-23 08:02:02 EDT

>Alt 5: [] 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 [
(SELECT PROCUNIT_ID FROM [?)))

Comment 8 Michael OBrien 2010-10-15 10:55:14 EDT

>5962622: This issue should have been in 2.1

Comment 9 Michael OBrien 2010-10-26 10:00:17 EDT

>See non-blocking bug# 328717 that may affect or be affected by a change here

Comment 10 Michael OBrien 2010-10-31 17:03:48 EDT

Created attachment 182121 [details]
325167 Alternative #7 use query hint to override default hash bind parameter char - pre code_review

Comment 11 Michael OBrien 2010-10-31 17:13:26 EDT

>Alternative #7 test results
http://wiki.eclipse.org/EclipseLink/Bugs/325167#Alternative_7:_Use_a_query_hint_to_change_the_default_bind_parameter_hash_char

>The following results show % binding and # pass-through on the Natvie SQL query
Query: UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = %anid) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM #DPAR_SCPROCUNIT)))

>with the following client change

     aQuery.setParameter("anid", 32);
     aQuery.setHint(QueryHints.PARAMETER_DELIMITER, "%");

Call: UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = 32) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM #DPAR_SCPROCUNIT)))
Query: DataModifyQuery(sql="UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = ?) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM #DPAR_SCPROCUNIT)))")

Comment 12 Michael OBrien 2010-10-31 22:18:09 EDT

Essentially, this change involves making the currently hardcoded # delimiter - user definable into something like % for example

Comment 13 Michael OBrien 2010-11-01 20:02:35 EDT

Created attachment 182181 [details]
325167 Alternative #7 use query hint to override default hash bind parameter char - pre code_review

Comment 14 Michael OBrien 2010-11-05 10:26:47 EDT

Created attachment 182475 [details]
325167 Alternative #7 use query hint to override default hash bind parameter char

Comment 15 Michael OBrien 2010-11-08 09:57:27 EST

Created attachment 182619 [details]
325167 Alternative #7 use query hint to override default hash bind parameter char (SQLCall removed - comments only)

>Updated patch (SQLCall had no mofifications)
- will merge with 2.1.2/2.1.3 when the stream opens shortly

Comment 16 Michael OBrien 2010-11-09 14:37:09 EST

>core regression results
    [junit] Tests run: 7813, Failures: 0, Errors: 0, Time elapsed: 1,807.902 sec

Comment 17 Michael OBrien 2010-11-10 09:33:30 EST

Created attachment 182814 [details]
2.1 stream : 325167 Alternative #7 use query hint to override default hash bind parameter char (comment change)

Comment 18 Michael OBrien 2010-11-10 09:34:16 EST

Created attachment 182815 [details]
2.2 stream : 325167 Alternative #7 use query hint to override default hash bind parameter char (comment change)

Comment 19 Michael OBrien 2010-11-12 11:37:51 EST

>Checked into trunk (2.2) under SVN rev# 8493 before checking into (2.1) in a future approved TXN
https://fisheye2.atlassian.com/changelog/eclipselink/?cs=8493

>Note: we do not run nightly HSQL testing but regression testing on other DB will be done on the trunk code as part of automated nightly testing.

>2.1 core LRG results
    [junit] Tests run: 7757, Failures: 0, Errors: 0, Time elapsed: 1,859.864 sec
    [junit] Tests run: 7757, Failures: 0, Errors: 0, Time elapsed: 1,899.747 sec
>2.1 clean view results
<testsuite errors="101" failures="7" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2461" time="1410.291" timestamp="2010-11-10T16:17:03">
>2.1 modified view results
<testsuite errors="104" failures="8" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2461" time="1576.638" timestamp="2010-11-11T14:23:35">
>2.2 trunk core LRG results
    [junit] Tests run: 7813, Failures: 0, Errors: 0, Time elapsed: 1,892.677 sec
>2.2 trunk JPA results
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2172" time="1480.224" timestamp="2010-11-10T14:49:39">

Comment 20 Michael OBrien 2010-12-15 09:03:01 EST

>closing until the 2.1 transaction is approved for 2.1.3 or later.
A 2.1 view is merged and ready for commit if this bug is reopened in the future.
>Currently "trunk" has all the changes since rev 8493.

Comment 21 Michael OBrien 2011-01-19 11:01:44 EST

>This patch does not address the following issues
- we in the past are marking both dynamic and static queries with setIsUserDefined(true) - we should only be doing this for static queries

>The patch allows parameter pass through but does not fix the other issue above with dynamic queries having the userdefined flag set

Comment 22 Eclipse Webmaster 2022-06-09 10:35:33 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink