Bug 905872

Summary:	SW1.8 on pgsql: Errata search by CVE fails
Product:	[Community] Spacewalk	Reporter:	pierre.casenove
Component:	Server	Assignee:	Tomas Lestach <tlestach>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Red Hat Satellite QA List <satqe-list>
Severity:	high	Docs Contact:
Priority:	unspecified
Version:	1.8	CC:	jpazdziora
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2013-03-06 18:35:19 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	917805

Description pierre.casenove 2013-01-30 11:12:55 UTC

Description of problem:
Spacewalk raises an exception when trying to search an errata by CVE.
In the search field, I put the name of the CVE (for example CVE-2012-0611) and select "Search by CVE" but it fails with error "could not execute query".
In the catalina.out file, I've got the following error:
2013-01-23 16:32:27,138 [TP-Processor8] WARN  com.redhat.rhn.frontend.action.errata.ErrataSearchAction - Performing errata search
redstone.xmlrpc.XmlRpcFault: redstone.xmlrpc.XmlRpcFault:
--- The error occurred in com/redhat/satellite/search/db/errata_handler.xml.
--- The error occurred while applying a parameter map.
--- Check the listErrataByCVE-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = character varying



Version-Release number of selected component (if applicable): SW 1.8


How reproducible:
Go to errata search, select "Search by CVE", put the name of the  CVE, hit "Search" button.


  
Actual results: Error page "could not execute query"


Expected results: corresponding errata should be listed


Additional info: When searching errata by issue date and not specifying anything in the search field, the same error occurs.
The requests in file errata_handler.xml seems to be postgresql incompatible on this part: ps.id = #sessionId#

Comment 1 Jan Pazdziora (Red Hat) 2013-02-01 16:05:50 UTC

I've tried to do

-                ps.id = #sessionId# and
+                ps.id = #sessionId# + 0 and

but that gives me

org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying + integer

Comment 2 Jan Pazdziora (Red Hat) 2013-02-01 16:12:34 UTC

We can do

-        args.put("sessionId", sessionId.toString());
+        args.put("sessionId", sessionId);

but that fails with

put(java.lang.String,java.lang.String) in java.util.Map<java.lang.String,java.lang.String> cannot be applied to (java.lang.String,java.lang.Long)

but we can change all the Maps from Map<String, String> to Map<String, Object> and that seems to work.

Or we could define compatibility SQL function to_number to take string and return number, along the lines of Oracle's

SQL> select to_number('123') from dual ;

TO_NUMBER('123')
----------------
	     123

Which approach do you prefer, Tomáš?

Comment 3 Tomas Lestach 2013-02-04 09:44:13 UTC

Changing Map<String, String> to Map<String, Object> is the preferred way.
Jan, thank you for the great investigation!

spacewalk.git: 8d61315e4d1f4daced19660ac7aa1acb407766a4

Comment 4 Stephen Herr 2013-03-01 17:07:47 UTC

Marking bug as ON_QA since tonight's build of Spacewalk nightly is a release candidate for Spacewalk 1.9.

Comment 5 Stephen Herr 2013-03-06 18:35:19 UTC

Spacewalk 1.9 has been released.

https://fedorahosted.org/spacewalk/wiki/ReleaseNotes19