From: Smylers Date: 13:51 on 20 Mar 2007 Subject: MySQL Function Naming MySQL has a couple of functions for extracting a substring from a string. They differ in how the substring is selected -- in one of them you specify a delimiter string, and in t'other you specify the index of the first character you want. The one in which you specify an index is called Substring(). The one in which instead of an index you specify a delimiter is called ... wait for it ... Substring_Index(). Wow. Smylers
From: Yossi Kreinin Date: 14:11 on 20 Mar 2007 Subject: Re: MySQL Function Naming Smylers wrote: > The one in which you specify an index is called Substring(). The one in > which instead of an index you specify a delimiter is called ... wait > for it ... Substring_Index(). > Well, "MySQL" is not much of a name by itself, so it's just being consistent. Although the even more consistent "MySubstring" could also be less misleading.
From: Aaron Crane
Date: 14:12 on 20 Mar 2007
Subject: Re: MySQL Function Naming
Smylers writes:
> MySQL has a couple of functions for extracting a substring from
> a string. The one in which you specify an index is called
> Substring(). The one in which instead of an index you specify
> a delimiter is called ... wait for it ... Substring_Index().
Ah, yes.
MySQL: The DBMS Designed To Make Your PHP Programmers Feel At Home.
On the assumption that this is a good moment for MySQL-related hate,
here's one that bit us earlier today:
mysql> select id, label, concat('[', label, ']') as literal
mysql> from tag where label = 'gpl' \G
*************************** 1. row ***************************
id: 255649
label: gpl
literal: [gpl ]
For the record, the column is of type `varchar(255) character set utf8
collate utf8_bin` -- not `char(N)`, and certainly not `char(4)`.
I don't know how better I can tell MySQL that I really really care about
equality of the actual values I'm trying to store in this column.
I'm prepared to believe that this is merely a bug in our particular
point-release of the server, rather than a fundamental design flaw, but
that doesn't stop it being incredibly hateful: if I'm looking for
something equal to an exemplar, then, no, something vaguely similar is
_not_ sufficient.
From: A. Pagaltzis
Date: 15:54 on 20 Mar 2007
Subject: Re: MySQL Function Naming
* Aaron Crane <hateful@xxxxxxxxxx.xx.xx> [2007-03-20 15:15]:
> I'm prepared to believe that this is merely a bug in our
> particular point-release of the server, rather than a
> fundamental design flaw, but that doesn't stop it being
> incredibly hateful: if I'm looking for something equal to an
> exemplar, then, no, something vaguely similar is _not_
> sufficient.
How about where you put a constraint on a column, say it's an
INTEGER and you say it must contain only values from 0 to 99, and
when you try to insert a row which has 200 as the value for that
column, MySQL helpfully truncates the value to 99 and then
proceeds to insert the row?
Now granted, v5 has finally added a strict mode or whatever it's
called, where this kind of constraint violation throws an error
instead of silently mangling your data. But IT TOOK THEM FIVE
VERSIONS to put that in, AND THEN IT'S STILL NOT THE DEFAULT.
Then there's the mindboggling idiocy where NULLs and empty
strings are often considered equivalent. I think they've improved
on that as well, but good grief, this shouldn't be broken to
begin with!
Or here, another case that was recently posted on use.Perl:
mysql> CREATE TEMPORARY TABLE the_dates (d DATE NOT NULL DEFAULT '0000-00-00');
mysql> INSERT INTO the_dates VALUES ('0000-00-00');
mysql> SELECT COUNT(*) FROM the_dates WHERE d IS NOT NULL;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
Fine so far? OK, here comes the punchline:
mysql> SELECT COUNT(*) FROM the_dates WHERE d IS NULL;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
*boggle* HOW does anyone conceive of THAT sort of braindamage?
I assume your horror story is rooted in the "everything's more or
less the same" kind of attitude; just a different symptom of the
same underlying problem.
You'll find loads of horror stories like that if you dig in even
a little. I used to just hate MySQL for being limited and crappy.
Since digging in I've also lost all faith that I can entrust my
data to its care. I have a few projects running on MySQL because
I didn't have the clout to insist against it, but they're now all
being moved to Pg post haste.
This stuff is... not even wrong. I mean, I'm starting to have
trouble even *hating* MySQL anymore. It's like a retard kid that
you just can't help kinda pitying.
Regards,
From: Aaron Crane
Date: 01:08 on 21 Mar 2007
Subject: Re: MySQL Function Naming
A. Pagaltzis writes:
> Now granted, v5 has finally added a strict mode or whatever it's
> called, where this kind of constraint violation throws an error
> instead of silently mangling your data. But IT TOOK THEM FIVE VERSIONS
> to put that in, AND THEN IT'S STILL NOT THE DEFAULT.
Ah, yes, the "version N+1" problem: everything you think you might want
out of MySQL is supported (somehow, even if brokenly and hatefully) in
the version _after_ the one that's available on your production servers.
(Actually, sometimes MySQL makes it version N+k, to give you some light
relief.) I believe the "version N+1" phrasing is due to Smylers (the
original poster in this thread), circa 2000. It's still true. Seven
years later. Still true.
> Or here, another case that was recently posted on use.Perl:
>
> mysql> CREATE TEMPORARY TABLE the_dates (d DATE NOT NULL DEFAULT '0000-00-00');
> mysql> INSERT INTO the_dates VALUES ('0000-00-00');
> mysql> SELECT COUNT(*) FROM the_dates WHERE d IS NOT NULL;
> +----------+
> | 1 |
> +----------+
> mysql> SELECT COUNT(*) FROM the_dates WHERE d IS NULL;
> +----------+
> | 1 |
> +----------+
>
> *boggle* HOW does anyone conceive of THAT sort of braindamage?
Yep, saw that a couple of years ago. (Before hates-software existed, or
I suspect I'd've ranted about it at that time.) Just for fun, try and
work out what sort of data structure they use that thinks a given value
is simultaneously NULL and not-NULL. Truly mind-blowing.
> You'll find loads of horror stories like that if you dig in even
> a little.
I'd like to think that I've seen my share of MySQL horror stories.
Disappointingly, MySQL doesn't agree: there's always another one just
a week or two round the corner. An alarmingly-large proportion of my
commits seem to contain mini-rants about some newly-discovered MySQL
idiocy.
> This stuff is... not even wrong. I mean, I'm starting to have
> trouble even *hating* MySQL anymore. It's like a retard kid that
> you just can't help kinda pitying.
The kindest thing you can say about MySQL is that if you use an unstable
beta release _and_ you carefully configure it to be non-broken to the
extent possible, it's not quite as bad as it was in the MySQL 3.22 days.
But for me it still inspires hate, not pity.
From: Michael G Schwern
Date: 21:22 on 20 Mar 2007
Subject: Re: MySQL Function Naming
Aaron Crane wrote:
> I'm prepared to believe that this is merely a bug in our particular
> point-release of the server, rather than a fundamental design flaw, but
> that doesn't stop it being incredibly hateful: if I'm looking for
> something equal to an exemplar, then, no, something vaguely similar is
> _not_ sufficient.
test]> select foo, concat("[", foo, "]") from foo where foo = "this";
+-------+-----------------------+
| foo | concat("[", foo, "]") |
+-------+-----------------------+
| this | [this ] |
+-------+-----------------------+
1 row in set (0.04 sec)
No such luck.
These are the people who decided it would be just spiffy if "LIKE" was case
insensitive.
test]> select foo, concat("[", foo, "]") from foo where foo like "THIS%";
+-------+-----------------------+
| foo | concat("[", foo, "]") |
+-------+-----------------------+
| this | [this ] |
+-------+-----------------------+
1 row in set (0.00 sec)
Also that the case-sensitivity of the table and database names should be
determined by the underlying filesystem because the idea of lower-casing the
name before using it as a filename was just beyond them.
Repeat this chant against MySQL hate: Pooooost Greeeeessss
From: Mischa Spiegelmock Date: 21:41 on 20 Mar 2007 Subject: Re: MySQL Function Naming --Apple-Mail-1-698673341 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed > Also that the case-sensitivity of the table and database names should be > determined by the underlying filesystem because the idea of lower- casing the > name before using it as a filename was just beyond them. God help you if you write a database application with a table called (e.g.) "Data" on MySQL on Mac OS X Server and then move it to some OS that is more case-sensitive. You're more screwed than a whore at a Perl convention. --Apple-Mail-1-698673341 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 <HTML><BODY style=3D"word-wrap: break-word; -khtml-nbsp-mode: space; = -khtml-line-break: after-white-space; "><DIV style=3D"margin-top: 0px; = margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT = class=3D"Apple-style-span" color=3D"#0000DD">>=A0</FONT><FONT = class=3D"Apple-style-span" color=3D"#0000DD">Also that the = case-sensitivity of the table and database names should = be</FONT></DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; = margin-bottom: 0px; margin-left: 0px; "><FONT class=3D"Apple-style-span" = color=3D"#0000DD">> determined by the underlying filesystem because = the idea of lower-casing the</FONT></DIV><DIV style=3D"margin-top: 0px; = margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT = class=3D"Apple-style-span" color=3D"#0000DD">> name before using it = as a filename was just beyond them.</FONT></DIV><DIV style=3D"margin-top: = 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; = min-height: 14px; "><FONT class=3D"Apple-style-span" color=3D"#0000DD"><BR= style=3D""></FONT></DIV><BR><DIV>God help you if you write a database = application with a table called (e.g.) "Data" on MySQL on Mac OS X = Server and then move it to some OS that is more case-sensitive. You're = more screwed than a whore at a Perl convention.</DIV><BR><DIV><BR = class=3D"khtml-block-placeholder"></DIV><DIV><BR = class=3D"khtml-block-placeholder"></DIV></BODY></HTML>= --Apple-Mail-1-698673341--
From: A. Pagaltzis Date: 15:37 on 20 Mar 2007 Subject: Re: MySQL Function Naming I stopped reading after the first word in the subject. Too easy to hate. Some hates-software mail just writes itself,
Generated at 10:27 on 16 Apr 2008 by mariachi