MySQL - Choose Something Else

The regular newsletter I get fromTara's outfit (as I like to call it :slight_smile: ) included a link entitled "Don't use MySQL" - here's the link: grimoire.ca/mysql/choose-something-else

I found it a very interesting read ... you might like this snippet:

CREATE TABLE account (
    accountid INTEGER
        AUTO_INCREMENT
        PRIMARY KEY,
    discountid INTEGER
);
Query OK, 0 rows affected (0.54 sec)

INSERT INTO account
    (discountid)
VALUES
    (0),
    (1),
    (2);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

SELECT *
FROM account
WHERE discountid = 'banana';
+-----------+------------+
| accountid | discountid |
+-----------+------------+
|         1 |          0 |
+-----------+------------+
1 row in set, 1 warning (0.05 sec)

Ok, unexpected, but there's at least a warning (do your apps check for those?) - let's see what it says:

SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'banana' |
+---------+------+--------------------------------------------+
1 row in set (0.03 sec)

I can count on one hand the number of DOUBLE columns in this example and still have five fingers left over.

In case you were wondering :slight_smile: SQL Server aborts the SELECT with:

Conversion failed when converting the varchar value 'banana' to data type int.

the article goes on to say:

Actually, you don't even need a table for this: SELECT 0 = 'banana' returns 1

We constantly bump up against competitors flogging MySQL based solutions, presumably using "free" versions of MySQL to shave some cost off ... my cries of "Its crap, don't trust your precious data to it" go unheeded of course ... nice to have a "You might like to read this thesis" line-to-shoot instead

Thanks Tara :slight_smile:

2 Likes

That's great! I love little "quirks" like that.

Heh! And to think that here I have been complaining about Microsoft's unhelpful message about something pithy as "String or binary data would be truncated".

Hehehe ... at least it complains, rather than silently truncating it ... or translating it to a different Code Page :grimacing:

Of course SQL will do implicit varchar conversions too ...

SELECT CASE WHEN 1 = 1 THEN 'T' ELSE 'F' END, CASE WHEN 1 = '1' THEN 'T' ELSE 'F' END

However, I was expecting to be able to say that '1E1' was valid for that string too ... but I just tried it and it aint :slight_smile:

But IsNumeric('1E1') is true of course.

The thing that I found most scary, in the article, was that there was no consistent enforced datatype on INSERT/UPDATE (you can set SQLMODE to a value that provides the safety you want, but someone else could do the same operation with SQLMODE set differently, and MySQL would happily store goofy-data as a consequence. And no way to enforce anything on the SELECT ... Lots and Lots of hours wasted sorting out bitten-in-the-bum issues like that ... NO THANKS!

I don't care how well all the DEVs know the DOCs and have peer-review of their code, someone someday is going to do something wrongly. I want to have the fewest inbuilt bugs as possible, and to my mind the best possible defensive-programming strategy is my route to that. I'd like WAY more warnings in SQL Server so that I could get to see that I have unintentionally used an implicit cast ... or whatever ... and MySQL does not fit my criteria for a framework that enforces the tightest possible nagging-ness.

Not to jump on the bag-on-MySQL-bandwagon but two of my biggest complaints about it are:

  1. You can't have sub-selects in a VIEW definition
  2. You can't reference a temp table in the same select statement. Since you don't have CTE's with MySQL, you have to loop to do anything recursive and not being able to insert into a temp table while also selecting from it makes this an inconvenient pain. Perhaps a newer version corrects this.

As the article pointed out, the way it uses views is also inefficient and has caused us LOTS of pain in getting them to perform correctly.

You get what you pay for and as the article pointed out, some of the pay versions have better capabilities.