Migration fails for Derby databases created with collation=TERRITORY_BASED

[andersb]

Which version and edition of Flyway are you using?

9.22.3

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Yes, Flyway 10 requires newer jdk than my software supports but the source code inside Flyway is the same so the problem would still be there.

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java API

Which database are you using? (Type & version)

Derby SQL 10.14 (same problem found on 10.15 and 10.16)

Which operating system are you using?

Windows 11

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

When creating a embedded database with the following connection string
jdbc:derby:somedb;create=true;territory=sv_SE;collation=TERRITORY_BASED

This is the code snippet

    log.info("Migrate main database. jdbcUrl=${jdbcUrl}")
    def configuration = new FluentConfiguration()
      .dataSource("${jdbcUrl};create=true;territory=sv_SE;collation=TERRITORY_BASED", null, null)
      .locations("migrations/maindb/")
    Flyway flyway = new Flyway(configuration)
    flyway.migrate()

you will get

Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: Unable to retrieve all tables in schema "APP"
---------------------------------------------
SQL State  : 42818
Error Code : 30000
Message    : Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')

	at org.flywaydb.core.internal.database.base.Schema.allTables(Schema.java:141)
	at org.flywaydb.core.internal.database.derby.DerbySchema.doEmpty(DerbySchema.java:50)
	at org.flywaydb.core.internal.database.base.Schema.empty(Schema.java:66)
	at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:162)
	at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:213)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:140)
	at org.flywaydb.core.Flyway$migrate.call(Unknown Source)
	at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:130)
	at se.easycashier.client.database.DatabaseService.migrateMainDatabase(DatabaseService.groovy:32)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:389)
	at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:333)
	at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:157)
	... 77 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement42.<init>(Unknown Source)
	at org.apache.derby.jdbc.Driver42.newEmbedPreparedStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
	at org.flywaydb.core.internal.jdbc.JdbcTemplate.prepareStatement(JdbcTemplate.java:300)
	at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForStringList(JdbcTemplate.java:82)
	at org.flywaydb.core.internal.database.derby.DerbySchema.listObjectNames(DerbySchema.java:156)
	at org.flywaydb.core.internal.database.derby.DerbySchema.doAllTables(DerbySchema.java:133)
	at org.flywaydb.core.internal.database.derby.DerbySchema.doAllTables(DerbySchema.java:31)
	at org.flywaydb.core.internal.database.base.Schema.allTables(Schema.java:139)
	... 94 common frames omitted

The problem arises because at DerbySchema.java:156 the sql being executed looks like this

SELECT TABLEname FROM sys.sysTABLEs WHERE schemaid in (SELECT schemaid FROM sys.sysschemas where schemaname = ?) AND TABLETYPE='T'

This very old email mentions this problem together with a workaround, https://lists.apache.org/thread/j5w8n8oqtmzpdrobsjh9ydqhcqnhyy4r

So the root cause might very well be a problem in Derby but the workaround could be implemented in Flyway.

If the above sql would use CAST then it work

SELECT TABLEname FROM sys.sysTABLEs WHERE schemaid in (SELECT schemaid FROM sys.sysschemas where CAST(schemaname as VARCHAR(128)) = ?) AND CAST(TABLETYPE as VARCHAR(128))='T'

If this would be something to implement then there might be other queries found in DerbySchema.java that need to be updated as well.