Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Time for a WTF MySQL Moment

gbl08ma.com

Post reply on HN
Print view View original

91–100 of 121 posts

Re: Time for a WTF MySQL Moment

#91

Post by Dylan16807 »

kochthesecond wrote: post #42
xeeeeeeeeeeenu wrote: post #24

A list of MySQL WTFs: https://grinnz.com/stuff/lolmysql.txt

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.…

> utf8 being effectively alias of utf8mb3 has cost us so much work its not even funny.

An extra warning about that mess: mysqldump in many configurations will silently convert utf8mb4 down to utf8mb3. So when you're testing your backups or migrations, do an extra check to make sure that emoji and rarer characters didn't get eaten!

Re: Time for a WTF MySQL Moment

#92

Post by gigatexal »

Here’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.

Re: Time for a WTF MySQL Moment

#93

Post by kstrauser »

rst wrote: post #83
Doctor_Fegg wrote: post #68

Earlier quoted context omitted.

BCD was outdated when I got my first Amstrad CPC (1984). The assembly language textbooks all said “this is a weird holdover from the 8080, don’t bother with it”.

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 operations to do nibble-level addressing.

Actually, the more I think about it the more awful it gets. I'll go ahead and assert that the only reason ever to consider BCD is for compatibility with legacy systems that use it, and even then you'd only want to use it on the edges of the codebase where the system interfaces live.

Re: Time for a WTF MySQL Moment

#94

Post by Dylan16807 »

eska wrote: post #10

This gives me flashbacks to the DOS filesystem timestamps. It's exactly the same mistake. By splitting the date into multiple fields, bits are wasted. If they hadn't tried to be smart and just made it one number, it would've been more precise with a wider range.

I don't really see it as the same thing. Using multiple fields of bits works fine here. 60 possibilities for seconds and minutes fit very well into 6 bits each. A raw 24-bit integer gives you plus or minus 2330 hours, and if you don't reserve a bit the format they actually went with gives you plus or minus 2047 hours. Since it just needs to represent 999 hours and change, that's fine.

The problem is the weird non-bit packing they did before.

DOS is different, partly because there are fields that fit worse, and partly because 32 bits is just barely enough to store seconds in the first place. If you did a DOS-style packing with 34 or 38 bits it would work fine. And it would be able to represent leap seconds, making it arguably better than unix timestamps!

Re: Time for a WTF MySQL Moment

#95

Post by chrisan »

TedShiller wrote: post #51

I gave up on MySQL a long time ago, when I realized that I had to activate special types of settings just to make Unicode characters work in tables. In Postgres, it just works out of the box.

Must have been more than 10 years ago when utf8mb4 was added where you don't have to activate any kind of special settings.

I was using MSSQL prior to 2010 so I have no idea of MySQL unicode handling before that

Re: Time for a WTF MySQL Moment

#96

Post by kaslai »

njharman wrote: post #57

This 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…

In the MySQL vs PgSQL comparison, it basically feels like MySQL tried to obtain fast performance first, then worked towards correct and useful behavior later, where PgSQL went for correct and useful behavior first, then fast performance later. While the end result after decades of development is comparable, the echos of those very different beginnings remain in the current products.

Re: Time for a WTF MySQL Moment

#97

Post by jcranmer »

kochthesecond wrote: post #42
xeeeeeeeeeeenu wrote: post #24

A list of MySQL WTFs: https://grinnz.com/stuff/lolmysql.txt

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.

Re: Time for a WTF MySQL Moment

#98

Post by int_19h »

rst wrote: post #83
Doctor_Fegg wrote: post #68

Earlier quoted context omitted.

BCD was outdated when I got my first Amstrad CPC (1984). The assembly language textbooks all said “this is a weird holdover from the 8080, don’t bother with it”.

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).

The usual approach is to use fixed point (often, integers with implicit point) or decimal floating point. Neither of which is BCD.

Re: Time for a WTF MySQL Moment

#99

Post by sk5t »

eyelidlessness wrote: post #87
wnoise wrote: post #85

Earlier quoted context omitted.

All of the array types are basically the same. The docs actually do mention this, but only in passing as a current limitation. https://www.postgresql.org/docs/13/arrays.html > The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or num…

Thanks! I don't know why I wasn't able to find it. Still, I would never have expected any of the behavior I described.

I always internalized postgres's array behavior based on the examples, which include multidimensional arrays on text[] columns. Also, common functions like array_length require an index argument--"which array within the possibly multiple arrays do you want the length of" etc.
Post reply on HN