pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmerge-join for domain with underlying type text
6+ messages / 3 participants
[nested] [flat]
* merge-join for domain with underlying type text
@ 2003-05-10 21:04 Randall Lucas <rlucas@tercent.net>
2003-05-10 22:13 ` Re: merge-join for domain with underlying type text Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Randall Lucas @ 2003-05-10 21:04 UTC (permalink / raw)
To: pgsql-sql
Hi Folks,
If I have a domain defined with an underlying type of "text" or
"varchar," and I need to FULL OUTER JOIN two tables based upon the
similarity in these fields, I am getting:
ERROR: FULL JOIN is only supported with mergejoinable join conditions
I have looked at the docs and it seems that it's a matter of making the
comparison operators for the domain type (as per SQL Reference ::
"CREATE OPERATOR" section). However, is there anything tricky about
adding the merge-join capability to a domain? Is there a preferred
method? Am I barking entirely up the wrong tree (could I, for example,
use a casting trick? Simply recasting the fields as type "text"
doesn't avoid the error)?
Postgres version is 7.3.
Best,
Randall
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: merge-join for domain with underlying type text
2003-05-10 21:04 merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
@ 2003-05-10 22:13 ` Rod Taylor <rbt@rbt.ca>
2003-05-10 23:40 ` Re: merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
0 siblings, 1 reply; 6+ messages in thread
From: Rod Taylor @ 2003-05-10 22:13 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: pgsql-sql
On Sat, 2003-05-10 at 17:04, Randall Lucas wrote:
> Hi Folks,
>
> If I have a domain defined with an underlying type of "text" or
> "varchar," and I need to FULL OUTER JOIN two tables based upon the
> similarity in these fields, I am getting:
Seems it works perfectly fine with integers and text based domains on
7.3 and 7.4. Do you have a full failing example?
junk=# create domain int as integer;
CREATE DOMAIN
junk=# create table a (col1 int);
CREATE TABLE
junk=# create table b (col1 int);
CREATE TABLE
junk=# select * from a full outer join b using (col1);
col1
------
(0 rows)
junk=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4
(1 row)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: merge-join for domain with underlying type text
2003-05-10 21:04 merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
2003-05-10 22:13 ` Re: merge-join for domain with underlying type text Rod Taylor <rbt@rbt.ca>
@ 2003-05-10 23:40 ` Randall Lucas <rlucas@tercent.net>
2003-05-11 00:48 ` Re: merge-join for domain with underlying type text Rod Taylor <rbt@rbt.ca>
2003-05-11 00:53 ` Re: merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
0 siblings, 2 replies; 6+ messages in thread
From: Randall Lucas @ 2003-05-10 23:40 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
-- Hmm, here's some code that manifests the problem:
-- using text itself:
create table text_test_1 (
key text,
stuff varchar
);
insert into text_test_1 values ('alpha', 'asdflkjasdf');
insert into text_test_1 values ('bravo', 'asdfdasfsaff');
insert into text_test_1 values ('delta', 'asdfasfdas');
create table text_test_2 (
other_key text,
more_stuff varchar
);
insert into text_test_2 values ('charlie', 'asdfasfasfda');
insert into text_test_2 values ('delta', 'asgasgdda');
insert into text_test_2 values ('echo', 'asdasfsdfsfda');
select * from text_test_1 full outer join text_test_2 on
text_test_1.key = text_test_2.other_key;
-- Works OK, BUT:
-- using domain textual:
create domain textual_test as text;
create table textual_test_1 (
key textual_test,
stuff varchar
);
insert into textual_test_1 values ('alpha', 'asdflkjasdf');
insert into textual_test_1 values ('bravo', 'asdfdasfsaff');
insert into textual_test_1 values ('delta', 'asdfasfdas');
create table textual_test_2 (
other_key textual_test,
more_stuff varchar
);
insert into textual_test_2 values ('charlie', 'asdfasfasfda');
insert into textual_test_2 values ('delta', 'asgasgdda');
insert into textual_test_2 values ('echo', 'asdasfsdfsfda');
select * from textual_test_1 full outer join textual_test_2 on
textual_test_1.key = textual_test_2.other_key;
-- Will give ERROR: FULL JOIN is only supported with mergejoinable
join conditions
-- clean up
drop table text_test_1;
drop table text_test_2;
drop table textual_test_1;
drop table textual_test_2;
drop domain textual_test;
-- Best,
--
-- Randall
On Saturday, May 10, 2003, at 06:13 PM, Rod Taylor wrote:
> On Sat, 2003-05-10 at 17:04, Randall Lucas wrote:
>> Hi Folks,
>>
>> If I have a domain defined with an underlying type of "text" or
>> "varchar," and I need to FULL OUTER JOIN two tables based upon the
>> similarity in these fields, I am getting:
>
> Seems it works perfectly fine with integers and text based domains on
> 7.3 and 7.4. Do you have a full failing example?
>
> junk=# create domain int as integer;
> CREATE DOMAIN
> junk=# create table a (col1 int);
> CREATE TABLE
> junk=# create table b (col1 int);
> CREATE TABLE
> junk=# select * from a full outer join b using (col1);
> col1
> ------
> (0 rows)
>
> junk=# select version();
> version
> -----------------------------------------------------------------------
> -
> PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4
> (1 row)
>
> --
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
> <signature.asc>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: merge-join for domain with underlying type text
2003-05-10 21:04 merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
2003-05-10 22:13 ` Re: merge-join for domain with underlying type text Rod Taylor <rbt@rbt.ca>
2003-05-10 23:40 ` Re: merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
@ 2003-05-11 00:48 ` Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Rod Taylor @ 2003-05-11 00:48 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: pgsql-sql
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
CAST(textual_test_1.key AS text) = CAST(textual_test_2.other_key AS
text);
-- LOG --
h=# create domain textual_test as text;
CREATE DOMAIN
h=# create table textual_test_1 (
h(# key textual_test,
h(# stuff varchar
h(# );
CREATE TABLE
h=# insert into textual_test_1 values ('alpha', 'asdflkjasdf');
INSERT 154456 1
h=# insert into textual_test_1 values ('bravo', 'asdfdasfsaff');
INSERT 154457 1
h=# insert into textual_test_1 values ('delta', 'asdfasfdas');
INSERT 154458 1
h=# create table textual_test_2 (
h(# other_key textual_test,
h(# more_stuff varchar
h(# );
CREATE TABLE
h=# insert into textual_test_2 values ('charlie', 'asdfasfasfda');
INSERT 154464 1
h=# insert into textual_test_2 values ('delta', 'asgasgdda');
INSERT 154465 1
h=# insert into textual_test_2 values ('echo', 'asdasfsdfsfda');
INSERT 154466 1
h=# select * from textual_test_1 full outer join textual_test_2 on
textual_test_1.key = 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)
h=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4
(1 row)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: merge-join for domain with underlying type text
2003-05-10 21:04 merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
2003-05-10 22:13 ` Re: merge-join for domain with underlying type text Rod Taylor <rbt@rbt.ca>
2003-05-10 23:40 ` Re: merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
@ 2003-05-11 00:53 ` Randall Lucas <rlucas@tercent.net>
2003-05-11 03:19 ` Re: merge-join for domain with underlying type text Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Randall Lucas @ 2003-05-11 00:53 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: Rod Taylor <rbt@rbt.ca>; pgsql-sql
OK, I ended up wrapping up the text equality and comparison functions
in simple sql functions that explicitly take my domain as the type,
then added =, <>, <, <=, >, >= operators pointing to those functions,
and now I can perform full outer joins with my domain "literal."
See below for ugly code.
Hackers, why is this? It seems to me that since everything else (or
everything I've run into so far, up to the full outer joins) is
implicitly the same for a simple domain and its underlying base type,
that it would make sense if this, too, Just Worked. Perhaps we could
have the create domain function implicitly perform the operator mapping
to underlying basetypes to permit merge joins?
Best,
Randall
-- begin ugly code:
create domain literal as text;
create or replace function literaleq(literal, literal) returns boolean
as '
select texteq($1::text, $2::text);
' language sql;
create or replace function literalne(literal, literal) returns boolean
as '
select textne($1::text, $2::text);
' language sql;
create or replace function literal_lt(literal, literal) returns boolean
as '
select text_lt($1::text, $2::text);
' language sql;
create or replace function literal_le(literal, literal) returns boolean
as '
select text_le($1::text, $2::text);
' language sql;
create or replace function literal_gt(literal, literal) returns boolean
as '
select text_gt($1::text, $2::text);
' language sql;
create or replace function literal_ge(literal, literal) returns boolean
as '
select text_ge($1::text, $2::text);
' language sql;
create operator < (
leftarg = literal,
rightarg = literal,
procedure = literal_lt,
commutator = >,
negator = >=,
restrict = scalarltsel,
join = scalarltjoinsel
);
create operator <= (
leftarg = literal,
rightarg = literal,
procedure = literal_le,
commutator = >=,
negator = >,
restrict = scalarltsel,
join = scalarltjoinsel
);
create operator > (
leftarg = literal,
rightarg = literal,
procedure = literal_gt,
commutator = <,
negator = <=,
restrict = scalargtsel,
join = scalargtjoinsel
);
create operator >= (
leftarg = literal,
rightarg = literal,
procedure = literal_ge,
commutator = <=,
negator = <,
restrict = scalargtsel,
join = scalargtjoinsel
);
create operator = (
leftarg = literal,
rightarg = literal,
procedure = literaleq,
commutator = =,
negator = <>,
restrict = eqsel,
join = eqjoinsel,
hashes,
sort1 = <,
sort2 = <
);
create operator <> (
leftarg = literal,
rightarg = literal,
procedure = literaleq,
commutator = <>,
negator = =,
restrict = neqsel,
join = neqjoinsel,
hashes,
sort1 = <,
sort2 = <
);
On Saturday, May 10, 2003, at 07:40 PM, Randall Lucas wrote:
> create table textual_test_1 (
> key textual_test,
> stuff varchar
> );
> insert into textual_test_1 values ('alpha', 'asdflkjasdf');
> insert into textual_test_1 values ('bravo', 'asdfdasfsaff');
> insert into textual_test_1 values ('delta', 'asdfasfdas');
> create table textual_test_2 (
> other_key textual_test,
> more_stuff varchar
> );
> insert into textual_test_2 values ('charlie', 'asdfasfasfda');
> insert into textual_test_2 values ('delta', 'asgasgdda');
> insert into textual_test_2 values ('echo', 'asdasfsdfsfda');
> select * from textual_test_1 full outer join textual_test_2 on
> textual_test_1.key = textual_test_2.other_key;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: merge-join for domain with underlying type text
2003-05-10 21:04 merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
2003-05-10 22:13 ` Re: merge-join for domain with underlying type text Rod Taylor <rbt@rbt.ca>
2003-05-10 23:40 ` Re: merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
2003-05-11 00:53 ` Re: merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
@ 2003-05-11 03:19 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-05-11 03:19 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: Rod Taylor <rbt@rbt.ca>; pgsql-sql
Randall Lucas <rlucas@tercent.net> writes:
> Hackers, why is this? It seems to me that since everything else (or
> everything I've run into so far, up to the full outer joins) is
> implicitly the same for a simple domain and its underlying base type,
> that it would make sense if this, too, Just Worked.
It does Just Work ... in CVS tip. I can't make it magically work in
7.3 --- at least not without back-patching a lot of stuff that hasn't
been through beta-testing yet.
The problem is that mergejoin in existing releases will only work on
plain "Var = Var" clauses. Your domain case doesn't look like that
because of the runtime cast operators.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-05-11 03:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-10 21:04 merge-join for domain with underlying type text Randall Lucas <rlucas@tercent.net>
2003-05-10 22:13 ` Rod Taylor <rbt@rbt.ca>
2003-05-10 23:40 ` Randall Lucas <rlucas@tercent.net>
2003-05-11 00:48 ` Rod Taylor <rbt@rbt.ca>
2003-05-11 00:53 ` Randall Lucas <rlucas@tercent.net>
2003-05-11 03:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox