Re: BUG #4899: Open parenthesis breaks query plan

While noodling around some more, I found that a comparison to '()' allows use of the index, as does '(abc)' and even '(a(b(c)d)e)'. It appears that mismatched open/close paren pairs trigger the bug. Obviously something is parsing the string literal and mishandling parentheses. I don't understand why parentheses should be significant inside a string literal in the first place.

Also, just to be 100% clear, the open paren can be anywhere in the string, so a comparison to 'abcdefgh(ijklmnop' still triggers the bug.

--
Peter Headland
Architect - e.Reports
Actuate Corporation

-----Original Message-----
From: Peter Headland
Sent: Saturday, July 04, 2009 18:03
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4899: Open parenthesis breaks query plan

The following bug has been logged online:

Bug reference: 4899
Logged by: Peter Headland
Email address: pheadland(at)actuate(dot)com
PostgreSQL version: 8.4.0
Operating system: Windows
Description: Open parenthesis breaks query plan
Details:

In a moderate-size table (~400,000 rows), an equality match on an unindexed
varchar column to a string that contains an open parenthesis '(' prevents
the optimizer from using an obvious index. Changing the open parenthesis to
another character, such as ')' allows the obvious index to be used. I have
been unable to reproduce this on simple test data so far, so it is obviously
fairly subtle.

Abstract example of the issue:

o table t has a composite index i comprising columns c1, c2, c3

o column t.c4 is not indexed

Illustration of the queries:

-- Full table scan
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = '(';

-- Uses index i
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = ')';

I am really hoping that this defect can be found by inspection of the
source, because trying to reproduce it is fast getting me nowhere.
Unfortunately, the data involved are customer confidential, so I cannot
provide the original table.