Flyway info failing - flyway-database-databricks - DatabricksTable - doExists

[rafaelqntn-cfi]

Hi! First, I would like to thank you all for releasing the Databricks support.

I was testing it today and got the following error message:

ERROR: Unable to check whether table `fwt`.`flyway_schema_history` exists
------------------------------------------------------------------
SQL State  : 42601
Error Code : 500051
Message    : [Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: 42601, Query: show table***, Error message from Server: org.apache.hive.service.cli.HiveSQLException: Error running 
query: [PARSE_SYNTAX_ERROR] org.apache.spark.sql.catalyst.parser.ParseException:
[PARSE_SYNTAX_ERROR] Syntax error at or near '?'. SQLSTATE: 42601 (line 1, pos 26)

== SQL ==
show tables in `fwt` like ?
--------------------------^^^

fwt is the schema I am using for testing purposes.

It looks like the issue is caused by flyway_schema_history (table name) not replacing the parameter placeholder ?. I think the problem can be solved by changing the following line of code:

https://github.com/flyway/flyway-community-db-support/blob/02ce47e7a0be94c7943c8a80e05d3ec9b874cea1/flyway-database-databricks/src/main/java/org/flywaydb/community/database/databricks/DatabricksTable.java#L36

This is called here (parent project):

    public boolean exists() {
        try {
            return doExists();
        } catch (SQLException e) {
            throw new FlywaySqlException("Unable to check whether table " + this + " exists", e);
        }
    }

https://github.com/flyway/flyway/blob/0e1f98e2069a8e363d51c885d66c5faef2cabe8e/flyway-core/src/main/java/org/flywaydb/core/internal/database/base/Table.java#L45

Thanks!

flyway --version output:

Flyway Community Edition 10.14.0 by Redgate

See release notes here: https://rd.gt/416ObMi
Plugin Name       | Version        
----------------- | ---------------
Redgate Compare   | 1.20.0.22298
OceanBase         | 10.12.0
TiDB              | 10.12.0
YugabyteDB        | 10.12.0
ClickHouse        | 10.12.0
Apache Ignite     | 10.12.0
SQLFluff          | not installed

Database: SparkSQL 3.1 JDBC jar: databricks-jdbc-2.6.38.jar

[rafaelqntn-cfi]

It works fine if we embed the name variable in the query string instead of passing it as a parameter to the jdbcTemplate.queryForList method.

    protected boolean doExists() throws SQLException {
        if (!schema.exists()) {
            return false;
        }
        List<Map<String, String>> tables = jdbcTemplate.queryForList(
            "show tables in " + database.quote(schema.getName()) + " like '"+ name +"';"
        );
        return tables.stream().anyMatch(table -> table.get("tableName").equals(name));
    }

[piotrgwiazda]

I was able to reproduce it with flyway version 10.15.2 as well used with Databricks, runtime version 15.1 (Apache Spark 3.5.0, Scala 2.12), no photon compute

[piers-williams]

It works fine if we embed the name variable in the query string instead of passing it as a parameter to the jdbcTemplate.queryForList method.

    protected boolean doExists() throws SQLException {
        if (!schema.exists()) {
            return false;
        }
        List<Map<String, String>> tables = jdbcTemplate.queryForList(
            "show tables in " + database.quote(schema.getName()) + " like '"+ name +"';"
        );
        return tables.stream().anyMatch(table -> table.get("tableName").equals(name));
    }

Would you be willing to raise a PR with the fix?

[rafaelqntn-cfi]

Yes, I can do it. I will check the developers' guide to see if I need to include something and submit the PR.