Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Document MySQL PI() weird behaviour #252

Open
AnyhowStep opened this issue Feb 18, 2020 · 2 comments
Open

Document MySQL PI() weird behaviour #252

AnyhowStep opened this issue Feb 18, 2020 · 2 comments
Assignees
Labels

Comments

@AnyhowStep
Copy link
Owner

AnyhowStep commented Feb 18, 2020

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 AnyhowStep self-assigned this Feb 18, 2020
@AnyhowStep
Copy link
Owner Author

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
Copy link
Owner Author

WHY MYSQL WHY

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

1 participant