insert into inet from text automatically adding subnet

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: insert into inet from text automatically adding subnet
Date: 2019-10-30 11:36:00
Message-ID: CAMa1XUjEEB3Jm-mBHJaBuP=0ohb5cU53Tbx00-J-6cvbjXFsHQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

This appears to me to be a bug (using 11.4). But I would like some review
to ensure I am not missing some intended functionality or nuance with this
data type. The example below seems certainly surprising.

I found that using a writable CTE to insert an IP address without a subnet
into a *text* field, then inserting result into another table with type
*inet*, is automatically adding a subnet. This is the only case in which I
found this happens.

In demo below, you can see everything returns 127.0.0.0, except for this
2-step writable CTE example. Also if I alter the type of the text field to
inet, the subnet addition goes away. So it seems to only affect text ->
inet insert.

Step to reproduce:

postgres=# CREATE TEMP TABLE a (host text);
CREATE TABLE
postgres=# CREATE TEMP TABLE b (host inet);
CREATE TABLE
postgres=# WITH insert_b AS (
INSERT INTO b VALUES ('127.0.0.0')
RETURNING *
)
SELECT host FROM insert_b;
host
-----------
127.0.0.0
(1 row)

postgres=# WITH insert_b AS (
INSERT INTO b VALUES ('127.0.0.0')
RETURNING *
)
INSERT INTO a SELECT host FROM insert_b RETURNING *;
host
--------------
127.0.0.0/32
(1 row)

INSERT 0 1
postgres=# ALTER TABLE a ALTER COLUMN host TYPE inet USING host::inet;
ALTER TABLE
postgres=# WITH insert_b AS (
INSERT INTO b VALUES ('127.0.0.0')
RETURNING *
)
INSERT INTO a SELECT host FROM insert_b RETURNING *;
host
-----------
127.0.0.0
(1 row)

INSERT 0 1

Thanks,
Jeremy

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-10-30 12:54:31 BUG #16089: Index only scan does not happen but expected
Previous Message Federico 2019-10-30 09:59:59 Re: BUG #16053: Query planner performance regression in sql query with multiple CTE in v12