Thread: possible wierd boolean bug?
I have a strange but reproducible problem where a query does not seem to return the same results. esp=# select 1::int4, * from data1.po_line_file esp-# where pol_po_no = '00000002' and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0) and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and pol_item_no = '1570') esp-# limit 1 ; [fields omitted] (0 rows) esp=# select 1::int4, * from data1.po_line_file esp-# where --pol_po_no = '00000002' and esp-# --(pol_po_no = '00000002' and pol_po_rel_no = 0) and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and pol_item_no = '1570') esp-# limit 1 ; [fields omitted, note commented redundant clauses] (1 row) The obviously silly Boolean clause here was sql that was generated by a driver. I recoded the driver to make more intelligent sql and now the problem is gone, at least from my application. Note that the same query form but with different tables/fields works correctly 99.99999% of the time, just not this time...I'm just curious as to how two seemingly equivalent statements could produce different results...perhpaps I missed something? Merlin
That is bizarre. Does EXPLAIN show any difference? --------------------------------------------------------------------------- Merlin Moncure wrote: > I have a strange but reproducible problem where a query does not seem to > return the same results. > > esp=# select 1::int4, * from data1.po_line_file > esp-# where pol_po_no = '00000002' and > esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0) and > esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and > pol_item_no = '1570') > esp-# limit 1 ; > [fields omitted] > (0 rows) > > esp=# select 1::int4, * from data1.po_line_file > esp-# where --pol_po_no = '00000002' and > esp-# --(pol_po_no = '00000002' and pol_po_rel_no = 0) and > esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and > pol_item_no = '1570') > esp-# limit 1 ; > [fields omitted, note commented redundant clauses] > (1 row) > > The obviously silly Boolean clause here was sql that was generated by a > driver. I recoded the driver to make more intelligent sql and now the > problem is gone, at least from my application. Note that the same query > form but with different tables/fields works correctly 99.99999% of the > time, just not this time...I'm just curious as to how two seemingly > equivalent statements could produce different results...perhpaps I > missed something? > > Merlin > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce wrote: > That is bizarre. Does EXPLAIN show any difference? > > ------------------------------------------------------------------------ -- esp=# explain analyze select 1::int4, * from data1.po_line_file esp-# where pol_po_no = '00000002' and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0) and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and pol_item_no = '1570') esp-# limit 1 ; QUER Y PLAN ------------------------------------------------------------------------ ---------------------------- ------------------------------------------------------------------------ ---------------------------- --Limit (cost=0.00..5.76 rows=1 width=313) (actual time=0.000..0.000 rows=0 loops=1) -> Index Scan using po_line_file_pkey on po_line_file (cost=0.00..5.76 rows=1 width=313) (actua l time=0.000..0.000 rows=0 loops=1) Index Cond: ((pol_po_no = '00000002'::bpchar) AND (pol_po_no = '00000002'::bpchar) AND (pol _po_no = '00000002'::bpchar) AND ((pol_po_rel_no)::smallint = 0) AND ((pol_po_rel_no)::smallint = 0) ) Filter: ((pol_item_no)::text = '1570'::text)Total runtime: 0.000 ms (5 rows) esp=# explain analyze select 1::int4, * from data1.po_line_file esp-# where -- pol_po_no = '00000002' and esp-# -- (pol_po_no = '00000002' and pol_po_rel_no = 0) and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and pol_item_no = '1570') esp-# limit 1 ; QUERY PLAN ------------------------------------------------------------------------ ---------------------------- ------------------------------------Limit (cost=0.00..5.75 rows=1 width=313) (actual time=0.000..0.000 rows=1 loops=1) -> Index Scan using po_line_file_pkey on po_line_file (cost=0.00..5.75 rows=1 width=313) (actua l time=0.000..0.000 rows=1 loops=1) Index Cond: ((pol_po_no = '00000002'::bpchar) AND ((pol_po_rel_no)::smallint = 0)) Filter: ((pol_item_no)::text = '1570'::text)Total runtime: 0.000 ms (5 rows)
> That is bizarre. Does EXPLAIN show any difference? Uh oh. esp=# reindex table data1.parts_order_line_file; REINDEX esp=# explain analyze select 1::int4, * from data1.po_line_file esp-# where pol_po_no = '00000002' and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0) and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and pol_item_no = '1570') esp-# limit 1 ; QUER Y PLAN ------------------------------------------------------------------------ ---------------------------- ------------------------------------------------------------------------ ---------------------------- --Limit (cost=0.00..5.76 rows=1 width=313) (actual time=0.000..0.000 rows=0 loops=1) -> Index Scan using po_line_file_pkey on po_line_file (cost=0.00..5.76 rows=1 width=313) (actua l time=0.000..0.000 rows=0 loops=1) Index Cond: ((pol_po_no = '00000002'::bpchar) AND (pol_po_no = '00000002'::bpchar) AND (pol _po_no = '00000002'::bpchar) AND ((pol_po_rel_no)::smallint = 0) AND ((pol_po_rel_no)::smallint = 0) ) Filter: ((pol_item_no)::text = '1570'::text)Total runtime: 0.000 ms (5 rows)
> > That is bizarre. Does EXPLAIN show any difference? > > Uh oh. > > esp=# reindex table data1.parts_order_line_file; whoops, never mind that. In fact, I re-indexed the wrong table. Reindexing makes no difference. Here, explain analyze and running query in psql return different results. Observe: esp=# select 1::int4, * from data1.po_line_file esp-# where pol_po_no = '00000002' and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0) and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and pol_item_no = '1570') esp-# limit 1 ;int4 | id | lastmod | pol_po_no | pol_po_rel_no | pol_seq_no | pol_parts_unit_no | pol_item_no | po l_desc1 | pol_desc2 | pol_vendor_part_no | pol_qty_ordered | pol_est_cost | pol_purchase_uom | pol_w eight | pol_requested_date | pol_expected_date | pol_plant_loc_needed | pol_workstation_needed | pol _stock_loc_needed | pol_internal_contact | pol_update_unit_cost | pol_gl_acct_no | pol_pur_to_inv_ra tio | pol_print_rev_no | pol_revision_flag | pol_qty_received | pol_parts_tracking_flag | pol_qty_re jected ------+----+---------+-----------+---------------+------------+--------- ----------+-------------+--- --------+-----------+--------------------+-----------------+------------ --+------------------+------ ------+--------------------+-------------------+----------------------+- -----------------------+---- ------------------+----------------------+----------------------+------- ---------+------------------ ----+------------------+-------------------+------------------+--------- ----------------+----------- ------- (0 rows) esp=# explain analyze select 1::int4, * from data1.po_line_file esp-# where pol_po_no = '00000002' and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0) and esp-# (pol_po_no = '00000002' and pol_po_rel_no = 0 and pol_item_no = '1570') esp-# limit 1 ; QUER Y PLAN ------------------------------------------------------------------------ ---------------------------- ------------------------------------------------------------------------ ---------------------------- --Limit (cost=0.00..5.79 rows=1 width=313) (actual time=0.000..0.000 rows=0 loops=1) -> Index Scan using po_line_file_pkey on po_line_file (cost=0.00..5.79 rows=1 width=313) (actua l time=0.000..0.000 rows=0 loops=1) Index Cond: ((pol_po_no = '00000002'::bpchar) AND (pol_po_no = '00000002'::bpchar) AND (pol _po_no = '00000002'::bpchar) AND ((pol_po_rel_no)::smallint = 0) AND ((pol_po_rel_no)::smallint = 0) ) Filter: ((pol_item_no)::text = '1570'::text)Total runtime: 0.000 ms (5 rows)
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
Attachment
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Try it with explain/analyze which reports 4 rows.
I don't see four rows. I do see different results when I add the third
redundant WHERE clause: it switches to a different index and fails to
find the row it should find. I suspect the problem is located in the
btree index scan setup code that I changed to support cross-data-type
comparisons. (7.4 does not fail, but it doesn't try to use the index
fully either.)
I'm on it... thanks for the test case!
regards, tom lane
Got it: _bt_preprocess_keys is setting keys_are_unique in cases where it
shouldn't. The test at the bottom of that routine used to be correct,
but no longer is, because the number of keys returned could be more than
the number of attributes being tested when redundant cross-data-type quals
are provided (see the comments above the routine).
A fix will be forthcoming.
regards, tom lane
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> 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.
The attached patch fixes the problem for me.
regards, tom lane
*** src/backend/access/nbtree/nbtutils.c.orig Sun Aug 29 01:06:40 2004
--- src/backend/access/nbtree/nbtutils.c Wed Dec 15 14:00:59 2004
***************
*** 224,234 **** BTScanOpaque so = (BTScanOpaque) scan->opaque; int numberOfKeys =
scan->numberOfKeys; int new_numberOfKeys; ScanKey inkeys; ScanKey outkeys;
ScanKey cur; ScanKey xform[BTMaxStrategyNumber];
- bool allEqualSoFar; bool hasOtherTypeEqual; Datum test; int i,
--- 224,234 ---- BTScanOpaque so = (BTScanOpaque) scan->opaque; int numberOfKeys =
scan->numberOfKeys; int new_numberOfKeys;
+ int numberOfEqualCols; ScanKey inkeys; ScanKey outkeys; ScanKey cur;
ScanKey xform[BTMaxStrategyNumber]; bool hasOtherTypeEqual; Datum test; int
i,
***************
*** 278,284 **** * Otherwise, do the full set of pushups. */ new_numberOfKeys = 0;
! allEqualSoFar = true; /* * Initialize for processing of keys for attr 1.
--- 278,284 ---- * Otherwise, do the full set of pushups. */ new_numberOfKeys = 0;
! numberOfEqualCols = 0; /* * Initialize for processing of keys for attr 1.
***************
*** 321,327 **** */ if (i == numberOfKeys || cur->sk_attno != attno) {
! bool priorAllEqualSoFar = allEqualSoFar; /* check input keys are correctly ordered */
if (i < numberOfKeys && cur->sk_attno != attno + 1)
--- 321,327 ---- */ if (i == numberOfKeys || cur->sk_attno != attno) {
! int priorNumberOfEqualCols = numberOfEqualCols; /* check input keys are correctly
ordered*/ if (i < numberOfKeys && cur->sk_attno != attno + 1)
***************
*** 355,368 **** xform[BTLessEqualStrategyNumber - 1] = NULL;
xform[BTGreaterEqualStrategyNumber- 1] = NULL; xform[BTGreaterStrategyNumber - 1] = NULL; }
else {
! /*
! * If no "=" for this key, we're done with required keys
! */
! if (!hasOtherTypeEqual)
! allEqualSoFar = false; } /* keep only one of <, <= */
--- 355,368 ---- xform[BTLessEqualStrategyNumber - 1] = NULL;
xform[BTGreaterEqualStrategyNumber- 1] = NULL; xform[BTGreaterStrategyNumber - 1] = NULL;
+ /* track number of attrs for which we have "=" keys */
+ numberOfEqualCols++; } else {
! /* track number of attrs for which we have "=" keys */
! if (hasOtherTypeEqual)
! numberOfEqualCols++; } /* keep only one of <, <= */
***************
*** 411,417 **** * If all attrs before this one had "=", include these keys * into the
required-keyscount. */
! if (priorAllEqualSoFar) so->numberOfRequiredKeys = new_numberOfKeys; /*
--- 411,417 ---- * If all attrs before this one had "=", include these keys * into the
required-keyscount. */
! if (priorNumberOfEqualCols == attno - 1) so->numberOfRequiredKeys = new_numberOfKeys;
/*
***************
*** 468,475 **** * If unique index and we have equality keys for all columns, set * keys_are_unique flag for
higherlevels. */
! if (allEqualSoFar && relation->rd_index->indisunique &&
! relation->rd_rel->relnatts == new_numberOfKeys) scan->keys_are_unique = true; }
--- 468,475 ---- * If unique index and we have equality keys for all columns, set * keys_are_unique flag for
higherlevels. */
! if (relation->rd_index->indisunique &&
! relation->rd_rel->relnatts == numberOfEqualCols) scan->keys_are_unique = true; }
> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > > 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. > > The attached patch fixes the problem for me. > > regards, tom lane > Yup. I just confirmed it from cvs. btw, the 4 rows I was complaining about was due to my erroneously reading the analyze output, so everything works ok now. Thanks! Anyways, it would be nice to be able to use the sql row constructor to do equality/comparison...wouldn't get caught writing such silly sql statements :) Merlin
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Anyways, it would be nice to be able to use the sql row constructor to
> do equality/comparison...wouldn't get caught writing such silly sql
> statements :)
You mean like this?
regression=# select row(1,2,3) = row(1,2,3);?column?
----------t
(1 row)
regression=# select row(1,2,3) = row(1,2,4);?column?
----------f
(1 row)
The semantics aren't right yet for non-equality comparisons, but it
works fine for = and != ...
regards, tom lane