Re: Bug Report

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports(at)postgresql(dot)org(dot)

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs(at)postgresql(dot)org(dot)

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches(at)postgresql(dot)org instead. Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

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

Your name : Douglas M. Westfall
Your email address : dougw(at)net(dot)kent(dot)edu

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel P4 Itanium 2.4

Operating System (example: Linux 2.0.26 ELF) : Linux
2.4.22-1.2087.nptlsmp #1 SMP i686 i686 i386 GNU/Linux (RH9+)

PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4

Compiler used (example: gcc 2.95.2) : RH9 using Ximian RUG rpm's ...
update of 19 Nov 2003

Please enter a FULL description of your problem:
------------------------------------------------
1) table contains a valid inet field
2) query works fine in pgsql 7.2 & 7.3
3) query no longer works in pgsql 7.4

select * from <table> where <field> ilike '%<inetvalue>%' ;

fails with:
ERROR: Unable to identify an operator '~~*' for types 'inet' and
'"unknown"'
You will have to retype this query using an explicit cast

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

select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%' order by
ip2_address asc;

CASTing the operator provides no resolution, as it causes syntax errors,
ie:
select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%'::inet
order by ip2_address asc;
returns:
ERROR: invalid INET value '%10.123.252.4%'
AND:
select * from ipaddrs2 where ip2_address ilike CAST('%10.123.252.4% as
inet) order by ip2_address asc;
returns:
ERROR: invalid INET value '%10.123.252.4%'

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

Apparent problem in ilike function dealing with inet values

Yes, I have searched the archives, and searches for 'inet' or 'ilike' or
'inet+ilike' and 'inet&ilike' return 0 results each.

Have I missed something?

Regards,

DougW

On Thu, 2003-11-20 at 23:09, Douglas M. Westfall wrote:
> If PostgreSQL failed to compile on your computer or you found a bug that
> is likely to be specific to one platform then please fill out this form
> and e-mail it to pgsql-ports(at)postgresql(dot)org(dot)
>
> To report any other bug, fill out the form below and e-mail it to
> pgsql-bugs(at)postgresql(dot)org(dot)
>
> If you not only found the problem but solved it and generated a patch
> then e-mail it to pgsql-patches(at)postgresql(dot)org instead. Please use the
> command "diff -c" to generate the patch.
>
> You may also enter a bug report at http://www.postgresql.org/ instead of
> e-mail-ing this form.
>
> ============================================================================
> POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
>
> Your name : Douglas M. Westfall
> Your email address : dougw(at)net(dot)kent(dot)edu
>
>
> System Configuration
> ---------------------
> Architecture (example: Intel Pentium) : Intel P4 Itanium 2.4
>
> Operating System (example: Linux 2.0.26 ELF) : Linux
> 2.4.22-1.2087.nptlsmp #1 SMP i686 i686 i386 GNU/Linux (RH9+)
>
> PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4
>
> Compiler used (example: gcc 2.95.2) : RH9 using Ximian RUG rpm's ...
> update of 19 Nov 2003
>
>
> Please enter a FULL description of your problem:
> ------------------------------------------------
> 1) table contains a valid inet field
> 2) query works fine in pgsql 7.2 & 7.3
> 3) query no longer works in pgsql 7.4
>
> select * from <table> where <field> ilike '%<inetvalue>%' ;
>
> fails with:
> ERROR: Unable to identify an operator '~~*' for types 'inet' and
> '"unknown"'
> You will have to retype this query using an explicit cast
>
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>
> select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%' order by
> ip2_address asc;
>
> CASTing the operator provides no resolution, as it causes syntax errors,
> ie:
> select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%'::inet
> order by ip2_address asc;
> returns:
> ERROR: invalid INET value '%10.123.252.4%'
> AND:
> select * from ipaddrs2 where ip2_address ilike CAST('%10.123.252.4% as
> inet) order by ip2_address asc;
> returns:
> ERROR: invalid INET value '%10.123.252.4%'
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
>
> Apparent problem in ilike function dealing with inet values
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

On Sat, 22 Nov 2003, Douglas M. Westfall wrote:

> Yes, I have searched the archives, and searches for 'inet' or 'ilike' or
> 'inet+ilike' and 'inet&ilike' return 0 results each.
>
> Have I missed something?

I believe your problem is that you were relying on a cast from inet to
text (on which ilike is defined) which isn't marked as implicit,
you'd probably want:
select * from <table> where CAST(<field> as text) ilike <pattern>;

> > Please enter a FULL description of your problem:
> > ------------------------------------------------
> > 1) table contains a valid inet field
> > 2) query works fine in pgsql 7.2 & 7.3
> > 3) query no longer works in pgsql 7.4
> >
> > select * from <table> where <field> ilike '%<inetvalue>%' ;
> >
> > fails with:
> > ERROR: Unable to identify an operator '~~*' for types 'inet' and
> > '"unknown"'
> > You will have to retype this query using an explicit cast
> >
> >
> > Please describe a way to repeat the problem. Please try to provide a
> > concise reproducible example, if at all possible:
> > ----------------------------------------------------------------------
> >
> > select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%' order by
> > ip2_address asc;
> >
> > CASTing the operator provides no resolution, as it causes syntax errors,
> > ie:
> > select * from ipaddrs2 where ip2_address ilike '%10.123.252.4%'::inet
> > order by ip2_address asc;
> > returns:
> > ERROR: invalid INET value '%10.123.252.4%'
> > AND:
> > select * from ipaddrs2 where ip2_address ilike CAST('%10.123.252.4% as
> > inet) order by ip2_address asc;
> > returns:
> > ERROR: invalid INET value '%10.123.252.4%'

"Douglas M. Westfall" <dougw(at)net(dot)kent(dot)edu> writes:
> 1) table contains a valid inet field
> 2) query works fine in pgsql 7.2 & 7.3
> 3) query no longer works in pgsql 7.4

> select * from <table> where <field> ilike '%<inetvalue>%' ;
> ERROR: Unable to identify an operator '~~*' for types 'inet' and '"unknown"'
> You will have to retype this query using an explicit cast

This certainly did not work in 7.3 (in fact, you are quoting the 7.3
spelling of the error message).

There is not and never has been a LIKE operator for inet values; it's
only defined for textual data types. The reason it "worked" in 7.2 was
that 7.2 would implicitly cast the inet value to text for you. In 7.3
we changed a lot of casts to not be implicitly invocable, because of the
potential for unexpected behavior. Personally I'd class this as an
example of exactly the kind of unexpected behavior we meant to
prevent...

Short anser: cast the field value to text.

regards, tom lane