RE: [BUGS] 'order by' and 'desc' not working in subquery using 'not in'
Title: Hi Rahul,Have you tried to quote each table name and column name by " , and constant by ' ? In your case, try delete from "reporttable" where ( "srvServerid" = 'serverid') and( "rptreportid" not in(select "rptreportid" from "reporttable"where ( "srvserverid" = 'serverid' order by "rpttimestamp" desc))); Appropriate quoting can help PostgreSQL understand SQL without ambiguity. EddyFormatter/Programmer,Internet Securities Inc. China (ISI)Rm 202, Bright China Chang An Bldg.7 Jian Guo Men Nei Ave.Beijing 15, ChinaE-mail: [EMAIL PROTECTED]A Euromoney Institutional Investor Company-Original Message-From: [EMAIL PROTECTED][mailto:[EMAIL PROTECTED]]On Behalf Of[EMAIL PROTECTED]Sent: Tuesday, July 31, 2001 4:16 PMTo: [EMAIL PROTECTED]Subject: [BUGS] 'order by' and 'desc' not working in subquery using 'notin'Rahul Gade ([EMAIL PROTECTED]) reports a bug with a severity of 1The lower the number the more severe it is.Short Description'order by' and 'desc' not working in subquery using 'not in'Long DescriptionHello, I am facing two big problems,they may not be really big but in my case these are big : 1) temporary tables can not be created inside function using PL/pgsql 2) order by and desc keywords are not functioning inside subquery in the following query i am trying to delete all the rows except the top 20 rows from reporttable.How i can do this.i have done this in MSSQL using same syntaxOut of these two 2nd problem is important for me,plase tell me what to do, --- Thanks for response Sample Code delete from reporttable where (srvServerid=serverid) and(rptreportid not in(select rptreportid from reporttablewhere (srvserverid=serverid order by rpttimestamp desc)));No file was uploaded with this report---(end of broadcast)---TIP 3: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly
Re: [BUGS] 'order by' and 'desc' not working in subquery using 'notin'
On Tue, 31 Jul 2001 [EMAIL PROTECTED] wrote: > Rahul Gade ([EMAIL PROTECTED]) reports a bug with a severity of 1 Umm, 1? IIRC, order by in subqueries isn't even standard SQL, however this should be taken care of in current sources so that you can use order by ... limit in subqueries which sounds like what you're trying to do (although your sample code doesn't include the limit) As for temporary tables in plpgsql. If you're using 7.1, you should be able to make this work by using execute. > The lower the number the more severe it is. > > Short Description > 'order by' and 'desc' not working in subquery using 'not in' > > Long Description > Hello, > I am facing two big problems,they may not be really big but in my case these are >big : > 1) temporary tables can not be created inside function using PL/pgsql > 2) order by and desc keywords are not functioning inside subquery > > in the following query i am trying to delete all the rows except the top 20 rows >from reporttable. > How i can do this. > i have done this in MSSQL using same syntax > > Out of these two 2nd problem is important for me, > plase tell me what to do, > --- Thanks for response > > > Sample Code > delete from reporttable where (srvServerid=serverid) and > (rptreportid not in(select rptreportid from reporttable > where (srvserverid=serverid order by rpttimestamp desc))); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] subquery results bypassed
Anthony Wood ([EMAIL PROTECTED]) writes:
> [ SELECT DISTINCT ON in a subquery-in-FROM misbehaves ]
Here's the patch against 7.1.2 to fix this problem. This also fixes a
related problem noted a few days ago, that outer WHERE clauses shouldn't
be pushed down into a sub-select that has a LIMIT clause.
regards, tom lane
*** src/backend/optimizer/path/allpaths.c.orig Wed Mar 21 22:59:34 2001
--- src/backend/optimizer/path/allpaths.c Tue Jul 31 14:05:05 2001
***
*** 125,135
* Non-pushed-down clauses will get evaluated as qpquals of
* the SubqueryScan node.
*
* XXX Are there any cases where we want to make a policy
* decision not to push down, because it'd result in a worse
* plan?
*/
! if (rte->subquery->setOperations == NULL)
{
/* OK to consider pushing down individual quals */
List *upperrestrictlist = NIL;
--- 125,141
* Non-pushed-down clauses will get evaluated as qpquals of
* the SubqueryScan node.
*
+* We can't push down into subqueries with LIMIT or DISTINCT ON
+* clauses, either.
+*
* XXX Are there any cases where we want to make a policy
* decision not to push down, because it'd result in a worse
* plan?
*/
! if (rte->subquery->setOperations == NULL &&
! rte->subquery->limitOffset == NULL &&
! rte->subquery->limitCount == NULL &&
! !has_distinct_on_clause(rte->subquery))
{
/* OK to consider pushing down individual quals */
List *upperrestrictlist = NIL;
*** src/backend/optimizer/util/clauses.c.orig Tue Mar 27 12:12:34 2001
--- src/backend/optimizer/util/clauses.cTue Jul 31 14:05:01 2001
***
*** 730,742
/*
*
*
*General clause-manipulating routines
*
*
*
*/
/*
! * clause_relids_vars
* Retrieves distinct relids and vars appearing within a clause.
*
* '*relids' is set to an integer list of all distinct "varno"s appearing
--- 730,794
/*
+ *Tests on clauses of queries
+ *
+ * Possibly this code should go someplace else, since this isn't quite the
+ * same meaning of "clause" as is used elsewhere in this module. But I can't
+ * think of a better place for it...
+ */
+
+ /*
+ * Test whether a query uses DISTINCT ON, ie, has a distinct-list that is
+ * just a subset of the output columns.
+ */
+ bool
+ has_distinct_on_clause(Query *query)
+ {
+ List *targetList;
+
+ /* Is there a DISTINCT clause at all? */
+ if (query->distinctClause == NIL)
+ return false;
+ /*
+* If the DISTINCT list contains all the nonjunk targetlist items,
+* then it's a simple DISTINCT, else it's DISTINCT ON. We do not
+* require the lists to be in the same order (since the parser may
+* have adjusted the DISTINCT clause ordering to agree with ORDER BY).
+*/
+ foreach(targetList, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(targetList);
+ Index ressortgroupref;
+ List *distinctClause;
+
+ if (tle->resdom->resjunk)
+ continue;
+ ressortgroupref = tle->resdom->ressortgroupref;
+ if (ressortgroupref == 0)
+ return true;/* definitely not in DISTINCT list */
+ foreach(distinctClause, query->distinctClause)
+ {
+ SortClause *scl = (SortClause *) lfirst(distinctClause);
+
+ if (scl->tleSortGroupRef == ressortgroupref)
+ break;
[BUGS] ERROR: parser: parse error at or near "execute"
Christian Villa Real Lopes ([EMAIL PROTECTED]) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
ERROR: parser: parse error at or near "execute"
Long Description
I created a function to drop a table if it exists. On pgsql-7.0.3 this function
returns an error, as follow :
template1=# SELECT func_drop_table('friend');
ERROR: parser: parse error at or near "execute"
On pgsql-7.1.2 this function works fine and no error returns just the following lines :
func_drop_table
---
friend
Sample Code
CREATE TABLE friend (
firstname CHAR(15),
lastname CHAR(20),
city CHAR(15),
state CHAR(2),
age INTEGER
);
CREATE FUNCTION "func_drop_table"(TEXT)
RETURNS TEXT AS '
DECLARE
v_reg RECORD;
v_tab TEXT;
BEGIN
FOR v_reg IN SELECT * FROM pg_tables WHERE tablename LIKE $1 LOOP
v_tab := ''DROP TABLE '' || v_reg.tablename || '';'';
EXECUTE v_tab;
END LOOP;
RETURN $1;
END;' LANGUAGE 'plpgsql';
SELECT func_drop_table('friend');
No file was uploaded with this report
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] ERROR: parser: parse error at or near "execute"
On Tue, 31 Jul 2001 [EMAIL PROTECTED] wrote:
> Christian Villa Real Lopes ([EMAIL PROTECTED]) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> ERROR: parser: parse error at or near "execute"
>
> Long Description I created a function to drop a table if it exists. On
> pgsql-7.0.3 this function returns an error, as follow :
>
> template1=# SELECT func_drop_table('friend');
> ERROR: parser: parse error at or near "execute"
>
> On pgsql-7.1.2 this function works fine and no error returns just the following
>lines :
IIRC, execute was added in 7.1.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Using nulls with earthdistance operator crashes backend
Hello! Here's now to reproduce my bug: * Start with Postgres 7.1.2 (specifically: PostgreSQL 7.1 on i386--freebsd4.3, compiled by GCC 2.95.3 ) * Install earthdistance operator from the contrib directory. * try this: cascade=> select null <@> '1,1'::point; ## The result I get: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ### I expected simply for "null" to be returned as the result. I can work around this by including an extra step to make sure that my data is not null before it's passed off a SQL statement like this. Thanks! -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Using nulls with earthdistance operator crashes backend
Mark Stosberg <[EMAIL PROTECTED]> writes: > * Install earthdistance operator from the contrib directory. > * try this: > cascade=> select null <@> '1,1'::point; > ## The result I get: > pqReadData() -- backend closed the channel unexpectedly. Probably the earthdistance functions are not NULL-safe and need to be marked "isStrict" in CREATE FUNCTION. Would you be willing to do the legwork on working up a patch for that? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Using nulls with earthdistance operator crashes backend
I'll give it a shot and post the patch or let you know I'm stuck. :) Thanks! -mark Tom Lane wrote: > > Mark Stosberg <[EMAIL PROTECTED]> writes: > > * Install earthdistance operator from the contrib directory. > > * try this: > > cascade=> select null <@> '1,1'::point; > > > ## The result I get: > > pqReadData() -- backend closed the channel unexpectedly. > > Probably the earthdistance functions are not NULL-safe and need to be > marked "isStrict" in CREATE FUNCTION. Would you be willing to do the > legwork on working up a patch for that? > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- http://mark.stosberg.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] 'order by' and 'desc' not working in subquery using 'not in'
Rahul Gade ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description 'order by' and 'desc' not working in subquery using 'not in' Long Description Hello, I am facing two big problems,they may not be really big but in my case these are big : 1) temporary tables can not be created inside function using PL/pgsql 2) order by and desc keywords are not functioning inside subquery in the following query i am trying to delete all the rows except the top 20 rows from reporttable. How i can do this. i have done this in MSSQL using same syntax Out of these two 2nd problem is important for me, plase tell me what to do, --- Thanks for response Sample Code delete from reporttable where (srvServerid=serverid) and (rptreportid not in(select rptreportid from reporttable where (srvserverid=serverid order by rpttimestamp desc))); No file was uploaded with this report ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
