regex - 正则表达式匹配从 PostgreSQL 8.3 到 9.2 有什么变化？

Question

如果我运行这个查询:

SELECT 'Via Orologio 122 A' SIMILAR TO '(Strada|Via) % [0-9]+( [A-Z])?';

我希望得到 TRUE。 postgreSQL 9.1.8 版返回预期值，但在 8.3 版中返回 FALSE。我认为问题是最后的问号。事实上，查询:

SELECT 'Via Orologio 122 A' SIMILAR TO '(Strada|Via) % [0-9]+( [A-Z])';

在两个版本中都返回 TRUE。

谁知道这两个版本有什么区别？

Answer 1

来自 changelog of 8.3.2 :

Fix a corner case in regular-expression substring matching (substring(string from pattern)) (Tom)
The problem occurs when there is a match to the pattern overall but the user has specified a parenthesized subexpression and that subexpression hasn't got a match. An example is substring('foo' from 'foo(bar)?'). This should return NULL, since (bar) isn't matched, but it was mistakenly returning the whole-pattern match instead (ie, foo)