Thread: possible wierd boolean bug?

possible wierd boolean bug?

From
"Merlin Moncure"
Date:
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



Re: possible wierd boolean bug?

From
Bruce Momjian
Date:
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
 


Re: possible wierd boolean bug?

From
"Merlin Moncure"
Date:
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)


Re: possible wierd boolean bug?

From
"Merlin Moncure"
Date:
> 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)


Re: possible wierd boolean bug?

From
"Merlin Moncure"
Date:
> > 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)


Re: possible wierd boolean bug?

From
"Merlin Moncure"
Date:
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

Re: possible wierd boolean bug?

From
Tom Lane
Date:
"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


Re: possible wierd boolean bug?

From
Tom Lane
Date:
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


Re: possible wierd boolean bug?

From
Tom Lane
Date:
"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; } 


Re: possible wierd boolean bug?

From
"Merlin Moncure"
Date:
> "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



Re: possible wierd boolean bug?

From
Tom Lane
Date:
"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