BETWEEN (WHERE clause) bug? or PEBKAC?
If I use the following query
SELECT cdl.id, YEAR(cdl.begin) year, cdl.begin begin, cdl.end end, vessel.name vessel,
CONCAT(city,', ',state) port, vessel.tons tons, cdl.fishermen men, cdl.cod cod, cdl.quintals quintals,
TRUNCATE((cod/tons),2) AS cpt,
TRUNCATE(cod/fishermen,2) AS cpm,
TRUNCATE(quintals/tons,2) AS qpt,
TRUNCATE(quintals/fishermen,2) AS qpm,
TRUNCATE((cod/(tons*(TO_DAYS(end) - TO_DAYS(begin)))),2) AS cptpd,
TRUNCATE((cod/(fishermen*(TO_DAYS(end) - TO_DAYS(begin)))),2) AS cpmpd
FROM cdl
LEFT JOIN vessel ON (cdl.vessel = vessel.id)
LEFT JOIN place ON (vessel.port = place.id)
WHERE tons BETWEEN 75 AND 100
I get a result set which does not contain vessels whose tonnages are exactly 75 or 100 (the tons column is FLOAT(6,2)). But if I ask the following, simpler query...
SELECT * FROM vessel WHERE tons BETWEEN 75 AND 100
...I get the desired result set, with vessels whose tonnages are exactly 75 or 100. In the documentation, it is stated that BETWEEN is equivalent to "WHERE tons >= 75 AND <=100."
Does my problem lie in my query or is this perhaps a bug (perhaps related to the joins)?