Make inet/cidr << and <<= operators indexable. From Alex Pilosov <alex@pilosoft.com>.

2 files changed, 81 insertions, 12 deletions

diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index 27f4e4e85a..0d41d582de 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -7,6 +7,10 @@ ERROR:  table "inet_tbl" does not exist
 CREATE TABLE INET_TBL (c cidr, i inet);
 INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.226/24');
 INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.0/24', '192.168.1.226');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/25');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/25');
 INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8');
 INSERT INTO INET_TBL (c, i) VALUES ('10.0.0.0', '10.1.2.3/8');
 INSERT INTO INET_TBL (c, i) VALUES ('10.1.2.3', '10.1.2.3/32');
@@ -26,6 +30,10 @@ SELECT '' AS ten, c AS cidr, i AS inet FROM INET_TBL;
 -----+----------------+------------------
      | 192.168.1.0/24 | 192.168.1.226/24
      | 192.168.1.0/24 | 192.168.1.226
+     | 192.168.1.0/24 | 192.168.1.0/24
+     | 192.168.1.0/24 | 192.168.1.0/25
+     | 192.168.1.0/24 | 192.168.1.255/24
+     | 192.168.1.0/24 | 192.168.1.255/25
      | 10.0.0.0/8     | 10.1.2.3/8
      | 10.0.0.0/32    | 10.1.2.3/8
      | 10.1.2.3/32    | 10.1.2.3
@@ -34,7 +42,7 @@ SELECT '' AS ten, c AS cidr, i AS inet FROM INET_TBL;
      | 10.0.0.0/8     | 10.1.2.3/8
      | 10.0.0.0/8     | 11.1.2.3/8
      | 10.0.0.0/8     | 9.1.2.3/8
-(10 rows)
+(14 rows)
 
 -- now test some support functions
 SELECT '' AS ten, i AS inet, host(i), text(i) FROM INET_TBL;
@@ -42,6 +50,10 @@ SELECT '' AS ten, i AS inet, host(i), text(i) FROM INET_TBL;
 -----+------------------+---------------+------------------
      | 192.168.1.226/24 | 192.168.1.226 | 192.168.1.226/24
      | 192.168.1.226    | 192.168.1.226 | 192.168.1.226/32
+     | 192.168.1.0/24   | 192.168.1.0   | 192.168.1.0/24
+     | 192.168.1.0/25   | 192.168.1.0   | 192.168.1.0/25
+     | 192.168.1.255/24 | 192.168.1.255 | 192.168.1.255/24
+     | 192.168.1.255/25 | 192.168.1.255 | 192.168.1.255/25
      | 10.1.2.3/8       | 10.1.2.3      | 10.1.2.3/8
      | 10.1.2.3/8       | 10.1.2.3      | 10.1.2.3/8
      | 10.1.2.3         | 10.1.2.3      | 10.1.2.3/32
@@ -50,7 +62,7 @@ SELECT '' AS ten, i AS inet, host(i), text(i) FROM INET_TBL;
      | 10.1.2.3/8       | 10.1.2.3      | 10.1.2.3/8
      | 11.1.2.3/8       | 11.1.2.3      | 11.1.2.3/8
      | 9.1.2.3/8        | 9.1.2.3       | 9.1.2.3/8
-(10 rows)
+(14 rows)
 
 SELECT '' AS ten, c AS cidr, broadcast(c),
   i AS inet, broadcast(i) FROM INET_TBL;
@@ -58,6 +70,10 @@ SELECT '' AS ten, c AS cidr, broadcast(c),
 -----+----------------+------------------+------------------+------------------
      | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.226/24 | 192.168.1.255/24
      | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.226    | 192.168.1.226
+     | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.0/24   | 192.168.1.255/24
+     | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.0/25   | 192.168.1.127/25
+     | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.255/24 | 192.168.1.255/24
+     | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.255/25 | 192.168.1.255/25
      | 10.0.0.0/8     | 10.255.255.255/8 | 10.1.2.3/8       | 10.255.255.255/8
      | 10.0.0.0/32    | 10.0.0.0         | 10.1.2.3/8       | 10.255.255.255/8
      | 10.1.2.3/32    | 10.1.2.3         | 10.1.2.3         | 10.1.2.3
@@ -66,7 +82,7 @@ SELECT '' AS ten, c AS cidr, broadcast(c),
      | 10.0.0.0/8     | 10.255.255.255/8 | 10.1.2.3/8       | 10.255.255.255/8
      | 10.0.0.0/8     | 10.255.255.255/8 | 11.1.2.3/8       | 11.255.255.255/8
      | 10.0.0.0/8     | 10.255.255.255/8 | 9.1.2.3/8        | 9.255.255.255/8
-(10 rows)
+(14 rows)
 
 SELECT '' AS ten, c AS cidr, network(c) AS "network(cidr)",
   i AS inet, network(i) AS "network(inet)" FROM INET_TBL;
@@ -74,6 +90,10 @@ SELECT '' AS ten, c AS cidr, network(c) AS "network(cidr)",
 -----+----------------+----------------+------------------+------------------
      | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.226/24 | 192.168.1.0/24
      | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.226    | 192.168.1.226/32
+     | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.0/24   | 192.168.1.0/24
+     | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.0/25   | 192.168.1.0/25
+     | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.255/24 | 192.168.1.0/24
+     | 192.168.1.0/24 | 192.168.1.0/24 | 192.168.1.255/25 | 192.168.1.128/25
      | 10.0.0.0/8     | 10.0.0.0/8     | 10.1.2.3/8       | 10.0.0.0/8
      | 10.0.0.0/32    | 10.0.0.0/32    | 10.1.2.3/8       | 10.0.0.0/8
      | 10.1.2.3/32    | 10.1.2.3/32    | 10.1.2.3         | 10.1.2.3/32
@@ -82,7 +102,7 @@ SELECT '' AS ten, c AS cidr, network(c) AS "network(cidr)",
      | 10.0.0.0/8     | 10.0.0.0/8     | 10.1.2.3/8       | 10.0.0.0/8
      | 10.0.0.0/8     | 10.0.0.0/8     | 11.1.2.3/8       | 11.0.0.0/8
      | 10.0.0.0/8     | 10.0.0.0/8     | 9.1.2.3/8        | 9.0.0.0/8
-(10 rows)
+(14 rows)
 
 SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)",
   i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL;
@@ -90,6 +110,10 @@ SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)",
 -----+----------------+---------------+------------------+---------------
      | 192.168.1.0/24 |            24 | 192.168.1.226/24 |            24
      | 192.168.1.0/24 |            24 | 192.168.1.226    |            32
+     | 192.168.1.0/24 |            24 | 192.168.1.0/24   |            24
+     | 192.168.1.0/24 |            24 | 192.168.1.0/25   |            25
+     | 192.168.1.0/24 |            24 | 192.168.1.255/24 |            24
+     | 192.168.1.0/24 |            24 | 192.168.1.255/25 |            25
      | 10.0.0.0/8     |             8 | 10.1.2.3/8       |             8
      | 10.0.0.0/32    |            32 | 10.1.2.3/8       |             8
      | 10.1.2.3/32    |            32 | 10.1.2.3         |            32
@@ -98,7 +122,7 @@ SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)",
      | 10.0.0.0/8     |             8 | 10.1.2.3/8       |             8
      | 10.0.0.0/8     |             8 | 11.1.2.3/8       |             8
      | 10.0.0.0/8     |             8 | 9.1.2.3/8        |             8
-(10 rows)
+(14 rows)
 
 SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)",
   i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL
@@ -113,10 +137,11 @@ SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)",
 
 SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
   WHERE c = i;
- six |    cidr     |   inet   
------+-------------+----------
-     | 10.1.2.3/32 | 10.1.2.3
-(1 row)
+ six |      cidr      |      inet      
+-----+----------------+----------------
+     | 192.168.1.0/24 | 192.168.1.0/24
+     | 10.1.2.3/32    | 10.1.2.3
+(2 rows)
 
 SELECT '' AS ten, i, c,
   i < c AS lt, i <= c AS le, i = c AS eq, 
@@ -128,6 +153,10 @@ SELECT '' AS ten, i, c,
 -----+------------------+----------------+----+----+----+----+----+----+----+-----+-----+-----
      | 192.168.1.226/24 | 192.168.1.0/24 | f  | f  | f  | t  | t  | t  | f  | t   | f   | t
      | 192.168.1.226    | 192.168.1.0/24 | f  | f  | f  | t  | t  | t  | t  | t   | f   | f
+     | 192.168.1.0/24   | 192.168.1.0/24 | f  | t  | t  | t  | f  | f  | f  | t   | f   | t
+     | 192.168.1.0/25   | 192.168.1.0/24 | f  | f  | f  | t  | t  | t  | t  | t   | f   | f
+     | 192.168.1.255/24 | 192.168.1.0/24 | f  | f  | f  | t  | t  | t  | f  | t   | f   | t
+     | 192.168.1.255/25 | 192.168.1.0/24 | f  | f  | f  | t  | t  | t  | t  | t   | f   | f
      | 10.1.2.3/8       | 10.0.0.0/8     | f  | f  | f  | t  | t  | t  | f  | t   | f   | t
      | 10.1.2.3/8       | 10.0.0.0/32    | t  | t  | f  | f  | f  | t  | f  | f   | t   | t
      | 10.1.2.3         | 10.1.2.3/32    | f  | t  | t  | t  | f  | f  | f  | t   | f   | t
@@ -136,14 +165,18 @@ SELECT '' AS ten, i, c,
      | 10.1.2.3/8       | 10.0.0.0/8     | f  | f  | f  | t  | t  | t  | f  | t   | f   | t
      | 11.1.2.3/8       | 10.0.0.0/8     | f  | f  | f  | t  | t  | t  | f  | f   | f   | f
      | 9.1.2.3/8        | 10.0.0.0/8     | t  | t  | f  | f  | f  | t  | f  | f   | f   | f
-(10 rows)
+(14 rows)
 
 -- check the conversion to/from text and set_netmask
 select '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
- ten |   set_masklen
+ ten |   set_masklen    
 -----+------------------
      | 192.168.1.226/24
      | 192.168.1.226/24
+     | 192.168.1.0/24
+     | 192.168.1.0/24
+     | 192.168.1.255/24
+     | 192.168.1.255/24
      | 10.1.2.3/24
      | 10.1.2.3/24
      | 10.1.2.3/24
@@ -152,5 +185,29 @@ select '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
      | 10.1.2.3/24
      | 11.1.2.3/24
      | 9.1.2.3/24
-(10 rows)
+(14 rows)
+
+-- check that index works correctly
+create index inet_idx1 on inet_tbl(i);
+set enable_seqscan to off;
+select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
+       c        |        i         
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/24 | 192.168.1.226
+(3 rows)
+
+select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
+       c        |        i         
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/24 | 192.168.1.226
+(6 rows)
 
+set enable_seqscan to on;
+drop index inet_idx1;
diff --git a/src/test/regress/sql/inet.sql b/src/test/regress/sql/inet.sql
index 6e59714fd9..5cdf688ed8 100644
--- a/src/test/regress/sql/inet.sql
+++ b/src/test/regress/sql/inet.sql
@@ -8,6 +8,10 @@ DROP TABLE INET_TBL;
 CREATE TABLE INET_TBL (c cidr, i inet);
 INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.226/24');
 INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.0/24', '192.168.1.226');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/25');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/24');
+INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/25');
 INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8');
 INSERT INTO INET_TBL (c, i) VALUES ('10.0.0.0', '10.1.2.3/8');
 INSERT INTO INET_TBL (c, i) VALUES ('10.1.2.3', '10.1.2.3/32');
@@ -49,3 +53,11 @@ SELECT '' AS ten, i, c,
 
 -- check the conversion to/from text and set_netmask
 select '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
+-- check that index works correctly
+create index inet_idx1 on inet_tbl(i);
+set enable_seqscan to off;
+select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
+select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
+set enable_seqscan to on;
+drop index inet_idx1;
+