Обсуждение: Problem with inet type.

Поиск
Список
Период
Сортировка

Problem with inet type.

От
Piotr Gadek
Дата:
============================================================================

                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================



Your name  : Piotr Gadek
Your email address : p.gadek@bph.krakow.pl


System Configuration
---------------------
  Architecture (example: Intel Pentium)   : Cyrix 6x86 P166+

  Operating System (example: Linux 2.0.26 ELF)  : Redhat 6.0 (kernel
2.2.5-22)

  PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-6.5.1

  Compiler used (example:  gcc 2.8.0)  : egcs-2.91.66


Please enter a FULL description of your problem:
------------------------------------------------
Sorting by "inet" type field gives incorrect result.
IP addresses are sorted due to subnets order but
within a subnet adresses are sorted in the wrong way.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Below is a simple script which creates table and
executes query.

--
DROP TABLE HOSTS;
CREATE TABLE HOSTS (i inet);

insert into hosts (i) values ('139.0.6.0/24');
insert into hosts (i) values ('139.0.6.205/24');
insert into hosts (i) values ('139.0.6.3/24');
insert into hosts (i) values ('139.0.6.5/24');
insert into hosts (i) values ('139.0.6.6/24');
insert into hosts (i) values ('139.0.6.10/24');
insert into hosts (i) values ('139.0.6.32/24');
insert into hosts (i) values ('139.0.6.33/24');
insert into hosts (i) values ('139.0.6.35/24');
insert into hosts (i) values ('139.0.6.36/24');
insert into hosts (i) values ('139.0.6.37/24');
insert into hosts (i) values ('139.0.6.102/24');
insert into hosts (i) values ('139.0.6.101/24');
insert into hosts (i) values ('139.0.6.190/24');
insert into hosts (i) values ('139.0.6.11/24');
insert into hosts (i) values ('139.0.6.192/24');
insert into hosts (i) values ('139.0.6.191/24');
insert into hosts (i) values ('139.0.6.200/24');
insert into hosts (i) values ('139.0.6.2/24');
insert into hosts (i) values ('139.0.6.206/24');
insert into hosts (i) values ('139.0.6.209/30');
insert into hosts (i) values ('139.0.6.213/30');
insert into hosts (i) values ('139.0.6.210/30');
insert into hosts (i) values ('139.0.6.214/30');
insert into hosts (i) values ('139.0.6.39/24');
insert into hosts (i) values ('139.0.6.38/24');
insert into hosts (i) values ('139.0.6.195/24');
insert into hosts (i) values ('139.0.6.194/24');
insert into hosts (i) values ('139.0.6.196/24');
insert into hosts (i) values ('139.1.113.17/28');
insert into hosts (i) values ('139.1.113.1/28');
insert into hosts (i) values ('139.1.113.33/28');
insert into hosts (i) values ('139.1.113.2/28');
insert into hosts (i) values ('139.1.113.18/28');
insert into hosts (i) values ('139.0.6.243/24');
insert into hosts (i) values ('139.0.6.222/24');
insert into hosts (i) values ('139.0.9.39/24');
insert into hosts (i) values ('139.0.7.10/24');
insert into hosts (i) values ('139.0.9.10/24');
insert into hosts (i) values ('139.0.9.1/24');
insert into hosts (i) values ('139.0.7.2/24');
insert into hosts (i) values ('139.1.113.34/28');
insert into hosts (i) values ('139.1.113.5/28');
insert into hosts (i) values ('139.1.113.24/28');
insert into hosts (i) values ('139.1.113.7/28');
insert into hosts (i) values ('139.1.113.15/28');
insert into hosts (i) values ('139.1.113.4/28');
insert into hosts (i) values ('139.1.113.8/28');
insert into hosts (i) values ('139.1.113.25/28');
insert into hosts (i) values ('139.1.113.22/28');
insert into hosts (i) values ('139.1.113.13/28');
insert into hosts (i) values ('129.0.0.33/24');
insert into hosts (i) values ('129.0.0.30/24');
insert into hosts (i) values ('129.0.0.20/24');
insert into hosts (i) values ('129.0.0.1/24');
insert into hosts (i) values ('10.0.0.7/24');
insert into hosts (i) values ('10.0.0.1/24');
insert into hosts (i) values ('10.0.0.10/24');
insert into hosts (i) values ('10.0.0.231/24');
insert into hosts (i) values ('10.0.0.27/24');
insert into hosts (i) values ('10.2.0.27/24');
insert into hosts (i) values ('10.3.0.27/24');
insert into hosts (i) values ('10.2.0.2/24');
insert into hosts (i) values ('10.3.0.10/24');
insert into hosts (i) values ('10.2.0.200/24');
insert into hosts (i) values ('10.2.0.0/24');

--
select i from hosts order by i;
--

Here is the result I obtained after script execution:

i
---------------
10.0.0.7/24
10.0.0.1/24
10.0.0.10/24
10.0.0.231/24
10.0.0.27/24
10.2.0.27/24
10.2.0.2/24
10.2.0.200/24
10.2.0.0/24
10.3.0.27/24
10.3.0.10/24
129.0.0.33/24
129.0.0.30/24
129.0.0.20/24
129.0.0.1/24
139.0.6.0/24
139.0.6.205/24
139.0.6.3/24
139.0.6.5/24
139.0.6.6/24
139.0.6.10/24
139.0.6.32/24
139.0.6.33/24
139.0.6.35/24
139.0.6.36/24
139.0.6.37/24
139.0.6.102/24
139.0.6.101/24
139.0.6.190/24
139.0.6.11/24
139.0.6.192/24
139.0.6.191/24
139.0.6.200/24
139.0.6.2/24
139.0.6.206/24
139.0.6.39/24
139.0.6.38/24
139.0.6.195/24
139.0.6.194/24
139.0.6.196/24
139.0.6.209/30
139.0.6.210/30
139.0.6.213/30
139.0.6.214/30
139.0.6.243/24
139.0.6.222/24
139.0.7.10/24
139.0.7.2/24
139.0.9.39/24
139.0.9.10/24
139.0.9.1/24
139.1.113.1/28
139.1.113.2/28
139.1.113.5/28
139.1.113.7/28
139.1.113.15/28
139.1.113.4/28
139.1.113.8/28
139.1.113.13/28
139.1.113.17/28
139.1.113.18/28
139.1.113.24/28
139.1.113.25/28
139.1.113.22/28
139.1.113.33/28
139.1.113.34/28
(66 rows)


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------



Regards
Piotr