Обсуждение: 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.

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

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

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




>>> "Andrus" <kobruleht2@hot.ee> 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

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

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

"Andrus" <kobruleht2@hot.ee> writes:
>> Here's the patch if it helps.

> Thank you.
> When 9.1 will released ?

This will be in 9.0.1, not 9.1.  Probably next week.

> Is it possible to remove 9.0 from downloads so that this issue will not
> propagated?

No, we won't do that.  TBH, if this is the worst bug in 9.0.0,
I will be astonished.

            regards, tom lane