Bug 699842 - ORA-00907: missing right parenthesis
ORA-00907: missing right parenthesis
Product: RHQ Project
Classification: Other
Component: Database (Show other bugs)
All All
urgent Severity high (vote)
: ---
: ---
Assigned To: Jay Shaughnessy
Mike Foley
Depends On:
Blocks: rhq401
  Show dependency treegraph
Reported: 2011-04-26 14:35 EDT by Mike Foley
Modified: 2013-09-02 03:22 EDT (History)
3 users (show)

See Also:
Fixed In Version: 4.0.1
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2013-09-02 03:22:13 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)
RHQ Log file showing SQL Exception (28.47 KB, application/octet-stream)
2011-04-26 14:35 EDT, Mike Foley
no flags Details
DEBUG level server logging to illuminate the issue (738.97 KB, application/octet-stream)
2011-04-27 11:56 EDT, Mike Foley
no flags Details

  None (edit)
Description Mike Foley 2011-04-26 14:35:25 EDT
Created attachment 495006 [details]
RHQ Log file showing SQL Exception

Description of problem:
SQL Exception with monitoring Apache Http server resource.

Version-Release number of selected component (if applicable):
RHQ 4.0 CR1

How reproducible:

Steps to Reproduce:
1.  Inventory --> Apache Http Server
2.  Monitoring -->  Traits
3.  Observe log file
Actual results:

Expected results:
SQL exception in RHQ log file due to invalid SQL, missing parenthesis

Additional info:
log files are attached.
Oracle database.
Comment 1 Mike Foley 2011-04-27 11:56:56 EDT
Created attachment 495282 [details]
DEBUG level server logging to illuminate the issue
Comment 2 John Mazzitelli 2011-04-27 13:59:35 EDT
I think this is an Oracle bug that shows up in my version of Oracle and probably Mike's. My version:

Oracle Database 10g Express Edition Release - Production

From this:


he states, "Oracle offers information regarding ORA-00907 in conjunction with FULL/LEFT OUTER joins in association with Bug 4433936.  This bug causing an ORA-00907 has been fixed in and future versions of 11g"

See this:


specifically " Bug Fixes by Category"

4433936 	ORA-907 from FULL / LEFT OUTER joins
Comment 3 John Mazzitelli 2011-04-27 14:02:35 EDT
here's a simple SQL to try:


this results in the ORA 907 exception "missing right parens"
Comment 4 Mike Foley 2011-04-27 14:05:13 EDT
documenting my oracle version:

Database Product Name :	
Database Product Version :	
Oracle Database 10g Express Edition Release - Production
Database Driver Name :	
Oracle JDBC driver
Database Driver Version :
Comment 5 Jay Shaughnessy 2011-04-27 14:33:13 EDT
I'm not sure if this is a bug or not, if it is I think it may be in Hibernate.  It has to do with the fact that we are using Criteria queries to pull the trait data.  The CriteriaQueryGenerator has a standard way of generating count queries like:

Select count(entity) From Entity entity Where ...

But because MeaasurementDataTrait extends MeasurmentData and MeasurementData has a composite key, this is translating into something unusual.  This is the root cause.  Now need to figure out what to do.
Comment 6 Jay Shaughnessy 2011-04-27 14:34:22 EDT
D'oh!  Looks like I was right, this is a Hibernate issue:


Comment 7 Jay Shaughnessy 2011-04-27 15:54:08 EDT
It seems like we could generate our count queries slightly differently to avoid this issue.  I don't think there is really a difference between:

Select Count( entity ) 
  From Entity entity
 Where ...


Select Count( * ) 
  From Entity entity
 Where ...

I made this change locally and things seem to be working as expected. Unless someone knows of any subtleties I think this is a valid workaround.
Comment 8 Jay Shaughnessy 2011-04-27 16:08:11 EDT
I pushed this potential workaround to master for further eval:

commit 15a53e58c10ba129ce0dd5b569aea1944de6836a
Author: Jay Shaughnessy <jshaughn@redhat.com>
Date:   Wed Apr 27 16:06:30 2011 -0400
Comment 9 John Mazzitelli 2011-05-10 12:33:15 EDT
cherry picked over to release-4.0.0 branch as commit: 64fd52bb1fb072f79f8e5f02793d11fcd31e6225
Comment 10 Mike Foley 2011-05-11 11:55:13 EDT
verified RHQ 4.0.1 by following the steps to reproduce.
Comment 11 Heiko W. Rupp 2013-09-02 03:22:13 EDT
Bulk closing of issues that were VERIFIED, had no target release and where the status changed more than a year ago.

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