3 Replies - 1108 Views - Last Post: 29 April 2013 - 02:45 AM Rate Topic: -----

#1 pistolizedpete  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 02-December 08

ERROR: more than one row returned by a subquery used as an expression

Posted 26 April 2013 - 06:10 AM

Hi,

I have a Postgres database with an "hours_extract" view as shown below:

SELECT aoext."DESCRIPTION" AS custname, COALESCE(( SELECT aoext."INVOICING_COUNTRY_ID"::text AS name
           FROM "AO_19CA43_PROJECT_EXT" aoext), 'PL'::text) AS invoicingcountry, COALESCE(( SELECT aoext."PROJECT_TYPE_ID"::text AS name
           FROM "AO_19CA43_PROJECT_EXT" aoext), 'Time and Materials'::text) AS projecttype, COALESCE(( SELECT aoext."TIME_ESTIMATION" AS ival
           FROM "AO_19CA43_PROJECT_EXT" aoext), 0::numeric) AS projectestimate, "substring"(aoext."ENRICHED_OBJECT_KEY"::text, 1, 50) AS pkey, aoext.project_id AS pid, aoext."PROJECT_NAME" AS projname, "substring"(aoext."PROJECT_LEAD"::text, 1, 50) AS projresp, COALESCE(( SELECT cfv.stringvalue
           FROM customfieldvalue cfv, customfield cf
          WHERE cfv.issue = j.id AND cf.id = cfv.customfield AND (cf.cfname::text = 'Billing'::text OR cf.cfname::text = 'billing'::text)
         LIMIT 1), 'Miscellaneous'::character varying) AS billing, xx.mainissue AS issueid, j.pkey AS issuekey, "substring"(j.summary::text, 1, 255) AS issuedesc, j.created AS issuecreated, round(j.timeoriginalestimate / 3600::numeric, 2) AS estimate, xx.workerid, xx.registered, xx.spent, xx.free
   FROM jiraissue j, "AO_19CA43_PROJECT_EXT" aoext, ( SELECT zz.mainissue, zz.workerid, zz.registered, sum(
                CASE
                    WHEN zz.mainissue = zz.id THEN zz.spent
                    WHEN zz.mainissue <> zz.id AND "substring"(lower(zz.billing::text), 1, 14) <> 'free of charge'::text THEN zz.spent
                    ELSE 0::numeric
                END) AS spent, sum(
                CASE
                    WHEN zz.mainissue <> zz.id AND "substring"(lower(zz.billing::text), 1, 14) = 'free of charge'::text THEN zz.spent
                    ELSE 0::numeric
                END) AS free
           FROM ( SELECT ji.id, COALESCE(( SELECT cfv.stringvalue
                           FROM customfieldvalue cfv, customfield cf
                          WHERE cfv.issue = ji.id AND cf.id = cfv.customfield AND (cf.cfname::text = 'Billing'::text OR cf.cfname::text = 'billing'::text)
                         LIMIT 1), 'Miscellaneous'::character varying) AS billing, "substring"(yy.workerid::text, 1, 50) AS workerid, COALESCE(( SELECT il.source
                           FROM issuelink il, issuelinktype ilt
                          WHERE il.destination = ji.id AND il.linktype = ilt.id AND ilt.linkname::text = 'jira_subtask_link'::text), ji.id) AS mainissue, yy.registered, yy.spent
                   FROM jiraissue ji, "AO_19CA43_PROJECT_EXT" aoext, ( SELECT wl.issueid, wl.author AS workerid, wl.startdate::date AS registered, round(sum(wl.timeworked) / 3600::numeric, 2) AS spent
                           FROM worklog wl
                          WHERE wl.created > to_date('30042009'::text, 'ddmmyyyy'::text)
                          GROUP BY wl.issueid, wl.author, wl.startdate) yy
                  WHERE ji.project = aoext.project_id AND yy.issueid = ji.id) zz
          GROUP BY zz.mainissue, zz.workerid, zz.registered) xx
  WHERE j.id = xx.mainissue AND j.project = aoext.project_id;



When I run the following query:

select distinct custname, 
	pkey as projid,
	projname, 
	projresp, 
	'https://example.com/jira/browse/'  || issuekey as issue, 
	issuedesc, 
	issuecreated
from 	iboj.hours_extract
where 	spent > 0 and registered between to_date('01012011','ddmmyyyy') and to_date('01122011','ddmmyyyy') 
and 'Miscellaneous' = billing 
and custname != 'EXAMPLE_CUST'
and projecttype != 'Fixed price'
order by custname,projresp, projname



I get an error stating one of the subqueries is returning more than one row. I'm having trouble figuring out which
subquery is returning multiple rows...

When I run the same query in this view:

 SELECT pe.customid AS custname, COALESCE(( SELECT pcfo.name::text AS name
           FROM prj_customfieldvalue pcfv, prj_customfieldoption pcfo, prj_customfield pcf
          WHERE pcf.name::text = 'InvoicingCountry'::text AND pcf.id = pcfv.customfieldid AND pcfv.projectid = p.id AND pcfv.optionvalue = pcfo.id), 'PL'::text) AS invoicingcountry, 
COALESCE(( SELECT pcfo.name::text AS name
           FROM prj_customfieldvalue pcfv, prj_customfieldoption pcfo, prj_customfield pcf
          WHERE pcf.name::text = 'ProjectType'::text AND pcf.id = pcfv.customfieldid AND pcfv.projectid = p.id AND pcfv.optionvalue = pcfo.id), 'Time and material'::text) AS projecttype, 
COALESCE(( SELECT pcfv.intvalue AS ival
           FROM prj_customfieldvalue pcfv, prj_customfield pcf
          WHERE pcf.name::text = 'TimeEstimation'::text AND pcf.id = pcfv.customfieldid AND pcfv.projectid = p.id), 0::numeric) AS projectestimate, "substring"(p.pkey::text, 1, 50) AS pkey, p.id AS pid, p.pname AS projname, "substring"(p.lead::text, 1, 50) AS projresp, 
COALESCE(( SELECT cfv.stringvalue
           FROM customfieldvalue cfv, customfield cf
          WHERE cfv.issue = j.id AND cf.id = cfv.customfield AND (cf.cfname::text = 'Billing'::text OR cf.cfname::text = 'billing'::text)
         LIMIT 1), 'Miscellaneous'::character varying) AS billing, xx.mainissue AS issueid, j.pkey AS issuekey, "substring"(j.summary::text, 1, 255) AS issuedesc, j.created AS issuecreated, round(j.timeoriginalestimate / 3600::numeric, 2) AS estimate, xx.workerid, xx.registered, xx.spent, xx.free
   FROM jiraissue j, project p, projectext pe, ( SELECT zz.mainissue, zz.workerid, zz.registered, sum(
                CASE
                    WHEN zz.mainissue = zz.id THEN zz.spent
                    WHEN zz.mainissue <> zz.id AND "substring"(lower(zz.billing::text), 1, 14) <> 'free of charge'::text THEN zz.spent
                    ELSE 0::numeric
                END) AS spent, sum(
                CASE
                    WHEN zz.mainissue <> zz.id AND "substring"(lower(zz.billing::text), 1, 14) = 'free of charge'::text THEN zz.spent
                    ELSE 0::numeric
                END) AS free
           FROM ( SELECT ji.id, COALESCE(( SELECT cfv.stringvalue
                           FROM customfieldvalue cfv, customfield cf
                          WHERE cfv.issue = ji.id AND cf.id = cfv.customfield AND (cf.cfname::text = 'Billing'::text OR cf.cfname::text = 'billing'::text)
                         LIMIT 1), 'Miscellaneous'::character varying) AS billing, "substring"(yy.workerid::text, 1, 50) AS workerid, COALESCE(( SELECT il.source
                           FROM issuelink il, issuelinktype ilt
                          WHERE il.destination = ji.id AND il.linktype = ilt.id AND ilt.linkname::text = 'jira_subtask_link'::text), ji.id) AS mainissue, yy.registered, yy.spent
                   FROM jiraissue ji, project p, projectext pe, ( SELECT wl.issueid, wl.author AS workerid, wl.startdate::date AS registered, round(sum(wl.timeworked) / 3600::numeric, 2) AS spent
                           FROM worklog wl
                          WHERE wl.created > to_date('30042009'::text, 'ddmmyyyy'::text)
                          GROUP BY wl.issueid, wl.author, wl.startdate) yy
                  WHERE ji.project = p.id AND yy.issueid = ji.id AND pe.id = p.id) zz
          GROUP BY zz.mainissue, zz.workerid, zz.registered) xx
  WHERE j.id = xx.mainissue AND j.project = p.id AND pe.id = p.id;



It all runs correctly and the only difference between the views is that the data from the project table and the projectext table have been merged into one "AO_19CA43_PROJECT_EXT" table.

Trying to analyze the queries is semi-useless because it will only return analysis for the working query.

Any way I can figure out what subquery is causing this?

Thanks in advance.

This post has been edited by pistolizedpete: 26 April 2013 - 06:12 AM


Is This A Good Question/Topic? 0
  • +

Replies To: ERROR: more than one row returned by a subquery used as an expression

#2 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,214
  • Joined: 23-August 08

Re: ERROR: more than one row returned by a subquery used as an expression

Posted 26 April 2013 - 06:39 AM

Have you tried running each of COALESCEd subqueries on its own? If one of them returns more than one result, that would seem to be the culprit.

EDIT: Just a shot in the dark...may not be possible, but that is a fairly complicated view; I don't envy debugging it.
Was This Post Helpful? 1
  • +
  • -

#3 pistolizedpete  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 02-December 08

Re: ERROR: more than one row returned by a subquery used as an expression

Posted 26 April 2013 - 07:13 AM

Tell me about ti Jack! I've been racking my brain for nearly two days now.
I've tried running the COALESCEd subqueries independently but it doesn't seem possible to do this for some... ie the ones with complicated FROM and/or WHERE clauses.

Essentially though, all that has changed between views is the p.id = pe.id WHERE clauses have been removed as those 2 tables have been merged and now each id is unique so I don't know why this should be a problem :(
Was This Post Helpful? 0
  • +
  • -

#4 pistolizedpete  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 02-December 08

Re: ERROR: more than one row returned by a subquery used as an expression

Posted 29 April 2013 - 02:45 AM

Here are two query plans for the first coalesced subquery. They produce the same set of data
but I'm not completely sure what all of the information here means.

Can you see a reason that the first would not return the aforementioned multiple rows error and the second would?

Nested Loop  (cost=1.05..17.16 rows=1 width=516) (actual time=0.156..1.687 rows=206 loops=1)
   ->  Nested Loop  (cost=1.05..16.84 rows=1 width=536) (actual time=0.151..1.026 rows=215 loops=1)
         ->  Hash Join  (cost=1.05..15.95 rows=3 width=40) (actual time=0.135..0.466 rows=215 loops=1)
               Hash Cond: (pcfv.customfieldid = pcf.id)
               ->  Seq Scan on prj_customfieldvalue pcfv  (cost=0.00..12.45 rows=645 width=60) (actual time=0.011..0.094 rows=645 loops=1)
               ->  Hash  (cost=1.04..1.04 rows=1 width=20) (actual time=0.041..0.041 rows=1 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  Seq Scan on prj_customfield pcf  (cost=0.00..1.04 rows=1 width=20) (actual time=0.036..0.037 rows=1 loops=1)
                           Filter: ((name)::text = 'InvoicingCountry'::text)
         ->  Index Scan using pk_prj_customfieldoption on prj_customfieldoption pcfo  (cost=0.00..0.29 rows=1 width=536) (actual time=0.002..0.002 rows=1 loops=215)
               Index Cond: (id = pcfv.optionvalue)
   ->  Index Scan using pk_project on project p  (cost=0.00..0.30 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=215)
         Index Cond: (id = pcfv.projectid)
 Total runtime: 1.904 ms
(14 rows)



Seq Scan on "AO_19CA43_PROJECT_EXT" aoext  (cost=0.00..10.20 rows=20 width=516) (actual time=0.037..0.107 rows=206 loops=1)
 Total runtime: 0.189 ms
(2 rows)



EDIT: So it was indeed the COALESCEd subqueries that were causing the issue. The first 3 are unnecessary as the information they are trying to join is now all stored in the same table.

Thanks.

This post has been edited by pistolizedpete: 29 April 2013 - 06:06 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1