Bug 699842

Summary: ORA-00907: missing right parenthesis
Product: [Other] RHQ Project Reporter: Mike Foley <mfoley>
Component: DatabaseAssignee: Jay Shaughnessy <jshaughn>
Status: CLOSED CURRENTRELEASE QA Contact: Mike Foley <mfoley>
Severity: high Docs Contact:
Priority: urgent    
Version: 4.0.0.Beta2CC: hrupp, jshaughn, mazz
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: All   
Whiteboard:
Fixed In Version: 4.0.1 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2013-09-02 03:22:13 EDT Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Bug Depends On:    
Bug Blocks: 703268    
Attachments:
Description Flags
RHQ Log file showing SQL Exception
none
DEBUG level server logging to illuminate the issue none

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:
100%

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 10.2.0.1.0 - Production

From this:

http://www.dba-oracle.com/sf_ora_00907_missing_right_parenthesis.htm

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 10.2.0.3 and future versions of 11g"

See this:

http://www.anysql.net/doc/bug10203.html

specifically "10.2.0.3 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:

select count((m.TIME_STAMP, m.SCHEDULE_ID)) as c from RHQ_MEASUREMENT_DATA_TRAIT m

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 :	
Oracle
Database Product Version :	
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Database Driver Name :	
Oracle JDBC driver
Database Driver Version :	
10.2.0.5.0
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:

http://lists.jboss.org/pipermail/hibernate-issues/2010-April/022211.html

Investigating...
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 ...

And 

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.