Here is a recursive CTE that will calculate the square root to the best
precision your processor math implementation is capable of. It limits the
recursion by prohibiting the insertion of duplicate guesses by using UNION
rather than UNION ALL, which will cause pathological cases that oscillate to
terminate. It then scans those guesses looking for the one that is the
"closest" approximation to the actual square root. If you ask for the square
root of a negative number, you get null, and if subnormal division gives a nan
(NULL) that will also terminate the recursion.
with guesses(findRootOf, guessRoot)
as (
select ?1,
case when ?1 < 0 then null else ?1 / 2.0 end
union
select findRootOf,
(guessRoot + findRootOf / guessRoot) / 2.0 as newGuessRoot
from guesses
where guessRoot > 0.0
)
select guessRoot
from guesses
order by abs(guessRoot*guessRoot - findRootOf)
limit 1;
Note that the parameter is used twice. Once to put in the table, and again to
compute the first "guess" (which is really to prime the guess with NULL to
prevent attempts to calculate the root of a negative number).
guessRoot exactly matches the results of the sqrt function 76% of the time, and
is within 1 ULP 100% of the time.
guessRoot*guessRoot == findRootOf 48% of the time, within 1 ULP 94% of the
time, and within 2 ULP 100% of the time.
*based on 5,000,000 randomly generated positive real numbers
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
