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.
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 |