BUG #7869: Expressions in a view for rows that are not part of the view result set are still evaluated

From: bernardjech(at)yahoo(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7869: Expressions in a view for rows that are not part of the view result set are still evaluated
Date: 2013-02-12 16:39:42
Message-ID: E1U5ItS-0003HY-NQ@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 7869
Logged by: Bernard Jech
Email address: bernardjech(at)yahoo(dot)com
PostgreSQL version: 9.2.3
Operating system: Mac OS X 10.6.8
Description:

-- Create the tables:

CREATE TABLE netblock (
id INTEGER,
ip_address inet NOT NULL
);

CREATE TABLE ipv4 (
netblock_id integer
);

-- Insert data:

INSERT INTO netblock VALUES(1, '2605:ee00::/32');
INSERT INTO netblock VALUES(2, '68.67.181.7/26');
INSERT INTO ipv4 VALUES(2);

-- Create the view:

CREATE OR REPLACE VIEW subnet AS
SELECT ipv4.netblock_id AS id,
set_masklen(n.ip_address, 32) - inet '0.0.0.0' AS cidr_address
FROM ipv4 JOIN netblock n ON ipv4.netblock_id = n.id;

If you select all rows from the view subnet, you get the expected result:

pokus=> select * from subnet;
id | cidr_address
----+--------------
2 | 1145287943
(1 row)

The surprising part comes when you add a WHERE clause to the SELECT
statement to select only this one row:

pokus=> select * from subnet where cidr_address = 1145287943;
ERROR: cannot subtract inet values of different sizes

This is apparently caused by the IPv6 entry in the netblock table but this
row should be excluded by the inner JOIN in the VIEW definition.

Browse pgsql-bugs by date

  From Date Subject
Next Message Greg Sabino Mullane 2013-02-13 14:55:22 Unlisten / listen in a transaction failure
Previous Message Tony Kurz 2013-02-12 12:39:15 Postgres 9.1 and 9.2 crashing with exit code 9