Skip site navigation (1) Skip section navigation (2)

SIMILAR TO incorrect with alternation

From: Aaron Bingham <bingham(at)cenix-bioscience(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: Oliver Gathmann <gathmann(at)cenix-bioscience(dot)com>,Andrew Walsh <walsh(at)cenix-bioscience(dot)com>,Liisa Koski <koski(at)cenix-bioscience(dot)com>
Subject: SIMILAR TO incorrect with alternation
Date: 2006-12-22 13:07:31
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs

We just had a case where invalid data entered our database, causing 
application failure.  A constraint was in place to prevent this and 
appeared correct, however, I was able to verify that the expression was 
evaluating to TRUE when I expected it to be FALSE.  As a work around we 
have fallen back to the nonstandard POSIX regular expression support, 
which works correctly.

Here is a simple test case which triggers the problematic behavior.  I 
would expect the SQL regular expression and POSIX regular expression 
below to give the same results.  However, they produce different 
answers.  The difference seems to be caused by a naive implementation of 
similar_escape(text, text) as also demonstrated below.

unidb=# select version();
 PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 
20050117 (prerelease) (SUSE Linux)
(1 row)

unidb=# select 'ab' similar to 'a|b';
(1 row)

unidb=# select 'ab' ~ '^(a|b)$';
(1 row)

unidb=# select similar_escape('a|b', NULL);
(1 row)


Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
Tatzberg 47                       phone: +49 (351) 4173-146
D-01307 Dresden, Germany          fax:   +49 (351) 4173-198 


pgsql-bugs by date

Next:From: Lubomir BrychtaDate: 2006-12-22 19:09:47
Subject: bug: Mac OS X 10.4.8 Intel
Previous:From: Robert LockeDate: 2006-12-22 10:39:19
Subject: BUG #2858: postgres periodically restarts (problem with MemoryContextAllocZeroAligned)...

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group