Escape special characters in CTAS statement

Help
1

I am trying to write an application against MemSQL and am having trouble getting CTAS queries to work for columns with special characters. Examples follow:

# this works
CREATE TABLE `test`.`test_column_names__a_semicolon` 
(`key` longtext, `a;semicolon` longtext);

# this works too
SELECT `key`, `a;semicolon`
FROM `test`.`test_column_names__a_semicolon`
WHERE 0 = 1;

# THIS DOESN'T WORK
CREATE TABLE `test`.`test_ctas__a_semicolon`
AS SELECT `key`, `a;semicolon`
FROM `test`.`test_column_names__a_semicolon`
WHERE 0 = 1;

The last query fails with the following stack trace (from MariaDB JDBC driver).

Caused by: java.sql.SQLSyntaxErrorException: (conn=104) Multiple statements detected in a single query. CREATE TABLE `tpch`.`tmp_presto_7901cf4d6122401888adaf908879bf61` AS SELECT `key`, `a;semicolon` FROM `tpch`.`test_column_names__a_semicolon__18kqv` WHERE 0 = 1
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:236)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:327)
	at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:378)
	at io.prestosql.plugin.jdbc.BaseJdbcClient.execute(BaseJdbcClient.java:872)
	... 43 more
Caused by: java.sql.SQLException: Multiple statements detected in a single query. CREATE TABLE `tpch`.`tmp_presto_7901cf4d6122401888adaf908879bf61` AS SELECT `key`, `a;semicolon` FROM `tpch`.`test_column_names__a_semicolon__18kqv` WHERE 0 = 1
Query is: CREATE TABLE `tpch`.`tmp_presto_7901cf4d6122401888adaf908879bf61` AS SELECT `key`, `a;semicolon` FROM `tpch`.`test_column_names__a_semicolon__18kqv` WHERE 0 = 1
java thread: Query-20201015_134153_00016_yhrwi-541
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:126)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:233)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:321)
	... 45 more

I tried running the same test on MySQL 5.5, 5.6, 5.7 and 8 and it works there with the same driver.

Doing the same from memsql CLI gives a different error but fails nonetheless.

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 '`a' at line 1
2

Thanks for the bug report. I’ve confirmed this is a known bug that we’ve fixed in our next upcoming major release, which will be available in the next couple months.

3

Thanks for confirming.

For now I’ve added a check on my end to disallow ; in column names both when issuing a CREATE TABLE or CREATE TABLE AS ... SELECT.