I confirmed the problem on a linux server running beta3...so this problem is quite reproducible by running the attached scripts on a freshly loaded database.
To reproduce the problem [adjust host,etc as necessary]:
1. type/cat test_boolean.sql | psql template1 (this will create a
database called 'test', connect to it, and load a few things.)
2. bzip -cd < poline.bzip | psql test (this will load a table into test
that was dumped via pg_dump)
3. try the following query:
select 1::int4, * from data1.po_line_file
where pol_po_no = '00000002' and
(pol_po_no = '00000002' and pol_po_rel_no = 0) and
(pol_po_no = '00000002' and pol_po_rel_no = 0 and
pol_item_no = '1570');
it should return 0 rows.
Try it with explain/analyze which reports 4 rows.
Try it a third time as:
select 1::int4, * from data1.po_line_file
where -- pol_po_no = '00000002' and
-- (pol_po_no = '00000002' and pol_po_rel_no = 0) and
(pol_po_no = '00000002' and pol_po_rel_no = 0 and
pol_item_no = '1570');
which is logically equivalent to the first form (isn't it?) and this
returns 1 row (the correct answer).
Merlin
test_boolean.sql
Description: test_boolean.sql
poline.bzip
Description: poline.bzip
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
