The following bug has been logged online:
Bug reference: 1409
Logged by: Lutischán Ferenc
Email address: yoursoft@freemail.hu
PostgreSQL version: 8.0 B5, 7.4.6
Operating system: WinXp, SLES9
Description: A good and a bad news: Crazy SQL JOIN?
Details:
Dear Developer Team!
A good news: Refering to my mail with 'Out of memory problem' (>>If you make
'create user' and 'alter group' sql command on existing users, and make it
many times, the server doesn't release the memory<<) -> this is solved in
the 7.4.6.
A bad news:
I don't understand the following situation:
Try to create these tables:
-------------------------------------------
SET client_encoding = 'UNICODE';
SET check_function_bodies = false;
SET client_min_messages = warning;
CREATE SCHEMA ifc AUTHORIZATION postgres;
SET search_path = ifc, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = true;
CREATE TABLE test (
col1 character varying(10),
col2 character varying(10)
);
ALTER TABLE ifc.test OWNER TO postgres;
CREATE TABLE test2 (
col1 character varying(10),
col2 character varying(10)
);
ALTER TABLE ifc.test2 OWNER TO postgres;
COPY test (col1, col2) FROM stdin;
b ac
ba a
\N aac
\N aab
\.
COPY test2 (col1, col2) FROM stdin;
b ac
\N aac
ba a
\N aaa
\.
-----------------------------------------------
And try to make the following selects:
-----------------------------------------
select a.col2 as col1, b.col2 from
ifc.test a full outer join ifc.test2 b on a.col2=b.col2
order by b.col2
-----------------------------------------
select a.col2 as col1, b.col2 from
ifc.test a full outer join ifc.test2 b on a.col2=b.col2
order by b.col2::char(8)
-----------------------------------------
A questions: Why different are the results?
Best Regards:
Ferenc