Thread: SQL request change when upgrade from 7.0.2 to 7.1.3
Hello, <p>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. <p>It was : (pretty long) <p>SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,<br />cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, <br />entreprise e WHERE e.id = aes.ent_idand aes.sect_id <> 3 and aes.sect_id <> 9 <br />and aes.sect_id <> 1 and aes.sect_id <>13 and aes.sect_id = 56 <br />UNION <br />SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,<br />cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, <br />entreprise e WHERE e.id= aes.ent_id and aes.sect_id <> 3 and aes.sect_id <> 9 <br />and aes.sect_id <> 1 and aes.sect_id <>13 and aes.sect_id <> 56 and aes.ent_id <br />not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id =56 and sect_id <> 3 <br />and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type,e.nom <p>Now, if I want that my request works well, I have to remove the order by statement. But, of course, it isnot ordered any more. <p>So how can I translate this request to one which can work with an order by statement ? <p>Thanks.<pre>-- Richard NAGY Presenceweb</pre>
Re: SQL request change when upgrade from 7.0.2 to 7.1.3
From
A_Schnabel@t-online.de (Andre Schnabel)
Date:
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 > > >
Josh Berkus a écrit : <blockquote type="CITE">Mr. Nagy, <p>> Recently, I have upgraded my postgresql server from 7.0.2to 7.1.3. <br />> But, <br />> now, one request which was 'good' before, don't want to work any more <br />>now. <p>I'm not sure I understand your question. What do you mean "doesn't <br />work"? Please give a detailed listof all steps taken, including any <br />error messages received. <p>> Now, if I want that my request works well, Ihave to remove the order <br />> by <br />> statement. But, of course, it is not ordered any more. <p>Er, by "request"do you mean "query"? <p>Using an ORDER BY statement as you appear to use it in that query is <br />permitted andcorrect. I suspect that your problem is located somewhere <br />else. For example, what interface tool are you usingto send queries to <br />the database? <p>-Josh <p>______AGLIO DATABASE SOLUTIONS___________________________ <br /> Josh Berkus <br /> Complete information technology josh@agliodbs.com <br /> and data management solutions (415) 565-7293 <br /> for law firms, small businesses fax 621-2533 <br /> and non-profit organizations. San Francisco <br /> <br /> </blockquote> Hello Josh BERKUS, <p>Thanks for youranswer and sorry for my english. It was a query and not a request! Well, the query works well on postgresql 7.0.2 butwhen I upgraded the RDBS to 7.1.3, it did not work any more. The error was : Relation e does not exist. <br />But, inthe 'order by' statement, I removed the 'e.'. Now, the query works well without any error. It is ordered but I think itis certainly due to the fact that the table was already ordered on disk. So, I have no more errors but I'm not sure thatit is completely good. <p>PS : The interface tool that I have used to send queries to the database was psql. <p>Regards<pre>-- Richard NAGY Presenceweb</pre>
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
Attachment
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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
Josh Berkus a écrit : <blockquote type="CITE">Richard, <p>I'm curious now. What happens if you remove the table qualifications,<br />e.g.: <p>ORDER BY type, nom; <p>-Josh <p>______AGLIO DATABASE SOLUTIONS___________________________ <br/> Josh Berkus <br /> Complete information technology josh@agliodbs.com <br/> and data management solutions (415) 565-7293 <br /> for law firms, small businesses fax 621-2533 <br/> and non-profit organizations. San Francisco</blockquote> Hello Josh, <p>Yes, it works! I don't know exactlywhy, but it works! <br />Thanks a lot. <pre>-- Richard NAGY Presenceweb</pre>
Andre Schnabel a écrit : <blockquote type="CITE">Hello Richard, <p>I did some testing and after all your query should beordered right. <br />The test's I have done: <p>Test=# select t.foo1 from testtable t <br />Test-# union <br />Test-# selectt.foo2 from testtable t <br />Test-# order by t.foo1; <br />ERROR: Relation 't' does not exist <p>-------- Same erroras you get ---- <p>Test=# select t.foo1 from testtable t <br />Test-# union <br />Test-# select t.foo2 from testtablet <br />Test-# order by foo1; <br /> foo1 <br />------ <br /> abc <br /> cdef <br />(2 rows) <p>-------- OrderedAscending (maybe by chance?) --- <p>Test=# select t.foo1 from testtable t <br />Test-# union <br />Test-# select t.foo2from testtable t <br />Test-# order by foo1 DESC; <br /> foo1 <br />------ <br /> cdef <br /> abc <br />(2 rows) <p>----Ordered descending (ordering works!) --- <p>I guess, the table-alias is not known to the order-clause. Maybe the <br/>execution (or parsing) order of the UNION changed from 7.0 to 7.1.</blockquote> Hello Andre, <p>Thanks very much forhaving tested. Yes, it works well. <pre>-- Richard NAGY Presenceweb</pre>
Tom Lane a écrit : <blockquote type="CITE">Richard NAGY <richard@presenceweb.com> writes: <br />> SELECT aes.ent_id,e.type, e.nom, aes.sect_id as voulu, <br />> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectactaes, <br />> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and <br />> aes.sect_id<> 9 <br />> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 <br />> UNION<br />> SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, <br />> cvd_new(current_date,e.date_entree, 'new') FROM ass_entrep_sectact aes, <br />> entreprise e WHERE e.id = aes.ent_id andaes.sect_id <> 3 and <br />> aes.sect_id <> 9 <br />> and aes.sect_id <> 1 and aes.sect_id <>13 and aes.sect_id <> 56 and <br />> aes.ent_id <br />> not in (SELECT ent_id FROM ass_entrep_sectactWHERE sect_id = 56 and <br />> sect_id <> 3 <br />> and sect_id <> 9 and sect_id <>1 and sect_id <> 13) ORDER BY e.type, <br />> e.nom <p>ORDER BY applied to the result of a UNION has tobe on the output <br />columns of the UNION. "e.type" etc are names of input-table columns. <br />Try just "type" and"nom", which will refer to the second and third <br />output columns of the UNION. <p>Pre-7.1 got this wrong (and wouldsometimes produce wrong output <br />ordering or even a backend crash, if the arms of the UNION didn't <br />all yieldthe same datatype). <p> regards, tom lane</blockquote> Hello Tom, <p>Yes, thanks a lot. It works!<br />Regards. <pre>-- Richard NAGY Presenceweb</pre>