pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL request change when upgrade from 7.0.2 to 7.1.3
9+ messages / 4 participants
[nested] [flat]

* SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-14 10:11  Richard NAGY <richard@presenceweb.com>
  0 siblings, 3 replies; 9+ messages in thread

From: Richard NAGY @ 2001-09-14 10:11 UTC (permalink / raw)
  To: pgsql-sql

Hello,

Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But,
now, one request which was 'good' before, don't want to work any more
now.

It was : (pretty long)

SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,
cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,

entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
aes.sect_id <> 9
and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56
UNION
SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,
cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,

entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
aes.sect_id <> 9
and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and
aes.ent_id
not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and
sect_id <> 3
and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type,
e.nom

Now, if I want that my request works well, I have to remove the order by
statement. But, of course, it is not ordered any more.

So how can I translate this request to one which can work with an order
by statement ?

Thanks.

--
Richard NAGY
Presenceweb

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-14 15:18  Josh Berkus <josh@agliodbs.com>
  parent: Richard NAGY <richard@presenceweb.com>
  2 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2001-09-14 15:18 UTC (permalink / raw)
  To: Richard NAGY <richard@presenceweb.com>; +Cc: pgsql-sql

Mr. Nagy,

> Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3.
> But,
> now, one request which was 'good' before, don't want to work any more
> now.

I'm not sure I understand your question.  What do you mean "doesn't
work"? Please give a detailed list of all steps taken, including any
error messages received.

> Now, if I want that my request works well, I have to remove the order
> by
> statement. But, of course, it is not ordered any more.

Er, by "request" do you mean "query"?

Using an ORDER BY statement as you appear to use it in that query is
permitted and correct.  I suspect that your problem is located somewhere
else.  For example, what interface tool are you using to send queries to
the database?  

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-14 15:29  Andre Schnabel <A_Schnabel@t-online.de>
  parent: Richard NAGY <richard@presenceweb.com>
  2 siblings, 1 reply; 9+ messages in thread

From: A_Schnabel@t-online.de @ 2001-09-14 15:29 UTC (permalink / raw)
  To: Richard NAGY <richard@presenceweb.com>; pgsql-sql

Can you post the exact errormessage?

----- Original Message ----- 
From: "Richard NAGY" <richard@presenceweb.com>
To: <pgsql-sql@postgresql.org>
Sent: Friday, September 14, 2001 12:11 PM
Subject: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3


> Hello,
> 
> Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But,
> now, one request which was 'good' before, don't want to work any more
> now.
> 
> It was : (pretty long)
> 
> SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> 
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56
> UNION
> SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> 
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and
> aes.ent_id
> not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and
> sect_id <> 3
> and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type,
> e.nom
> 
> Now, if I want that my request works well, I have to remove the order by
> statement. But, of course, it is not ordered any more.
> 
> So how can I translate this request to one which can work with an order
> by statement ?
> 
> Thanks.
> 
> --
> Richard NAGY
> Presenceweb
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-14 17:31  Richard NAGY <richard@presenceweb.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Richard NAGY @ 2001-09-14 17:31 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Josh Berkus a écrit :

> Mr. Nagy,
>
> > Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3.
> > But,
> > now, one request which was 'good' before, don't want to work any more
> > now.
>
> I'm not sure I understand your question.  What do you mean "doesn't
> work"? Please give a detailed list of all steps taken, including any
> error messages received.
>
> > Now, if I want that my request works well, I have to remove the order
> > by
> > statement. But, of course, it is not ordered any more.
>
> Er, by "request" do you mean "query"?
>
> Using an ORDER BY statement as you appear to use it in that query is
> permitted and correct.  I suspect that your problem is located somewhere
> else.  For example, what interface tool are you using to send queries to
> the database?
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>
>

Hello Josh BERKUS,

Thanks for your answer and sorry for my english. It was a query and not a
request! Well, the query works well on postgresql 7.0.2 but when I upgraded
the RDBS to 7.1.3, it did not work any more. The error was : Relation e does
not exist.
But, in the 'order by' statement, I removed the 'e.'. Now, the query works
well without any error. It is ordered but I think it is certainly due to the
fact that the table was already ordered on disk. So, I have no more errors
but I'm not sure that it is completely good.

PS : The interface tool that I have used to send queries to the database was
psql.

Regards

--
Richard NAGY
Presenceweb

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-14 19:19  Josh Berkus <josh@agliodbs.com>
  parent: Richard NAGY <richard@presenceweb.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2001-09-14 19:19 UTC (permalink / raw)
  To: Richard NAGY <richard@presenceweb.com>; +Cc: pgsql-sql

Richard,

I'm curious now.  What happens if you remove the table qualifications,
e.g.:

ORDER BY type, nom;

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-16 19:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Richard NAGY <richard@presenceweb.com>
  2 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2001-09-16 19:11 UTC (permalink / raw)
  To: Richard NAGY <richard@presenceweb.com>; +Cc: pgsql-sql

Richard NAGY <richard@presenceweb.com> writes:
> SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56
> UNION
> SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and
> aes.ent_id
> not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and
> sect_id <> 3
> and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type,
> e.nom

ORDER BY applied to the result of a UNION has to be on the output
columns of the UNION.  "e.type" etc are names of input-table columns.
Try just "type" and "nom", which will refer to the second and third
output columns of the UNION.

Pre-7.1 got this wrong (and would sometimes produce wrong output
ordering or even a backend crash, if the arms of the UNION didn't
all yield the same datatype).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-17 07:45  Richard NAGY <richard@presenceweb.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Richard NAGY @ 2001-09-17 07:45 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Josh Berkus a écrit :

> Richard,
>
> I'm curious now.  What happens if you remove the table qualifications,
> e.g.:
>
> ORDER BY type, nom;
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco

Hello Josh,

Yes, it works! I don't know exactly why, but it works!
Thanks a lot.

--
Richard NAGY
Presenceweb

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-17 08:15  Richard NAGY <richard@presenceweb.com>
  parent: Andre Schnabel <A_Schnabel@t-online.de>
  0 siblings, 0 replies; 9+ messages in thread

From: Richard NAGY @ 2001-09-17 08:15 UTC (permalink / raw)
  To: Andre Schnabel <A_Schnabel@t-online.de>; +Cc: pgsql-sql

Andre Schnabel a écrit :

> Hello Richard,
>
> I did some testing and after all your query should be ordered right.
> The test's I have done:
>
> Test=# select t.foo1 from testtable t
> Test-# union
> Test-# select t.foo2 from testtable t
> Test-# order by t.foo1;
> ERROR:  Relation 't' does not exist
>
> -------- Same error as you get ----
>
> Test=# select t.foo1 from testtable t
> Test-# union
> Test-# select t.foo2 from testtable t
> Test-# order by foo1;
>  foo1
> ------
>  abc
>  cdef
> (2 rows)
>
> -------- Ordered Ascending (maybe by chance?) ---
>
> Test=# select t.foo1 from testtable t
> Test-# union
> Test-# select t.foo2 from testtable t
> Test-# order by foo1 DESC;
>  foo1
> ------
>  cdef
>  abc
> (2 rows)
>
> ---- Ordered descending (ordering works!) ---
>
> I guess, the table-alias is not known to the order-clause. Maybe the
> execution (or parsing) order of the UNION changed from 7.0 to 7.1.

Hello Andre,

Thanks very much for having tested. Yes, it works well.

--
Richard NAGY
Presenceweb

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: SQL request change when upgrade from 7.0.2 to 7.1.3
@ 2001-09-17 08:27  Richard NAGY <richard@presenceweb.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Richard NAGY @ 2001-09-17 08:27 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Tom Lane a écrit :

> Richard NAGY <richard@presenceweb.com> writes:
> > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,
> > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> > aes.sect_id <> 9
> > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56
> > UNION
> > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,
> > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> > aes.sect_id <> 9
> > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and
> > aes.ent_id
> > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and
> > sect_id <> 3
> > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type,
> > e.nom
>
> ORDER BY applied to the result of a UNION has to be on the output
> columns of the UNION.  "e.type" etc are names of input-table columns.
> Try just "type" and "nom", which will refer to the second and third
> output columns of the UNION.
>
> Pre-7.1 got this wrong (and would sometimes produce wrong output
> ordering or even a backend crash, if the arms of the UNION didn't
> all yield the same datatype).
>
>                         regards, tom lane

Hello Tom,

Yes, thanks a lot. It works!
Regards.

--
Richard NAGY
Presenceweb

^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2001-09-17 08:27 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-14 10:11 SQL request change when upgrade from 7.0.2 to 7.1.3 Richard NAGY <richard@presenceweb.com>
2001-09-14 15:18 ` Josh Berkus <josh@agliodbs.com>
2001-09-14 17:31   ` Richard NAGY <richard@presenceweb.com>
2001-09-14 19:19     ` Josh Berkus <josh@agliodbs.com>
2001-09-17 07:45       ` Richard NAGY <richard@presenceweb.com>
2001-09-14 15:29 ` Andre Schnabel <A_Schnabel@t-online.de>
2001-09-17 08:15   ` Richard NAGY <richard@presenceweb.com>
2001-09-16 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-09-17 08:27   ` Richard NAGY <richard@presenceweb.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox