Description of problem: If choosing one of the first four entries in the resources menu, server raises exceptions and no resources are displayed. 18:53:58,254 WARN [JDBCExceptionReporter] SQL Error: 933, SQLState: 42000 18:53:58,255 ERROR [JDBCExceptionReporter] ORA-00933: SQL command not properly ended Version-Release number of selected component (if applicable): 3.0.0B03 How reproducible: Choose one of the first four entries from the resources menu. Steps to Reproduce: 1. 2. 3. Actual results: No resources are displayed. Expected results: Resources to display. Additional info: Problem is caused by an invalid SQL statement: SELECT COUNT(r.ID) AS target_cnt, COUNT(DISTINCT(t.name)) AS bare_type_cnt,COUNT(DISTINCT(t.name || '~!@#)))' || t .plugin)) AS full_type_cnt,COUNT(DISTINCT(r.name)) AS l1_cnt,COUNT(DISTINCT(r.name || '~!@#)))' || (CASE WHEN p1.n ame IS NULL THEN 'null' ELSE p1.name END))) AS l2_cnt,COUNT(DISTINCT(r.name || '~!@#)))' || (CASE WHEN p1.name IS NULL THEN 'null' ELSE p1.name END) || '~!@#)))' || (CASE WHEN p2.name IS NULL THEN 'null' ELSE p2.name END))) AS l3_cnt,COUNT(DISTINCT(r.name || '~!@#)))' || (CASE WHEN p1.name IS NULL THEN 'null' ELSE p1.name END) || '~!@#)))' || (CASE WHEN p2.name IS NULL THEN 'null' ELSE p2.name END) || '~!@#)))' || (CASE WHEN p3.name IS N ULL THEN 'null' ELSE p3.name END))) AS l4_cnt,COUNT(DISTINCT(r.name || '~!@#)))' || (CASE WHEN p1.name IS NULL THE N 'null' ELSE p1.name END) || '~!@#)))' || (CASE WHEN p2.name IS NULL THEN 'null' ELSE p2.name END) || ' ~!@#)))' || (CASE WHEN p3.name IS NULL THEN 'null' ELSE p3.name END) || '~!@#)))' || (CASE WHEN p4.name IS NU LL THEN 'null' ELSE p4.name END))) AS l5_cnt,COUNT(DISTINCT(r.name || '~!@#)))' || (CASE WHEN p1.name IS NULL THEN 'null' ELSE p1.name END) || '~!@#)))' || (CASE WHEN p2.name IS NULL THEN 'null' ELSE p2.name END) || '~ !@#)))' || (CASE WHEN p3.name IS NULL THEN 'null' ELSE p3.name END) || '~!@#)))' || (CASE WHEN p4.name IS NUL L THEN 'null' ELSE p4.name END) || '~!@#)))' || (CASE WHEN p5.name IS NULL THEN 'null' ELSE p5.name END))) AS l6_cnt,COUNT(DISTINCT(r.name || '~!@#)))' || (CASE WHEN p1.name IS NULL THEN 'null' ELSE p1.name END)|| '~!@ #)))' || (CASE WHEN p2.name IS NULL THEN 'null' ELSE p2.name END) || '~!@#)))' || (CASE WHEN p3.name IS NULL THEN 'null' ELSE p3.name END) || '~!@#)))' || (CASE WHEN p4.name IS NULL THEN 'null' ELSE p4.name END) || '~!@#)))' || (CASE WHEN p5.name IS NULL THEN 'null' ELSE p5.name END) || '~!@#)))' || (CASE WHEN p6.name IS NULL THEN 'null' ELSE p6.name END))) AS l7_cnt FROM RHQ_RESOURCE AS r JOIN RHQ_RESOURCE_TYPE AS t ON r.RESOURCE_T YPE_ID = t.ID LEFT OUTER JOIN RHQ_RESOURCE AS p1 ON r.PARENT_RESOURCE_ID = p1.ID LEFT OUTER JOIN RHQ_RESOURCE AS p 2 ON p1.PARENT_RESOURCE_ID = p2.ID LEFT OUTER JOIN RHQ_RESOURCE AS p3 ON p2.PARENT_RESOURCE_ID = p3.ID LEFT OUTER JOIN RHQ_RESOURCE AS p4 ON p3.PARENT_RESOURCE_ID = p4.ID LEFT OUTER JOIN RHQ_RESOURCE AS p5 ON p4.PARENT_RESOURCE_ ID = p5.ID LEFT OUTER JOIN RHQ_RESOURCE AS p6 ON p5.PARENT_RESOURCE_ID = p6.ID WHERE r.ID IN (:1,:2,:3,:4,:5,:6,:7 ,:8,:9,:10,:11,:12,:13,:14,:15) In Oracle, table aliases must not be prefixed with the "AS" keyword. This is only valid for column aliases. Remove the "AS" keywords and the statement works (e.g. old: ...JOIN RHQ_RESOURCE AS p6 ON... new: ...JOIN RHQ_RESOURCE p6 ON...)
Corey, can you confirm this is no longer a problem
Pushing to ON_QA so they know to test it.
Verified on JON build #210 with Oracle 11g Steps: 1) Install JON build #210 with Oracle 11g 2) Click on one bye one all first four entries in the resources menu 3) Observed server log There isn't observed any error/exception like: WARN [JDBCExceptionReporter] SQL Error: 933, SQLState: 42000 ERROR [JDBCExceptionReporter] ORA-00933: SQL command not properly ended
Verified on JON build #210 with Oracle 10g/11g
Mass-closure of verified bugs against JON.