SELECT with regular pattern '~' delivers wrong output
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)
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
Show quoted text
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)
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