Bugzilla – Full Text Bug Listing
|
Description
Michael OBrien
>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) >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)) >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(); } Created attachment 178884 [details]
experimental non-call pattern-matching only for "from #" case so far, to preempt userDefined being set to true
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 >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 >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 [?))) >5962622: This issue should have been in 2.1
>See non-blocking bug# 328717 that may affect or be affected by a change here
Created attachment 182121 [details]
325167 Alternative #7 use query hint to override default hash bind parameter char - pre code_review
>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)))") Essentially, this change involves making the currently hardcoded # delimiter - user definable into something like % for example Created attachment 182181 [details]
325167 Alternative #7 use query hint to override default hash bind parameter char - pre code_review
Created attachment 182475 [details]
325167 Alternative #7 use query hint to override default hash bind parameter char
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 >core regression results
[junit] Tests run: 7813, Failures: 0, Errors: 0, Time elapsed: 1,807.902 sec
Created attachment 182814 [details]
2.1 stream : 325167 Alternative #7 use query hint to override default hash bind parameter char (comment change)
Created attachment 182815 [details]
2.2 stream : 325167 Alternative #7 use query hint to override default hash bind parameter char (comment change)
>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"> >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. >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 The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink |