Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT with regular pattern '~' delivers wrong output

SELECT with regular pattern '~' delivers wrong output

From
Fabio Nanni
Date:
Hi,
I just found the strange behaviour reported below; I was expecting the
same response from the last two queries...

thanks
Fabio


LONG DESCRIPTION:
A SELECT with regular patterns (~) won't give you the right rows.

CODE SAMPLE:
select version();
-- OUTPUT:
--                              version
---------------------------------------------------------------------
-- PostgreSQL 7.1beta4 on alphaev67-dec-osf4.0f, compiled by cc -std
--(1 row)


create table cities (name text);

insert into cities values ('Alhambra');
insert into cities values ('Jackson');
insert into cities values ('Jacksonville');
insert into cities values ('Zurich');

select * from cities ;
-- OUTPUT:
--     name
-----------------
-- Alhambra
-- Jackson
-- Jacksonville
-- Zurich
--(4 rows)




select name from cities where name !~ '^A|^B|^C|^[D-I]|^[J][a-c]+kson'
order by name;
-- OUTPUT:
--  name
----------
-- Zurich
--(1 row)




select name from cities where name !~
'^A|^B|^C|^D|^[E-I]|^[J][a-c]+kson' order by name;
-- OUTPUT:
--     name
----------------
-- Alhambra
-- Jackson
-- Jacksonville
-- Zurich
--(4 rows)

Re: SELECT with regular pattern '~' delivers wrong output

From
Tom Lane
Date:
Fabio Nanni <f.nanni@albourne.com> writes:
> A SELECT with regular patterns (~) won't give you the right rows.

Strange.  I get only 'Zurich' in both cases, as expected, on HP-PA.
Perhaps an Alpha-specific portability problem in the regex code?
Can anyone else duplicate this misbehavior on other platforms?

            regards, tom lane

> I just found the strange behaviour reported below; I was expecting the
> same response from the last two queries...

> thanks
> Fabio


> LONG DESCRIPTION:
> A SELECT with regular patterns (~) won't give you the right rows.

> CODE SAMPLE:
> select version();
> -- OUTPUT:
> --                              version
> ---------------------------------------------------------------------
> -- PostgreSQL 7.1beta4 on alphaev67-dec-osf4.0f, compiled by cc -std
> --(1 row)


> create table cities (name text);

> insert into cities values ('Alhambra');
> insert into cities values ('Jackson');
> insert into cities values ('Jacksonville');
> insert into cities values ('Zurich');

> select * from cities ;
> -- OUTPUT:
> --     name
> -----------------
> -- Alhambra
> -- Jackson
> -- Jacksonville
> -- Zurich
> --(4 rows)




> select name from cities where name !~ '^A|^B|^C|^[D-I]|^[J][a-c]+kson'
> order by name;
> -- OUTPUT:
> --  name
> ----------
> -- Zurich
> --(1 row)




> select name from cities where name !~
> '^A|^B|^C|^D|^[E-I]|^[J][a-c]+kson' order by name;
> -- OUTPUT:
> --     name
> ----------------
> -- Alhambra
> -- Jackson
> -- Jacksonville
> -- Zurich
> --(4 rows)

Re: SELECT with regular pattern '~' delivers wrong output

From
Tom Lane
Date:
Fabio Nanni <f.nanni@albourne.com> writes:
> A SELECT with regular patterns (~) won't give you the right rows.

Turns out there were some portability problems with our regexp code
on Alphas (or anywhere else that sizeof(int) < sizeof(long)).  I've
applied fixes to current CVS; they should be in tomorrow morning's
snapshot if you want to do more-extensive testing.

            regards, tom lane