The quiz presented poses with an uncommon, though valid SQL syntax: one is allowed to use quoted name aliases. Thus, it is valid to write:
SELECT Name AS 'n', Continent AS 'c' FROM countries
But what does the above mean? Let’s see the results of our three questions:
Question #1
SELECT id, Name AS 'n', Continent AS 'c' FROM countries ORDER BY c ; +----+--------------------------+---------------+ | id | n | c | +----+--------------------------+---------------+ | 15 | North Korea | Asia | | 12 | Nepal | Asia | | 11 | Norway | Europe | | 10 | Netherlands | Europe | | 8 | Nicaragua | North America | | 1 | Netherlands Antilles | North America | | 3 | Namibia | Africa | | 5 | Niger | Africa | | 7 | Nigeria | Africa | | 9 | Niue | Oceania | | 4 | New Caledonia | Oceania | | 2 | Northern Mariana Islands | Oceania | | 13 | Nauru | Oceania | | 14 | New Zealand | Oceania | | 6 | Norfolk Island | Oceania | +----+--------------------------+---------------
Result is sorted by continent, as may be expected. We have aliased continent as ‘c’, and ORDER BY c went well. The quotes were meaningless here.
Question #2
SELECT MIN(Name) AS 'n', Continent AS 'c' FROM countries GROUP BY c ; +----------------------+---------------+ | n | c | +----------------------+---------------+ | Nepal | Asia | | Netherlands | Europe | | Netherlands Antilles | North America | | Namibia | Africa | | Nauru | Oceania | +----------------------+---------------+
As with first query, the quoted ‘c’ acts just like non-quoted c.
Question #3
SELECT MIN(Name) AS 'n', Continent AS 'c' FROM countries GROUP BY 'c' ; +---------+---------------+ | n | c | +---------+---------------+ | Namibia | North America | +---------+---------------+
Obviously something went wrong here. The query is valid, yet returns a single row. Why is that?
What we’ve just done is to group by the constant ‘c’, not on the alias c. Results are complete wrong.
This query is a simplified version of a true customer’s query. The GROUP BY clause included some 5 fields, the constant literal being one of them. The results of that query were used in very important business decision making. Well, decisions were misinformed…
Quoted aliases?
I’m not sure why anyone would want these. Why is this even a valid syntax?
Multi-word aliases? Seems like wrong coding convention. But, if you must, do not use single quotes: use `backticks`.
I’m not going to bother with the holy war of MySQL vs $other_free_databases.
However, I will note that the developer attitudes *are* changing within MySQL / Oracle – look at 5.5/5.6 with InnoDB being the new default storage engine. There is certainly more of that to come. We’re serious about having a transactionally safe and correct database at install/start time.
As for having syntax that is non-standard? Aww come on, tell me *one single database* that conforms to the standard and doesn’t extend it. That argument is rubbish. 😉
Now, allowing it by default? Sure, I can agree there (preaching to the choir).. 🙂 But yes, the chances of people relying on that behaviour now in their apps *does* make it hard change that by default now.
I believe that the one of the greatest attractions to MySQL is it’s *flexibility*. The choice is left to the operator on whether they want to be “strict” with their application or not. Maybe the default is seen as the wrong way around – but the choice is there.
@Shai, you’re right that a lot of this comes from MyISAM, and from MyISAM/MySQL’s original developer. Thankfully, that attitude is no longer within the MySQL group at Oracle.
@Mark,
I’d like to clarify that I’m not coming from a holy-free-database-war perspective; I’m arguing strictly against MySql, and I’m considering the free-as-in-beer “express” editions of proprietary databases just as valid for most use cases as the free-as-in-speech databases.
I’m glad to hear that the attitude is changing; I also see that you appreciate the difficulties in effecting the implied changes in the product. With no snide intended, I wish you good luck with that. If you pull it through, the world will be somewhat better.
As for syntax that is non-standard: If you are referring to the quoting, that is not a useful extension, but one that just invites bugs. In particular, the difference in treating single quotes in the select list vs. elsewhere in the query is just asking for trouble. If you’re referring to the group by, that’s not extended syntax, it’s extended semantics, and again, it’s extended in a dangerous way. The argument isn’t just that MySql extends the standard; it’s that the extensions are error-prone and foolhardy.
I think the “choice” argument is rubbish. Would you recommend a programming language that leaves to users the choice whether 1+1=2 or 1+1=3, in the name of “flexibility”? In my book, the two most basic requirements of a database system is that it store data correctly and retrieve it correctly. With MySql, these are hard to get (and in some cases impossible). Perhaps some future version will be there; until then, I’m keeping my position.
@Shai,
I believe you’re missing out a lot of things, unknown to you.
MySQL supports such non-standard syntax as INSERT IGNORE, INSERT INTO… ON DUPLICATE KEY UPDATE, REPLACE INTO, … and others.
I have tutored MySQL to many Oracle/SQL server DBAs. Yes, many times they were disappointed with its relative simplicity. At other times, they were impressed with the ease of some features; the strength behind other (including aforementioned syntax) etc.
You have chosen to relate to the alias quotes and the GROUP BY relaxed condition. I agree on these. I agree on others. As you said, I have encountered many troubling issues. I have also encountered many spectacular issues with MySQL (it’s just so much easier to blog on things that went wrong!).
As Mark said, things are increasingly becoming stricter within the MySQL world. MyISAM, as I personally see it, is very old news by now. It is commonly agreed that we can all stop talking about MySQL+MyISAM as being non-strict, because it’s so much not interesting by now.
“a preference for short-term developer convenience, at the expense of operational correctness” – is just not so. On the contrary, recent years (as well as not so recent by now) development has made great improvements on operational correctness. I can list these on another occasion.
We have very interesting developments with InnoDB; some within Oracle/MySQL, some outside (Percona, Google, Facebook).
I want to carefully tread on the PostgreSQL issue. I say carefully, since I am not interested in unholy wars; nor am I a competent PostgreSQL DBA;and, to clarify, I don’t claim either is superior to the other.
You say: “functionally-superior alternatives are just as free, if not free-er (PostgreSQL uses an MIT license). Why bother with MySql?”
And I disagree strongly here.
We can argue all day long about PostgreSQL being functionally superior to MySQL. It IS superior in many respects; but then again, so is MySQL, in other respects. There’s a lot of functionality in PostgreSQL I wished we had in MySQL years ago; but other MySQL features are long desired on PostgreSQL (built-in replication has only just been introduced in PG, and is still lacking many features long supported by MySQL, e.g. chained replication, so commonly used).
There must be *some* reason why Facebook, Google, Wikipedia, Flickr, Yahoo, YouTube, Tweeter, etc. etc. all chose using MySQL. I can’t speak their minds.
Reasons could vary from ease of operation, to supporting 3rd party software, to community size, to documentation; I don’t know the reasons at hand.
I can safely state: MySQL is more popular than PostgreSQL; at least here in Israel. Why? I don’t know. How many PostgreSQL books have been released in the past 6 years? Two, in the past year. How many MySQL books? A lot; I have all of them on my shelf, and I’m in need of new shelves. This is just an example, of course, and allow me to repeat: I’m not a competent PostgreSQL DBA, and am missing a lot of info on Postgres. Indeed, if I had the time, I would be happy to become a competent PG DBA.
I’ve written the above to suggest that MySQL is not so obviously inferior to pg as you suggest.
Mark is right in his observation that nobody is standard. Everyone make extensions to ANSI SQL.
Why bother with MySQL? I know a lot of companies who are happily running their DBs on MySQL; and are flourishing. I don’t think the word ‘bother’ is becoming. Nor do I think it is possible to claim MySQL is inferior to other free RDBMS.
Regards
Hi all,
this remark by @Shai:
“As for syntax that is non-standard: If you are referring to the quoting, that is not a useful extension, but one that just invites bugs.”
I completely and wholeheartedly agree.
There is one sql_mode that corresponds with quoting: ANSI_QUOTES. I enable it in for all MySQL installations I control and don’t run third party apps on. Unfortunately this only allows you to use double quoted identifiers (and thus banishes double quoted string literals). However, what this mode does not do, is forbid the use of single quotes for column aliases.
Thinking a bit more about this, it seems that it actually isn’t in the end a problem with quoting though – it seems that the real problem is that constant expression simply make no sense ever at all in either ORDER BY or GROUP BY lists. So in theory it would be a good thing if the standard would forbid constants in those contexts.
In practice however, you should probably simply not use single quotes except for string literals.
@Shlomi,
Thanks for your detailed response. I’d like to say a little more about where I’m coming from: I’m not a DBA at all, but a software developer with personal interest in databases. Much of the code I write has a target of being cross-DBMS; as such, deficiencies in implementing the SQL standard matter to me much more than extensions. More so, when the extensions are not alternate implementations of the same features (I’ll be writing the long-full-version of INSERT INTO… ON DUPLICATE KEY UPDATE in order to be portable anyway, so it doesn’t really help me; but missing deferred FKs means that I will accumulate integrity violations in my development DB, and not having the WITH syntax means recursive queries are out).
Other than that, all I have is variations on the previous themes — like pointing out the MySql clustering solution, ndb, as testament to the living heritage of MyISAM even after its demise. As I said, that attitude may have been purged from the core team at Oracle, but removing it from the whole ecosystem is a herculean task.
I used to think there were no use cases where MySql was the best alternative, and I don’t hold that position anymore; my ‘why bother’ comment was, indeed, overstated and perhaps even trollish. I do think that these use cases are few and far between. I think that while MySql has some advantages in the areas of deployment and operations, it is fair to say that it is significantly inferior to other systems — all other major players, in fact — in data management.
Again, thanks for your attention and responses.