possible wierd boolean bug?

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, Pennsylvania 19073

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

"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-keys count.
*/
! if (priorAllEqualSoFar)
so->numberOfRequiredKeys = new_numberOfKeys;

  			/*
--- 411,417 ----
  			 * If all attrs before this one had "=", include these keys
  			 * into the required-keys count.
  			 */
! 			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 higher levels.
*/
! 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 higher levels.
  	 */
! 	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