X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 91B57475F0D for ; Sat, 10 May 2003 20:48:38 -0400 (EDT) Received: from tomts20-srv.bellnexxia.net (tomts20.bellnexxia.net [209.226.175.74]) by postgresql.org (Postfix) with ESMTP id 91CF34758F1 for ; Sat, 10 May 2003 20:48:37 -0400 (EDT) Received: from [192.168.1.200] ([64.231.213.3]) by tomts20-srv.bellnexxia.net (InterMail vM.5.01.04.19 201-253-122-122-119-20020516) with ESMTP id <20030511004840.XMSF12310.tomts20-srv.bellnexxia.net@[192.168.1.200]>; Sat, 10 May 2003 20:48:40 -0400 Subject: Re: merge-join for domain with underlying type text From: Rod Taylor To: Randall Lucas Cc: pgsql-sql@postgresql.org In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-bkl6szTstoswzr83vYLA" Organization: Message-Id: <1052614116.48831.79.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 10 May 2003 20:48:37 -0400 X-Spam-Status: No, hits=-40.7 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,PGP_SIGNATURE_2, RCVD_IN_NJABL,REFERENCES,REPLY_WITH_QUOTES, USER_AGENT_XIMIAN autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/199 X-Sequence-Number: 13364 --=-bkl6szTstoswzr83vYLA Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Sat, 2003-05-10 at 19:40, Randall Lucas wrote: > -- Hmm, here's some code that manifests the problem: Seems it's been fixed in 7.4. In the mean time, you might try this: select * from textual_test_1 full outer join textual_test_2 on=20 CAST(textual_test_1.key AS text) =3D CAST(textual_test_2.other_key AS text); -- LOG -- h=3D# create domain textual_test as text; CREATE DOMAIN h=3D# create table textual_test_1 ( h(# key textual_test, h(# stuff varchar h(# ); CREATE TABLE h=3D# insert into textual_test_1 values ('alpha', 'asdflkjasdf'); INSERT 154456 1 h=3D# insert into textual_test_1 values ('bravo', 'asdfdasfsaff'); INSERT 154457 1 h=3D# insert into textual_test_1 values ('delta', 'asdfasfdas'); INSERT 154458 1 h=3D# create table textual_test_2 ( h(# other_key textual_test, h(# more_stuff varchar h(# ); CREATE TABLE h=3D# insert into textual_test_2 values ('charlie', 'asdfasfasfda'); INSERT 154464 1 h=3D# insert into textual_test_2 values ('delta', 'asgasgdda'); INSERT 154465 1 h=3D# insert into textual_test_2 values ('echo', 'asdasfsdfsfda'); INSERT 154466 1 h=3D# select * from textual_test_1 full outer join textual_test_2 on=20 textual_test_1.key =3D textual_test_2.other_key; key | stuff | other_key | more_stuff -------+--------------+-----------+--------------- alpha | asdflkjasdf | | bravo | asdfdasfsaff | | | | charlie | asdfasfasfda delta | asdfasfdas | delta | asgasgdda | | echo | asdasfsdfsfda (5 rows) =20 h=3D# select version(); version ------------------------------------------------------------------------ PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4 (1 row) --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-bkl6szTstoswzr83vYLA Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) iD8DBQA+vZ3k6DETLow6vwwRAk3lAJwKq8O+yr1pLrO/k3GJkKPCupzMGACfYBJJ Zad9hMNlX6+1refWhjYTAxI= =+WNA -----END PGP SIGNATURE----- --=-bkl6szTstoswzr83vYLA--