Thread: Select gives the wrong results

Select gives the wrong results

From
Crystle Numan
Date:
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


Re: Select gives the wrong results

From
Martijn van Oosterhout
Date:
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

Re: Select gives the wrong results

From
Stephan Szabo
Date:
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.

Re: Select gives the wrong results

From
Moises Alberto Lindo Gutarra
Date:
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

Re: Select gives the wrong results

From
Mike Rylander
Date:
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

Re: Select gives the wrong results

From
Alban Hertroys
Date:
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//

Re: Select gives the wrong results

From
Crystle Numan
Date:
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


Re: Select gives the wrong results

From
"Reid Thompson"
Date:
> 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