IIRC there was also another problem with MySQL and .NET: MySQL used the year 0 as an uninitialized DateTime but .NET starts at year 1.
I think the workaround was to pass some parameter in the connection string.
Time for a WTF MySQL Moment
111–120 of 121 posts
Re: Time for a WTF MySQL Moment
#112That's yet another incompatibility with the ISO/ANSI SQL specification. In the specification, the TIME type is defined as containing HOUR, MINUTE and SECOND fields, representing the "hour within day", "minute within hour" and "second within minute" values, respectively, so, the valid range for that type supposed to be "00:00:00:00.00000..." to "23:59:59.99999...". It's not intended to represent an interval, although…
Well MySQL has a history of ignoring the SQL standard even for the most simple things (even if those were defined before MySQL even existed), so it's not really surprising they got the TIME data type wrong as well.
Re: Time for a WTF MySQL Moment
#113Here’s a Redshift oddity that I don’t think is documented: select sum(y.a), count(y.a) from(select distinct x.a from ( select 1 as a union all select 2 as a union all select 1 as a)x)y sum | count -----+------- 4 | 3 Sqlite3 returns the correct results of sum of 3 count of 2. To fix this don’t use subqueries.
Interesting, Postgres does this correctly:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc4ce40bd52695...
Re: Time for a WTF MySQL Moment
#114A list of MySQL WTFs: https://grinnz.com/stuff/lolmysql.txt
Is there something similar for Postgres? I realize it might be a lot shorter, but any system big enough has its share of gotchas.
Re: Time for a WTF MySQL Moment
#115So, mysql is preserving backwards compatibility? Good. A flag to break this backwards compatibility and offer a larger range is probably warranted, but until that's implemented this behavior is fine. "I think this is stupid" is a really poor reason to break backwards compatibility, despite how many other software projects use this reasoning. But of course, MySQL bad, PostgreSQL good.
MySQL wouldn't be in that situation if they had paid attention to the definition of the TIME (and INTERVAL) data types specified in the SQL standard long before MySQL was created
Re: Time for a WTF MySQL Moment
#116Earlier quoted context omitted.
Nice These three points has made me raving mad from working with mysql: - The default 'latin1' character set is in fact cp1252, not ISO-8859-1, meaning it contains the extra characters in the Windows codepage. 'latin2', however, is ISO-8859-2. - The 'utf8' character set is limited to unicode characters that encode to 1-3 bytes in UTF-8. 'utf8mb4' was added in MySQL 5.5.3 and supports up to 4-byte encoded characters.…
> - The default 'latin1' character set is in fact cp1252, not ISO-8859-1, meaning it contains the extra characters in the Windows codepage. Actually, it's generally saner to assume that people mean Windows-1252 when they say ISO-8859-1. Charset labeling is frequently incorrect, and C1 characters are so infrequently used that seeing one pop up probably means you actually wanted Windows-1252 instead.
Well, the thing is that Mysql latin1/cp1252 isn't actually code page 1252, it's a tad different. There are extra characters that CP1252 doesn't know about, so it's not entirely compatible with CP1252 or Windows-1252.
Re: Time for a WTF MySQL Moment
#117Earlier quoted context omitted.
Nice These three points has made me raving mad from working with mysql: - The default 'latin1' character set is in fact cp1252, not ISO-8859-1, meaning it contains the extra characters in the Windows codepage. 'latin2', however, is ISO-8859-2. - The 'utf8' character set is limited to unicode characters that encode to 1-3 bytes in UTF-8. 'utf8mb4' was added in MySQL 5.5.3 and supports up to 4-byte encoded characters.…
I am currently trying to fix a program that was made by a person that didn't knew those details of MySQL... Most weirdly, the fact that the default collation is SWEDISH. It is a complete freak show, the users kinda got used to it, butchering our language (portuguese) to use only characters valid in english, hoping MySQL won't barf spetacularly on them.
But... why would you not just set the correct collation?
Re: Time for a WTF MySQL Moment
#118Earlier quoted context omitted.
BCD is still in use in many financial applications, where it's the usual way to handle decimal fractions which can't be exactly represented in binary (floating or fixed-point fractions).
Is there any advantage at all to BCD over the int number of cents? Either approach gives exact precision but requires a marker to say how many digits to the right of the decimal. Fixed-point decimal is vastly faster for calculations. BCD requires weird carry calculations, is less memory efficient per digit stored, and requires a choice between truly awful memory density (one digit per byte) or using bitwise operation…
Not really, other than compatibility. When the previous poster said financial applications, what was probably meant was "mainframe applications". BCD is still popular on z/OS because it's a first-class format there, and even editors knows how to handle files that contain BCD values.
Re: Time for a WTF MySQL Moment
#119This is going to sound insulting, maybe it is, sorry. It's definitely subjective. The reason I reach for Postgres over MySQL isn't features or technical superiority. Although those result from the reason. Which is, PG devs consistently have "taste", they have "good" style. They make good choices. MySQL devs are not consistently strong in these areas. I'm guessing that MySQL is now so full of tech and design debt (lik…
I mean, with the exception of the name, of course.
"postgre" feels and sounds like a plate full of very wet, oily rice and the subsequent addition of clear vomit to the mix.
Re: Time for a WTF MySQL Moment
#120MySQL has come a long long way indeed but not enough to make me turn away from Postgres.
For me MySQL isn't even worth touching. Just this week their 'stable' binaries from their website threw seg faults while executing large SQL batches on my dev box. I replaced it with mariaDB and had no issues.