MySQL - `Unknown date type timestamp` for sq query

[Nava2]

SQLDelight Version

2.0.0-alpha02, 2.0.0-alpha03

Operating System

macos

Gradle Version

7.4.2

Kotlin Version

1.6.10

Dialect

app.cash.sqldelight:mysql-dialect:2.0.0-alpha03

AGP Version

No response

Describe the Bug

When compiling on +2.0.0-alpha02, compiling fails with:

* What went wrong:
Execution failed for task ':backend:persistence:generateMainBackendDatabaseInterface'.
> A failure occurred while executing app.cash.sqldelight.gradle.SqlDelightTask$GenerateInterfaces
   > Failed to compile /Users/kevinbrightwell/Development/backend/backend/persistence/src/main/sqldelight/com/faire/ReferralB2BListGroupDetails.sq:211:
       MAX(onboarding_transition.created_at)

Query in question:

getReferralListGroupDetailsB2BAccount:
SELECT
  b.token AS brand_token
  , MAX(ot.created_at) AS onboarding_started_at
FROM brands b
LEFT JOIN brand_state_transitions ot ON ot.brand_id = b.id
  AND ot.to_state = 'ONBOARDING_FORM_STARTED'
WHERE b.token IN :brandTokens
GROUP BY 1
;

changing up MAX to max, MIN or min all fail.

Table:

CREATE TABLE `brand_state_transitions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `brand_id` bigint(20) NOT NULL,
  `from_state` varchar(100) DEFAULT NULL,
  `to_state` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_brand_id` (`brand_id`,`to_state`),
  KEY `idx_updated_at_to_state` (`updated_at`,`to_state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

This is successful on -alpha01

Stacktrace

Caused by: java.lang.IllegalStateException: Failed to compile /Users/kevinbrightwell/Development/backend/backend/persistence/src/main/sqldelight/com/faire/ReferralB2BListGroupDetails.sq:75:
  MAX(ot.created_at)

	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:169)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:177)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:177)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:177)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:177)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:177)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:177)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.access$annotateRecursively(SqlCoreEnvironment.kt:60)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment$annotate$1.invoke(SqlCoreEnvironment.kt:145)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment$annotate$1.invoke(SqlCoreEnvironment.kt:140)
	at app.cash.sqldelight.core.SqlDelightEnvironment$forSourceFiles$1.invoke(SqlDelightEnvironment.kt:118)
	at app.cash.sqldelight.core.SqlDelightEnvironment$forSourceFiles$1.invoke(SqlDelightEnvironment.kt:113)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.forSourceFiles$lambda-1(SqlCoreEnvironment.kt:154)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:209)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:206)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:206)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:206)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContent(SqlCoreEnvironment.kt:200)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.forSourceFiles(SqlCoreEnvironment.kt:152)
	at app.cash.sqldelight.core.SqlDelightEnvironment.forSourceFiles(SqlDelightEnvironment.kt:113)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotate(SqlCoreEnvironment.kt:140)
	at app.cash.sqldelight.core.SqlDelightEnvironment.generateSqlDelightFiles(SqlDelightEnvironment.kt:129)
	at app.cash.sqldelight.gradle.SqlDelightTask$GenerateInterfaces.execute(SqlDelightTask.kt:102)
	at org.gradle.workers.internal.DefaultWorkerServer.execute(DefaultWorkerServer.java:63)
	at org.gradle.workers.internal.AbstractClassLoaderWorker$1.create(AbstractClassLoaderWorker.java:49)
	at org.gradle.workers.internal.AbstractClassLoaderWorker$1.create(AbstractClassLoaderWorker.java:43)
	at org.gradle.internal.classloader.ClassLoaderUtils.executeInClassloader(ClassLoaderUtils.java:97)
	at org.gradle.workers.internal.AbstractClassLoaderWorker.executeInClassLoader(AbstractClassLoaderWorker.java:43)
	at org.gradle.workers.internal.IsolatedClassloaderWorker.run(IsolatedClassloaderWorker.java:49)
	at org.gradle.workers.internal.IsolatedClassloaderWorker.run(IsolatedClassloaderWorker.java:30)
	at org.gradle.workers.internal.IsolatedClassloaderWorkerFactory$1.lambda$execute$0(IsolatedClassloaderWorkerFactory.java:57)
	at org.gradle.workers.internal.AbstractWorker$1.call(AbstractWorker.java:44)
	at org.gradle.workers.internal.AbstractWorker$1.call(AbstractWorker.java:41)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$CallableBuildOperationWorker.execute(DefaultBuildOperationRunner.java:204)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$CallableBuildOperationWorker.execute(DefaultBuildOperationRunner.java:199)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$2.execute(DefaultBuildOperationRunner.java:66)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$2.execute(DefaultBuildOperationRunner.java:59)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.execute(DefaultBuildOperationRunner.java:157)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.execute(DefaultBuildOperationRunner.java:59)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.call(DefaultBuildOperationRunner.java:53)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.call(DefaultBuildOperationExecutor.java:73)
	at org.gradle.workers.internal.AbstractWorker.executeWrappedInBuildOperation(AbstractWorker.java:41)
	at org.gradle.workers.internal.IsolatedClassloaderWorkerFactory$1.execute(IsolatedClassloaderWorkerFactory.java:49)
	at org.gradle.workers.internal.DefaultWorkerExecutor.lambda$submitWork$2(DefaultWorkerExecutor.java:205)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.runExecution(DefaultConditionalExecutionQueue.java:187)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.access$700(DefaultConditionalExecutionQueue.java:120)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner$1.run(DefaultConditionalExecutionQueue.java:162)
	at org.gradle.internal.Factories$1.create(Factories.java:31)
	at org.gradle.internal.work.DefaultWorkerLeaseService.withLocks(DefaultWorkerLeaseService.java:270)
	at org.gradle.internal.work.DefaultWorkerLeaseService.runAsWorkerThread(DefaultWorkerLeaseService.java:119)
	at org.gradle.internal.work.DefaultWorkerLeaseService.runAsWorkerThread(DefaultWorkerLeaseService.java:124)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.runBatch(DefaultConditionalExecutionQueue.java:157)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.run(DefaultConditionalExecutionQueue.java:126)
	... 2 more
Caused by: java.lang.IllegalArgumentException: Unknown date type timestamp
	at app.cash.sqldelight.dialects.mysql.MySqlTypeResolver.definitionType(MySqlTypeResolver.kt:82)
	at app.cash.sqldelight.core.lang.psi.ColumnTypeMixin.type(ColumnTypeMixin.kt:65)
	at app.cash.sqldelight.core.lang.util.TreeUtilKt.type(TreeUtil.kt:67)
	at app.cash.sqldelight.core.lang.util.TreeUtilKt.type(TreeUtil.kt:71)
	at app.cash.sqldelight.core.lang.util.TreeUtilKt.type(TreeUtil.kt:83)
	at app.cash.sqldelight.core.lang.util.ExprUtilKt.ansiType(ExprUtil.kt:269)
	at app.cash.sqldelight.core.lang.util.ExprUtilKt.access$ansiType(ExprUtil.kt:1)
	at app.cash.sqldelight.core.lang.util.AnsiSqlTypeResolver.resolvedType(ExprUtil.kt:72)
	at app.cash.sqldelight.dialects.mysql.MySqlTypeResolver.resolvedType(MySqlTypeResolver.kt:32)
	at app.cash.sqldelight.dialect.api.TypeResolverKt.encapsulatingType(TypeResolver.kt:54)
	at app.cash.sqldelight.dialect.api.TypeResolverKt.encapsulatingType(TypeResolver.kt:44)
	at app.cash.sqldelight.dialects.mysql.MySqlTypeResolver.mySqlFunctionType(MySqlTypeResolver.kt:60)
	at app.cash.sqldelight.dialects.mysql.MySqlTypeResolver.functionType(MySqlTypeResolver.kt:47)
	at app.cash.sqldelight.core.lang.psi.FunctionExprMixin.annotate(FunctionExprMixin.kt:11)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.annotateRecursively(SqlCoreEnvironment.kt:164)

Gradle Build Script

// partial build.gradle.kts

plugins {
  kotlin("jvm")
  id("app.cash.sqldelight") version "2.0.0-alpha03"
}

sqldelight {
  // Database name
  database("BackendDatabase") {
    packageName = "com.faire.persistence"

    // An array of folders where the plugin will read your '.sq' and '.sqm' files.
    sourceFolders = listOf("resources/db/migration", "sqldelight")

    deriveSchemaFromMigrations = true

    dialect("app.cash.sqldelight:mysql-dialect:2.0.0-alpha03")
  }
}

[Nava2]

cc @AlecStrong / @shellderp You were chatting about this offline :)

[Nava2]

This was introduced between -alpha01 and -alpha02, this works on -alpha01 👍🏻

[AlecKazakova]

Looks like this isn't caps safe, if you change the timestamp definition to be all caps TIMESTAMP it should work.

Thanks for the report!