Thread: 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@postgresql.org. To report any other bug, fill out the form below and e-mail it to pgsql-bugs@postgresql.org. If you not only found the problem but solved it and generated a patch then e-mail it to pgsql-patches@postgresql.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@net.kent.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@postgresql.org. > > To report any other bug, fill out the form below and e-mail it to > pgsql-bugs@postgresql.org. > > If you not only found the problem but solved it and generated a patch > then e-mail it to pgsql-patches@postgresql.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@net.kent.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@net.kent.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