Search Postgresql Archives

Re: Select gives the wrong results

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, 29 Aug 2005, Crystle Numan wrote:

> I am fairly knowledgeable about PostgreSQL but this behaviour is
> stumping me. Any help would be wonderful. If you think it is a bug, let
> me now and I'll file one.
>
> (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005,
> no results)
>
> db_name=# SELECT * from person_detail WHERE field='2' AND
> value>'946702800' AND value<'1104555600';
>
>  id | person | field | value
> ----+--------+-------+-------
> (0 rows)
>
> (select values in DB (date stamps) between Jan 1, 2003 and Jan 1, 2005, 4
> results (!))
>
> db_name=# SELECT * from person_detail WHERE field='2' AND
> value>'1041397200' AND value<'1104555600';
>   id  | person | field |   value
> ------+--------+-------+------------
>  1187 |    454 |     2 | 1051156800
>  1188 |    460 |     2 | 1053316800
>  1219 |    472 |     2 | 1057723200
>  1181 |    441 |     2 | 1042520400
> (4 rows)
>
> The first select should have those 4 results plus any more. We tried
> putting quotes (") around the word 'value' to see if that made a
> difference, and no it didn't. We tried reversing the two clauses and
> that made no difference.
>
> Here's another funny one. Not the one that doesn't belong.
>
> db_name=# SELECT * from person_detail WHERE field='2' AND
> value>='1000000001' AND value<='1104555600';
>   id  | person | field |   value
> ------+--------+-------+------------
>     3 |    218 |     2 | 1017464400
>   253 |    295 |     2 | 1002340800
>   514 |    323 |     2 | 100155600
>  1126 |    405 |     2 | 1006750800
>  1179 |    439 |     2 | 1035172800
>  1187 |    454 |     2 | 1051156800
>  1188 |    460 |     2 | 1053316800
>  1219 |    472 |     2 | 1057723200
>  1181 |    441 |     2 | 1042520400
>  1152 |    434 |     2 | 1032321600
>  1129 |    410 |     2 | 1024027200
> (11 rows)
>
> Anyone see what's going on here?

What type is value?  I think you're expecting a numeric comparison but
getting a string one.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux