My new favorite example of why it isn’t a good idea to use reserved words as column names.

Some show commands support a where clause. The column name that can be used in the expression for the where clause depends on the result of the show command. For example in show tables the column is Tables_in_foo where foo is the database name.

MariaDB [test]> show tables where Tables_in_test = ‘t’;
+—————-+
| Tables_in_test |
+—————-+
| t |
+—————-+
1 row in set (0.00 sec)

This is a problem with the show databases command because databases aren’t really *in* anything. Database is a reserved word so this happens.

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
3 rows in set (0.00 sec)

MariaDB [(none)]> show databases where database=’test’;
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 ‘=’test” at line 1
MariaDB [(none)]> show databases where `database`=’test’;
+———-+
| Database |
+———-+
| test |
+———-+
1 row in set (0.00 sec)

I can’t decide if this is a bug or the greatest example of why choosing descriptive non-reserved word column names is very important.

2 Comments

  1. sbester says:

    I prefer the SHOW DATABASES LIKE ‘test’; syntax..
    But I’m strictly following a simple rule these days, quote *every* identifier in backticks when writing code, even down to user variables, so I’ll write @`a` instead of @a :) The reason is I always end up needing to test weird characters, or randomly generated names and then the code breaks :)

  2. I’d say it’s a bug in how the show resultset was defined, the bug being that the column was called “Database”.

    It should have been called “database_name” or so. That’s what it’s selecting: a name, not the database itself.

Leave a Reply