ClickHouse Versus MySQL Handling of Double Quotes

March 4, 2020

Author

Peter Zaitsev

Insight for DBAs

MySQL

Share this Post:

If you’re a MySQL user trying ClickHouse, one thing which is likely to surprise – and annoy you – is the handling of Double Quotes. In MySQL, you can use both double quotes and single quotes to quote strings, and as an example, these two queries are equivalent:

mysql> select * from performance_schema.global_variables where variable_name='max_connections';<br>+-----------------+----------------+<br>| VARIABLE_NAME   | VARIABLE_VALUE |<br>+-----------------+----------------+<br>| max_connections | 151            |<br>+-----------------+----------------+<br>1 row in set (0.01 sec)<br><br>mysql> select * from performance_schema.global_variables where variable_name="max_connections";<br>+-----------------+----------------+<br>| VARIABLE_NAME   | VARIABLE_VALUE |<br>+-----------------+----------------+<br>| max_connections | 151            |<br>+-----------------+----------------+<br>1 row in set (0.00 sec)<br>

1

mysql> select * from performance_schema.global_variables where variable_name='max_connections'; +-----------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-----------------+----------------+ | max_connections | 151 | +-----------------+----------------+ 1 row in set (0.01 sec) mysql> select * from performance_schema.global_variables where variable_name="max_connections"; +-----------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-----------------+----------------+ | max_connections | 151 | +-----------------+----------------+ 1 row in set (0.00 sec)

This means that many of us tend to use single quotes and double quotes interchangeably. This is not the case, however, in ClickHouse.

fe0df8467851 :) select value from system.settings where name='max_threads';<br><br>SELECT value<br>FROM system.settings<br>WHERE name = 'max_threads'<br><br>┌─value─┐<br>│ 1     │<br>└───────┘<br><br>1 rows in set. Elapsed: 0.009 sec.<br><br>fe0df8467851 :) select value from system.settings where name="max_threads";<br><br>SELECT value<br>FROM system.settings<br>WHERE name = max_threads<br><br>Received exception from server (version 19.7.5):<br>Code: 47. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Missing columns: 'max_threads' while processing query: 'SELECT value FROM system.settings  WHERE name = max_threads', required columns: 'value' 'name' 'max_threads', source columns: 'description' 'changed' 'value' 'name'.<br><br>0 rows in set. Elapsed: 0.007 sec.<br>

1

fe0df8467851 :) select value from system.settings where name='max_threads'; SELECT value FROM system.settings WHERE name = 'max_threads' ┌─value─┐ │ 1 │ └───────┘ 1 rows in set. Elapsed: 0.009 sec. fe0df8467851 :) select value from system.settings where name="max_threads"; SELECT value FROM system.settings WHERE name = max_threads Received exception from server (version 19.7.5): Code: 47. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Missing columns: 'max_threads' while processing query: 'SELECT value FROM system.settings WHERE name = max_threads', required columns: 'value' 'name' 'max_threads', source columns: 'description' 'changed' 'value' 'name'. 0 rows in set. Elapsed: 0.007 sec.

ClickHouse only allows using single quotes to quote strings while double quotes are used to quote identifiers (if you want to use special characters in the column name or something).

It is worth noting that it is ClickHouse’s behavior, not MySQL’s, according to the SQL Standard. In fact, if you set MySQL to ANSI SQL compatibility you will get a similar error message:

mysql> set sql_mode='ansi';<br>Query OK, 0 rows affected, 1 warning (0.00 sec)<br><br>mysql> select * from performance_schema.global_variables where variable_name="max_connections";<br>ERROR 1054 (42S22): Unknown column 'max_connections' in 'where clause'<br>

1	mysql> set sql_mode='ansi';<br>Query OK, 0 rows affected, 1 warning (0.00 sec)<br><br>mysql> select * from performance_schema.global_variables where variable_name="max_connections";<br>ERROR 1054 (42S22): Unknown column 'max_connections' in 'where clause'<br>

Unfortunately, there is no similar option in ClickHouse to allow double quotes for string quoting, so you may need to make this change when converting your queries to ClickHouse.