13 ms·

Time for a WTF MySQL Moment

327 points by gbl08ma 6y ago

jarym 6y ago
MySQL has come a long long way indeed but not enough to make me turn away from Postgres.
- Florin_Andrei 6y ago
  I used to see literally this same comment as far back as 10 years ago, maybe more. Just saying - I have no horse in this race.
  - jarym 6y ago
    Well I’m not wed to anything - maybe sometime we’ll see an article about reasons TO switch.
- pippy 6y ago
  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.
falcolas 6y ago
So, 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.
- Sebb767 6y ago
  > "I think this is stupid" is a really poor reason to break backwards compatibility, despite how many other software projects use this reasoning. True. But his argument seems to be in the direction of "this is highly unexpected behaviour" and I tend to agree. The number of applications broken by extending the date range probably dwarves to the number of bugs avoided by not having the time span break at such a strange length.
  - falcolas 6y ago
    > "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 limit should be larger."
    - Sebb767 6y ago
      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).
      - falcolas 6y ago
        > how much compatibility would it really break Probably only a few applications; but that's still too many. And backwards compatibility does matter. A change here means that some few dozen developers have to now troubleshoot a previously-stable application which now fails silently in odd corner cases.
        nytgop77 6y ago
        then create new type. time2 or datetime13 with improved behaviour and say in docs to prefer newer unless your specicaly know that you need old behaviour.
      - toast0 6y ago
        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.
- hans_castorp 6y ago
  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
hackbinary 6y ago
I learned the semi hard way on PoC system where the Mysql index corrupted. I was lucky and could simply redeploy my application, but I have never used Mysql since.
- falcolas 6y ago
  How does a corrupted index and a TIME type come into play with each other?
  - hackbinary 6y ago
    Because the way MySQL does other things seems has been troublesome. IIRC, MySQL essentially generates incremented integer table and column ids, so rebuilding tables relationships meant that you had to manually regenerate the table schema, then update all the old table files with the new table and column ids which were incremented on from what they were previously. With Postgres, you can just drop the binary table data files in, restart the database and pg rebuilds the indexes and relationships. From a quick google, it now looks like MySQL is now more robust in this regard.
meritt 6y ago
Edit. Fuck HN.
- janwillemb 6y ago
  The issue seems to be that the .NET core provider for MySQL that OP uses maps the .NET Timespan type to this MySQL TIME datatype. They probably didn't think this through.
  - meritt 6y ago
    Edit. Fuck HN.
    - gbl08ma 6y ago
      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 alternative.
      - bcrosby95 6y ago
        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.
        craftinator 6y ago
        It seems like poor design to me, but more so on the .NET side than MySQL side. MySQL retaining backwards compatibility is sensible, though seems a bit awkward that they don't introduce a TIME_LONG type or similar that can hold more useful ranges. .NET just mapping a time span with a wider range down to MySQL's fairly limited range seems destined to cause problems.
josefx 6y ago
> 1 bit sign (1= non-negative, 0= negative) First time I ever saw a number where the leading sign bit has to be set to 1 to indicate non-negative.
- jnwatson 6y ago
  Sorts better that way.
  - karmakaze 6y ago
    It would binary sort weird with negatives < 0 < positives but would put the largest magnitude negative closest to 0. Also not Offset binary which uses 0111 for -1 rather than MySQL's 0001.
- js2 6y ago
  https://en.wikipedia.org/wiki/Offset_binary https://en.wikipedia.org/wiki/Offset_binary
eska 6y ago
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.
- falcolas 6y ago
  FWIW, even keeping an interval in a single number still imposes limits. As proven by the epoch's 2038 problem.
  - mackal 6y ago
    Which MySQL still isn't safe for :P (2038 that is, for where they use it)
  - saturn_vk 6y ago
    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
    - cesarb 6y ago
      > 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).
      - WkndTriathlete 6y ago
        https://stackoverflow.com/a/60709400/571787 https://stackoverflow.com/a/60709400/571787 states otherwise with recent Linux kernels + glibcs.
        cesarb 6y ago
        Note that the glibc release mentioned there (2.32) has been released only two months ago (https://lwn.net/Articles/828210/ https://lwn.net/Articles/828210/), and its release notes don't mention time_t now being 64 bits. The glibc manual page linked to by that answer (https://www.gnu.org/software/libc/manual/html_node/64_002dbit-time-symbol-handling.html https://www.gnu.org/software/libc/manual/html_node/64_002dbi...) says "at this point, 64-bit time support in dual-time configurations is work-in-progress, so for these configurations, the public API only makes the 32-bit time support available", which probably means in practice that 64-bit time_t is still not available for common 32-bit configurations (unless you want to lose binary compatibility with all existing software). I haven't been following this Y2038 work, but it's probably been postponed for a later glibc release (perhaps even the next one).
munk-a 6y ago
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 of date - PG10 no longer follows the different array sized result, it uses null filling) which I managed to find after struggling with an issue where an experimental query was resulting in nulls in the output while unnesting arrays without nulls. All DBs have their warts and while MySQL has an over abundance of warts they tend to be quite well documented. The warts that postgres has tend to be quite buried and their documentation is very good for syntax comprehension but rather light when it comes to deeper learning. 1. https://stackoverflow.com/questions/50364475/how-to-force-postgresql-cartesian-product-behavior-when-unnesting-multiple-ar https://stackoverflow.com/questions/50364475/how-to-force-po...
- masklinn 6y ago
  > I'm a big fan of Postgres too for a number of reasons, but this issue is pretty clearly documented Of course it is, the documentation is where TFAA got the information in the 4th paragraph of the story, out of 15 or so. The range itself is what nerd-sniped the author and led them to try and find out why mysql had such an odd yet specific range.
- benesch 6y ago
  > 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 separate LATERAL ROWS FROM( ... ) item... And there's even a note about the crazy behavior pre-PostgreSQL 10: > Before PostgreSQL 10, putting more than one set-returning function in the same select list did not behave very sensibly unless they always produced equal numbers of rows. Otherwise, what you got was a number of output rows equal to the least common multiple of the numbers of rows produced by the set-returning functions. Also, nested set-returning functions did not work as described above; instead, a set-returning function could have at most one set-returning argument, and each nest of set-returning functions was run independently. Also, conditional execution (set-returning functions inside CASE etc) was previously allowed, complicating things even more. Use of the LATERAL syntax is recommended when writing queries that need to work in older PostgreSQL versions, because that will give consistent results across different versions. I agree that allowing SRFs in the SELECT clause is a wart that should never have been permitted, but I think the PostgreSQL docs do a pretty great job describing both the old behavior and the new behavior that has to balance backwards compatibility with sensibility. (And, indeed, the 9.6 docs have this to say on the behavior of SRFs in the SELECT list: "The key problem with using set-returning functions in the select list, rather than the FROM clause, is that putting more than one set-returning function in the same select list does not behave very sensibly.") 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! [1] [0]: https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC... [1]: https://www.postgresql.org/docs/current/bookindex.html https://www.postgresql.org/docs/current/bookindex.html
simias 6y ago
> 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 used to be pretty popular. By modern standards it's insane, but at a time where binary to decimal conversions could be a serious performance concern it might have made sense. For instance if you had a date in "hours, minutes, seconds" and wanted to add or subtract one of these TIME values, you could do it without a single multiply or divide. Now I was 8 when MySQL first released in 1995, so I can't really comment on whether that choice really made sense back then. 1995 does seem a bit late for BCD shenanigans, but maybe they based their design on existing applications and de-facto standards that could easily go back to the 80's.
- kstrauser 6y ago
  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.
  - Doctor_Fegg 6y ago
    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”.
    - rst 6y ago
      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).
xeeeeeeeeeeenu 6y ago
A list of MySQL WTFs: https://grinnz.com/stuff/lolmysql.txt https://grinnz.com/stuff/lolmysql.txt
- kochthesecond 6y ago
  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. UTF-8 has been defined to encode characters to up to 4 bytes since 2003. - Neither the 'utf8' nor 'utf8mb4' character sets have any case sensitive collation other than 'utf8_bin' and 'utf8mb4_bin', which sort characters by their numeric codepoint. utf8 being effectively alias of utf8mb3 has cost us so much work its not even funny.
  - speeder 6y ago
    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.
    - reaperducer 6y ago
      Most weirdly, the fact that the default collation is SWEDISH. It is a complete freak show, Unless you're Swedish, I imagine. Then it's quite handy. I believe the author of MySQL was Swedish, so to me it all makes sense. It also provides a learning opportunity for people who believe the entire planet operates on ASCII.
    - ranieuwe 6y ago
      MySQL being a Swedish company, the default collation for MySQL was (is?) latin1_swedish_ci.
jspaetzel 6y ago
How'd this get voted so many points? RTFM and use a more appropriate datatype.
deleted 6y ago
[deleted]
crazygringo 6y ago
Ultimately this is a bit of a rant about why MySQL didn't bother changing the TIME type to support an elegant maximum value of 1,024 hours instead of 838. But, seriously? Who cares? It's not even close to an extra order of magnitude of range. The type is obviously meant to be used for time values that have a context of hours within a day, supporting a few days as headroom... so supporting 1,024 instead of 838 is pointless -- if you're getting anywhere even close to the max value, you probably shouldn't be using this type in the first place. And yes, it's probably best not to change it for backwards compatibility. Can I imagine a case where it could break something? No, not off the top of my head. But it probably would break some application somewhere. And for such a widely deployed piece of critical foundational infrastructure, being conservative is the way to go. Nothing about this seems WTF at all, except for the author's seeming opinion that elegant, power-of-two ranges ought to trump backwards compatibility with things that probably made sense at the time.
TedShiller 6y ago
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.
- chrisan 6y ago
  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
  - lmm 6y ago
    > Must have been more than 10 years ago when utf8mb4 was added where you don't have to activate any kind of special settings. Less than 10 years ago; MySQL 5.5 went GA in December 2010.
njharman 6y ago
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 (like OP issue) that they're just stuck, without choice.
- smitty1e 6y ago
  MySQL and PHP are a dynamic duo that never fail to surprise. And not in a desirable way.
- kaslai 6y ago
  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.
- dancemethis 6y ago
  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.
sfilargi 6y ago
“I am struggling to imagine the circumstances where ..... can break ....” If only I had a penny for everyone I heard this argument and we ended up breaking regression tests or something really obscure in the qa or customer setup
- forcemajeure 6y ago
  Fear the unknown unknowns!
- nullsense 6y ago
  I always catch myself when I'm saying it and try to make sense of the cognitive dissonance of saying nothing will break and being somewhat convinced that something, somewhere will.
- sfilargi 6y ago
  everytime* not everyone And yes, I have done it so many times myself too
mikorym 6y ago
TL;DR So the answer is just it's for backwards compatibility with MySQL 3? I was kind of hoping for more.
smeeth 6y ago
I have yet to find a situation where using a native datetime format made more sense than using a unix timestamp in integer fields.
- castorp 6y ago
  https://blog.sql-workbench.eu/post/epoch-mania/ https://blog.sql-workbench.eu/post/epoch-mania/
gigatexal 6y ago
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.
- hans_castorp 6y ago
  Interesting, Postgres does this correctly: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc4ce40bd526955b38db4bc96cb781c2 https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc4ce40bd52695...
nitramt 6y ago
That'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 that seems to be the intended semantics for MySQL's TIME type: (from https://dev.mysql.com/doc/refman/8.0/en/time.html https://dev.mysql.com/doc/refman/8.0/en/time.html): > but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative). For representing temporal intervals, the specification defines two kinds of INTERVAL types (year-month and day-time). Year-month intervals can represent intervals in terms of years, months, or a combination of years and months. Similarly, day-time interval, can represent intervals in terms of days, hours, minutes or seconds, or combinations of them (e.g, days+hours, days+hours+minutes, hours+minutes, etc.) As a sidenote, the TIME and DATE types are related to the TIMESTAMP type in that TIMESTAMP can be thought of as combination of a DATE part (year, month, day) and a TIME (hour, minute, second) part.
- hans_castorp 6y ago
  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.
jmnicolas 6y ago
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.

327 points by gbl08ma 6y ago

jarym 6y ago

MySQL has come a long long way indeed but not enough to make me turn away from Postgres.

Florin_Andrei 6y ago
I used to see literally this same comment as far back as 10 years ago, maybe more. Just saying - I have no horse in this race.
- jarym 6y ago
  Well I’m not wed to anything - maybe sometime we’ll see an article about reasons TO switch.
pippy 6y ago
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.

falcolas 6y ago

So, 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.

Sebb767 6y ago
> "I think this is stupid" is a really poor reason to break backwards compatibility, despite how many other software projects use this reasoning. True. But his argument seems to be in the direction of "this is highly unexpected behaviour" and I tend to agree. The number of applications broken by extending the date range probably dwarves to the number of bugs avoided by not having the time span break at such a strange length.
- falcolas 6y ago
  > "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 limit should be larger."
  - Sebb767 6y ago
    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).
    - falcolas 6y ago
      > how much compatibility would it really break Probably only a few applications; but that's still too many. And backwards compatibility does matter. A change here means that some few dozen developers have to now troubleshoot a previously-stable application which now fails silently in odd corner cases.
      - nytgop77 6y ago
        then create new type. time2 or datetime13 with improved behaviour and say in docs to prefer newer unless your specicaly know that you need old behaviour.
    - toast0 6y ago
      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.
hans_castorp 6y ago
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

hackbinary 6y ago

I learned the semi hard way on PoC system where the Mysql index corrupted. I was lucky and could simply redeploy my application, but I have never used Mysql since.

falcolas 6y ago
How does a corrupted index and a TIME type come into play with each other?
- hackbinary 6y ago
  Because the way MySQL does other things seems has been troublesome. IIRC, MySQL essentially generates incremented integer table and column ids, so rebuilding tables relationships meant that you had to manually regenerate the table schema, then update all the old table files with the new table and column ids which were incremented on from what they were previously. With Postgres, you can just drop the binary table data files in, restart the database and pg rebuilds the indexes and relationships. From a quick google, it now looks like MySQL is now more robust in this regard.

meritt 6y ago

Edit. Fuck HN.

janwillemb 6y ago
The issue seems to be that the .NET core provider for MySQL that OP uses maps the .NET Timespan type to this MySQL TIME datatype. They probably didn't think this through.
- meritt 6y ago
  Edit. Fuck HN.
  - gbl08ma 6y ago
    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 alternative.
    - bcrosby95 6y ago
      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.
      - craftinator 6y ago
        It seems like poor design to me, but more so on the .NET side than MySQL side. MySQL retaining backwards compatibility is sensible, though seems a bit awkward that they don't introduce a TIME_LONG type or similar that can hold more useful ranges. .NET just mapping a time span with a wider range down to MySQL's fairly limited range seems destined to cause problems.

josefx 6y ago

> 1 bit sign (1= non-negative, 0= negative) First time I ever saw a number where the leading sign bit has to be set to 1 to indicate non-negative.

jnwatson 6y ago
Sorts better that way.
- karmakaze 6y ago
  It would binary sort weird with negatives < 0 < positives but would put the largest magnitude negative closest to 0. Also not Offset binary which uses 0111 for -1 rather than MySQL's 0001.
js2 6y ago
https://en.wikipedia.org/wiki/Offset_binary https://en.wikipedia.org/wiki/Offset_binary

eska 6y ago

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.

falcolas 6y ago
FWIW, even keeping an interval in a single number still imposes limits. As proven by the epoch's 2038 problem.
- mackal 6y ago
  Which MySQL still isn't safe for :P (2038 that is, for where they use it)
- saturn_vk 6y ago
  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
  - cesarb 6y ago
    > 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).
    - WkndTriathlete 6y ago
      https://stackoverflow.com/a/60709400/571787 https://stackoverflow.com/a/60709400/571787 states otherwise with recent Linux kernels + glibcs.
      - cesarb 6y ago
        Note that the glibc release mentioned there (2.32) has been released only two months ago (https://lwn.net/Articles/828210/ https://lwn.net/Articles/828210/), and its release notes don't mention time_t now being 64 bits. The glibc manual page linked to by that answer (https://www.gnu.org/software/libc/manual/html_node/64_002dbit-time-symbol-handling.html https://www.gnu.org/software/libc/manual/html_node/64_002dbi...) says "at this point, 64-bit time support in dual-time configurations is work-in-progress, so for these configurations, the public API only makes the 32-bit time support available", which probably means in practice that 64-bit time_t is still not available for common 32-bit configurations (unless you want to lose binary compatibility with all existing software). I haven't been following this Y2038 work, but it's probably been postponed for a later glibc release (perhaps even the next one).

munk-a 6y ago

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 of date - PG10 no longer follows the different array sized result, it uses null filling) which I managed to find after struggling with an issue where an experimental query was resulting in nulls in the output while unnesting arrays without nulls. All DBs have their warts and while MySQL has an over abundance of warts they tend to be quite well documented. The warts that postgres has tend to be quite buried and their documentation is very good for syntax comprehension but rather light when it comes to deeper learning. 1. https://stackoverflow.com/questions/50364475/how-to-force-postgresql-cartesian-product-behavior-when-unnesting-multiple-ar https://stackoverflow.com/questions/50364475/how-to-force-po...

masklinn 6y ago
> I'm a big fan of Postgres too for a number of reasons, but this issue is pretty clearly documented Of course it is, the documentation is where TFAA got the information in the 4th paragraph of the story, out of 15 or so. The range itself is what nerd-sniped the author and led them to try and find out why mysql had such an odd yet specific range.
benesch 6y ago
> 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 separate LATERAL ROWS FROM( ... ) item... And there's even a note about the crazy behavior pre-PostgreSQL 10: > Before PostgreSQL 10, putting more than one set-returning function in the same select list did not behave very sensibly unless they always produced equal numbers of rows. Otherwise, what you got was a number of output rows equal to the least common multiple of the numbers of rows produced by the set-returning functions. Also, nested set-returning functions did not work as described above; instead, a set-returning function could have at most one set-returning argument, and each nest of set-returning functions was run independently. Also, conditional execution (set-returning functions inside CASE etc) was previously allowed, complicating things even more. Use of the LATERAL syntax is recommended when writing queries that need to work in older PostgreSQL versions, because that will give consistent results across different versions. I agree that allowing SRFs in the SELECT clause is a wart that should never have been permitted, but I think the PostgreSQL docs do a pretty great job describing both the old behavior and the new behavior that has to balance backwards compatibility with sensibility. (And, indeed, the 9.6 docs have this to say on the behavior of SRFs in the SELECT list: "The key problem with using set-returning functions in the select list, rather than the FROM clause, is that putting more than one set-returning function in the same select list does not behave very sensibly.") 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! [1] [0]: https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC... [1]: https://www.postgresql.org/docs/current/bookindex.html https://www.postgresql.org/docs/current/bookindex.html

simias 6y ago

> 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 used to be pretty popular. By modern standards it's insane, but at a time where binary to decimal conversions could be a serious performance concern it might have made sense. For instance if you had a date in "hours, minutes, seconds" and wanted to add or subtract one of these TIME values, you could do it without a single multiply or divide. Now I was 8 when MySQL first released in 1995, so I can't really comment on whether that choice really made sense back then. 1995 does seem a bit late for BCD shenanigans, but maybe they based their design on existing applications and de-facto standards that could easily go back to the 80's.

kstrauser 6y ago
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.
- Doctor_Fegg 6y ago
  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”.
  - rst 6y ago
    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).

xeeeeeeeeeeenu 6y ago

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

kochthesecond 6y ago
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. UTF-8 has been defined to encode characters to up to 4 bytes since 2003. - Neither the 'utf8' nor 'utf8mb4' character sets have any case sensitive collation other than 'utf8_bin' and 'utf8mb4_bin', which sort characters by their numeric codepoint. utf8 being effectively alias of utf8mb3 has cost us so much work its not even funny.
- speeder 6y ago
  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.
  - reaperducer 6y ago
    Most weirdly, the fact that the default collation is SWEDISH. It is a complete freak show, Unless you're Swedish, I imagine. Then it's quite handy. I believe the author of MySQL was Swedish, so to me it all makes sense. It also provides a learning opportunity for people who believe the entire planet operates on ASCII.
  - ranieuwe 6y ago
    MySQL being a Swedish company, the default collation for MySQL was (is?) latin1_swedish_ci.

jspaetzel 6y ago

How'd this get voted so many points? RTFM and use a more appropriate datatype.

deleted 6y ago

[deleted]

crazygringo 6y ago

Ultimately this is a bit of a rant about why MySQL didn't bother changing the TIME type to support an elegant maximum value of 1,024 hours instead of 838. But, seriously? Who cares? It's not even close to an extra order of magnitude of range. The type is obviously meant to be used for time values that have a context of hours within a day, supporting a few days as headroom... so supporting 1,024 instead of 838 is pointless -- if you're getting anywhere even close to the max value, you probably shouldn't be using this type in the first place. And yes, it's probably best not to change it for backwards compatibility. Can I imagine a case where it could break something? No, not off the top of my head. But it probably would break some application somewhere. And for such a widely deployed piece of critical foundational infrastructure, being conservative is the way to go. Nothing about this seems WTF at all, except for the author's seeming opinion that elegant, power-of-two ranges ought to trump backwards compatibility with things that probably made sense at the time.

TedShiller 6y ago

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.

chrisan 6y ago
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
- lmm 6y ago
  > Must have been more than 10 years ago when utf8mb4 was added where you don't have to activate any kind of special settings. Less than 10 years ago; MySQL 5.5 went GA in December 2010.

njharman 6y ago

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 (like OP issue) that they're just stuck, without choice.

smitty1e 6y ago
MySQL and PHP are a dynamic duo that never fail to surprise. And not in a desirable way.
kaslai 6y ago
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.
dancemethis 6y ago
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.

sfilargi 6y ago

“I am struggling to imagine the circumstances where ..... can break ....” If only I had a penny for everyone I heard this argument and we ended up breaking regression tests or something really obscure in the qa or customer setup

forcemajeure 6y ago
Fear the unknown unknowns!
nullsense 6y ago
I always catch myself when I'm saying it and try to make sense of the cognitive dissonance of saying nothing will break and being somewhat convinced that something, somewhere will.
sfilargi 6y ago
everytime* not everyone And yes, I have done it so many times myself too

mikorym 6y ago

TL;DR So the answer is just it's for backwards compatibility with MySQL 3? I was kind of hoping for more.

smeeth 6y ago

I have yet to find a situation where using a native datetime format made more sense than using a unix timestamp in integer fields.

castorp 6y ago
https://blog.sql-workbench.eu/post/epoch-mania/ https://blog.sql-workbench.eu/post/epoch-mania/

gigatexal 6y ago

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.

hans_castorp 6y ago
Interesting, Postgres does this correctly: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc4ce40bd526955b38db4bc96cb781c2 https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc4ce40bd52695...

nitramt 6y ago

That'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 that seems to be the intended semantics for MySQL's TIME type: (from https://dev.mysql.com/doc/refman/8.0/en/time.html https://dev.mysql.com/doc/refman/8.0/en/time.html): > but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative). For representing temporal intervals, the specification defines two kinds of INTERVAL types (year-month and day-time). Year-month intervals can represent intervals in terms of years, months, or a combination of years and months. Similarly, day-time interval, can represent intervals in terms of days, hours, minutes or seconds, or combinations of them (e.g, days+hours, days+hours+minutes, hours+minutes, etc.) As a sidenote, the TIME and DATE types are related to the TIMESTAMP type in that TIMESTAMP can be thought of as combination of a DATE part (year, month, day) and a TIME (hour, minute, second) part.

hans_castorp 6y ago
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.

jmnicolas 6y ago

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.