RETURNING makes unrelated queries fail the build

[JorgeCastilloPrz]

Build Environment
SQLDelight version: 1.5.0
OS: MacOS 10.15.4
Gradle version: 7.0.2
Kotlin version: 1.5.10
AGP Version (if applicable): Not an Android project.

Describe the bug

Having the following .sq file:

CREATE TABLE IF NOT EXISTS userEntity (
    user_id SERIAL PRIMARY KEY,
    slack_user_id VARCHAR NOT NULL
);

CREATE TABLE IF NOT EXISTS subscriptionEntity (
    user_id2 SERIAL NOT NULL,
    FOREIGN KEY (user_id2) REFERENCES userEntity(user_id)
);

insertSubscription:
INSERT INTO subscriptionEntity(user_id2)
VALUES (?);

insertUser:
WITH inserted_ids AS (
  INSERT INTO userEntity(slack_user_id)
  VALUES (?)
  RETURNING user_id AS insert_id
) SELECT insert_id FROM inserted_ids;

Compilation fails unless you comment the insertSubscription query.

Here's the build.gradle file:

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
  kotlin("jvm") version Version.kotlin
  kotlin("kapt") version Version.kotlin
  id("org.springframework.boot") version Version.springBoot
  id("io.spring.dependency-management") version "1.0.11.RELEASE"
  kotlin("plugin.spring") version Version.kotlin
  id("org.jlleitschuh.gradle.ktlint") version Version.ktlint
  id("com.squareup.sqldelight") version Version.sqlDelight // (1.5.0)
}

java.sourceCompatibility = JavaVersion.VERSION_1_8

// Do not run ktlint for generated code
ktlint {
  filter {
    exclude { projectDir.toURI().relativize(it.file.toURI()).path.contains("/generated/") }
  }
}

sqldelight {
  database("SubscriptionsDb") {
    // Package name used for the generated SubscriptionsDb.kt
    packageName = "com.fortysevendegrees.subscriptions.db"
    dialect = "postgresql"
  }
}

tasks.withType<KotlinCompile> {
  kotlinOptions {
    freeCompilerArgs = listOf("-Xjsr305=strict", "-XXLanguage:+InlineClasses", "-Xopt-in=kotlin.time.ExperimentalTime")
    jvmTarget = "1.8"
  }
}

tasks.withType<Test> {
  useJUnitPlatform()
}

repositories {
  mavenCentral()
}

dependencies {
  implementation(platform("org.testcontainers:testcontainers-bom:${Version.testcontainers}"))

  implementation(Libs.kotlinxCoroutines)
  implementation(Libs.kotlinxCoroutinesReactive)
  implementation(Libs.kotlinxCoroutinesReactor)
  implementation(Libs.kotlinJdk8)
  implementation(Libs.kotlinReflect)

  implementation(Libs.arrowFxCoroutines)

  implementation(Libs.springBootStarterWebflux)
  kapt(Libs.springBootConfigurationProcessor)
  implementation(Libs.springBootStarterJdbc)
  implementation(Libs.springDocOpenApiWebflux)
  implementation(Libs.springDocOpenApiKotlin)

  implementation(Libs.sqlDelightJdbcDriver)
//  implementation(Libs.flywayCore)
  runtimeOnly(Libs.postgresql)
  implementation(Libs.postgresqlSocketFactory)

  implementation(Libs.hoplite)

  implementation(Libs.moshiKotlin)
  kapt(Libs.moshiKotlinCodegen)
  implementation(Libs.moshiAdapters)
  implementation(Libs.kotshiApi)
  kapt(Libs.kotshiCompiler)

  // Testcontainers
  implementation(Libs.testcontainers)
  testImplementation(Libs.testcontainersJUnit)
  testImplementation(Libs.testcontainersPostgres)
  testImplementation(Libs.testcontainersJupiterApi)
  testImplementation(Libs.testcontainersJupiterParams)
  testRuntimeOnly(Libs.testcontainersJupiterEngine)
  testImplementation(Libs.springBootStarterTest)
  testImplementation(Libs.springMockk)
  testImplementation(Libs.kotestSpring)
  testImplementation(Libs.kotestRunner)
  testImplementation(Libs.kotestAssertions)
  testImplementation(Libs.kotestProperty)

  implementation(Libs.springKafka)
  testImplementation(Libs.springKafkaTest)
}

tasks.withType<KotlinCompile> {
  kotlinOptions {
    freeCompilerArgs = listOf("-Xjsr305=strict")
    jvmTarget = "1.8"
  }
}

tasks.withType<Test> {
  useJUnitPlatform()
}

Starts compiling fine as soon as you comment the insertSubscription query, or simply by removing the WITH and writing the insertUser query like the following instead:

INSERT INTO userEntity(slack_user_id)
VALUES (?);

The problem on doing this is that I never got RETURNING to work with postgresql dialect without the WITH.

Here's the stacktrace:

Click here to see the stacktrace

  ERROR: INSERT INTO userEntity(slack_user_id)
  VALUES (?)
  RETURNING user_id AS insert_id
 parent=WITH inserted_ids AS (
  INSERT INTO userEntity(slack_user_id)
  VALUES (?)
  RETURNING user_id AS insert_id
)
java.lang.Throwable: INSERT INTO userEntity(slack_user_id)
  VALUES (?)
  RETURNING user_id AS insert_id
 parent=WITH inserted_ids AS (
  INSERT INTO userEntity(slack_user_id)
  VALUES (?)
  RETURNING user_id AS insert_id
)
	at sqldelight.com.intellij.openapi.diagnostic.Logger.error(Logger.java:146)
	at sqldelight.com.intellij.psi.impl.PsiElementBase.notNullChild(PsiElementBase.java:284)
	at sqldelight.com.alecstrong.sql.psi.core.psi.impl.SqlWithClauseAuxiliaryStmtImpl.getCompoundSelectStmt(SqlWithClauseAuxiliaryStmtImpl.java:32)
	at com.squareup.sqldelight.core.lang.util.SelectStmtUtilKt.referencedTables(SelectStmtUtil.kt:34)
	at com.squareup.sqldelight.core.lang.util.SelectStmtUtilKt.tablesObserved(SelectStmtUtil.kt:17)
	at com.squareup.sqldelight.core.compiler.model.NamedQuery$tablesObserved$2.invoke(NamedQuery.kt:120)
	at com.squareup.sqldelight.core.compiler.model.NamedQuery$tablesObserved$2.invoke(NamedQuery.kt:46)
	at kotlin.SynchronizedLazyImpl.getValue(LazyJVM.kt:74)
	at com.squareup.sqldelight.core.compiler.model.NamedQuery.getTablesObserved$sqldelight_compiler(NamedQuery.kt)
	at com.squareup.sqldelight.core.compiler.MutatorQueryGenerator$queriesUpdated$1.invoke(MutatorQueryGenerator.kt:70)
	at com.squareup.sqldelight.core.compiler.MutatorQueryGenerator$queriesUpdated$1.invoke(MutatorQueryGenerator.kt:11)
	at com.squareup.sqldelight.core.lang.SqlDelightQueriesFile.iterateSqlFiles(SqlDelightQueriesFile.kt:130)
	at com.squareup.sqldelight.core.compiler.MutatorQueryGenerator.queriesUpdated$sqldelight_compiler(MutatorQueryGenerator.kt:22)
	at com.squareup.sqldelight.core.compiler.ExecuteQueryGenerator.notifyQueries(ExecuteQueryGenerator.kt:42)
	at com.squareup.sqldelight.core.compiler.ExecuteQueryGenerator.function(ExecuteQueryGenerator.kt:65)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator$addExecute$1.invoke(QueriesTypeGenerator.kt:140)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator$addExecute$1.invoke(QueriesTypeGenerator.kt:22)
	at com.squareup.sqldelight.core.compiler.SqlDelightCompilerKt.tryWithElement(SqlDelightCompiler.kt:220)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator.addExecute(QueriesTypeGenerator.kt:131)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator.generateType(QueriesTypeGenerator.kt:119)
	at com.squareup.sqldelight.core.compiler.SqlDelightCompiler.writeImplementations(SqlDelightCompiler.kt:85)
	at com.squareup.sqldelight.core.SqlDelightEnvironment.generateSqlDelightFiles(SqlDelightEnvironment.kt:187)
	at com.squareup.sqldelight.gradle.SqlDelightTask$GenerateInterfaces.execute(SqlDelightTask.kt:97)
	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:200)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$CallableBuildOperationWorker.execute(DefaultBuildOperationRunner.java:195)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$3.execute(DefaultBuildOperationRunner.java:75)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$3.execute(DefaultBuildOperationRunner.java:68)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.execute(DefaultBuildOperationRunner.java:153)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.execute(DefaultBuildOperationRunner.java:68)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.call(DefaultBuildOperationRunner.java:62)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.lambda$call$2(DefaultBuildOperationExecutor.java:76)
	at org.gradle.internal.operations.UnmanagedBuildOperationWrapper.callWithUnmanagedSupport(UnmanagedBuildOperationWrapper.java:54)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.call(DefaultBuildOperationExecutor.java:76)
	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:206)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.runExecution(DefaultConditionalExecutionQueue.java:214)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.runBatch(DefaultConditionalExecutionQueue.java:164)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.run(DefaultConditionalExecutionQueue.java:131)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:64)
	at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:48)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:56)
	at java.base/java.lang.Thread.run(Thread.java:829)

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':generateMainSubscriptionsDbInterface'.
> A failure occurred while executing com.squareup.sqldelight.gradle.SqlDelightTask$GenerateInterfaces
   > Failed to compile PostgreSqlInsertStmtImpl(INSERT_STMT): [] :
     INSERT INTO subscriptionEntity(user_id2)
     VALUES (?)

* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

* Get more help at https://help.gradle.org

BUILD FAILED in 8s

[JorgeCastilloPrz]

CC @veyndan