Wednesday, September 05, 2007

SQL mode quiz

MySQL follows ANSI SQL standard quite close. But there are a few points where it differs. To make MySQL follow the ANSI standard more closely you can use the SQL_MODE system system variable.

If you set the SQL mode to ANSI, MySQL will interpret you SQL commands differently. Some of your SELECT statements might work differently or not at all. But which ones? Let's see if you know....

Suppose you change your sql mode setting from none to ANSI. Which of the following statements will be affected:

a) SELECT concat(table_name,' ',column_name) FROM information_schema.COLUMNS
b) SELECT TABLE_NAME||' '||COLUMN_NAME FROM information_schema.COLUMNS
c) SELECT count (*) FROM information_schema.COLUMNS
d) SELECT "TABLE_NAME" FROM information_schema.COLUMNS
e) SELECT `TABLE_NAME` FROM information_schema.COLUMNS

2 comments:

Dossy said...

My guesses would be (B) and (E).

Roland Bouman said...

I say, B and D

But it gets more interestings still when adding ONLY_FULL_GROUP_BY