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 know you’re heavily invested in MySql, but really, what you show here is a bug, not a feature. Most database systems I’ve worked with differentiate quoted aliases from string literals: aliases use double-quotes (“), string literals use single quotes. IIRC, this is true for Oracle, SQL Server, and PostgreSQL. The third example is particularly damning: Standard SQL says this is an error — in a summary select (one having ‘group by’), you’re only allowed to select either aggregates (min, max, sum etc) or columns named in the ‘group by’ clause. This is not ideal — the ideal would be… Read more »
@Shai, Thank you. Indeed, the third query only works with a relaxed sql_mode. With ‘ONLY_FULL_GROUP_BY’ this leads to an error, as you would expect. I should have expected this; relaxed sql_mode has led me before into many pitfalls. Regardless of being ‘invested’ in MySQL: I have a lot of complaints on MySQL, like here and here, both relevant to the topic of this article; I’m not sure, though, those consist of “deal breakers”. As contrast, I can provide with a list of non-standard SQL features available in MySQL which actually improve life, some are long-time requested on PostgreSQL. Anyway, this… Read more »
It’s worth noting that on top of having a sql mode to protect from the non-full GROUP BY, the identifier usage is also documented (and hence not a bug):
“In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:
mysql> SELECT 1 AS `one`, 2 AS ‘two’;
+—–+—–+
| one | two |
+—–+—–+
| 1 | 2 |
+—–+—–+
Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal.”
http://dev.mysql.com/doc/refman/5.1/en/identifiers.html
@Mark
thanks. I think Shai’s point is that it does not matter if it’s documented (hence officially not a bug) or not; he reject the entire notion of this allowed syntax, as being non-conforming to standards.
I can see his point on this.
@Shlomi Thank you. The reason I recommend against MySql is not a specific failing on this point or that, but an attitude that I see behind a set of them; a preference for short-term developer convenience, at the expense of operational correctness. One instance of this is the faulty default group-by behavior; other cases where MySql prefers to “smooth out” errors and make the program seem to work are silent data truncation and date validation (or is February 30th not a valid date by default now?). I also attribute MyISAM (no transactions or FKs) to this attitude. These are just… Read more »
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… Read more »
@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… Read more »
@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… Read more »
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… Read more »
@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… Read more »
In MySQL aliases can be quoted with single quotes, double quotes and backticks