Materialize/test/sqllogictest/cockroach/inet.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/inet
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

# not supported yet
halt

mode cockroach

# Basic IPv4 tests

query T
SELECT '192.168.1.2/24':::INET;
----
192.168.1.2/24

query T
SELECT '192.168.1.2/32':::INET;
----
192.168.1.2

query T
SELECT '192.168.1.2':::INET;
----
192.168.1.2

query T
SELECT '192.168.1.2/24':::INET;
----
192.168.1.2/24

query T
SELECT '0.0.0.0':::INET;
----
0.0.0.0

query T
SELECT '::/0'::inet::text::inet;
----
::/0

# Basic IPv6 tests

query T
SELECT '::ffff:192.168.1.2':::INET;
----
::ffff:192.168.1.2

query T
SELECT '::ffff:192.168.1.2/120':::INET;
----
::ffff:192.168.1.2/120

query T
SELECT '::ffff':::INET;
----
::ffff

query T
SELECT '2001:4f8:3:ba:2e0:81ff:fe22:d1f1/120':::INET;
----
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/120

query T
SELECT '2001:4f8:3:ba:2e0:81ff:fe22:d1f1':::INET;
----
2001:4f8:3:ba:2e0:81ff:fe22:d1f1

# Test casting

query T
SELECT '192.168.1.2/24'::INET;
----
192.168.1.2/24


query T
SELECT '192.168.1.200/10':::INET
----
192.168.1.200/10

# Test for less than 4 octets with mask

query T
SELECT '192.168.1/10':::INET
----
192.168.1.0/10

query T
SELECT '192.168/10':::INET
----
192.168.0.0/10

query T
SELECT '192/10':::INET
----
192.0.0.0/10

# Test for preservation of masked bits

query T
SELECT '255/10':::INET
----
255.0.0.0/10

# Test that less than 4 octets requires a mask

statement error could not parse
SELECT '192':::INET

statement error could not parse
SELECT '19.0':::INET

# Test that the mask can't be larger than the provided octet bits

statement error could not parse
SELECT '19.0/32':::INET

statement error could not parse
SELECT '19/32':::INET

statement error could not parse
SELECT '19/16':::INET

query T
SELECT '19/15':::INET
----
19.0.0.0/15

# Misc tests

statement error could not parse
SELECT '192.168/24/1':::INET

statement error could not parse
SELECT '':::INET

statement error could not parse
SELECT '0':::INET

query T
SELECT '0.0.0.0':::INET
----
0.0.0.0

# Testing equivilance

query B
SELECT '::ffff:192.168.0.1/24'::INET = '::ffff:192.168.0.1/24'::INET
----
true

query B
SELECT '::ffff:192.168.0.1/24'::INET = '::ffff:192.168.0.1/25'::INET
----
false

query B
SELECT '::ffff:192.168.0.1/24'::INET = '::ffff:192.168.0.1'::INET
----
false

query B
SELECT '::ffff:192.168.0.1'::INET = '::ffff:192.168.0.1'::INET
----
true

# Ensure IPv4-mapped IPv6 is not equal to its mapped IPv4

query B
SELECT '::ffff:192.168.0.1'::INET = '192.168.0.1'::INET
----
false

query B
SELECT '192.168.0.1'::INET = '192.168.0.1'::INET
----
true

query B
SELECT '192.168.0.1/0'::INET = '192.168.0.1'::INET
----
false

query B
SELECT '192.168.0.1/0'::INET = '192.168.0.1/0'::INET
----
true

query B
SELECT '192.168.0.1/0'::INET = '192.168.0.1/0'::INET
----
true

# Testing basic comparisons

query B
SELECT '192.168.0.2/24'::INET < '192.168.0.1/25'::INET
----
true

query B
SELECT '1.2.3.4':::INET < '1.2.3.5':::INET
----
true

query B
SELECT '192.168.0.1/0'::INET > '192.168.0.1/0'::INET
----
false

query B
SELECT '192.168.0.0'::INET > '192.168.0.1/0'::INET
----
true

query B
SELECT '::ffff:1.2.3.4':::INET > '1.2.3.4':::INET
----
true

# Testing contains/contained by logic

query B
SELECT '192.168.200.95/17'::INET >> '192.168.162.1'::INET
----
true

query B
SELECT '192.168.200.95/8'::INET >> '192.168.2.1/8'::INET
----
false

query B
SELECT '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET >> '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET
----
false

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET >> '2001:0db8:0000:0000:0000:0000:0000:0001/100'::INET
----
false

query B
SELECT '192.168.200.95/8'::INET >>= '192.168.2.1/8'::INET
----
true

query B
SELECT '192.168.200.95/17'::INET >>= '192.168.2.1/24'::INET
----
false

query B
SELECT '192.168.200.95/8'::INET >>= '192.168.2.1/8'::INET
----
true

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET >>= '2001:0db8:0000:0000:0000:0000:0000:0001/100'::INET
----
false

query B
SELECT '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET >>= '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET
----
true

query B
SELECT '192.168.200.95'::INET << '192.168.2.1/8'::INET
----
true

query B
SELECT '192.168.200.95/8'::INET << '192.168.2.1/8'::INET
----
false

query B
SELECT '192.168.200.95'::INET <<= '192.168.2.1/8'::INET
----
true

query B
SELECT '192.168.200.95/8'::INET <<= '192.168.2.1/8'::INET
----
true

query B
SELECT '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET << '2001:0db8:0000:0000:0000:0000:0000:0001/100'::INET
----
false

query B
SELECT '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET << '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET
----
false

query B
SELECT '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET <<= '2001:0db8:0000:0000:0000:0000:0000:0001/100'::INET
----
false

query B
SELECT '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET <<= '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET
----
true

query B
SELECT '192.168.200.95/16'::INET && '192.168.2.1/24'::INET
----
true

query B
SELECT '192.168.200.95/17'::INET && '192.168.2.1/24'::INET
----
false

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET && '2001:0db8:0000:0000:0000:0000:0000:0001/100'::INET
----
false

query B
SELECT '2001:0db8:0000:0000:0500:5000:0000:0001/50'::INET && '2001:0db8:0000:0000:0000:0000:0000:0001/100'::INET
----
true

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET >> '192.168.2.1/8'::INET
----
false

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET >>= '192.168.2.1/8'::INET
----
false

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET << '192.168.2.1/8'::INET
----
false

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET <<= '192.168.2.1/8'::INET
----
false

query B
SELECT '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET && '192.168.2.1/8'::INET
----
false

query B
SELECT '192.168.2.1/8'::INET >> '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET
----
false

query B
SELECT '192.168.2.1/8'::INET >>= '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET
----
false

query B
SELECT '192.168.2.1/8'::INET << '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET
----
false

query B
SELECT '192.168.2.1/8'::INET <<= '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET
----
false

query B
SELECT '192.168.2.1/8'::INET && '2001:0db8:0500:0000:0500:5000:0000:0001/50'::INET
----
false

# Binary operations

query T
SELECT ~'192.168.1.2/10':::INET
----
63.87.254.253/10

query T
SELECT ~'192.168.1.2/0':::INET
----
63.87.254.253/0

query T
SELECT ~'2001:4f8:3:ba::/64':::INET
----
dffe:fb07:fffc:ff45:ffff:ffff:ffff:ffff/64

query T
SELECT '255.255.255.250/2':::INET & '0.5.0.5/17':::INET
----
0.5.0.0/17

query T
SELECT '0000:0564:0000:0aab:0000:0000:0060:0005/23':::INET & 'ffff:ffff:ffff:ffff:ffff:ffff:ffff:0005/123':::INET
----
0:564:0:aab::60:5/123

query T
SELECT '192.168.1.2/1':::INET | '192.168.1.3/17':::INET
----
192.168.1.3/17

query T
SELECT '6e32:8a01:373b:c9ce:8ed5:9f7f:dc7e:5cfc/99':::INET | 'c33e:9867:5c98:f0a2:2b2:abf9:c7a5:67d':::INET
----
ef3e:9a67:7fbb:f9ee:8ef7:bfff:dfff:5efd

statement error pq: cannot AND inet values of different sizes
SELECT '0000:0564:0000:0aab:0000:0000:0060:0005/23':::INET & '192.168.1.2/1':::INET

statement error pq: cannot OR inet values of different sizes
SELECT '0000:0564:0000:0aab:0000:0000:0060:0005/23':::INET | '192.168.1.2/1':::INET

# Addition and Subtraction

query T
SELECT '192.168.1.2':::INET + 184836468
----
203.172.98.118

query T
SELECT '0.0.0.5':::INET - 5
----
0.0.0.0

query T
SELECT '203.172.98.118/23':::INET - 184836468
----
192.168.1.2/23

query T
SELECT '0.0.0.5':::INET - -5
----
0.0.0.10

query T
SELECT '::4104:4066:5de7:b1fa':::INET - 4684658846864486648
----
::ffff:192.168.1.2

query T
SELECT '::4104:4066:5de7:b1fa/121':::INET + -4684658846864486648
----
::ffff:192.168.1.2/121

query T
SELECT '::4104:4066:5de7:b1fa/101':::INET + 2
----
::4104:4066:5de7:b1fc/101

query T
SELECT '::5/128':::INET - -2
----
::7

query I
SELECT '203.172.98.118/17':::INET - '192.168.1.2/1':::INET
----
184836468

query I
SELECT '::4104:4066:5de7:b1fa/79':::INET - '::ffff:192.168.1.2/44':::INET
----
4684658846864486648

statement error pq: result out of range
SELECT '255.255.0.5':::INET + 2000000000

statement error pq: result out of range
SELECT '0.0.0.5':::INET - 10

statement error pq: result out of range
SELECT '::5/128':::INET - 10

statement error pq: result out of range
SELECT 'ff00:5::/128':::INET - '::ff00:5/128':::INET

# Edge case: postgres compatibility

query T
SELECT '0.0.0.0.':::INET
----
0.0.0.0

statement error could not parse
SELECT '.0.0.0.0.':::INET

statement error could not parse
SELECT '0.0.0.0.0':::INET

# Test storage round-trip

statement ok
CREATE TABLE u (ip inet PRIMARY KEY,
                ip2 inet)

statement ok
INSERT INTO u VALUES ('192.168.0.1', '192.168.0.1')

statement error duplicate key value
INSERT INTO u VALUES ('192.168.0.1', '192.168.0.2')

statement ok
INSERT INTO u VALUES ('192.168.0.2', '192.168.0.2')

statement ok
INSERT INTO u VALUES ('192.168.0.5/24', '192.168.0.5')

statement ok
INSERT INTO u VALUES ('192.168.0.1/31', '192.168.0.1')

statement ok
INSERT INTO u VALUES ('192.168.0.0', '192.168.0.1')

statement ok
INSERT INTO u VALUES ('192.0.0.0', '127.0.0.1')

statement ok
INSERT INTO u (ip) VALUES ('::1')

statement ok
INSERT INTO u (ip) VALUES ('::ffff:1.2.3.4')

query TT
SELECT * FROM u ORDER BY ip
----
192.168.0.5/24  192.168.0.5
192.168.0.1/31  192.168.0.1
192.0.0.0       127.0.0.1
192.168.0.0     192.168.0.1
192.168.0.1     192.168.0.1
192.168.0.2     192.168.0.2
::1             NULL
::ffff:1.2.3.4  NULL

statement ok
CREATE TABLE arrays (ips INET[])

statement ok
INSERT INTO arrays VALUES
    (ARRAY[]),
    (ARRAY['192.168.0.1/10', '::1']),
    (ARRAY['192.168.0.1', '192.168.0.1/10', '::1', '::ffff:1.2.3.4'])

query T rowsort
SELECT * FROM arrays
----
{}
{192.168.0.1/10,::1}
{192.168.0.1,192.168.0.1/10,::1,::ffff:1.2.3.4}


# Testing builtins

# Test abbrev
# For INET, abbev has the same output as ::string. This is not the case for
# CIDR. The input string is not always equal to the output string, e.g.
# abbrev('10.0/16'::inet) => '10.0.0.0/16'

query T
SELECT abbrev('10.1.0.0/16'::INET)
----
10.1.0.0/16

query T
SELECT abbrev('192.168.0.1/16'::INET)
----
192.168.0.1/16

query T
SELECT abbrev('192.168.0.1'::INET)
----
192.168.0.1

query T
SELECT abbrev('192.168.0.1/32'::INET)
----
192.168.0.1

query T
SELECT abbrev('10.0/16'::INET)
----
10.0.0.0/16

query T
SELECT abbrev('::ffff:192.168.0.1'::INET)
----
::ffff:192.168.0.1

query T
SELECT abbrev('::ffff:192.168.0.1/24'::INET)
----
::ffff:192.168.0.1/24

# Test broadcast

query T
SELECT broadcast('10.1.0.0/16'::INET)
----
10.1.255.255/16

query T
SELECT broadcast('192.168.0.1/16'::INET)
----
192.168.255.255/16

query T
SELECT broadcast('192.168.0.1'::INET)
----
192.168.0.1

query T
SELECT broadcast('192.168.0.1/32'::INET)
----
192.168.0.1

query T
SELECT broadcast('::ffff:192.168.0.1'::INET)
----
::ffff:192.168.0.1

query T
SELECT broadcast('::ffff:1.2.3.1/20'::INET)
----
0:fff:ffff:ffff:ffff:ffff:ffff:ffff/20

query T
SELECT broadcast('2001:4f8:3:ba::/64'::INET)
----
2001:4f8:3:ba:ffff:ffff:ffff:ffff/64

# Test family

query I
SELECT family('10.1.0.0/16'::INET)
----
4

query I
SELECT family('192.168.0.1/16'::INET)
----
4

query I
SELECT family('192.168.0.1'::INET)
----
4

query I
SELECT family('::ffff:192.168.0.1'::INET)
----
6

query I
SELECT family('::ffff:1.2.3.1/20'::INET)
----
6

query I
SELECT family('2001:4f8:3:ba::/64'::INET)
----
6

# Test host

query T
SELECT host('10.1.0.0/16'::INET)
----
10.1.0.0

query T
SELECT host('192.168.0.1/16'::INET)
----
192.168.0.1

query T
SELECT host('192.168.0.1'::INET)
----
192.168.0.1

query T
SELECT host('192.168.0.1/32'::INET)
----
192.168.0.1

query T
SELECT host('::ffff:192.168.0.1'::INET)
----
::ffff:192.168.0.1

query T
SELECT host('::ffff:192.168.0.1/24'::INET)
----
::ffff:192.168.0.1

# Test hostmask

query T
SELECT hostmask('192.168.1.2'::INET)
----
0.0.0.0

query T
SELECT hostmask('192.168.1.2/16'::INET)
----
0.0.255.255

query T
SELECT hostmask('192.168.1.2/10'::INET)
----
0.63.255.255

query T
SELECT hostmask('2001:4f8:3:ba::/64'::INET)
----
::ffff:ffff:ffff:ffff

# Test masklen

query I
SELECT masklen('192.168.1.2'::INET)
----
32

query I
SELECT masklen('192.168.1.2/16'::INET)
----
16

query I
SELECT masklen('192.168.1.2/10'::INET)
----
10

query I
SELECT masklen('2001:4f8:3:ba::/64'::INET)
----
64

query I
SELECT masklen('2001:4f8:3:ba::'::INET)
----
128

# Test netmask

query T
SELECT netmask('192.168.1.2'::INET)
----
255.255.255.255

query T
SELECT netmask('192.168.1.2/16'::INET)
----
255.255.0.0

query T
SELECT netmask('192.168.1.2/10'::INET)
----
255.192.0.0

query T
SELECT netmask('192.168.1.2/0'::INET)
----
0.0.0.0

query T
SELECT netmask('2001:4f8:3:ba::/64'::INET)
----
ffff:ffff:ffff:ffff::

query T
SELECT netmask('2001:4f8:3:ba::/0'::INET)
----
::

query T
SELECT netmask('2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128'::INET)
----
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff

query T
SELECT netmask('::ffff:1.2.3.1/120'::INET)
----
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ff00

query T
SELECT netmask('::ffff:1.2.3.1/20'::INET)
----
ffff:f000::

# Test set_masklen

query T
SELECT set_masklen('10.1.0.0/16'::INET, 10)
----
10.1.0.0/10

query T
SELECT set_masklen('192.168.0.1/16'::INET, 32)
----
192.168.0.1

statement error invalid mask length
SELECT set_masklen('192.168.0.1'::INET, 100)

statement error invalid mask length
SELECT set_masklen('192.168.0.1'::INET, 33)

statement error invalid mask length
SELECT set_masklen('192.168.0.1'::INET, -1)

query T
SELECT set_masklen('192.168.0.1'::INET, 0)
----
192.168.0.1/0

query T
SELECT set_masklen('::ffff:192.168.0.1'::INET, 100)
----
::ffff:192.168.0.1/100

statement error invalid mask length
SELECT set_masklen('::ffff:192.168.0.1'::INET, -1)

statement error invalid mask length
SELECT set_masklen('::ffff:192.168.0.1'::INET, 129)

query T
SELECT set_masklen('::ffff:192.168.0.1/24'::INET, 0)
----
::ffff:192.168.0.1/0

# Test text
# The difference between text and abbrev/::string is that text always outputs
# the prefix length, whereas abbrev omit it when the prefix length is the
# total bits size (32 for IPv4, 128 for IPv6).

query T
SELECT text('10.1.0.0/16'::INET)
----
10.1.0.0/16

query T
SELECT text('192.168.0.1/16'::INET)
----
192.168.0.1/16

query T
SELECT text('192.168.0.1'::INET)
----
192.168.0.1/32

query T
SELECT text('192.168.0.1/32'::INET)
----
192.168.0.1/32

query T
SELECT text('::ffff:192.168.0.1'::INET)
----
::ffff:192.168.0.1/128

query T
SELECT text('::ffff:192.168.0.1/24'::INET)
----
::ffff:192.168.0.1/24

# Test inet_same_family

query T
SELECT text('::ffff:192.168.0.1/24'::INET)
----
::ffff:192.168.0.1/24

# Verify the inet datum gets serialized correctly for distsql.
query T
SELECT host(max('192.168.0.2/24'::INET)) FROM (VALUES (1)) AS t(x)
----
192.168.0.2