Hide Forgot
Description of problem: When calling the 'getContainedParties' method on a c.a.kernel.Group object, an SQL syntax exception is thrown by Oracle. This has only started happening since test-qgen. Version-Release number of selected component (if applicable): How reproducible: Steps to Reproduce: 1. Create a group 2. Add some members 3. Call getContainedParties() Actual results: [junit] 2004-04-06 18:03:38,043 [ main] ERROR rdbms.RDBMSEngine - select t0.party_id as "this.id", [junit] t12.object_type as "this.objectType", [junit] t12.display_name as "this.displayName", [junit] t12.default_domain_class as "this.defaultDomainClass", [junit] t0.primary_email as "this.primaryEmail", [junit] t0.uri as "this.uri" [junit] from parties t0 [junit] join acs_objects t12 on t0.party_id = t12.object_id [junit] where t0.party_id in (select party_id from ( [junit] select member_id as party_id [junit] from group_member_map [junit] where group_id = ? [junit] UNION ALL [junit] select subgroup_id as party_id [junit] from group_subgroup_map [junit] where group_id = ?) insub) [junit] java.sql.SQLException: ORA-00923: FROM keyword not found where expected [junit] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180) [junit] at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:1759) [junit] at oracle.jdbc.oci8.OCIDBAccess.parseExecuteDescribe(OCIDBAccess.java:953) [junit] at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2110) [junit] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2324) [junit] at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:421) [junit] at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:486) [junit] at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:488) [junit] at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:430) [junit] at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:264) [junit] at com.redhat.persistence.Cursor.execute(Cursor.java:130) [junit] at com.redhat.persistence.Cursor.next(Cursor.java:114) [junit] at com.arsdigita.persistence.DataQueryImpl.next(DataQueryImpl.java:645) [junit] at com.arsdigita.domain.DomainQuery.next(DomainQuery.java:93) [junit] at com.arsdigita.kernel.GroupMembershipTest.testContainedParties(GroupMembershipTest.java:133) [junit] at java.lang.reflect.Method.invoke(Native Method) [junit] at junit.framework.TestCase.runTest(TestCase.java:154) [junit] at com.arsdigita.tools.junit.framework.BaseTestCase.runBare(BaseTestCase.java:46) [junit] at junit.framework.TestResult$1.protect(TestResult.java:106) [junit] at junit.framework.TestResult.runProtected(TestResult.java:124) [junit] at junit.framework.TestResult.run(TestResult.java:109) [junit] at junit.framework.TestCase.run(TestCase.java:118) [junit] at junit.framework.TestSuite.runTest(TestSuite.java:208) [junit] at junit.framework.TestSuite.run(TestSuite.java:203) [junit] at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) [junit] at junit.extensions.TestDecorator.run(TestDecorator.java:28) [junit] at com.arsdigita.tools.junit.extensions.PermissionDecorator.access$001(PermissionDecorator.java:31) [junit] at com.arsdigita.tools.junit.extensions.PermissionDecorator$1.excurse(PermissionDecorator.java:42) [junit] at com.arsdigita.kernel.KernelExcursion.run(KernelExcursion.java:56) [junit] at com.arsdigita.tools.junit.extensions.PermissionDecorator.run(PermissionDecorator.java:46) [junit] at junit.framework.TestSuite.runTest(TestSuite.java:208) [junit] at junit.framework.TestSuite.run(TestSuite.java:203) [junit] at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) [junit] at com.arsdigita.tools.junit.extensions.BaseTestSetup$1.protect(BaseTestSetup.java:73) [junit] at junit.framework.TestResult.runProtected(TestResult.java:124) [junit] at com.arsdigita.tools.junit.extensions.BaseTestSetup.run(BaseTestSetup.java:78) [junit] at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:325) [junit] at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:524) Expected results: No exception Additional info: This is a very critical show stopper bug preventing user/group administration working in APLAWS portal application.
I added a test case to GroupMemberTest in the KernelSuite at p4 42006.
Is there an oracle version number for this? The SQL seems to run fine in my sqlplus.
Yes, more investigation shows that it only affects certain oracle DBs. I have it failing on: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production Oracle9i Release 9.2.0.1.0 - Production But working on: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production Yes, it really does work & fail on different instances of 9.2.0.1.0, which is rather odd...
That's what I was afraid of. Does someone with a metalink account want to search or loan me the account info so I can search?
What versions of JDBC are you using on each of those installs? It's possible that the bug is in the JDBC driver and not oracle itself. You could also try executing the same query in sqlplus on each instance to see if we can reproduce it with no JDBC in the equation.
I've searched on Metalink & it appears to be a bug fixed in 9.2.0.2.0. The work around for earlier versions of oracle is: alter session set "_PUSH_JOIN_UNION_VIEW" = false; We had this workaround present in 5.2 in the OracleConnectionPoolImpl class, but its missing from Rickshaw.
The neccessary code appears to be present in c.a.util.jdbc.Connections: // XXX Use connection metadata to find out if this is the // bad oracle. Do we need to do this this often? //if (false) { // final PreparedStatement stmt = conn.prepareStatement // ("alter session set \"_push_join_union_view\" = false"); // stmt.execute(); //} We ought to be able to uncomment this & change 'if(false)' to DatabaseMetadata meta = conn.getMetaData(); if ("Oracle".equals(meta.getDatabaseProductName()) && (meta.getDatabaseProductVersion().indexOf("9.0.1") != -1 || meta.getDatabaseProductVersion().indexOf("9.2.0.1.0") != -1)) { .... }
Ah, interesting. I believe when we originally encountered the bug it resulted in an 0600 rather than the syntax error. I'll submit a fix.
This should be fixed as of @42010.
It occurs to me that the fact that the newly added test passes for me doesn't mean much since my version of oracle doesn't have the bug, so we should probably verify this fix on the same db that is known to exhibit the problem.
I've verified this fixes the problem on our unpatched Oracle DB, so marking it RAWHIDE.