PostgreSQL Discussion - Select gives the wrong results

Author

Select gives the wrong results

Crystle Numan

2005-08-29, 8:24 pm

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

Martijn van Oosterhout

2005-08-29, 8:24 pm

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.

Stephan Szabo

2005-08-29, 8:24 pm

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

Moises Alberto Lindo Gutarra

2005-08-29, 8:24 pm

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:
>
> <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/
>
>
>

--
Atte

Moises Alberto Lindo Gutarra
Consultor y Desarrollador Java / Open Source
TUMI Solutions SAC
Tel: +51.13481104
Cel: +51.197366260
MSN : mlindo@tumisolutions
.com

---------------------------(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

Mike Rylander

2005-08-29, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alban Hertroys

2005-08-30, 7:24 am

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//

---------------------------(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

Crystle Numan

2005-08-30, 7:24 am

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:
>
> <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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reid Thompson

2005-08-30, 9:25 am

> Crystle Numan wrote:
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links