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.
Time for a WTF MySQL Moment
51–60 of 121 posts
Re: Time for a WTF MySQL Moment
#52Earlier quoted context omitted.
MySQL not having a proper type to express time spans seems like a fault to me, and "poor design". Of course you can just use an integer for it, but that is a slippery slope, in the end you'll find that you can use strings or byte arrays for everything and you end up with no type system at all. The surprise here is not that the type has limits but that they are so awkward and that there is no better strongly-typed alt…
A missing feature is not the same thing as poor design. If you need time spans in your project it could be a fault, but every product has missing features and choosing a product that is missing features you want might be a poor decision - depending upon other tradeoffs you're making.
If MySQL did not have a TIME type, then I would agree with you.
The problem is that MYSQL does have such a type, and it seems to not be very useful for modern applications.
Maybe you could argue semantically that this is still a “feature” that’s missing. Perhaps. But it seems more like a defect, even if it is documented.
If arrays in Golang could only be 5 elements long (say), I think we would characterize that as a defect, even if it is well documented.
Re: Time for a WTF MySQL Moment
#53Earlier quoted context omitted.
> "this is highly unexpected behaviour" The behavior in question is having an upper and lower bound on a time interval. This strikes me as highly expected behavior. The maximum on the interval is lower than the author expected; and reading the docs quickly cleared up what the interval maximum is. The entire rant boils down to "MySQL's choice to keep backwards compatibility is stupid, because I think this interval lim…
I agree it's not a necessary change. But, on the other hand, how much compatibility would it really break? I cannot imagine much applications are dependent on MySQL throwing an error at 800-odd hours (and it would arguably be a very strange design decision).
Having a type mean different things on different versions of the database is not good. If you want a new type that does new things, it should have a new name. If the old type is so bad, you could make an sql mode to blacklist it.
Yes, that leaves a legacy of why should I use TIME2 or whatever instead of TIME, etc. That's the downfall of having a successful project over a long time frame and not having designed it perfectly at the beginning.
See also UTF8 shouldn't be used in MySQL because it's dumb. But you can't change UTF8 to do the right thing, you have to have a new name that's better.
Re: Time for a WTF MySQL Moment
#54> This format is even less wieldy than the current one, requiring multiplication and division to do basically anything with it, except string formatting and parsing – once again showing that MySQL places too much value on string IO and not so much on having types that are convenient for internal operations and non-string-based protocols. Not necessarily an odd choice in the Olden Days, after all BCD representation us…
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 formats.
Re: Time for a WTF MySQL Moment
#55Earlier 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
> even on 32bit linux systems, time_t is still 64bit
No, on 32-bit Linux systems, time_t is 32 bits (except perhaps on newer ports like 32-bit RISC-V).
Re: Time for a WTF MySQL Moment
#56I'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}}}}}}` can be inserted into columns of the following types:
- `TEXT[]`
- `TEXT[][]`
- `TEXT[][][]`
- `TEXT[][][][]`
- `TEXT[][][][][]`
- `TEXT[][][][][][]`
The inverse is true as well! A column specified `TEXT[][]` (and so on) will accept `{text}`. Of course, none of this (as far as I've been able to find) is documented.
But wait, there's more!
`UNNEST` does not allow you to specify depth, it always unnests to the deepest dimension. This, too, is undocumented. In fact, it's anti-documented. The documents provide an example function to unnest a two-dimensional array that is wholly unnecessary (and likely performs worse than the built-in `UNNEST`, but I'm just guessing). Said documentation would seem to imply that the depth of `UNNEST` is 1, but of course that's not the case.
But wait, there's more still!
What if you want to get at a nested array? Idk, I'm sure it's possible, but if you thought `SELECT that_array[1]` is the way to do it, look under your seat because you're getting a `NULL`!
- - -
Postscript: I discovered the first part of this in a production system where a migration had incorrectly nested some data, and where that data was in turn causing certain requests to unexpectedly fail. Of course, given that this was in production, I didn't have a lot of time to research the issue. Found the problem, fixed it, moved on with my day. In the course of fixing it, I discovered the `UNNEST` issue, which... okay fun, fix it a slightly different way than I expected.
So in the course of verifying the particulars to write this comment, I played around with some things, and discovered the `NULL` issue.
At least when Postgres has wildly unexpected behavior, it's exceptionally unexpected behavior.
Re: Time for a WTF MySQL Moment
#57This 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 (like OP issue) that they're just stuck, without choice.
Re: Time for a WTF MySQL Moment
#58A list of MySQL WTFs: https://grinnz.com/stuff/lolmysql.txt
Those are certainly valid WTFs. I've been bitten by the GROUP_CONCAT issue before.
This list is missing the WTF that cascaded deletes/updates don't cause triggers to fire on child tables:
Re: Time for a WTF MySQL Moment
#59This 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.
An oldie but a goodie:
https://people.cs.nctu.edu.tw/~tsaiwn/sisc/runtime_error_200...
Re: Time for a WTF MySQL Moment
#60I'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}}}}}}`…
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://stackoverflow.com/questions/715432/why-is-sql-server...