Bug 572631 - ORA-00933 on choosing from Resources menu
Summary: ORA-00933 on choosing from Resources menu
Status: CLOSED CURRENTRELEASE
Alias: None
Product: RHQ Project
Classification: Other
Component: Core UI   
(Show other bugs)
Version: 3.0.0
Hardware: All
OS: Linux
high
high vote
Target Milestone: ---
: ---
Assignee: RHQ Project Maintainer
QA Contact: John Sefler
URL:
Whiteboard:
Keywords:
Depends On:
Blocks: jon-sprint11-bugs
TreeView+ depends on / blocked
 
Reported: 2010-03-11 18:12 UTC by Thilo Solbrig
Modified: 2010-08-12 16:48 UTC (History)
2 users (show)

Fixed In Version: 2.4
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2010-08-12 16:48:49 UTC
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

Description Thilo Solbrig 2010-03-11 18:12:40 UTC
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...)

Comment 1 Charles Crouch 2010-05-26 03:45:57 UTC
Corey, can you confirm this is no longer a problem

Comment 2 Joseph Marques 2010-06-14 15:12:06 UTC
Pushing to ON_QA so they know to test it.

Comment 3 Rajan Timaniya 2010-06-15 08:47:08 UTC
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

Comment 4 Rajan Timaniya 2010-06-15 08:50:01 UTC
Verified on JON build #210 with Oracle 10g/11g

Comment 5 Corey Welton 2010-08-12 16:48:49 UTC
Mass-closure of verified bugs against JON.


Note You need to log in before you can comment on or make changes to this bug.