Thread: Select gives the wrong results
Dear all:
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?
Thanks!
Crystle
--
Crystle Numan, B.Sc., Web Developer
Guided Vision: the possibilities are endless
905.528.3095 http://guidedvision.com
On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: > Dear all: > > 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) <snip results> Looks to me like "value" is a string type, is this possible? ORDER BY value should make it more obvious. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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.
a beter idea is to use yyyy-mm-dd hh:mi:ss format 2005/8/29, Martijn van Oosterhout <kleptog@svana.org>: > On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: > > Dear all: > > > > 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) > > <snip results> > > Looks to me like "value" is a string type, is this possible? > > ORDER BY value should make it more obvious. > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. > > > -- Atte Moises Alberto Lindo Gutarra Consultor y Desarrollador Java / Open Source TUMI Solutions SAC Tel: +51.13481104 Cel: +51.197366260 MSN : mlindo@tumisolutions.com
It looks like your "value" column is of a varchar(), char() or text
type. The > and < operators compare the ordinal value of the text
when used on text types. You'll want to use ALTER TABLE ... ALTER
COLUMN ... to change value into a numeric type (probably INT or
BIGINT), and then you'll get the intended result
On 8/29/05, Crystle Numan <crys@guidedvision.com> wrote:
> Dear all:
>
> 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?
>
> Thanks!
> Crystle
>
>
> --
> Crystle Numan, B.Sc., Web Developer
> Guided Vision: the possibilities are endless
> 905.528.3095 http://guidedvision.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org
Crystle Numan wrote:
> Dear all:
>
> 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)
You are comparing strings, which is not quite the same as a numerical
comparison. The above range is empty, as '9...' > '1...' (even though
the left string is shorter).
Either compare numbers, or left pad your left string with zeroes until
it's the same length as the right string.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
On Mon, 2005-08-29 at 23:42 +0200, Martijn van Oosterhout wrote: > On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: > > Dear all: > > > > 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) > > <snip results> > > Looks to me like "value" is a string type, is this possible? > > ORDER BY value should make it more obvious. I think you are correct, that because the field is a string the comparison is not working as I expected. Unfortunately, the way we set up the database, I can't change the type to int as other values in this 'value' field contain letters. I'll have to find another way to get the proper comparison. Thanks to all who replied! Crystle -- Crystle Numan, B.Sc., Web Developer Guided Vision: the possibilities are endless 905.528.3095 http://guidedvision.com
> Crystle Numan wrote:
>> Dear all:
>>
>> 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?
>>
>> Thanks!
>> Crystle
>>
>>
>>
> http://archives.postgresql.org
Assuming that value is epoch date,
$ psql test -c "select date(1051156800)"
date
------------
2003-04-24
(1 row)
$ psql test -c "select date(1053316800)"
date
------------
2003-05-19
(1 row)
$ psql test -c "select date(0)"
date
------------
1969-12-31
(1 row)
$ psql test -c "select date(86400)"
date
------------
1970-01-01
(1 row)
Then wouldn't something along the lines of:
SELECT * from person_detail WHERE field='2' AND value between
date(1000000001) and date(1104555600); work....
reid