[BUGS] Bug #526: Three levels deeply nested SELECT goes wrong
Maarten Fokkinga ([EMAIL PROTECTED]) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Three levels deeply nested SELECT goes wrong
Long Description
A 3 levels deeply nested SELECT with "R(a)" in the top-most FROM part and "R.a=S.a AND
XXX" in the middle WHERE part may give different results if in a SELECT within XXX the
term "S.a" is replaced by "R.a".
Since the innermost SELECT lies in the "scope" of the conjunct "R.a=S.a", it should
not make any difference if "R.a" is used or "S.a".
See the simple example code to reproduce the error.
Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
Sample Code
create table R(a int);
create table S(a int, b int);
create table T(a int, b int);
insert into R values (1);
insert into R values (2);
insert into S values (2,20);
insert into S values (1,10);
insert into T values (2,20);
insert into T values (1,10);
-- the order of the rows in R, S, T is significant
-- first query:
select a from R where
exists (select b from S where
S.a=R.a AND S.b in (select b from T where a=S.a));
-- gives two rows (rows "(1)" and "(2)")
-- second query:
select a from R where
exists (select b from S where
S.a=R.a AND S.b in (select b from T where a=R.a));
-- gives one row ("(1)" only)
No file was uploaded with this report
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Vá: Va: [BUGS] Va: [BUGS] Bug #519: Bug in order b y clausule
[EMAIL PROTECTED] writes: > All Hungarian -and other non standard ANSI- users should expect > "order by problems" due the locale settings.? As far as I can see, this is *not* a Postgres bug. If you don't like the sort order specified by your locale file, you need to choose another locale file. We're just doing what the locale file says to do. Depending on what it is you want from the locale features, you might be able to get the right effect with some combination like LC_COLLATE=C and LC_CTYPE=hu_HU. Or maybe you should just recompile Postgres with locale support turned off. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #526: Three levels deeply nested SELECT goes wrong
[EMAIL PROTECTED] writes: > Three levels deeply nested SELECT goes wrong Seems to still behave the same in current sources :-( Thanks for the report! regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Vá: Va: [BUGS] Va: [BUGS] Bug #
On Fri, 30 Nov 2001 [EMAIL PROTECTED] wrote: > > > So, it is still a mystery for me > > You probably need the locale for sorting the single character letters > > but you don't want the collation values of the multiple character ones. > > I think you're probably going to need to get an alternate locale > > file but I'm not sure what's involved in that outside of postgres. > > For postgres you'd need to dump, initdb under the new locale and restore > > probably. > [Vig, Sandor] > > I'll overwrite the hu_HU collocation file with the en_EN one. It is > NOT > a sollution, it just corrects the problem. Do you guys find it OK, > to > have such an effect? Should we say: > > All Hungarian -and other non standard ANSI- users should expect > "order by problems" due the locale settings.? I'm not sure this is true. As far as the system is concerned this is the correct ordering. ;) If you don't want collation based on your locale I'd suggest either turning off locale in postgres (see configure options) or initdb in "C" locale, you don't need to change the system's collation to do this, just set the locale for the shell that runs initdb. > I suggest to have -at least- a patch for such problem, or a special > postmaster switch, where an alternate collocation file could be > specified. I must work with DB2, and there is a lot of anoing side > effects > with the country selections. f.e.: No codepage translation between > Hungarian and German settings, locale-ized client full with bugs, > etc... > It would be great, not to have these things in Postgresql. There's been talk about implementing the full SQL character set stuff in the future which would probably at least limit these problems probably (it would for example presumably allow you to collate a field you didn't want to collate via hungarian rules by a different collation). ---(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] Bug #526: Three levels deeply nested SELECT goes wrong
[EMAIL PROTECTED] writes:
> A 3 levels deeply nested SELECT with "R(a)" in the top-most FROM part
> and "R.a=S.a AND XXX" in the middle WHERE part may give different
> results if in a SELECT within XXX the term "S.a" is replaced by "R.a".
> Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
I have committed a fix for this problem into current sources
(7.2-to-be). The fix would not apply cleanly in 7.0.*, but if you
don't care to run CVS-tip code you could update to 7.1.3 and apply
the attached patch to it.
regards, tom lane
*** src/backend/optimizer/plan/subselect.c.orig Wed Mar 21 22:59:37 2001
--- src/backend/optimizer/plan/subselect.c Fri Nov 30 14:11:52 2001
***
*** 324,329
--- 324,335
* is anything more complicated than a plain sequential scan, and
* we do it even for seqscan if the qual appears selective enough
* to eliminate many tuples.
+*
+* XXX It's pretty ugly to be inserting a MATERIAL node at this
+* point. Since subquery_planner has already run SS_finalize_plan
+* on the subplan tree, we have to kluge up parameter lists for
+* the MATERIAL node. Possibly this could be fixed by postponing
+* SS_finalize_plan processing until setrefs.c is run.
*/
if (node->parParam == NIL)
{
***
*** 362,369
}
if (use_material)
{
! plan = (Plan *) make_material(plan->targetlist, plan);
! node->plan = plan;
}
}
--- 368,380
}
if (use_material)
{
! Plan *matplan;
!
! matplan = (Plan *) make_material(plan->targetlist,
plan);
! /* kluge --- see comments above */
! matplan->extParam = listCopy(plan->extParam);
! matplan->locParam = listCopy(plan->locParam);
! node->plan = plan = matplan;
}
}
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] Bug #527: server hangs up and refuses any connection
Alla El Gohary ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description server hangs up and refuses any connection Long Description dear pgsql-bugs i'm running postgresql 7.1.2 on freebsd4.3 my application consists of several components running on win32s clients some of the modules interact with each other on the network using udp and tcp packets i have nearly 14 clients running on the server i mentioned before on a workgroup network using a 3com 10/100 switch while the traffic is high i mean all the clients are interacting together the server hangs up and all the connections to the server stops even pinging doesn't reply. at first i switched to freebsd 3.1 i thought its something related to freebsd but again the same thing happened but this time a message poped up on the server "sdec kernel table full" and the server hang up when i restart the server everything comes back to normal i'm connecting to the server from the win32s clients through postgresql ODBC driver v7 i run the postmaster using /usr/local/pgsql/bin/postmaster -i -S -o -F please if there's a solution to that reply me back ASAP Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #527: server hangs up and refuses any connection
[EMAIL PROTECTED] writes: > "sdec kernel table full" and the server hang up Sounds to me like you're running out of kernel file-table slots or some such. Please read the Postgres documentation on configuring kernel resources. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
