AnyhowStep / tsql

A SQL query-builder/ORM
37 stars 3 forks source link

Document MySQL PI() weird behaviour #252

Open AnyhowStep opened 4 years ago

AnyhowStep commented 4 years ago

You know how one of the oft-touted rules for databases is to decouple data and representation? MySQL really ignores that rule for the PI() functions.

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_pi

The default number of decimal places displayed is seven, but MySQL uses the full double-precision value internally.

So, PI() is 3.141593, but PI() + 0e0 is 3.141592653589793

And, PI() = 3.141592653589793 is TRUE because the displayed value and actual value are different.

However, database drivers don't generally know about this So, you might think a MySQL driver would return the "actual" value of PI() And not the "display" value of PI() Nope MySQL drivers return the "display" value of PI() !@#$%^&*


This gives weird shit like,

SELECT PI(), PI() = 3.141592653589793, 3.141593 = 3.141592653589793;
> 3.141593, TRUE, FALSE

We also have,

SELECT PI() = 3.14159265358979
> TRUE
AnyhowStep commented 4 years ago
SELECT
PI() = 3.14159265358979,
3.14159265358979 = PI(),
PI() = 3.141592653589793,
3.141592653589793 = PI(),
3.14159265358979 = 3.141592653589793,
3.141592653589793 = 3.14159265358979, 

PI(),
3.14159265358979, 
3.141592653589793,

(PI()+0e0) = 3.14159265358979,
3.14159265358979 = (PI()+0e0),
(PI()+0e0) = 3.141592653589793,
3.141592653589793 = (PI()+0e0)
;
PI() = 3.14159265358979 3.14159265358979 = PI() PI() = 3.141592653589793 3.141592653589793 = PI() 3.14159265358979 = 3.141592653589793 3.141592653589793 = 3.14159265358979 PI() 3.14159265358979 3.141592653589793 (PI()+0e0) = 3.14159265358979 3.14159265358979 = (PI()+0e0) (PI()+0e0) = 3.141592653589793 3.141592653589793 = (PI()+0e0)
1 1 1 1 0 0 3.141593 3.14159265358979 3.141592653589793 0 0 1 1
AnyhowStep commented 4 years ago

WHY MYSQL WHY