pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL 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>
2001-09-14 15:18 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Josh Berkus <josh@agliodbs.com>
2001-09-14 15:29 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Andre Schnabel <A_Schnabel@t-online.de>
2001-09-16 19:11 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Tom Lane <tgl@sss.pgh.pa.us>
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 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 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 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 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 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Josh Berkus <josh@agliodbs.com>
@ 2001-09-14 17:31 ` Richard NAGY <richard@presenceweb.com>
2001-09-14 19:19 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 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 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 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Josh Berkus <josh@agliodbs.com>
2001-09-14 17:31 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Richard NAGY <richard@presenceweb.com>
@ 2001-09-14 19:19 ` Josh Berkus <josh@agliodbs.com>
2001-09-17 07:45 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 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-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 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Josh Berkus <josh@agliodbs.com>
2001-09-14 17:31 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Richard NAGY <richard@presenceweb.com>
2001-09-14 19:19 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Josh Berkus <josh@agliodbs.com>
@ 2001-09-17 07:45 ` Richard NAGY <richard@presenceweb.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-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:29 ` Andre Schnabel <A_Schnabel@t-online.de>
2001-09-17 08:15 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 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 10:11 SQL request change when upgrade from 7.0.2 to 7.1.3 Richard NAGY <richard@presenceweb.com>
2001-09-14 15:29 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Andre Schnabel <A_Schnabel@t-online.de>
@ 2001-09-17 08:15 ` Richard NAGY <richard@presenceweb.com>
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-14 10:11 SQL request change when upgrade from 7.0.2 to 7.1.3 Richard NAGY <richard@presenceweb.com>
@ 2001-09-16 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-09-17 08:27 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 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-14 10:11 SQL request change when upgrade from 7.0.2 to 7.1.3 Richard NAGY <richard@presenceweb.com>
2001-09-16 19:11 ` Re: SQL request change when upgrade from 7.0.2 to 7.1.3 Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-09-17 08:27 ` Richard NAGY <richard@presenceweb.com>
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