How to force select to return exactly one row

Try wrapping the entire statement in a COALESCE((statement),
<DEFAULT_VALUE>);

-m

2010/6/21 Andrus <kobruleht2@hot.ee>

Martin,

Thank you. SELECT statement returns lot of columns.

I tried

select coalesce( (select 1,2 ), null);

but got

ERROR: subquery must return only one column

How to use your suggestion if select returns lot of columns ?

Andrus.

----- Original Message -----
From: Martin
To: Andrus
Cc: pgsql-general@postgresql.org
Sent: Monday, June 21, 2010 10:14 PM
Subject: Re: [GENERAL] How to force select to return exactly one row

Try wrapping the entire statement in a COALESCE((statement), <DEFAULT_VALUE>);

-m

2010/6/21 Andrus <kobruleht2@hot.ee>

Autogenerated select statement contains 0 .. n left joins:

SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue';

This select can return only 0 or 1 rows depending if ko row with primary key
'someprimarykeyvalue' exists or not.

Problem:

if there is no searched primary key row in ko database, select should also
return empty row.

To get this result I added right join:

SELECT somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';

but it still does not return row if primary key row 'someprimarykeyvalue'
does not exist.

How to force this statement to return one row always ?

Andrus.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Andrus Moor
OÜ Eetasoft
Akadeemia 21-G302
Tallinn 12618
www.eetasoft.ee
tel. 6654214, 6654215

"Andrus" <kobruleht2@hot.ee> wrote:

Autogenerated select statement contains 0 .. n left joins:

SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue';

This select can return only 0 or 1 rows depending if ko row with primary key
'someprimarykeyvalue' exists or not.

Problem:

if there is no searched primary key row in ko database, select should also
return empty row.

To get this result I added right join:

SELECT somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';

but it still does not return row if primary key row 'someprimarykeyvalue'
does not exist.

How to force this statement to return one row always ?

It's a bit difficult to decipher what you're looking for
(what do you mean by "empty row"?), but you may want to try
something along the lines of:

| SELECT v.primarykey, ko.somecolumns
| FROM (VALUES ('someprimarykeyvalue')) AS v (primarykey)
| LEFT JOIN ko ON v.primarykey = ko.primarykey
| LEFT JOIN t1 ON t1.primarykey = ko.t1foreignkey
| [...]
| LEFT JOIN tn ON tn.primarykey = ko.tnforeignkey;

Whether that suits your needs depends very much on the data
structure and the tools you use.

Tim

Ah yes sorry I missed the multi-columns. "My way" doesn't work for that.
If Tim's suggestion doesn't work for you, you could try a union...
it's fairly nasty and you will always have your "fake" row in the result.

Also I too am confused by "empty row". Are you trying to loop through the
results in code and it fails if there are no rows at all?
Or some other equally odd thing? =)

Anyway here is an example UNION that I think would work (but note, this row
will always be included even when your statement returns something, so it
might not work for you).

(YOUR SELECT HERE)
UNION
(SELECT '','',1,1,perfectly_matched_datatype_cols_here); --those first
couple are just examples

Mind you, I think this is nasty and would highly suggest taking another look
at the code that is using this statement to see if you can deal more
gracefully with an empty resultset.

hope this helps,
-m

On Mon, Jun 21, 2010 at 12:32 PM, Andrus <kobruleht2@hot.ee> wrote:

Use a case staement to test for a null output, & return whatever you want in the event of it being null, else the actual value:

from the top of my head, something like:

SELECT case when
(select somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue') not null

then (select somecolumns

FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue')

else
0
end

It does require the query to be run twice, so does have extra overhead. You could wrap a function around this to get & store the result & test that, then having stored it you can use it for the output value without a second query. All depends on how much overhead there is in teh query.

HTH,

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand

"Andrus" 06/22/10 10:12 AM >>>

Autogenerated select statement contains 0 .. n left joins:

SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue';

This select can return only 0 or 1 rows depending if ko row with primary key
'someprimarykeyvalue' exists or not.

Problem:

if there is no searched primary key row in ko database, select should also
return empty row.

To get this result I added right join:

SELECT somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';

but it still does not return row if primary key row 'someprimarykeyvalue'
does not exist.

How to force this statement to return one row always ?

Andrus.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.

How about:
SELECT * from (
SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue'
UNION ALL
SELECT default_value
)
LIMIT 1;

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrus
Sent: Tuesday, 22 June 2010 5:08 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to force select to return exactly one row

Autogenerated select statement contains 0 .. n left joins:

SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue';

This select can return only 0 or 1 rows depending if ko row with primary key
'someprimarykeyvalue' exists or not.

Problem:

if there is no searched primary key row in ko database, select should also
return empty row.

To get this result I added right join:

SELECT somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';

but it still does not return row if primary key row 'someprimarykeyvalue'
does not exist.

How to force this statement to return one row always ?

Andrus.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Brett Mc Bride <brett.mcbride@deakin.edu.au> wrote:

How about:
SELECT * from (
SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue'
UNION ALL
SELECT default_value
)
LIMIT 1;
[...]

... with a proper "ORDER BY" clause.

Tim

My understanding of UNION ALL is that it won't sort the rows...?

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tim Landscheidt
Sent: Tuesday, 22 June 2010 9:41 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to force select to return exactly one row

Brett Mc Bride <brett.mcbride@deakin.edu.au> wrote:

How about:
SELECT * from (
SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue'
UNION ALL
SELECT default_value
)
LIMIT 1;
[...]

... with a proper "ORDER BY" clause.

Tim

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Brett Mc Bride <brett.mcbride@deakin.edu.au> wrote:

My understanding of UNION ALL is that it won't sort the rows...?
[...]

It doesn't, but that's not promised for every data set, ev-
ery PostgreSQL version, every phase of the moon. To quote
<URI:http://www.postgresql.org/docs/8.4/interactive/queries-union.html&gt;:

| UNION effectively appends the result of query2 to the result
| of query1 (although there is no guarantee that this is the
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| order in which the rows are actually returned). Furthermore,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| it eliminates duplicate rows from its result, in the same
| way as DISTINCT, unless UNION ALL is used.

SQL deals with (unordered) sets, and therefore any use of
"LIMIT" without "ORDER BY" indicates a bug waiting to bite
you when you least expect it.

Tim

Martin,

Also I too am confused by "empty row". Are you trying to loop through the
results in code and it fails if there are no rows at all?

Or some other equally odd thing? =)

Anyway here is an example UNION that I think would work (but note, this row
will always be included even when your statement returns something, so it
might not work for you).
(YOUR SELECT HERE)
UNION

(SELECT '','',1,1,perfectly_matched_datatype_cols_here); --those first
couple are just examples

Mind you, I think this is nasty and would highly suggest taking another
look at the code that is using this statement to see if you can deal more
gracefully with an empty resultset.

Returned row is used to enter report parameters, search conditions etc. in
dialog forms where exactly one row should be
present always.
Code is simpler if it can assume that single row is always returned: in this
case it can generate only update statement.
Otherwize separate branch should check for insert or update clause. This
makes app code complicated.

I changed appl code to:

1. Execute original select statement.
2. If it returns no rows, add one row:

insert into ko (primarykey) ('primarykeyvalue');

3. Re-execute original select statement.

This requires 3 database calls from application and two times to execute
query.

How to implement this using single db call and execute query only once ?

if it possible to use

CREATE TEMP TABLE temp AS
original_select ON COMMIT DROP;

IF (SELECT COUNT(*) FROM temp) =0 THEN
INSERT INTO temp DEFAULT_VALUES;
ENDIF;

SELECT * FROM temp;

Andrus.

2010/6/21 Andrus <kobruleht2@hot.ee>:

if there is no searched primary key row in ko database, select should also
return empty row.

To get this result I added right join:

SELECT somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';

The reason this won't return a row if there is no matching PK is that
the WHERE clause is applied after all the joins to filter the overall
result set.

So to get what you want, you would need to re-arrange that to something like:

SELECT original_query.* FROM
( Original query including WHERE clause ) AS original_query
RIGHT JOIN (SELECT 1) AS one_row ON true;

Regards,
Dean

In 9.0 RC1 query below causes error

Variable not found in subplan target lists

In earlier version this query works OK.

How to fix ?

Andrus.

SELECT *
FROM (select 1) xtoode
LEFT JOIN (SELECT hmguid.toode,
COALESCE(hinnamtr.hinnak,klient.hinnak ) AS hinnak
FROM (
SELECT
hmhinnak.toode,
''::char as guid
FROM (

select ''::char as toode,
1 as prioriteet
) prioriteet

JOIN (

SELECT
''::char as toode,
''::text as guid,
1 as prioriteet
) hmhinnak

USING (toode,prioriteet)

) hmguid

JOIN (select ''::char as guid, ''::char as hinnak) hinnamtr USING (guid)
LEFT JOIN klient ON klient.kood=''
) temphinnaks on true;

Hey Andrus,

Did you tried it on 9.0 release?

--
// Dmitriy.

"Andrus" <kobruleht2@hot.ee> writes:

In 9.0 RC1 query below causes error
Variable not found in subplan target lists

Could we see a complete test case for this? No one can guess at the
tables, indexes, etc that might be needed to provoke the problem.

regards, tom lane

Could we see a complete test case for this? No one can guess at the
tables, indexes, etc that might be needed to provoke the problem.

Complete testcase is below.
Should I try 9.0 release or other idea how to fix without removing primary
key and keeping query structure so that query runs in all >=8.1 servers ?

Andrus.

create temp table klient (kood char(12) primary key, hinnak char(5)) on
commit drop;

SELECT *
FROM (select 1) xtoode
LEFT JOIN (SELECT hmguid.toode,
COALESCE(hinnamtr.hinnak,klient.hinnak ) AS hinnak
FROM (
SELECT
hmhinnak.toode,
''::char as guid
FROM (

select ''::char as toode,
1 as prioriteet
) prioriteet

JOIN (

SELECT
''::char as toode,
''::text as guid,
1 as prioriteet
) hmhinnak

USING (toode,prioriteet)

) hmguid

JOIN (select ''::char as guid, ''::char as hinnak) hinnamtr USING (guid)
LEFT JOIN klient ON klient.kood=''
) temphinnaks on true

"Andrus" <kobruleht2@hot.ee> writes:

Could we see a complete test case for this? No one can guess at the
tables, indexes, etc that might be needed to provoke the problem.

Complete testcase is below.

Thanks, I can reproduce it now.

Should I try 9.0 release

No, it's still busted in HEAD :-(. Probably won't be too hard to fix,
but I need to go find the bug.

regards, tom lane

"Andrus" <kobruleht2@hot.ee> writes:

Should I try 9.0 release

No, it's still busted in HEAD :-(. Probably won't be too hard to fix,
but I need to go find the bug.

Here's the patch if it helps.

regards, tom lane

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 5fc056e2a572db2299f2624f19e8988e671df034..1355c18317a7399e028c16f3b8edffc410687a27 100644
*** a/src/backend/optimizer/plan/analyzejoins.c
--- b/src/backend/optimizer/plan/analyzejoins.c
***************
*** 26,31 ****
--- 26,32 ----
  #include "optimizer/pathnode.h"
  #include "optimizer/paths.h"
  #include "optimizer/planmain.h"
+ #include "optimizer/var.h"

/* local functions */
static bool join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo);
*************** join_is_removable(PlannerInfo *root, Spe
*** 197,212 ****
}

/*
! * Similarly check that the inner rel doesn't produce any PlaceHolderVars
! * that will be used above the join.
*/
foreach(l, root->placeholder_list)
{
PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(l);

! if (bms_is_subset(phinfo->ph_eval_at, innerrel->relids) &&
! !bms_is_subset(phinfo->ph_needed, joinrelids))
! return false;
}

  	/*
--- 198,220 ----
  	}

/*
! * Similarly check that the inner rel isn't needed by any PlaceHolderVars
! * that will be used above the join. We only need to fail if such a PHV
! * actually references some inner-rel attributes; but the correct check
! * for that is relatively expensive, so we first check against ph_eval_at,
! * which must mention the inner rel if the PHV uses any inner-rel attrs.
*/
foreach(l, root->placeholder_list)
{
PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(l);

! if (bms_is_subset(phinfo->ph_needed, joinrelids))
! continue; /* PHV is not used above the join */
! if (!bms_overlap(phinfo->ph_eval_at, innerrel->relids))
! continue; /* it definitely doesn't reference innerrel */
! if (bms_overlap(pull_varnos((Node *) phinfo->ph_var),
! innerrel->relids))
! return false; /* it does reference innerrel */
}

/*

Here's the patch if it helps.

Thank you.
When 9.1 will released ?
Is it possible to remove 9.0 from downloads so that this issue will not
propagated?

Andrus.

2010/9/26 Andrus <kobruleht2@hot.ee>

Here's the patch if it helps.

Thank you.
When 9.1 will released ?
Is it possible to remove 9.0 from downloads so that this issue will not
propagated?

If committed, this fix will be available in 9.0.1.

Thanks for the bug report and the reproducible test case.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device