Skip site navigation (1) Skip section navigation (2)

BUG #1409: A good and a bad news: Crazy SQL JOIN?

From: "Lutischn Ferenc" <yoursoft(at)freemail(dot)hu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1409: A good and a bad news: Crazy SQL JOIN?
Date: 2005-01-18 09:14:23
Message-ID: 200501180914.j0I9ENIU025570@developer.pgadmin.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1409
Logged by:          Lutischn Ferenc
Email address:      yoursoft(at)freemail(dot)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

Responses

pgsql-bugs by date

Next:From: Dave BodenstabDate: 2005-01-18 09:43:20
Subject: Re: psql 8.0.0 rc5 parser error on \dt?
Previous:From: andrzejDate: 2005-01-18 09:01:29
Subject: BUG #1408: don't see index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group