[BUGS] BUG #4422: select ... where ... NOT EXISTS / NOT IN
The following bug has been logged online: Bug reference: 4422 Logged by: vasile Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Centos 4 Description:select ... where ... NOT EXISTS / NOT IN Details: I have this 2 similar queries. Why the 1st query is returning 1000+ rows and the 2nd one no rows ? The col1 is not empty in both tables. 1) SELECT col1, col2, col3 FROM table1 t1 WHERE NOT EXISTS (SELECT t2.col1 FROM table2 t2 WHERE t1.col1 = t2.col1 ); 2) SELECT col1, col2, col3 FROM table1 t1 WHERE t1.col1 NOT IN (SELECT t2.col1 FROM table2 t2 ); If I build the query with "LEFT JOIN" I have the same result set like in the 1st query: SELECT t1.col1, t1.col2, t1.col3 FROM table1 t1 LEFT JOIN table2 t2 ON (t1.col1 = t2.col1) WHERE t2.col1 is null; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4421: convert_to() should be immutable
The following bug has been logged online: Bug reference: 4421 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3 Operating system: Linux Description:convert_to() should be immutable Details: The function convert_to(string text, dest_encoding name) is not allowed to be used in a index expression, because it is not marked as "IMMUTABLE". According to the documentation, a function is immutable if it does not modify the database, and for the same arguments, it returns always the same results. I think that all of these conditions can be applied to the convert_to()-function, therefore it should be marked as "IMMUTABLE". -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4421: convert_to() should be immutable
[EMAIL PROTECTED] wrote: PostgreSQL version: 8.3.3 Operating system: Linux Description:convert_to() should be immutable Details: The function convert_to(string text, dest_encoding name) is not allowed to be used in a index expression, because it is not marked as "IMMUTABLE". According to the documentation, a function is immutable if it does not modify the database, and for the same arguments, it returns always the same results. I think that all of these conditions can be applied to the convert_to()-function, therefore it should be marked as "IMMUTABLE". You can change the way a conversion is done with CREATE/DROP CONVERSION. That's why it can't be IMMUTABLE. (I doubt any sane person would actually do that, but that's another debate) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4422: select ... where ... NOT EXISTS / NOT IN
On Wed, Sep 17, 2008 at 02:53:51PM +, vasile wrote: > I have this 2 similar queries. > > Why the 1st query is returning 1000+ rows and the 2nd one no rows ? > The col1 is not empty in both tables. there is no bug. check this: http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/ Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4421: convert_to() should be immutable
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: >> The function convert_to(string text, dest_encoding name) is not allowed to >> be used in a index expression, because it is not marked as "IMMUTABLE". > You can change the way a conversion is done with CREATE/DROP CONVERSION. > That's why it can't be IMMUTABLE. The other reason is that it depends on the database encoding. I suppose you could make an argument that that's fixed for as long as IMMUTABLE needs to think about --- but we'd have to remember to undo the marking if database encoding ever becomes less fixed. Just out of curiosity, what's the use-case for this function in an index anyway? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 0x1A in control file on Windows
I found a bug that pg_controldata ends with error if control files
contain 0x1A (Ctrl+Z) on Windows.
We probably need to add PG_BINARY when we open control files
because 0x1A is an end-of-file marker on Windows.
This fix needs to be applied in back versions (8.2, 8.3 and HEAD).
Index: src/bin/pg_controldata/pg_controldata.c
===
--- src/bin/pg_controldata/pg_controldata.c (head)
+++ src/bin/pg_controldata/pg_controldata.c (pg_control_0x1A)
@@ -107,7 +107,7 @@
snprintf(ControlFilePath, MAXPGPATH, "%s/global/pg_control", DataDir);
- if ((fd = open(ControlFilePath, O_RDONLY, 0)) == -1)
+ if ((fd = open(ControlFilePath, O_RDONLY | PG_BINARY, 0)) == -1)
{
fprintf(stderr, _("%s: could not open file \"%s\" for reading:
%s\n"),
progname, ControlFilePath, strerror(errno));
Index: src/bin/pg_resetxlog/pg_resetxlog.c
===
--- src/bin/pg_resetxlog/pg_resetxlog.c (head)
+++ src/bin/pg_resetxlog/pg_resetxlog.c (pg_control_0x1A)
@@ -373,7 +373,7 @@
char *buffer;
pg_crc32crc;
- if ((fd = open(XLOG_CONTROL_FILE, O_RDONLY, 0)) < 0)
+ if ((fd = open(XLOG_CONTROL_FILE, O_RDONLY | PG_BINARY, 0)) < 0)
{
/*
* If pg_control is not there at all, or we can't read it, the
odds
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [HACKERS] 0x1A in control file on Windows
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > I found a bug that pg_controldata ends with error if control files > contain 0x1A (Ctrl+Z) on Windows. > We probably need to add PG_BINARY when we open control files > because 0x1A is an end-of-file marker on Windows. Well, why is that a bug? If the platform is so silly as to define text files that way, who are we to argue? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [HACKERS] 0x1A in control file on Windows
Tom Lane <[EMAIL PROTECTED]> wrote: > ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > > We probably need to add PG_BINARY when we open control files > > because 0x1A is an end-of-file marker on Windows. > > Well, why is that a bug? If the platform is so silly as to define text > files that way, who are we to argue? Google says it is for for backward compatibility with CP/M http://en.wikipedia.org/wiki/End-of-file and adding O_BINARY is the answer. http://codenewbie.com/forum/standard-c-c/1208-binary-i-o-file-reading-0x1a-trouble.html Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
