Commented by: @asfernandes

It seems the arguments must be verified with some precision. The test try to call asin with a float 1.000000... that's greater than int(1).

Commented by: @hvlad

Should note that 32-bit build works ok while 64-bit build failed.

Commented by: @hvlad

Change in argument verification did nothing as value really greater than 1 and ASIN will return NAN.

Seems that x64 performs operations more precise than i86.

I have no idea what 0.71939789 and 0.25357501 numbers are but i see that 0.0174532925 is Pi / 180 rounded to 10 digits.
I tried 11 digits (0.01745329252) and it works well on both x64 and i86 :

SELECT
ACOS(
SIN(latitude*(0.01745329252)) * SIN(0.71939789) +
COS(latitude*(0.01745329252)) * COS(0.71939789) * COS(0.25357501 - (Longitude*(0.01745329252)))
) * 6371
from TEST

returns 0

Hope this helps.

I have no idea why RC2 was OK for you, any chance you used 32-bit build ?

Commented by: vander clock stephane (arkadia)

it's work ok in 64 bit from FB2.0 to FB2.5 RC2 (x64). I never used the 32 bit build.
if you use http://web2.0calc.com/ then you can see that

(SIN(41.218463*(0.0174532925)) * SIN(0.71939789)) + (COS(41.218463*(0.0174532925)) * COS(0.71939789) * COS(0.25357501 - (14.528778*(0.0174532925)))) = 0.999999999999994
and
ACOS(0.999999999999994) = 0

so it's not possible that FB say that Acos need a value between [1 and -1] because the value (0.999999999999994) is < 1

in other hand
(SIN(41.218463*(0.01745329252)) * SIN(0.71939789)) + (COS(41.218463*(0.01745329252)) * COS(0.71939789) * COS(0.25357501 - (14.528778*(0.01745329252)))) = 1.00000000000017478
and 1.00000000000017478 is > 1 ! so here it's must raise an error

but the hint to use PI()/180 instead of 0.01745329252 is good, i will try this !

Commented by: @hvlad

Its all because of limited precision supported by Firebird.
I'm sure web calculator you mention used something more precise than our NUMERIC (up to 18 digits) or double precision (up to 15 digits) types.

Also, note, that for sin\cos\etc we use CRT which works with double precision type.
So we already have precision lost converting exact numeric into double precision and back.

Another workaround for this your case is to use NUMERIC(11, 6) in table definition.
You inserted numbers with 6 digits after the point so it could be OK, but i don't know for sure if it is possible for your task.

Commented by: vander clock stephane (arkadia)

it's look now very strange !

i create 2 table

first

CREATE TABLE TEST (
ID VARCHAR(100),
Longitude NUMERIC(11,8),
Latitude NUMERIC(10,8)
);
COMMIT;

and second (was already created, our production table)

CREATE TABLE GEO_PLACE (
ID VARCHAR(100) CHARACTER SET ISO8859_1 NOT NULL COLLATE ISO8859_1,
ID_LOCAL VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LAST_ALTERATION_DATE TIMESTAMP DEFAULT 'NOW',
LABEL VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_NORMALIZED VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_TREE VARCHAR(1200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_FRA VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_NORMALIZED_FRA VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_TREE_FRA VARCHAR(1200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_ASCII_FRA VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_URL_FRA VARCHAR(600) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_USA VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_NORMALIZED_USA VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_TREE_USA VARCHAR(1200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_ASCII_USA VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_URL_USA VARCHAR(600) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_RUS VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_NORMALIZED_RUS VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_TREE_RUS VARCHAR(1200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_ASCII_RUS VARCHAR(150) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LABEL_URL_RUS VARCHAR(600) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
LONGITUDE NUMERIC(11, 8),
LATITUDE NUMERIC(10, 8),
RANK INTEGER DEFAULT 0,
RANK_RESIDENTIAL_FORSALE INTEGER DEFAULT 0,
RANK_RESIDENTIAL_FORRENT INTEGER DEFAULT 0,
RANK_COMMERCIAL_FORSALE INTEGER DEFAULT 0,
RANK_COMMERCIAL_FORRENT INTEGER DEFAULT 0,
RANK_VACATIONRENTALS INTEGER DEFAULT 0,
RANK_VACATIONRENTALS_DISCOUNT INTEGER DEFAULT 0);

i insert in both table this row
INSERT INTO TEST (ID, LATITUDE, LONGITUDE) VALUES
('IT-00298601-00298707-00298781', 43.620648, 11.470315);

(the second table have also lot of other row)

i do after to check :

Select
First 1 latitude, longitude from TEST where
latitude > 43.53118784 and latitude < 43.71105216 and Longitude > 11.43263691 and longitude < 11.68146909
union
Select
First 1 GP1.latitude, GP1.longitude from GEO_PLACE GP1 where
GP1.latitude > 43.53118784 and GP1.latitude < 43.71105216 and GP1.Longitude > 11.43263691 and GP1.longitude < 11.68146909

the Two row are exactly the same (ie only one row returned, or 2 row if Union ALL)
43.620648 11.470315

now i do

Select
First 1 http://GP1.ID from TEST GP1 where
GP1.latitude > 43.53118784 and GP1.latitude < 43.71105216 and GP1.Longitude > 11.43263691 and GP1.longitude < 11.68146909
order by
ACOS((SIN(GP1.latitude*(pi()/180)) * SIN(43.62112*(pi()/180))) + (COS(GP1.latitude*(pi()/180)) * COS(43.62112*(pi()/180)) * COS((11.557053*(pi()/180)) - (GP1.Longitude*(pi()/180))))) * 6371

Work OK

now i just replace TEST by GEO_PLACE

Select
First 1 http://GP1.ID from GEO_PLACE GP1 where
GP1.latitude > 43.53118784 and GP1.latitude < 43.71105216 and GP1.Longitude > 11.43263691 and GP1.longitude < 11.68146909
order by
ACOS((SIN(GP1.latitude*(pi()/180)) * SIN(43.62112*(pi()/180))) + (COS(GP1.latitude*(pi()/180)) * COS(43.62112*(pi()/180)) * COS((11.557053*(pi()/180)) - (GP1.Longitude*(pi()/180))))) * 6371

and i have the error Argument for ACOS must be in the range [-1, 1] !!

the most important to remembered, the row and the colum definition are EXACTLY THE SAME in the both tables !

Commented by: @hvlad

Seems GEO_PLACE have another records satisfying your WHERE clause but throwing error in ORDER BY expression
To check - run last query without ORDER BY and without FIRST

Commented by: @asfernandes

Libc acos manpage says:
On success, these functions return the arc cosine of x in radians; the return value is in the range [0, pi].

   If x is a NaN, a NaN is returned\.

   If x is \+1, \+0 is returned\.

   If x is positive infinity or negative infinity, a domain error occurs, and a NaN is returned\.

   If x is outside the range \[\-1, 1\], a domain error occurs, and a NaN is returned\.

So, what about call acos without verify the parameter, but verifying if the returned value was a NaN?

Commented by: @hvlad

> So, what about call acos without verify the parameter, but verifying if the returned value was a NaN?

It will just replace one error code by another :) Or do you offer to pass NaN's up to the application as is ?

Commented by: @asfernandes

Vlad, the user said everything was working before. Problem happened only when we start detecting if input in in range -1 to 1 in Windows.

So I'd say, MSVC implementation of ACOS detect it in different way (i.e., at some precision).

Commented by: @hvlad

Adriano, i already *run* his sample and argument *really* not in range [1, -1] and ACOS *returns NaN* when i disabled the range check

Commented by: vander clock stephane (arkadia)

actually i correct this bug by doing a round(xxx,15) inside the acos to be sure to be in the [-1..1] boundary

ie:

                                                      'ACOS\('\+
                                                            'ROUND\('\+  // round because of bug in FB 2\.5\.\.\. [CORE2970](https://github.com/FirebirdSQL/firebird/issues?q=CORE2970+in%3Atitle)
                                                                   '\(' \+
                                                                     '\(' \+
                                                                       'SIN\(GP1\.latitude\*\(pi\(\)/180\)\) \* '\+
                                                                       'SIN\('\+FormatFloat\('0\.#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠',Latitude,VWinRE\_DefaultFormatSettingsEx\.FormatSettings\)\+'\*\(pi\(\)/180\)\)'\+
                                                                     '\)' \+
                                                                     ' \+ ' \+
                                                                     '\(' \+
                                                                       'COS\(GP1\.latitude\*\(pi\(\)/180\)\) \* '\+
                                                                       'COS\('\+FormatFloat\('0\.#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠',Latitude,VWinRE\_DefaultFormatSettingsEx\.FormatSettings\)\+'\*\(pi\(\)/180\)\) \* '\+
                                                                       'COS\(\('\+FormatFloat\('0\.#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠',Longitude,VWinRE\_DefaultFormatSettingsEx\.FormatSettings\) \+ '\*\(pi\(\)/180\)\) \- \(GP1\.Longitude\*\(pi\(\)/180\)\)\)'\+
                                                                     '\)' \+
                                                                   '\),' \+
                                                                   '15' \+
                                                                 '\)' \+
                                                          '\) \* '\+

but yes from FB 1.5 to FB 2.5 RC2 it's was working perfectly and in FB 2.5 RC3 then it's start to raise an exception :(
So it's unportant to know why and to alarm user about it in there migration of FB to FB 2.5 ..