Re: BUG #4317: problem with comparision of datatype date

The following bug has been logged online:

Bug reference: 4317
Logged by: Sanjay Rajdev
Email address: sanjay(dot)rajdev(at)featherstoneinformatics(dot)com
PostgreSQL version: 8.3.1
Operating system: Window Server 2003
Description: problem with comparision of datatype date
Details:

I installed a new PostgreSQL server 8.3.1 few days back, and then restored a
backup taken from our old server which had older version of PostgreSQL
Server.

We have a application that uses this database, and at most of the place we
have used = sign for date comparision.

Here is what is happening, I have a datatype "date" column in my database
called "somedate" there are 277 rows in the database which have "somedate" =
"2008-07-18".

When I execute the below query
"select * from myTable where somedate = '2008-07-18'"
I only get 145 rows back from the database.

If I execute
"select * from myTable where somedate = cast('2008-07-18' as Date)"
Still I get 145 rows

If I execute
"select * from myTable where somedate between '2008-07-18' and
'2008-07-18'"
I get all 277 rows.

I don't know what is wrong in the first and second queries. Can someone
please help?
we have used similar queries at most of place in our application.

Sanjay Rajdev wrote:

> If I execute
> "select * from myTable where somedate between '2008-07-18' and
> '2008-07-18'"
> I get all 277 rows.

I'm not able to help you out directly (just another user) but answering
the following questions might help others:

- What was the previous database version?
- Can you provide DDL for the table?
- What is the exact data type of the field `somedate' ?

--
Craig Ringer

"Sanjay Rajdev" <sanjay(dot)rajdev(at)featherstoneinformatics(dot)com> writes:
> Here is what is happening, I have a datatype "date" column in my database
> called "somedate" there are 277 rows in the database which have "somedate" =
> "2008-07-18".

> When I execute the below query
> "select * from myTable where somedate = '2008-07-18'"
> I only get 145 rows back from the database.

Is there an index on somedate, and if so does reindexing it help?

regards, tom lane

Tom,

Thanks for the thought, I think that this can be the problem, as earlier there was no indexing on the column, but 2 days back we added index on 5 of columns in the table and "somedate" is one of them. This problem have been noticed after the index's were added.
I can't test this out as this happens while people are using the software, and when someone complains, we have to fix it immediately we can't have people wait for the re-indexing to be done.

For fixing it we just update the date to some other date and then update it back to what was originally, this way it works. Any suggestion to get it fixed.

Regards,
Sanjay Rajdev

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Sanjay Rajdev" <sanjay(dot)rajdev(at)featherstoneinformatics(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Sent: Friday, July 18, 2008 10:17:03 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: Re: [BUGS] BUG #4317: problem with comparision of datatype date

"Sanjay Rajdev" <sanjay(dot)rajdev(at)featherstoneinformatics(dot)com> writes:
> Here is what is happening, I have a datatype "date" column in my database
> called "somedate" there are 277 rows in the database which have "somedate" =
> "2008-07-18".

> When I execute the below query
> "select * from myTable where somedate = '2008-07-18'"
> I only get 145 rows back from the database.

Is there an index on somedate, and if so does reindexing it help?

regards, tom lane

>>> On Fri, Jul 18, 2008 at 2:35 PM, in message
<4744486(dot)108381216409720544(dot)JavaMail(dot)root(at)mail(dot)featherstoneinformatics(dot)com>,
Sanjay Rajdev <sanjay(dot)rajdev(at)featherstoneinformatics(dot)com> wrote:

> Thanks for the thought, I think that this can be the problem, as
earlier
> there was no indexing on the column, but 2 days back we added index
on 5 of
> columns in the table and "somedate" is one of them. This problem have
been
> noticed after the index's were added.
> I can't test this out as this happens while people are using the
software,

Try adding another index on the column (with a different index name)
using CREATE INDEX CONCURRENTLY. Then drop the old index.

-Kevin