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