From: | "Phil Mayers" <p(dot)obfuscate(dot)mayers(at)ic(dot)ac(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FULL JOIN: macaddr equality is not a mergejoinable join condition? |
Date: | 2001-09-20 14:13:32 |
Message-ID: | 9octi7$ici$1@jura.cc.ic.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Try this:
create table interface ( mac macaddr, primary key(mac) );
insert into interface (mac) values ('00:11:22:33:44:55');
insert into interface (mac) values ('00:11:22:33:44:5a');
insert into interface (mac) values ('00:11:22:33:44:5f');
create table host (
mac macaddr,
ip inet,
foreign key (mac) references interface,
primary key (ip)
);
insert into host ( mac, ip ) values ('00:11:22:33:44:55', '192.168.1.1');
insert into host ( mac, ip ) values (null, '192.168.1.2');
select * from interface full join host using (mac);
ERROR: FULL JOIN is only supported with mergejoinable join conditions
If you replace the "macaddr" columns with "varchar(17)":
mac | ip
-------------------+-------------
00:11:22:33:44:55 | 192.168.1.1
00:11:22:33:44:5a |
00:11:22:33:44:5f |
| 192.168.1.2
Why? This seems like a bug. Running on postgresql 7.1.3, compiled from the
source RPM on the download site. If people could CC any replies to my email,
I'd be grateful.
--
Regards,
Phil
+------------------------------------------+
| Phil Mayers |
| Network & Infrastructure Group |
| Information & Communication Technologies |
| Imperial College |
+------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Phil Mayers | 2001-09-20 14:21:30 | inet types and LIKE doesn't work as expected |
Previous Message | Tille, Andreas | 2001-09-20 12:52:46 | Re: Performance question (stripped down the problem) |