On 7/22/09 4:39 PM, Tom Lane wrote: > "Benjamin Reed" <ran...@opennms.org> writes: >> I've hit a regression vs. PostgreSQL 8.2 and 8.3 (and probably others) >> related to subselects. This query: > > It's not going to be possible to examine this with just the query. > You need to provide a self-contained test case.
Attached is a test case, including the query that causes the error at the end. On 8.3, it returns 1 row (192.168.1.1), on 8.4 including the git 8.4 branch, it returns none. -- Benjamin Reed The OpenNMS Group http://www.opennms.org/
drop table if exists node cascade;
drop table if exists categories cascade;
drop table if exists category_node cascade;
drop table if exists ipinterface cascade;
drop table if exists ifservices cascade;
drop table if exists service cascade;
create table node (
nodeId serial not null,
nodeLabel varchar,
constraint pk_nodeId primary key (nodeId)
);
insert into node values (1, 'node1');
insert into node values (2, 'node2');
insert into node values (3, 'node3');
insert into node values (4, 'node4');
create table ipInterface (
id serial not null,
nodeId integer not null,
ipAddr varchar(16) not null,
constraint ipinterface_pkey PRIMARY KEY (id),
constraint fk_nodeId1 foreign key (nodeId) references node ON DELETE
CASCADE
);
insert into ipInterface values (1, 1, '192.168.1.1');
insert into ipInterface values (2, 1, '192.168.1.2');
insert into ipInterface values (3, 1, '192.168.1.3');
insert into ipInterface values (4, 2, '192.168.2.1');
insert into ipInterface values (5, 2, '192.168.2.2');
insert into ipInterface values (6, 2, '192.168.2.3');
insert into ipInterface values (7, 3, '192.168.3.1');
insert into ipInterface values (8, 3, '192.168.3.2');
insert into ipInterface values (9, 3, '192.168.3.3');
insert into ipInterface values (10, 4, '192.168.4.1');
insert into ipInterface values (11, 4, '192.168.4.2');
insert into ipInterface values (12, 4, '192.168.4.3');
create table categories (
categoryId serial not null,
categoryName varchar(64) not null,
constraint category_pkey primary key (categoryId)
);
create unique index category_idx ON categories(categoryName);
insert into categories values (1, 'DEV_AC');
insert into categories values (2, 'IMP_mid');
insert into categories values (3, 'OPS_Online');
insert into categories values (4, 'some category');
create table category_node (
categoryId integer,
nodeId integer,
constraint categoryid_fkey1 foreign key (categoryId) references
categories (categoryId) ON DELETE CASCADE,
constraint nodeid_fkey1 foreign key (nodeId) references node ON DELETE
CASCADE
);
insert into category_node values (1, 1);
insert into category_node values (2, 1);
insert into category_node values (3, 1);
insert into category_node values (2, 2);
insert into category_node values (3, 3);
insert into category_node values (1, 4);
create table service (
serviceID serial not null,
serviceName varchar(32) not null,
constraint pk_serviceID primary key (serviceID)
);
insert into service values (1, 'ICMP');
insert into service values (2, 'SNMP');
insert into service values (3, 'HTTP');
create table ifServices (
id serial not null,
nodeID integer not null,
ipAddr varchar(16) not null,
serviceID integer not null,
ipInterfaceId integer not null,
constraint ifservices_pkey PRIMARY KEY (id),
constraint ipinterface_fkey FOREIGN KEY (ipInterfaceId) REFERENCES
ipInterface (id) ON DELETE CASCADE,
constraint fk_nodeID3 foreign key (nodeID) references node ON DELETE
CASCADE,
constraint fk_serviceID1 foreign key (serviceID) references service ON
DELETE CASCADE
);
create unique index ifservices_nodeid_ipaddr_svc_unique on ifservices(nodeID,
ipAddr, serviceId);
create index ifservices_nodeid_idx on ifservices(nodeID);
create index ifservices_serviceid_idx on ifservices(serviceID);
create index ifservices_nodeid_serviceid_idx on ifservices(nodeID, serviceID);
create index ifservicves_ipInterfaceId_idx on ifservices(ipInterfaceId);
insert into ifservices values (1, 1, '192.168.1.1', 1, 1);
insert into ifservices values (2, 1, '192.168.1.1', 2, 1);
insert into ifservices values (3, 1, '192.168.1.2', 1, 2);
insert into ifservices values (4, 1, '192.168.1.2', 3, 2);
insert into ifservices values (5, 1, '192.168.1.3', 1, 3);
insert into ifservices values (6, 2, '192.168.2.1', 1, 4);
insert into ifservices values (7, 2, '192.168.2.1', 2, 4);
insert into ifservices values (8, 2, '192.168.2.2', 1, 5);
insert into ifservices values (9, 2, '192.168.2.2', 3, 5);
insert into ifservices values (10, 2, '192.168.2.3', 1, 6);
insert into ifservices values (11, 3, '192.168.3.1', 1, 7);
insert into ifservices values (12, 3, '192.168.3.1', 2, 7);
insert into ifservices values (13, 3, '192.168.3.2', 1, 8);
insert into ifservices values (14, 3, '192.168.3.2', 3, 8);
insert into ifservices values (15, 3, '192.168.3.3', 1, 9);
--# SHOULD return:
--# ipaddr
--# -----------
--# 192.168.1.1
--# (1 row)
--#
--# But on 8.4, returns no rows
SELECT DISTINCT ipInterface.ipAddr FROM ipInterface
JOIN node ON (ipInterface.nodeID = node.nodeID)
JOIN ifServices ON (ipInterface.id = ifServices.ipInterfaceId)
JOIN service ON (ifServices.serviceID = service.serviceID)
WHERE
(
node.nodeID IN (
SELECT category_node.nodeID FROM category_node,
categories
WHERE categories.categoryID = category_node.categoryID
AND categories.categoryName = 'IMP_mid'
)
) AND
(
node.nodeID IN (
SELECT category_node.nodeID FROM category_node,
categories
WHERE categories.categoryID = category_node.categoryID
AND categories.categoryName = 'DEV_AC'
)
) AND
(
node.nodeID IN (
SELECT category_node.nodeID FROM category_node,
categories
WHERE categories.categoryID = category_node.categoryID
AND categories.categoryName = 'OPS_Online'
)
) AND
(node.nodeId = 1) AND
(ipInterface.ipAddr = '192.168.1.1') AND
(service.serviceName = 'ICMP')
LIMIT 1;
signature.asc
Description: OpenPGP digital signature
