Why my issue select run endless

[s.mallmann]

Hi @ all

I have a problem with my SQL Query.
If i insert the underlined row the statement run endless.
What i want is that i get a list with all issues and the value of selected custom field.

SELECT 
ji.id,
ji.issuenum AS "Incident ID",
issuestatus.pname AS "Incident Status",
ji.priority AS Priorität,
--ji.component AS "Kategorie betroffenes Produkt", --NV
--ji.component AS "Kategorie verursachendes Produkt", --NV
--(select customfieldvalue.textvalue from customfieldvalue where customfieldvalue.issue = ji.id and customfieldvalue.customfield = 10139)as Umgebung,
--(select customfieldvalue.textvalue from customfieldvalue  where customfieldvalue.issue = ji.id and customfieldvalue.customfield = 10133)as Sicherheitsrelevant,
--(select customfieldvalue.textvalue from customfieldvalue  where customfieldvalue.issue = ji.id and customfieldvalue.customfield = 10135)as Datenschutzrelevant,
(select cfv.stringvalue from customfieldvalue cfv, customfieldoption cfo where cfv.customfield = 10132 and cfv.customfield = cfo.customfield and cfv.stringvalue = cfo.id and customfieldvalue.issue = jiraissue.id) AS "TI-Notfall",
ji.reporter AS "Incident Melder",
ji.assignee AS "Incident Bearbeiter",
ji.summary AS "Incident Beschreibung",
DATE_FORMAT(ji.duedate, '%Y-%m-%dT%T%+02' )  AS "Zieltermin", --NV
resolution.pname  AS "Indicent Lösung"
FROM jiraissue ji join project on ji.project=project.ID 
join issuetype on ji.issuetype=issuetype.ID
join issuestatus on ji.issuestatus=issuestatus.ID
left outer join resolution on ji.resolution=resolution.ID

Please, can anybody help me.

Best regards,

Stephan

[Andy Heinzer]

I tried to run just that underlined select statement against my own Jira database. But I ran into some problems that might be helpful to cover here.  I started trying this in Postgressql, but then finished in mysql to confirm this:

1. First error was in regards to different field types.  This was in postgres, so maybe not exactly the same, but  It looks like you're trying to compare a string value to a numeric field.  Some flavors of SQL get upset with that:
   

   ERROR:  operator does not exist: character varying = numeric
   LINE 1: ...customfield = cfo.customfield and cfv.stringvalue = cfo.id a...
                                                                ^
   HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
   
   ********** Error **********
   
   ERROR: operator does not exist: character varying = numeric
   SQL state: 42883
   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
   Character: 161

2. I was able to get around that with using a CAST operator
3. Next I found a few inconsistencies in regards to which table/column are being referred to here.  Maybe that's a postgres difference, but either way, I tweaked this a bit and was able to get that subquery to work when it looks like this:
   
   

select cfv.stringvalue from customfieldvalue cfv, customfieldoption cfo, jiraissue ji 
where cfv.customfield = 10100 and 
cfv.customfield = cfo.customfield and 
CAST(cfv.stringvalue as SIGNED) = cfo.id and 
cfv.issue = ji.id

That is the specifically MySQL syntax.   I did change the customfield value in my test to 10100 just because that was a valid value in my system, so you'll have to change that back.  But I did test this syntax and it appears to work correctly with mysql.  I made sure that the jiraissues table is referred to as 'ji' and I also CAST that string value as a signed integer, which should be sufficient to then make the comparison of the other numeric field accurately.

I hope this helps.

[s.mallmann]

Hi, thank you very much for your great help and sorry for my late response.

If I run the statement alone everything is fine after changing the

select cfv.stringvalue   

 to

select cfo.customvalue 

but if i run this

SELECT
ji.issuenum AS "Incident ID",
issuestatus.pname AS "Incident Status",
ji.priority AS Priorität,
ji.component AS "Kategorie betroffenes Produkt",
ji.component AS "Kategorie verursachendes Produkt",
(select cfo.customvalue from customfieldvalue cfv, customfieldoption cfo, jiraissue ji where cfv.customfield = 10133 and cfv.customfield = cfo.customfield and CAST(cfv.stringvalue as SIGNED) = cfo.id and cfv.issue = ji.id) AS "TI-Notfall",
DATE_FORMAT(ji.created, '%Y-%m-%dT%T%+02' )  AS "Zieltermin",
ji.reporter AS "Incident Melder",
ji.assignee AS "Incident Bearbeiter",
ji.summary AS "Incident Beschreibung",
DATE_FORMAT(ji.duedate, '%Y-%m-%dT%T%+02' )  AS "Zieltermin",
resolution.pname  AS "Indicent Lösung"
FROM jiraissue ji join project on ji.project=project.ID
join issuetype on ji.issuetype=issuetype.ID
join issuestatus on ji.issuestatus=issuestatus.ID
left outer join resolution on ji.resolution=resolution.ID

 I get a error like this.

[Code: 1054, SQL State: 42S22]  Unknown column 'customfieldvalue.issue' in 'where clause'

I think the error is a little weird because customfieldvalue.issue or cfv.issue is a valid column.

Best regards,

Stephan

[s.mallmann]

Hi,

I fix the problem above by my self. But now i have the endless running statement as before.

...

I can't find a way out.

Please.. need help.

Thanks

Stephan