1

I use the Haversine query to calculate the distance to property. But even though it works, I did notice I get Warning 1292 messages in PhpMyAdmin and HeidiSQL. I'd like to get rid of those but my search has come to an end.

The query looks like this and as said, works fine but with Warnings:

SELECT t1.id, t1.garage, t1.more_info_url, t1.date_b, t1.mak_id, t2.id AS m_id, t1.is_b, t1.price, t1.lat, t1.lng, 
(6371 * ACOS(COS(RADIANS('52.0791005')) * COS(RADIANS(t1.lat)) * COS(RADIANS(t1.lng) - RADIANS('4.2666311')) + SIN(RADIANS('52.0791005')) * SIN(RADIANS(t1.lat)))) AS distance
FROM alert t1
LEFT JOIN users t2 ON t2.id = t1.mak_id
WHERE is_b = 'on' 
AND (date_b BETWEEN CURDATE() - INTERVAL '180' DAY AND CURDATE()) 
AND (t1.price > 180000) 
AND (t1.price < 840000)
HAVING DISTANCE < '140'
ORDER BY distance ASC

This results in this error multiple times:

Warning: #1292 Truncated incorrect DOUBLE value: ''

However, when I remove the last line, the ORDER BY clause, all is fine. I have already tried to switch things around, had (single)quotes at every possible position, but I don't understand it anymore.

So, this runs without warnings:

 SELECT t1.id, t1.garage, t1.more_info_url, t1.date_b, t1.mak_id, t2.id AS m_id, t1.is_b, t1.price, t1.lat, t1.lng, 
    (6371 * ACOS(COS(RADIANS('52.0791005')) * COS(RADIANS(t1.lat)) * COS(RADIANS(t1.lng) - RADIANS('4.2666311')) + SIN(RADIANS('52.0791005')) * SIN(RADIANS(t1.lat)))) AS distance
    FROM alert t1
    LEFT JOIN users t2 ON t2.id = t1.mak_id
    WHERE is_b = 'on' 
    AND (date_b BETWEEN CURDATE() - INTERVAL '180' DAY AND CURDATE()) 
    AND (t1.price > 180000) 
    AND (t1.price < 840000)
    HAVING DISTANCE < '140'

Not sure if it is related, but the only DOUBLE field is "price" in t1. It holds the price of real estate property. I had that surrounded by single quotes in my tests as well. I'm afraifd I have tried all that I know.

Thanks.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Kim K.
  • 121
  • 2
  • 13
  • Why do you quote `140`? It's a number, it doesn't need to be quoted. – Barmar Oct 14 '19 at 19:01
  • You have unnecessary quotes around lots of numbers. Why? I have a feeling this is related to the problem. – Barmar Oct 14 '19 at 19:03
  • The quotes around the lat/lng are from the real_escape_string function, some of the others are there from testing. They did not influence the outcome. – Kim K. Oct 14 '19 at 20:39
  • 1
    You shouldn't be using `real_escape_string`, you should use prepared statements with `bind_param()`. – Barmar Oct 14 '19 at 20:43
  • Thanks for the tip. We do that on some other projects, not this one (it's another discussion as well I guess, I think prepared statements are longwinded :-)). Either way I don't think that is the problem here. – Kim K. Oct 14 '19 at 20:47
  • See https://stackoverflow.com/questions/16068993/error-code-1292-truncated-incorrect-double-value-mysql This error happens when trying to compare a number and a string in strict mode. So it's probably related to quoting numbers. – Barmar Oct 14 '19 at 20:50
  • I had read that and many other posts about it before asking myself. I did a lot of research, which is why I stated I did try with and without quotes as I go tthat from just about all I could find on Stack E. and Google. Thanks for your efforts though, really appreciate it. – Kim K. Oct 14 '19 at 20:56
  • Can you reproduce this in a sqlfiddle? – Barmar Oct 14 '19 at 21:01
  • Never done that. I'll try to find a way – Kim K. Oct 14 '19 at 21:06
  • The site I like is db-fiddle.com. – Barmar Oct 14 '19 at 21:08

1 Answers1

0

I tried to create a fiddle and got an error there (not on my own setups, only at the fiddle) about my date field:

date_available DATE NULL DEFAULT '0000-00-00',

So I took a closer look and tested more. Eventually the warnings were gone after putting the date_available, as well as the distance in single quotes in the ORDER BY clause.

Kim K.
  • 121
  • 2
  • 13