Обсуждение: BUG #1179: Time comparison fail

The following bug has been logged online:

Bug reference:      1179
Logged by:          Mauricio CASTRO ESPINOSA

Email address:      mcastroe@hotmail.com

PostgreSQL version: 7.2.4

Operating system:   RedHat 7.3 uname:Linux Play 2.4.18-3 #1 Thu Apr 18
07:37:53 EDT 2002 i686 unknown

Description:        Time comparison fail

Details:

must_betrue is false for the query bellow


psql:
----------------------------
age(endeddatetime,starteddatetime),time,date_part('second',(age(endeddateti
me,starteddatetime)))*1000,date_part('second',(age(endeddatetime,starteddat
mex2=# select etime)))*1000=time as
Must_beTrue,starteddatetime,endeddatetime from entry where  not
date_part('second',(age(endeddatetime,starteddatetime)))*1000=time;
     age      | time | ?column? | must_betrue |      starteddatetime       |
      endeddatetime
--------------+------+----------+-------------+----------------------------
+----------------------------
 00:00:04.031 | 4031 |     4031 | f           | 2004-05-10 12:47:51.171-05 |
2004-05-10 12:47:55.202-05
 00:00:04.094 | 4094 |     4094 | f           | 2004-05-10 12:47:51.171-05 |
2004-05-10 12:47:55.265-05
 00:00:04.047 | 4047 |     4047 | f           | 2004-05-10 12:50:32.781-05 |
2004-05-10 12:50:36.828-05
 00:00:02.047 | 2047 |     2047 | f           | 2004-05-10 14:10:54.171-05 |
2004-05-10 14:10:56.218-05
 00:00:04.046 | 4046 |     4046 | f           | 2004-05-10 14:13:05.312-05 |
2004-05-10 14:13:09.358-05
(5 rows)


mex2=# select count(*) from entry;
 count
-------
   689
(1 row)

mex2=# \d entry
                                       Table "entry"
      Column       |           Type           |                 Modifiers
-------------------+--------------------------+----------------------------
----------------
 id                | integer                  | not null default
nextval('entry_id'::text)
 ord               | integer                  |
 func              | character varying(200)   |
 source            | character varying(200)   |
 started           | bigint                   |
 starteddatetime   | timestamp with time zone |
 time              | bigint                   |
 size              | bigint                   |
 operation         | character varying(200)   |
 result            | character varying(200)   |
 type              | character varying(200)   |
 url               | character varying(1500)  |
 authority         | character varying(200)   |
 path              | character varying(750)   |
 query             | character varying(750)   |
 incache           | boolean                  |
 lastmodificaction | timestamp with time zone |
 expires           | timestamp with time zone |
 lastcacheupdate   | timestamp with time zone |
 lastaccess        | timestamp with time zone |
 endeddatetime     | timestamp with time zone |
Primary key: entry_pkey

mex2=# SELECT version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

mex2=# \!uname -a
Linux Play 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown

On Tue, 22 Jun 2004, PostgreSQL Bugs List wrote:

> must_betrue is false for the query bellow

One of those is a bigint, the other is a double expression that is
probably very slightly different from the integer you're comparing it to.
What is the result of the date_part(...)-time?

On Tue, 22 Jun 2004, Mauricio CASTRO wrote:

> You are right!
>
> I am doing something wrong? I understand that there is a difference but I
> don't know why.

Floating point math is inexact.  The date_part(...) is returning a value
that is very close to, but not exactly 4.031 due to accuracy limitations.
So, when you multiply it by 1000 you can get a value very close to, but
not exactly 4031. When it tries to compare it to the bigint 4031, it
converts the bigint to a double as well and gets a different double from
the first.

You are right!

I am doing something wrong? I understand that there is a difference but I
don't know why.

mex2=# select
mex2-#   age(endeddatetime,starteddatetime),
mex2-#   time,
mex2-#   date_part('second',(age(endeddatetime,starteddatetime)))*1000,
mex2-#   date_part('second',(age(endeddatetime,starteddatetime)))*1000-time
as Must_be_Zero,
mex2-#   date_part('second',((starteddatetime)))*1000 as starteddatetime_s,
mex2-#   date_part('second',((endeddatetime)))*1000 as endeddatetime_s,
mex2-#   starteddatetime,
mex2-#   endeddatetime
mex2-#   from entry where  not
date_part('second',(age(endeddatetime,starteddatetime)))*1000=time;
     age      | time | ?column? |     must_be_zero      | starteddatetime_s
| endeddatetime_s |      starteddatetime       |       endeddatetime

--------------+------+----------+-----------------------+-------------------+-----------------+----------------------------+----------------------------
00:00:04.031 | 4031 |     4031 | -4.54747350886464e-13 |             51171 |
           55202 | 2004-05-10 12:47:51.171-05 | 2004-05-10 12:47:55.202-05
00:00:04.094 | 4094 |     4094 |  4.54747350886464e-13 |             51171 |
           55265 | 2004-05-10 12:47:51.171-05 | 2004-05-10 12:47:55.265-05
00:00:04.047 | 4047 |     4047 | -4.54747350886464e-13 |             32781 |
           36828 | 2004-05-10 12:50:32.781-05 | 2004-05-10 12:50:36.828-05
00:00:02.047 | 2047 |     2047 |  2.27373675443232e-13 |             54171 |
           56218 | 2004-05-10 14:10:54.171-05 | 2004-05-10 14:10:56.218-05
00:00:04.046 | 4046 |     4046 |  4.54747350886464e-13 |              5312 |
            9358 | 2004-05-10 14:13:05.312-05 | 2004-05-10 14:13:09.358-05
(5 rows)

_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail