Cannot use Cloud SQL array item syntax

[O1O1O1O]

When attempting to access an item of an array in a query using syntax that is accepted in interactive SQL console we get a parsing error from nl.topicus.jdbc.shaded.net.sf.jsqlparser

ERROR - SQL execution failed (Reason: Error while parsing sql statement SELECT DISTINCT DOMAIN FROM foo WHERE client_id=4 AND profile_id=42 AND visits[0] > 0: null):

   SELECT DISTINCT DOMAIN FROM foo WHERE client_id=4 AND profile_id=42 AND visits[0] > 0

nl.topicus.jdbc.exception.CloudSpannerSQLException: Error while parsing sql statement SELECT DISTINCT DOMAIN FROM foo WHERE client_id=4 AND profile_id=42 AND visits[0] > 0: null
  at nl.topicus.jdbc.statement.CloudSpannerPreparedStatement.executeQuery(CloudSpannerPreparedStatement.java:141)
  at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
  at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
  at scalikejdbc.DBConnectionAttributesWiredPreparedStatement.executeQuery(DBConnectionAttributesWiredPreparedStatement.scala:63)
  at scalikejdbc.StatementExecutor$$anonfun$executeQuery$1.apply(StatementExecutor.scala:337)
  at scalikejdbc.StatementExecutor$$anonfun$executeQuery$1.apply(StatementExecutor.scala:337)
  at scalikejdbc.StatementExecutor$NakedExecutor.apply(StatementExecutor.scala:18)
  at scalikejdbc.StatementExecutor$$anon$1.scalikejdbc$StatementExecutor$LoggingSQLAndTiming$$super$apply(StatementExecutor.scala:319)
  at scalikejdbc.StatementExecutor$LoggingSQLAndTiming$class.apply(StatementExecutor.scala:263)
  at scalikejdbc.StatementExecutor$$anon$1.scalikejdbc$StatementExecutor$LoggingSQLIfFailed$$super$apply(StatementExecutor.scala:319)
  at scalikejdbc.StatementExecutor$LoggingSQLIfFailed$class.apply(StatementExecutor.scala:296)
  at scalikejdbc.StatementExecutor$$anon$1.apply(StatementExecutor.scala:319)
  at scalikejdbc.StatementExecutor.executeQuery(StatementExecutor.scala:337)
  at scalikejdbc.DBSession$$anonfun$collection$1.apply(DBSession.scala:318)
  at scalikejdbc.DBSession$$anonfun$collection$1.apply(DBSession.scala:317)
  at scalikejdbc.LoanPattern$class.using(LoanPattern.scala:18)
  at scalikejdbc.ActiveSession.using(DBSession.scala:705)
  at scalikejdbc.DBSession$class.collection(DBSession.scala:316)
  at scalikejdbc.ActiveSession.collection(DBSession.scala:705)
  at scalikejdbc.DBSession$class.list(DBSession.scala:302)
  at scalikejdbc.ActiveSession.list(DBSession.scala:705)
  at scalikejdbc.DBSessionWrapper$$anonfun$list$1.apply(DBSessionWrapper.scala:53)
  at scalikejdbc.DBSessionWrapper$$anonfun$list$1.apply(DBSessionWrapper.scala:53)
  at scalikejdbc.DBSessionWrapper$$anonfun$withAttributesSwitchedDBSession$1.apply(DBSessionWrapper.scala:35)
  at scalikejdbc.DBSessionWrapper$$anonfun$withAttributesSwitchedDBSession$1.apply(DBSessionWrapper.scala:34)
  at scalikejdbc.DBSessionAttributesSwitcher.withSwitchedDBSession(DBSessionAttributesSwitcher.scala:31)
  at scalikejdbc.DBSessionWrapper.withAttributesSwitchedDBSession(DBSessionWrapper.scala:34)
  at scalikejdbc.DBSessionWrapper.list(DBSessionWrapper.scala:53)
  at scalikejdbc.SQLToList$class.result(SQL.scala:855)
  at scalikejdbc.SQLToListImpl.result(SQL.scala:868)
  at scalikejdbc.SQLToListImpl.result(SQL.scala:868)
  at scalikejdbc.SQLToResult$$anonfun$11.apply(SQL.scala:731)
  at scalikejdbc.SQLToResult$$anonfun$11.apply(SQL.scala:731)
  at scalikejdbc.SQLToResult$class.apply(SQL.scala:737)
  at scalikejdbc.SQLToListImpl.apply(SQL.scala:868)
  at $anonfun$1.apply(<console>:25)
  at $anonfun$1.apply(<console>:24)
  at scalikejdbc.DBConnection$$anonfun$readOnly$1.apply(DBConnection.scala:201)
  at scalikejdbc.DBConnection$$anonfun$readOnly$1.apply(DBConnection.scala:201)
  at scalikejdbc.LoanPattern$class.using(LoanPattern.scala:18)
  at scalikejdbc.NamedDB.using(NamedDB.scala:20)
  at scalikejdbc.DBConnection$class.readOnly(DBConnection.scala:201)
  at scalikejdbc.NamedDB.readOnly(NamedDB.scala:20)
  ... 43 elided
Caused by: nl.topicus.jdbc.shaded.net.sf.jsqlparser.JSQLParserException: nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "[" "["
    at line 1, column 79.

Was expecting one of:

    "&&"
    ";"
    "AND"
    "CONNECT"
    "EXCEPT"
    "FOR"
    "GROUP"
    "HAVING"
    "INTERSECT"
    "MINUS"
    "ORDER"
    "START"
    "UNION"
    <EOF>

  at nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:55)
  at nl.topicus.jdbc.statement.CloudSpannerPreparedStatement.executeQuery(CloudSpannerPreparedStatement.java:139)
  ... 85 more
Caused by: nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "[" "["
    at line 1, column 79.

Was expecting one of:

    "&&"
    ";"
    "AND"
    "CONNECT"
    "EXCEPT"
    "FOR"
    "GROUP"
    "HAVING"
    "INTERSECT"
    "MINUS"
    "ORDER"
    "START"
    "UNION"
    <EOF>

  at nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:18164)
  at nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:18014)
  at nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:91)
  at nl.topicus.jdbc.shaded.net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:53)
  ... 86 more

[O1O1O1O]

See JSQLParser/JSqlParser#698

This is most frustrating... will your work on #127 have any impact on this or does all source still have to go through the problematic JSQLParser ?

[olavloite]

@O1O1O1O I am planning to replace the JSQLParser with my own simple parser for most SQL statements once #127 is done.

[olavloite]

@O1O1O1O I have created a quick fix for this problem and released a 1.1.4 version for queries that do not contain any JDBC parameters. These do not need to be parsed by the SQL parser and can be sent directly to Spanner, so these error will be ignored for those queries. Queries that do contain parameters, however, will still rely on the SQL Parser for processing and will fail in version 1.1.x.

The issue will therefore remain open for a better fix in 1.2 where these queries will not rely on the SQL Parser for parameter processing.

[O1O1O1O]

Thanks for the quick workaround Knut. Does the entire query have to be unparameterized, or do you mean it is only a problem if the array index is itself unparameterized?

Unfortunately since I'm on a tight deadline I had to switch to the Simba driver which I just noticed now supports full DML (and the Spanner console is now upgraded to support it too). It is not without issues - you can't write Long to an INT64 column.

[O1O1O1O]

I had to revert to using your driver as the Simba driver was not compatible with connection pools in Scala Play (all kinds of multi-threading corruption issues occurred). So I was able to test your fix for this and everything is working fine. It was not a big issue that I could not use prepared statements with parameters since the only parameter I really needed to insert was an integer. Thanks for the quick fix. Looking forward to trying 1.2