Time for a WTF MySQL Moment • Hacker News Board

Re: Time for a WTF MySQL Moment

#81

Post by pimeys » Tue, Oct 06, 2020, 8:35 PM UTC

ThePadawan wrote: post #66

eyelidlessness wrote: post #60

Earlier quoted context omitted.

Also while we're picking on other DBs, another fun WTF I've encountered (this time in an external system): SQL Server stores timestamps to ~1/300th of a second resolution. This[1] StackOverflow question describes different behavior than I saw (it rounded differently), so apparently it's not even consistent. I'd assume across versions? IDK, never had time to look too deeply into this one either. [1]: https://stackover…

Note that only happens using the DATETIME data type. As long as I remember, the documentation for DATETIME [0] has had a disclaimer at the top to not use it, but use DATETIME2 instead. Of course, that doesn't excuse older systems, but this is an issue that can be avoided for new work. [0] https://docs.microsoft.com/en-us/sql/t-sql/data-types/dateti...

And datetime2 was introduced in mssql 2008… So 12 years ago.

Of course when maintaining the rust crate for mssql, implementing support for these old types was necessary and I had some wtf moments. Another interesting one is the smalldatetime, and the biggest annoyance the division to varchar and nvarchar -- the n-variants of cource using ucs2 to store the data.

This only changed in the 2019 version, where one can use varchar to store utf8 data with a special collation.

Still prefer mssql over mysql, any day.

Re: Time for a WTF MySQL Moment

#82

Post by thaumasiotes » Tue, Oct 06, 2020, 8:43 PM UTC

benesch wrote: post #44

munk-a wrote: post #19

I'm a big fan of Postgres too for a number of reasons, but this issue is pretty clearly documented so I'd like to counter with an issue I hit in Postgres recently that is terribly documented. UNNEST works a bit funky, and in particular it works super funky if you have multiple calls in the same select statement (or any set expanded function calls it turns out). There's a bit of a dive into here[1] (though that is out…

> I hit in Postgres recently that is terribly documented. I'm going to have to disagree with you there. This issue is quite well documented in the "SQL Functions Returning Sets" section [0]. The relevant bit starts thusly: > ...Set-returning functions can be nested in a select list, although that is not allowed in FROM-clause items. In such cases, each level of nesting is treated separately, as though it were a separ…

> I do think one notable defect with the PostgreSQL docs is that they were designed in a time before modern search engines. They are better understood as a written manual in electronic form. Almost always the information you need is there, but possibly not in the chapter that Google will surface. But there are all sorts of tricks you can use if you update your mental model of how to read the PostgreSQL docs. For example, there's an old-style index!

I think of the Postgres docs as significantly better than most other documentation for this reason. The information is there and it's organized in a way that makes sense.

Re: Time for a WTF MySQL Moment

#83

Post by rst » Tue, Oct 06, 2020, 8:53 PM UTC

Doctor_Fegg wrote: post #68

kstrauser wrote: post #54

Earlier quoted context omitted.

I'll comment: it absolutely didn't make sense back then, either. We didn't use BCD for pretty much anything in '95. If anything, all timestamps were 32 bit signed ints. Edit: plenty of things still stored dates as strings where the emphasis of the app was on displaying information. Int and float types carried the day whenever any kind of math was going to be used, or when you wanted to output the data in multiple for…

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

Re: Time for a WTF MySQL Moment

#84

Post by smitty1e » Tue, Oct 06, 2020, 9:18 PM UTC

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…

MySQL and PHP are a dynamic duo that never fail to surprise.

And not in a desirable way.

Re: Time for a WTF MySQL Moment

#85

Post by wnoise » Tue, Oct 06, 2020, 9:22 PM UTC

eyelidlessness wrote: post #56

munk-a wrote: post #19

I'm a big fan of Postgres too for a number of reasons, but this issue is pretty clearly documented so I'd like to counter with an issue I hit in Postgres recently that is terribly documented. UNNEST works a bit funky, and in particular it works super funky if you have multiple calls in the same select statement (or any set expanded function calls it turns out). There's a bit of a dive into here[1] (though that is out…

I am also a big fan of Postgres, and tend to have a bit of fun picking on MySQL having been scarred by it in a past life. But since we're picking at Postgres warts... One which bit me recently, and is still utterly baffling to me, is that a column defined as an array type will accept values of that array's type in any number of dimensions greater than that specified for the column. In other words, `{{{{{{text}}}}}}`…

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 number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

Similar text extends at least all the way back to the documentation for 7.1.

Re: Time for a WTF MySQL Moment

#86

Post by retox » Tue, Oct 06, 2020, 9:23 PM UTC

ThePadawan wrote: post #66

eyelidlessness wrote: post #60

Earlier quoted context omitted.

Also while we're picking on other DBs, another fun WTF I've encountered (this time in an external system): SQL Server stores timestamps to ~1/300th of a second resolution. This[1] StackOverflow question describes different behavior than I saw (it rounded differently), so apparently it's not even consistent. I'd assume across versions? IDK, never had time to look too deeply into this one either. [1]: https://stackover…

Note that only happens using the DATETIME data type. As long as I remember, the documentation for DATETIME [0] has had a disclaimer at the top to not use it, but use DATETIME2 instead. Of course, that doesn't excuse older systems, but this is an issue that can be avoided for new work. [0] https://docs.microsoft.com/en-us/sql/t-sql/data-types/dateti...

I got bitten by this recently maintaining a legacy system. The same .NET DateTime object is stored in a DATETIME field and also embedded in an XML document in the same database record.

A comparison was done between the value stored in the XML and in the DATETIME field, but even using the same DateTime object there is sometimes a 1 to 3 millisecond difference between the two.

Re: Time for a WTF MySQL Moment

#87

Post by eyelidlessness » Tue, Oct 06, 2020, 9:29 PM UTC

wnoise wrote: post #85

eyelidlessness wrote: post #56

Earlier quoted context omitted.

I am also a big fan of Postgres, and tend to have a bit of fun picking on MySQL having been scarred by it in a past life. But since we're picking at Postgres warts... One which bit me recently, and is still utterly baffling to me, is that a column defined as an array type will accept values of that array's type in any number of dimensions greater than that specified for the column. In other words, `{{{{{{text}}}}}}`…

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.

Re: Time for a WTF MySQL Moment

#88

Post by kbenson » Tue, Oct 06, 2020, 9:45 PM UTC

ThePadawan wrote: post #66

eyelidlessness wrote: post #60

Earlier quoted context omitted.

Also while we're picking on other DBs, another fun WTF I've encountered (this time in an external system): SQL Server stores timestamps to ~1/300th of a second resolution. This[1] StackOverflow question describes different behavior than I saw (it rounded differently), so apparently it's not even consistent. I'd assume across versions? IDK, never had time to look too deeply into this one either. [1]: https://stackover…

Note that only happens using the DATETIME data type. As long as I remember, the documentation for DATETIME [0] has had a disclaimer at the top to not use it, but use DATETIME2 instead. Of course, that doesn't excuse older systems, but this is an issue that can be avoided for new work. [0] https://docs.microsoft.com/en-us/sql/t-sql/data-types/dateti...

Given the time of that question, my guess would be they are using floating point underneath, and have worked out the math so you get approximately that much precision. There's some weirdness in this between 32 bit and 64 bit, based on how the calculation is done. See [1].

I had a mysql instance once where I migrated from a 32 bit system to a 64 bit system, and one of the main tables' primary key was a float that was a high resolution timestamp. Unfortunately, on inserting the old data into the new 64 bit system with a MySQL compiled for 64 bit, the values started having lots of collisions on the primary key, where two previously distinct high-resolution float values now rounded to the same float in 64 bit. Counterintuitive, and a nightmare.

1: https://stackoverflow.com/questions/31415712/floating-point-...

Re: Time for a WTF MySQL Moment

#89

Post by dang » Tue, Oct 06, 2020, 9:51 PM UTC

rdiddly wrote: post #37

meritt wrote: post #6

Edit. Fuck HN.

"If we want to complain that MySQL has an utterly useless TIME datatype, and separately they really should have an INTERVAL datatype, then let's do that." Uhh, done? "The issue" is that MySQL has a TIME type that is goofy as all get-out, and this guy's friend had a bug that prompted him to look into the TIME type and find out how goofy it is. I don't recall him complaining that it's not in the docs (since that's wher…

Please don't call names or cross into personal attack on HN, regardless of what other commenters do.

https://news.ycombinator.com/newsguidelines.html

Re: Time for a WTF MySQL Moment

#90

Post by nwallin » Tue, Oct 06, 2020, 10:18 PM UTC

saturn_vk wrote: post #38

falcolas wrote: post #31

Earlier quoted context omitted.

FWIW, even keeping an interval in a single number still imposes limits. As proven by the epoch's 2038 problem.

That's not really a problem though, at least from what i understand. even on 32bit linux systems, time_t is still 64bit. on windows it also seems to be 64bit

The linux kernel has the option CONFIG_COMPAT_32BIT_TIME, which adds syscalls for 32 bit time_t. If this option is not enabled, much badness happens if you run 32 bit binaries, for instance, Steam and many 32 bit native linux games fail to run. (I ran into this problem a few weeks ago)

32 bit linux applications can use 64 bit time_t if they were configured to do so and glibc and the kernel are new enough. But enough closed source binaries don't support 64 bit time_t that we'll probably have problems in 2038.

https://cateee.net/lkddb/web-lkddb/COMPAT_32BIT_TIME.html