1

I want to use a variable like this :

myVariable = 0.5
myQuery.filter(myTable.column1 == myVariable*myTable.column2)

I have then no results when I apply all() to myQuery. If I replace the variable with its value, it is OK.

queryBidOffer = session.query(BidOffer.id, BidOffer.price, BidOffer.qmin, BidOffer.qmax)
queryBidOffer = queryBidOffer.join(Equipment).filter(BidOffer.equipment==Equipment.id, Equipment.equipment_type.in_(['L','P','W','M']))
queryBidOffer_day = queryBidOffer.filter(BidOffer.day == day)
queryBidOffer_hour = queryBidOffer_day.filter(BidOffer.start_hour == timeSlice)

queryBidOffer_hour = queryBidOffer_hour.join(EquipmentDayHour, BidOffer.equipment == EquipmentDayHour.equipment)
queryBidOffer_hour = queryBidOffer_hour.filter(EquipmentDayHour.day == day)
queryBidOffer_hour = queryBidOffer_hour.filter(EquipmentDayHour.hour == timeSlice)

factor1 = 1.00 - 0.07
queryBidOffer_hour = queryBidOffer_hour.filter(BidOffer.equipment == EquipmentDayHour.equipment, factor1*func.abs(EquipmentDayHour.ref_prog) == 930)

The problem is with the two last lines (factor1). In the last line, if I replace factor1 by its value, it is OK.

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
Oodini
  • 829
  • 1
  • 6
  • 16
  • Surprise ! If I replace the last line but one by factor1 = 0.93, it does work... – Oodini Jul 04 '12 at 14:02
  • When I set factor1 = 0.93, its value in the request is 0.93000000000000005. When I set factor1 = 1-0.07, its value in the request is 0.92999999999999994. The value in the database is 0.93*1000. – Oodini Jul 05 '12 at 16:21

2 Answers2

1

IEEE floating point numbers as used by Python, and many databases do not always work as your school math. The reason why 0.93 worked was that that was the bitwisely exact value that you had originally stored in database. But slight error appears in the subtraction.

See this for example:

>>> print "%0.64f" % (1.0 - 0.07)
0.9299999999999999378275106209912337362766265869140625000000000000
>>> print "%0.64f" % (0.93)
0.9300000000000000488498130835068877786397933959960937500000000000

Read more here How should I do floating point comparison and then for very in depth essay you could read What Every Computer Scientist Should Know About Floating-Point Arithmetic...

One solution could be to use decimal.Decimal in Python and Numeric in SQL.

Community
  • 1
  • 1
Antti Haapala -- Слава Україні
  • 129,958
  • 22
  • 279
  • 321
-1

I've just tested out something similar to the code you are describing, but get results whether I use a variable or a literal:

myVariable = 0.5
myQuery.filter(myTable.column1 == myVariable*myTable.column2).all()

vs.

myQuery.filter(myTable.column1 == 0.5*myTable.column2).all()

Can you post up the literal SQL that is being genarated? i.e. the results of 

print myQuery

for both of those?

EoghanM
  • 25,161
  • 23
  • 90
  • 123