SQL mode quiz answers

My quiz about SQL mode was a bit more difficult than I expected. The question was which ones of the following statements are affected by a change from the default SQL mode to ANSI mode.

I used the INFORMATION_SCHEMA tables for examples throughout. This is to make it easy to try for yourself, without needing to create tables etc. If you use the Query Browser to try this out you should beware that setting the sql_mode might not work, since the Query Browser by default creates a new database connection for every statement. The easiest way to check this si to use the 'set global sql_mode' command, but you should not do this on a production database, since strange things might happen....

If you want to read the documentation, you find it here.

Now for the solution...

a) SELECT concat(table_name,' ',column_name) FROM information_schema.COLUMNS

This was quite an easy one. CONCAT is a MySQL extension to ANSI SQL, but it is not disabled by the ANSI SQL mode. The command will work just as well whatever the SQL mode. But CONCAT is not portable to other databases. Oracle has a CONCAT function, but it takes only two arguments. SQL Server has no CONCAT, and I don't know any other databases that has one.

b) SELECT TABLE_NAME||' '||COLUMN_NAME FROM information_schema.COLUMNS

That was also an easy one. Double pipes is the ANSI standard way of string concatenation. In MySQL it means OR.

So, in ANSI mode you will get:

+-------------------------------------+

| TABLE_NAME||' '||COLUMN_NAME |

+-------------------------------------+

| CHARACTER_SETS CHARACTER_SET_NAME

| CHARACTER_SETS DEFAULT_COLLATE_NAME

| CHARACTER_SETS DESCRIPTION

etc

In default mode:

+------------------------------+
| TABLE_NAME||' '||COLUMN_NAME |
+------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |

c) SELECT count (*) FROM information_schema.COLUMNS
This was a tricky one. I guess it was not easy to see, but there is actually a space between count and the left parenthesis. This makes it invalid in MySQL standard mode:

mysql> SELECT count (*) FROM information_schema.COLUMNS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '*) FR
OM information_schema.COLUMNS' at line 1

Change to ANSI mode, and it works:

mysql> set sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count (*) FROM information_schema.COLUMNS;
+-----------+
| count (*) |
+-----------+
| 503 |
+-----------+
1 row in set (0.11 sec)

So, this is a statement that works differently....

d) SELECT "TABLE_NAME" FROM information_schema.COLUMNS
This was also quite easy. Double quote is according to ANSI standard used for identifiers like table and column names. You only need them if you have characters that would otherwise be illegal or if you want to have mixed case in your identifiers.

By default MySQL uses double quotes for strings. This means that the select will give the following result:

+------------+
| TABLE_NAME |
+------------+
| TABLE_NAME |
| TABLE_NAME |
| TABLE_NAME |
| TABLE_NAME |

If you change to ANSI, you will get the following result:

+----------------+
| TABLE_NAME |
+----------------+
| CHARACTER_SETS |
| CHARACTER_SETS |
| CHARACTER_SETS |
| CHARACTER_SETS |
| COLLATIONS |

e) SELECT `TABLE_NAME` FROM information_schema.COLUMNS
This is the MySQL alternative to ANSI double quote. It does not follow ANSI standard, but the MySQL ANSI mode will not turn it off. I do not know another database that allows this syntax, so if you try to convert your system you will have to remove them.

Conclusion
Statements b, c and d are the ones that will behave differently if you change to ANSI mode. Statements a and e will work the same.

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