Comment 1 Yiping Zhao 2010-09-23 15:44:21 EDT

Note:
(1). Verified that the tests passed with revision 8176 and failed with 8177, that means James's changes are the cause.
(2). The changes caused test failure of testDeleteEmployee() in jpa/advanced model on DB2 V9.7 universal driver, it doesn't fail with same DB2 when using datadirect driver.

The error:

Only 13 sql statements should have occured:16 

junit.framework.AssertionFailedError: Only 13 sql statements should have occured:16
at org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite.testDeleteEmployee(EntityManagerJUnitTestSuite.java:2718)
at org.eclipse.persistence.testing.framework.junit.JUnitTestCase.runBare(JUnitTestCase.java:466)
at org.eclipse.persistence.testing.framework.TestExecutor.execute(TestExecutor.java:248)
at org.eclipse.persistence.testing.framework.TestModel.execute(TestModel.java:208)
at org.eclipse.persistence.testing.framework.TestCollection.run(TestCollection.java:313)

Comment 2 Michael OBrien 2010-11-10 11:47:59 EST

>investigation starting in 24h

Comment 3 Michael OBrien 2010-11-12 16:25:32 EST

>Triaged: 1 Error is specific to IBM DB2 9.7 (Universal driver) in SE - not related to the Spring framework itself

I reproduced the error using the following SE config in the testing browser
org.eclipse.persistence.platform.database.DB2Platform
com.ibm.db2.jcc.DB2Driver
jdbc:db2://**046.**.**.com:50000/TOPLINK2
dev1/**
C:\opt\jdbc_drivers\IBM-DB2\9.7\db2jcc4.jar
C:\opt\jdbc_drivers\IBM-DB2\9.7\db2jcc_license_cu.jar
	Database: DB2/NT64  Version: SQL09070
	Driver: IBM Data Server Driver for JDBC and SQLJ  Version: 4.7.85

>stacktrace
TEST SUITE NAME: org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite
TEST NAME: testDeleteEmployee(org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite)
##FAILURE##
RESULT:      Error (failure)
junit.framework.AssertionFailedError: Only 13 sql statements should have occured:16
	at junit.framework.Assert.fail(Assert.java:47)
	at org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite.testDeleteEmployee(EntityManagerJUnitTestSuite.java:2747)

>Will verify that the other "no value for key" error is also non-Spring related
>before changing title from
jpa/fieldaccess-advanced tests in <test-spring-srg> failed 
>to
jpa/fieldaccess-advanced tests on DB2 9.7 Universal driver failed

Comment 4 Michael OBrien 2010-11-16 09:53:14 EST

>We get 7 errors, 1 failure on the SE JPA tests under DB2 including the testDeleteEmployee one
- I am goint to ignore the 7 errors as they are not currently part of the scope of this bug.

# IBM DB2 9.7 Universal driver
db.url=jdbc:db2://*.com:50000/*LINK2
db.user=*
db.password=*
db.pwd=*
jdbc.driver.jar=C:/opt/jdbc_drivers/IBM-DB2/9.7/db2jcc4.jar;C:/opt/jdbc_drivers/IBM-DB2/9.7/db2jcc_license_cu.jar
db.driver=com.ibm.db2.jcc.DB2Driver
db.platform=org.eclipse.persistence.platform.database.DB2Platform

>ant test-jpa
<testsuite errors="7" failures="1" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2172" time="466.922" timestamp="2010-11-15T16:35:48">

>removing reference to Spring and expanding scope to generic JPA
some jpa/fieldaccess-advanced tests in <test-spring-srg> failed
>to
JPA: fieldaccess-advanced/testDeleteEmployee fails in rev 8177 on DB2 9.7 Universal

>Also this test case requires more triage info, like
- Were the jpa results 0,0 without the universal driver?
- "some" is not defined - only one testDeleteEmployee is referenced
- possibly what part of the large transaction is at fault?
- We should not rely on Oracle specific testing links at the top of the bug, we need to rerelease the results in an open-source manner (also the stame02 url needs to be scrubbed before posting to a public forum)
- the servers referenced above have been transitioned and are no longer live

Comment 5 Michael OBrien 2010-11-16 11:33:08 EST

>Debug Triage:
The following line is failing on
org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite.testDeleteEmployee():2747

            if (isWeavingEnabled() && counter.getSqlStatements().size() > 13) {
                fail("Only 13 sql statements should have occured: " + counter.getSqlStatements().size());
            }

Testing Browser (JPA) [Java Application]	
	org.eclipse.persistence.testing.framework.ui.TestingBrowserFrame at localhost:52521	
		Thread [Thread-381] (Suspended)	
			QuerySQLTracker.getSqlStatements() line: 88	
			EntityManagerJUnitTestSuite.testDeleteEmployee() line: 2746	
	C:\jdk1.6.0_32bit\bin\javaw.exe (2010-11-16 10:54:10 AM)	

>because the 3 extra SQL statements in the QuerySQLTracker counter
[SELECT t1.EMP_ID, t2.EMP_ID, t1.F_NAME, t1.GENDER, t1.L_NAME, t1.PAY_SCALE, t1.ROOM_NUM, t2.SALARY, t1.STATUS, t1.VERSION, t1.START_TIME, t1.END_TIME, t1.START_OVERTIME, t1.END_OVERTIME, t1.FORMER_COMPANY, t1.FORMER_END_DATE, t1.FORMER_START_DATE, t1.END_DATE, t1.START_DATE, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.HUGE_PROJ_ID, t0.ID, t0.NAME, t0.DEPT_HEAD FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((t1.MANAGER_EMP_ID = ?) AND (t2.EMP_ID = t1.EMP_ID))
	bind => [1502], SELECT TYPE, AREA_CODE, OWNER_ID, NUMB FROM CMP3_PHONENUMBER WHERE (OWNER_ID = ?)
	bind => [1502], SELECT DEALER_ID, F_NAME, L_NAME, STATUS, VERSION FROM CMP3_DEALER WHERE (FK_EMP_ID = ?)
	bind => [1502], DELETE FROM CMP3_EMP_PROJ WHERE (EMPLOYEES_EMP_ID = ?)
	bind => [1502], DELETE FROM CMP3_PHONE_STATUS WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [1502, home], DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null, DELETE FROM CMP3_PHONENUMBER WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [1502, home], DELETE FROM CMP3_PHONE_STATUS WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [1502, fax], DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null, DELETE FROM CMP3_PHONENUMBER WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [1502, fax], DELETE FROM CMP3_RESPONS WHERE (EMP_ID = ?)
	bind => [1502], DELETE FROM Employee_WORKWEEK WHERE (EMP_ID = ?)
	bind => [1502], UPDATE CMP3_DEALER SET FK_EMP_ID = ? WHERE (FK_EMP_ID = ?)
	bind => [null, 1502], DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null, DELETE FROM CMP3_SALARY WHERE (EMP_ID = ?)
	bind => [1502], DELETE FROM CMP3_EMPLOYEE WHERE ((EMP_ID = ?) AND (VERSION = ?))
	bind => [1502, 1]]

>contain DB2 SQL warnings instead of statements
	bind => [1502, home], DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null, DELETE FROM CMP3_PHONENUMBER WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [1502, fax], DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null, DELETE FROM CMP3_PHONENUMBER WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [null, 1502], DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null, DELETE FROM CMP3_SALARY WHERE (EMP_ID = ?)

>Proposed fix: looks like QuerySQLTracker testing assistance tool needs to filter warnings
>and should we be concerned with the DB2 specific SQLCode=100 warnings

Comment 6 Michael OBrien 2010-11-17 16:06:11 EST

What is happening is that there is no differentiation in the session.sessionLog(QuerySQLTracker) 
between a warning and actual SQL.
Or we need to change .log() to .warning() in databaseAccessor.basicExecuteCall when logging warnings. 
Testing Browser (JPA) [Java Application]	
	org.eclipse.persistence.testing.framework.ui.TestingBrowserFrame at localhost:50020	
		Thread [Thread-3] (Suspended (breakpoint at line 61 in QuerySQLTracker))	
			QuerySQLTracker.log(SessionLogEntry) line: 61	
			IsolatedClientSession(AbstractSession).log(SessionLogEntry) line: 2665	
			IsolatedClientSession(AbstractSession).log(int, String, String, Object[], Accessor, boolean) line: 3777	
			DatabaseAccessor.basicExecuteCall(Call, AbstractRecord, AbstractSession) line: 668	

>We have 3 options
1) change the test to filter out sql tracking logs in the queries List that are actually warnings
- scope = testing framework only, this test only
2) change QuerySQLTracker to not add warning logs that are not actually SQL into the queries List
   By either subclassing the tracker, subclassing the SessionLogEntry or parsing the warning string.
- scope = testing framework only
3) change DatabaseAccessor.basicExecuteCall() to warn but not log SQL warnings
- scope = all of foundation/jpa

Analysis:
  Doing test-level changes in 1) and 2) are going to be difficult as we either need to parse the warning string or
add an override log(,,,boolean isWarning) method.
  The issue is only the calling function that is 3 levels lower in the stack at basicExecuteCall() knows that this is not a real SQL statement.
  To do 3) we could key on the fact that the SQLwarning is an instance of 
    com.ibm.db2.jcc.am.SqlWarning: DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85
  However I think it would be better to override log() with an added isWarningOverride boolean.
  This overridden function will only have 1 implementor in DatabaseAccessor so far, the rest use the default of false.

>running JPA/Core regression using the option 3 patch

Comment 7 Michael OBrien 2010-11-17 16:07:30 EST

Created attachment 183347 [details]
Option3: Filter sessionLog tracking of sql warnings with log() override function for DatabaseAccessor

Comment 8 Michael OBrien 2010-11-17 16:09:50 EST

>We still need to cover off (can we ignore the 3 DB2 warnings?) before closing this bug

Comment 9 Michael OBrien 2010-11-17 16:20:17 EST

>Error are gone from the JPA regression tests
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2174" time="508.903" timestamp="2010-11-17T21:05:31">

Comment 10 Michael OBrien 2010-11-17 16:33:10 EST

>Core
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.TestRunModel" tests="7521" time="756.129" timestamp="2010-11-17T21:17:26">

Comment 11 Michael OBrien 2010-11-17 16:45:17 EST

Waldorf:
I get the same 828,5,350 results as a clean view running DB2 9.7U - inconclusive in this case.

Comment 12 Michael OBrien 2010-11-19 09:20:48 EST

>In response to my c8 check on what the warnings are https://bugs.eclipse.org/bugs/show_bug.cgi?id=325605#c8

>The call
SQLCall(DELETE FROM CMP3_PHONE_STATUS WHERE ((OWNER_ID = ?) AND (TYPE = ?)))
>The SQLWarning (From the spec call statement.getWarnings())
com.ibm.db2.jcc.am.SqlWarning: DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85

>The delete returns with no rows and no exception - it emits a FINEST warning though
[EL Finest]: ClientSession(9862663)--Connection(9455352)--Thread(Thread[Thread-3,6,main])--DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null
>what does this warning mean?
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fddp%2Frbal1sqlc.htm
SQLCODE SQLSTATE Description 
+100 02000 This SQLSTATE reports a No Data exception warning due to an SQL operation on an empty table, zero rows identified in an SQL UPDATE or SQL DELETE statement, or the cursor in an SQL FETCH statement was after the last row of the result table. 
>It looks like this is warning that the delete had no effect 
- either there was no OWNER_ID=3300 CMP3_PHONE_STATUS entry or the table was already empty
>I will check if delete cascade behaviour was changed.

>debugging to track how many deletes we see of the Employee:3350 and its child objects...

    public void testDeleteEmployee() {        
        Employee employee = new Employee();
        employee.addPhoneNumber(new PhoneNumber("home", "123", "4567"));
        employee.addPhoneNumber(new PhoneNumber("fax", "456", "4567"));
        employee.addResponsibility("work hard"); 
        employee.addResponsibility("write code");
        employee.addProject(new Project());
        employee.setWorkWeek(new HashSet<Employee.Weekdays>());
        employee.getWorkWeek().add(Employee.Weekdays.MONDAY);
        employee.getWorkWeek().add(Employee.Weekdays.TUESDAY);
        QuerySQLTracker counter = new QuerySQLTracker(getServerSession());
        EntityManager em = createEntityManager();
        try {
            beginTransaction(em);
            em.persist(employee);
>2743        commitTransaction(em);
[EL Finer]: ClientSession(12033748)--Connection(10388370)--Thread(Thread[Thread-3,6,main])--begin transaction
[EL Finest]: UnitOfWork(10830160)--Thread(Thread[Thread-3,6,main])--Execute query InsertObjectQuery(Employee: 3350)
[EL Fine]: ClientSession(12033748)--Connection(10388370)--Thread(Thread[Thread-3,6,main])--INSERT INTO CMP3_PHONENUMBER (TYPE, AREA_CODE, NUMB, OWNER_ID) VALUES (?, ?, ?, ?)
	bind => [home, 123, 4567, 3350]

            closeEntityManager(em);
            clearCache();
            em = createEntityManager();
            beginTransaction(em);
>           employee = em.find(Employee.class, employee.getId());
[EL Fine]: ServerSession(30095183)--Connection(32317054)--Thread(Thread[Thread-3,6,main])--SELECT t1.EMP_ID, t2.EMP_ID, t1.F_NAME, t1.GENDER, t1.L_NAME, t1.PAY_SCALE, t1.ROOM_NUM, t2.SALARY, t1.STATUS, t1.VERSION, t1.START_TIME, t1.END_TIME, t1.START_OVERTIME, t1.END_OVERTIME, t1.FORMER_COMPANY, t1.FORMER_END_DATE, t1.FORMER_START_DATE, t1.END_DATE, t1.START_DATE, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.HUGE_PROJ_ID, t0.ID, t0.NAME, t0.DEPT_HEAD FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((t1.EMP_ID = ?) AND (t2.EMP_ID = t1.EMP_ID))
	bind => [3350]

            counter.getSqlStatements().clear();
>           em.remove(employee);
[EL Finest]: UnitOfWork(15678753)--Thread(Thread[Thread-3,6,main])--remove() operation called on: Employee: 3350
[EL Finest]: ServerSession(30095183)--Thread(Thread[Thread-3,6,main])--Execute query ReadAllQuery(name="managedEmployees" referenceClass=Employee )
[EL Fine]: ServerSession(30095183)--Connection(32317054)--Thread(Thread[Thread-3,6,main])--SELECT t1.EMP_ID, t2.EMP_ID, t1.F_NAME, t1.GENDER, t1.L_NAME, t1.PAY_SCALE, t1.ROOM_NUM, t2.SALARY, t1.STATUS, t1.VERSION, t1.START_TIME, t1.END_TIME, t1.START_OVERTIME, t1.END_OVERTIME, t1.FORMER_COMPANY, t1.FORMER_END_DATE, t1.FORMER_START_DATE, t1.END_DATE, t1.START_DATE, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.HUGE_PROJ_ID, t0.ID, t0.NAME, t0.DEPT_HEAD FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((t1.MANAGER_EMP_ID = ?) AND (t2.EMP_ID = t1.EMP_ID))
	bind => [3350]
[EL Finest]: UnitOfWork(15678753)--Thread(Thread[Thread-3,6,main])--Register the existing object PhoneNumber[fax]: (
[EL Finest]: UnitOfWork(15678753)--Thread(Thread[Thread-3,6,main])--Register the existing object Employee: 3350
[EL Finest]: UnitOfWork(15678753)--Thread(Thread[Thread-3,6,main])--remove() operation called on: PhoneNumber[fax]: (

>           commitTransaction(em);
[EL Finer]: UnitOfWork(15678753)--Thread(Thread[Thread-3,6,main])--begin unit of work commit
[EL Fine]: ClientSession(18424848)--Connection(10388370)--Thread(Thread[Thread-3,6,main])--DELETE FROM CMP3_PHONE_STATUS WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [3350, home]
[EL Fine]: ClientSession(18424848)--Connection(10388370)--Thread(Thread[Thread-3,6,main])--DELETE FROM CMP3_PHONE_STATUS WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [3350, home]
>[EL Finest]: ClientSession(18424848)--Connection(10388370)--Thread(Thread[Thread-3,6,main])--DB2 SQL Warning: SQLCODE=100, SQLSTATE=02000, SQLERRMC=null, DRIVER=4.7.85:02000 - null

            // We do not count any SQL warnings that may occur (DB2 may have 3)
            if (isWeavingEnabled() && counter.getSqlStatements().size() > 13) {
                fail("Only 13 sql statements should have occured: " + counter.getSqlStatements().size());
            }

>Result: It looks like CMP3_PHONE_STATUS is never written to before a delete is encountered
>This may have always been occurring but only warned about recently.

Comment 13 Michael OBrien 2010-11-19 12:09:46 EST

>It looks like our cascade delete is not accounting for an empty Collection of enums when the Collection is un-instantiated
>We are trying to delete from the enum table - entries matching the owner ID when there are no entries

>The warning is on a collection of enums - in Employee (PhoneNumber.status)
public class PhoneNumber implements Serializable {
    public enum PhoneStatus { ACTIVE, ASSIGNED, UNASSIGNED, DEAD }
    private Collection<PhoneStatus> status;

>Persistence Unit predeploy
[EL Config]: Thread(Thread[Thread-3,6,main])--The default table generator could not locate or convert a java type (class org.eclipse.persistence.testing.models.jpa.advanced.PhoneNumber$PhoneStatus) into a database type for database field (CMP3_PHONE_STATUS.STATUS). The generator uses java.lang.String as default java type for the field.

>DDL Generation
[EL Fine]: ServerSession(12487742)--Connection(33105463)--Thread(Thread[Thread-3,6,main])--DROP TABLE CMP3_PHONE_STATUS
[EL Finest]: ServerSession(12487742)--Thread(Thread[Thread-3,6,main])--Execute query DataModifyQuery(sql="CREATE TABLE CMP3_PHONE_STATUS (OWNER_ID BIGINT NOT NULL, TYPE VARCHAR(15) NOT NULL, STATUS VARCHAR(20) NOT NULL)")
[EL Fine]: ServerSession(12487742)--Connection(33105463)--Thread(Thread[Thread-3,6,main])--CREATE TABLE CMP3_PHONE_STATUS (OWNER_ID BIGINT NOT NULL, TYPE VARCHAR(15) NOT NULL, STATUS VARCHAR(20) NOT NULL)

>In the PhoneNumber class, the enum collection is not set (but we don't know if it is empty as well)
PhoneNumber[fax]: (
  {UnitOfWorkQueryValueHolder: not instantiated}

employee	Employee  (id=891)	
  m_phoneNumbers	IndirectList  (id=744)	
    attributeName	"phoneNumbers" (id=904)	
    delegate	Vector<E>  (id=905)	
      elementData	Object[2]  (id=922)	
        [0]	PhoneNumber  (id=924)	
          number	"4567" (id=933)	
          status	IndirectList  (id=935)	
            attributeName	"status" (id=938)	
>           delegate	null	
            valueHolder	UnitOfWorkQueryValueHolder  (id=942)	
              sourceAttributeName	"status" (id=938)	
              sourceObject	null	
>             value	null	

>Experiment 1 = OK: setting the status enum should get rid of the warning
        // set enum on 1 of the 2 phones, leave the other Collection of enums unset - but do a later find to instantiate the Collection
        homePhone.addStatus(PhoneNumber.PhoneStatus.ASSIGNED);


employee	Employee  (id=150)	
	m_phoneNumbers	Vector<E>  (id=186)	
		elementData	Object[10]  (id=208)	
			[0]	PhoneNumber  (id=165)	
				number	"4567" (id=213)	
				owner	Employee  (id=150)	
				status	Vector<E>  (id=214)	
					elementData	Object[10]  (id=216)	
						[0]	PhoneNumber$PhoneStatus  (id=217)	
							name	"ASSIGNED" (id=219)	
				type	"home" (id=215)	
			[1]	PhoneNumber  (id=167)	
				status	Vector<E>  (id=257)	
					elementData	Object[10]  (id=262)	

>the insert
[EL Fine]: ClientSession(12907672)--Connection(24168355)--Thread(Thread[Thread-3,6,main])--INSERT INTO CMP3_PHONE_STATUS (OWNER_ID, TYPE, STATUS) VALUES (?, ?, ?)
	bind => [3750, home, ASSIGNED]

>no warnings now on either the filled list and an instantiated list
[EL Fine]: ClientSession(31090991)--Connection(24168355)--Thread(Thread[Thread-3,6,main])--DELETE FROM CMP3_PHONE_STATUS WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [3750, home]
[EL Fine]: ClientSession(31090991)--Connection(24168355)--Thread(Thread[Thread-3,6,main])--DELETE FROM CMP3_PHONENUMBER WHERE ((OWNER_ID = ?) AND (TYPE = ?))
	bind => [3750, home]

Looks like we are blindly cascading the delete for an empty collection of enums

Discussed this with Peter - we cannot know the indirect list is empty unless we instantiate it
I will do a find in the test on one of the PhoneNumber(s) to verify we do not check empty collections when they are instantiated

>Experiment 2 = OK: instantiate the empty list to verify we do not try to cascade delete in this case
            employee = em.find(Employee.class, employee.getId());
            // instantiate the empty Collection of enums to verify we do not cascade delete if we "know" the Collection is empty
            employee.getPhoneNumbers();

>Results: All 1 level deep
1) an indirect (un-instantiated) Collection of enums will generate [empty record] warnings on DB2 because we currently do not check if the list is empty
2) an instantiated (but empty) Collection of enums will not generate [empty record] warnings on DB2 because we will not cascade the delete in this case
3) an instantiated and filled Collection of enums behaves normally without warnings in a cascade delete

Comment 14 Michael OBrien 2010-11-19 15:03:31 EST

>20101119:1430 Discussion with Peter, Tom and Andrei
We will add a new SQL_Warning category to the log instead of overloading the log() function, as an alternative way of flagging FINEST logs that are actually SQL warnings.
The QuerySQLTracker will only track the SQL category.
We will continue to log these SQL warnings as FINEST at the end of every basicExecuteCall() during the existing SQLWarning warning = statement.getWarnings(); call

Comment 15 Michael OBrien 2010-11-19 15:05:03 EST

>Just for reference is a definition of the DB2 warnings we are seeing
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fddp%2Frbal1sqlc.htm

SQLCODE SQLSTATE Description 
+100 02000 This SQLSTATE reports a No Data exception warning due to an SQL operation on an empty table, zero rows identified in an SQL UPDATE or SQL DELETE statement, or the cursor in an SQL FETCH statement was after the last row of the result table.

Comment 16 Michael OBrien 2010-11-23 09:21:58 EST

Created attachment 183667 [details]
Option 4 (in use): add new sessionLog SQL_WARNING category and use it in databaseAccessor.basicExecuteCall()

>JPA regression test results on DB2
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2176" time="474.703" timestamp="2010-11-22T21:36:35">

Comment 17 Michael OBrien 2010-11-23 10:17:31 EST

>Core regression test results under DB2 = OK (Same 5,0 as a clean view)
- unrelated errors are in "conforming" 
>Modified view
<testsuite errors="5" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.TestRunModel" tests="7521" time="799.473" timestamp="2010-11-23T14:18:24">


>Clean view
<testsuite errors="5" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.TestRunModel" tests="7521" time="738.041" timestamp="2010-11-23T14:44:59">

Comment 18 Michael OBrien 2010-11-23 11:19:42 EST

>merge with 8539 ok with same regression results
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2176" time="519.553" timestamp="2010-11-23T15:43:33">

>checked in to SVN rev# 8452
https://fisheye2.atlassian.com/changelog/eclipselink/?cs=8542
>Note: ConstantExpression for bug # 304512 was not submitted as part of this txn
- it can be ignored in the option 4 patch.
https://bugs.eclipse.org/bugs/attachment.cgi?id=183667&action=diff#foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions/ConstantExpression.java_sec1

>The name of the new category is SQL_WARNING
https://bugs.eclipse.org/bugs/attachment.cgi?id=183667&action=diff#foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/logging/SessionLog.java_sec2

Comment 19 Michael OBrien 2010-11-23 11:20:14 EST

>The name of the new category is SQL_WARNING
https://bugs.eclipse.org/bugs/attachment.cgi?id=183667&action=diff#foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/logging/SessionLog.java_sec2

Comment 20 Michael OBrien 2010-11-23 11:22:34 EST

https://fisheye2.atlassian.com/browse/eclipselink/trunk/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/logging/SessionLog.java?r1=8491&r2=8542

Comment 21 Michael OBrien 2010-11-23 14:51:14 EST

>FINE* logs that are not SQL Statements will also break the QuerySQLTracker like getObject
A new patch that does not track logs that are FINE* in QuerySQLTracker is required.

Comment 22 Michael OBrien 2010-11-23 15:49:37 EST

Created attachment 183711 [details]
325605 option 5: filter sessionLog tracking of FINE to FINEST logs that are the SQL category

>after code review by Doug and Peter

>Issues: now that we do not track non-SQL statements - we break other tests
Because tests such as testFetchGroups() expect the extra logs in error
These 20 tests need to be fixed as well.

Comment 23 Michael OBrien 2010-11-23 15:51:49 EST

>A patch file that fixes all the 20 failures that expect non-SQL Statement tracking is pending

Comment 24 Michael OBrien 2010-11-23 16:39:30 EST

    The category cutoff for "no SQL tracking" needs to be reduced to FINEST as 20 tests in the JPA suite rely on SQL statement tracking of at least the FINE level.  Or we can modify JPA/Core to log SQL Statements only above FINE.

    This patch is the same as the previous but with a single line change
-                && entry.getLevel() > SessionLog.FINE) {
+                && entry.getLevel() > SessionLog.FINEST) {

>for example jpql.testFetchGroups() logs at FINE == 3 which is not compatible with a filter cutoff at the FINE level
entry    SessionLogEntry  (id=1299)   
>    level    3   
    message    "SELECT t0.EMP_ID, t0.F_NAME, t0.L_NAME, t0.VERSION FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.EMP_ID = ?) AND (t1.EMP_ID = t0.EMP_ID))\r\n\tbind => [9557]" (id=1311)   
    nameSpace    "sql" (id=1314)   

Thread [Thread-1121] (Suspended (breakpoint at line 67 in QuerySQLTracker))   
    QuerySQLTracker.log(SessionLogEntry) line: 67   
    ServerSession(AbstractSession).log(SessionLogEntry) line: 2665   
    ServerSession(AbstractSession).log(int, String, String, Object[], Accessor, boolean) line: 3777   
    DatabaseAccessor.basicExecuteCall(Call, AbstractRecord, AbstractSession) line: 572   
    DatabaseAccessor.executeCall(Call, AbstractRecord, AbstractSession) line: 526   
    ServerSession.executeCall(Call, AbstractRecord, DatabaseQuery) line: 530   
    JPQLCallQueryMechanism(DatasourceCallQueryMechanism).executeCall(DatasourceCall) line: 206   
    JPQLCallQueryMechanism(DatasourceCallQueryMechanism).executeCall() line: 192   
    JPQLCallQueryMechanism(DatasourceCallQueryMechanism).selectOneRow() line: 664   
    JPQLCallQueryMechanism(ExpressionQueryMechanism).selectOneRowFromTable() line: 2603   
    JPQLCallQueryMechanism(ExpressionQueryMechanism).selectOneRow() line: 2574   
    ReadObjectQuery.executeObjectLevelReadQuery() line: 444   
    ReadObjectQuery(ObjectLevelReadQuery).executeDatabaseQuery() line: 1080   
    ReadObjectQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 782   
    ReadObjectQuery(ObjectLevelReadQuery).execute(AbstractSession, AbstractRecord) line: 1040   
    ReadObjectQuery.execute(AbstractSession, AbstractRecord) line: 412   
    ReadObjectQuery(ObjectLevelReadQuery).executeInUnitOfWork(UnitOfWorkImpl, AbstractRecord) line: 1126   
    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>.executeReadQuery() line: 484   
    EJBQueryImpl<X>.getSingleResult() line: 772   
    AdvancedQueryTestSuite.testFetchGroups() line: 292   


>results at FINEST
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2176" time="514.914" timestamp="2010-11-23T21:06:09">


    We can discuss this tomorrow.

Comment 25 Michael OBrien 2010-11-24 12:07:14 EST

Created attachment 183781 [details]
option 5: filter sessionLog tracking of [FINEST] logs that are the SQL category

>We have decided on the FINEST level - FINER could be filtered but may affect batch writing
>regression DB2 9.7U
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2176" time="514.914" timestamp="2010-11-23T21:06:09">

>regression Oracle 11 (same for clean and modified view)
<testsuite errors="0" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2176" time="587.754" timestamp="2010-11-24T16:10:37">
<testsuite errors="42" failures="0" hostname="xps435" name="org.eclipse.persistence.testing.tests.TestRunModel" tests="5162" time="541.526" timestamp="2010-11-24T16:39:46">

Comment 26 Michael OBrien 2010-11-24 12:15:14 EST

>See SVN rev# 8555
https://fisheye2.atlassian.com/changelog/eclipselink/?cs=8555

Comment 27 Michael OBrien 2010-11-29 10:59:27 EST

http://dev.eclipse.org/mhonarc/lists/eclipselink-dev/msg05216.html

Comment 28 Eclipse Webmaster 2022-06-09 10:20:20 EDT

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