In order to support and automate deployments we have created a deployment manager that uses the Cloudera JDBC driver to execute statements from within script files. Given that the execute method does not support multi-command statements the file is split into discrete statements by the database manager and each command/statement executed individually.
During this dev work it was noted that if one ends up executing a command such as:
-- -- This is a comment --;
You get an exception as per:
Caused by: java.sql.SQLException: [JDBC Driver]String index out of range: 3 at java.lang.String.substring(String.java:1963) at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.isUseStatement(Unknown Source) at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeHelper(Unknown Source) at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.execute(Unknown Source) at com.cloudera.impala.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.cloudera.impala.jdbc.common.SStatement.execute(Unknown Source) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at com.mizuho.erp.database.manager.DatabaseUtils.executeSql(DatabaseUtils.java:86) at com.mizuho.erp.database.manager.DatabaseSecurityManager.executeCommandList(DatabaseSecurityManager.java:262) at com.mizuho.erp.database.manager.DatabaseSecurityManager.executeSecurityScripts(DatabaseSecurityManager.java:223) at com.mizuho.erp.database.manager.DatabaseSecurityManager.applyPermissions(DatabaseSecurityManager.java:92) at com.mizuho.erp.database.manager.DatabaseCreationManager.create(DatabaseCreationManager.java:131) at com.mizuho.erp.database.manager.Application.run(Application.java:68) at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:797) ... 13 more Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: 3 ... 27 more
I would have thought that comments would execute (or not) as a nop but seemingly this is not the case so is this expected behaviour? I'm thinking it's a bug but open to correction...
Impala JDBC driver 22.214.171.1244
It is not a bug.
When you write
-- -- This is the comment -- select 1;
But if you do:
-- -- This is a comment;
It tries to execute the command (because of the semicolon) so it fails.
So the solution is: remove the semicolon
With regards removing the semi-colon, yes, that works. However, some more background...
In order to permission the database as per requirements, we needed roles that provide read-only permissions on all tables or views etc. Given this functionality does not exist natively we have written this ourselves as part of the database installer. Essentially we provide the permissions we would like against a placeholder. The installer then replaces the placeholder with each table name or view name in the database (derived from SHOW TABLES; and SHOW CREATE TABLE x;). In the script however, we have commented the different aspects of the permissions but this then means that during execution, the logs are filled with every view permission getting the preceding comment which is less than ideal. Now given that you cannot execute a script over the JDBC driver, only discrete statements, the obvious workaround to that was to add a semi-colon to the comment (given statements are separated by semi-colons) so that this is executed as a separate statement followed by the iterated GRANTs.
So you are absolutely correct that removing the semi-colon from the comment would be a workaround but that would cause the comment to be executed with each iteration unless I did further work in the installer to strrip out the comments on subsequent iterations myself.
All of that said, ignoring my use case for a moment, one surely has to ask why the semi-colon is not treated as part of the comment given it is in a comment? Not doing so dictates that comments cannot contain semi-colons which according to the documentation at https://www.cloudera.com/documentation/enterprise/5-14-x/topics/impala_comments.html appears not to be the case. Even so, assuming that comments can't contain semi-colons, it shouldn't cause an exception like the following should it?
Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: 3