Strange behaviour executing query on postgresql

Question

I'll try to explain what happend to me without pasting every single piece of my code because it's a lot.

If I run this query alone it returns 6.0 (the expected behaviour)

select distancia_euclediana_para_imagenes(vector_cuadrante1,
                       vector_cuadrante2,
                       vector_cuadrante3,
                       vector_cuadrante4,
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)])
from imagen,pivotes 
where id=id_imagen and indice_pivote=2

and if I run this other query it returns 2 (again the expected behaviour)

with distancia_a_pivote(distancia) as
(select distancia_euclediana_para_imagenes(vector_cuadrante1,
                       vector_cuadrante2,
                       vector_cuadrante3,
                       vector_cuadrante4,
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)])
from imagen,pivotes 
where id=id_imagen and indice_pivote=2)
select id from imagen, indice, distancia_a_pivote d
where id=id_imagen  and
fqa[2]>= 6-1 and
fqa[2]<= d.distancia+1;

However, with this little change it stops working and returns nothing

with distancia_a_pivote(distancia) as
(select distancia_euclediana_para_imagenes(vector_cuadrante1,
                       vector_cuadrante2,
                       vector_cuadrante3,
                       vector_cuadrante4,
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)],
                       ARRAY[SQRT(8),SQRT(8)])
from imagen,pivotes 
where id=id_imagen and indice_pivote=2)
select id from imagen, indice, distancia_a_pivote d
where id=id_imagen  and
fqa[2]>= d.distancia-1 and
fqa[2]<= d.distancia+1;

I'll be grateful if you could help me because I have no idea what can cause this issue. Besides I've been coding in a TDD style, so I have tests to prove the right behaviour and I was pretty it was going to work

Thanks

What is fqa[2]? The integer 5 perhaps?

mu is too short
– mu is too short

2012-02-10 01:18:19 +00:00
Commented Feb 10, 2012 at 1:18 — mu is too short
– mu is too short, Commented Feb 10, 2012 at 1:18
yes, do you have an idea what could be wrong?

Watt
– Watt

2012-02-10 02:05:32 +00:00
Commented Feb 10, 2012 at 2:05 — Watt
– Watt, Commented Feb 10, 2012 at 2:05

mu is too short · Accepted Answer · 2012-02-10 09:50:57Z

1

You say that fqa[2] is the integer 5 and d.distancia is the floating point value 6.0. That means that you end up looking at this:

5 >= 6.0 - 1

That 6.0 probably isn't exact so 6.0 - 1 could come out a shade less than 5.0 and your comparison would fail. One solution is to add a little bit wiggle room to account for the usual floating point problems:

fqa[2] >= d.distancia - 1.00001 and
fqa[2] <= d.distancia + 1.00001

The 0.00001 is just an example, you'd need to look at your situation to see how much extra you should allow. Adding ceil and floor calls might be another option as they would give you better control over the floating point to integer conversion.

edited Feb 10, 2012 at 9:50

answered Feb 10, 2012 at 2:45

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Strange behaviour executing query on postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related