Skip to content

StackOverflowError on compiling WITH RECURSIVE #5891

New issue
New issue
Closed as duplicate of#5039
Closed as duplicate of#5039
StackOverflowError on compiling WITH RECURSIVE#5891
Labels
bug
@Nek-12

Description

@Nek-12
Nek-12
opened on Aug 20, 2025

SQLDelight Version

2.1.0

Operating System

MacOS

Gradle Version

9.0.1

Kotlin Version

2.2.20-RC

Dialect

sqlite-3.38

AGP Version

8.12.0

Describe the Bug

I am unable to compile my desired functon:

CREATE TABLE IF NOT EXISTS Entry (
id BLOB AS Uuid PRIMARY KEY NOT NULL,
pointsDelta INTEGER AS Int NOT NULL,]
createdAt INTEGER AS Instant NOT NULL ,
updatedAt INTEGER AS Instant NOT NULL ,
);

WITH RECURSIVE
    Today AS (
        -- Get today's date as an epoch day number based on the local timezone.
        SELECT CAST(strftime('%s', 'now', 'localtime') * 1000 / 86400000 AS INTEGER) AS day
    ),
    CompletedDays AS (
        -- Get all distinct days with a positive entry.
        SELECT DISTINCT createdAt / 86400000 AS day
        FROM Entry
        WHERE pointsDelta > 0
    ),
    StopDay AS (
        -- Determine the day before the first-ever completed entry to limit the date series.
        SELECT MIN(day) - 1 AS day FROM CompletedDays
    ),
    DateSeries(d) AS (
        -- Generate a series of dates backwards from today.
        SELECT day FROM Today
        UNION ALL
        -- The recursion stops when it reaches the day before the first entry,
        -- or immediately if there are no completed entries at all (the JOIN fails).
        SELECT T.d - 1
        FROM DateSeries T JOIN StopDay S ON T.d > S.day
    ),
    FirstGap AS (
        -- Find the most recent day that is not in CompletedDays.
        -- This is the day the streak was broken.
        SELECT MAX(d) AS day
        FROM (
            -- Find all generated dates that are not in the completed days list.
            SELECT d FROM DateSeries
            EXCEPT
            SELECT day FROM CompletedDays
        )
    )
-- The streak is the difference between today and the first day of the gap.
-- If CompletedDays is empty, FirstGap will be today, resulting in a streak of 0.
SELECT (SELECT day FROM Today) - FirstGap.day FROM FirstGap;

Stacktrace

Failed to compile SqlCompoundSelectStmtImpl(COMPOUND_SELECT_STMT): [] :
     WITH RECURSIVE
         Today AS (
             -- Get today's date as an epoch day number based on the local timezone.
             SELECT CAST(strftime('%s', 'now', 'localtime') * 1000 / 86400000 AS INTEGER) AS day
         ),
         CompletedDays AS (
             -- Get all distinct days with a positive entry.
             SELECT DISTINCT createdAt / 86400000 AS day
             FROM Entry
             WHERE pointsDelta > 0
         ),
         StopDay AS (
             -- Determine the day before the first-ever completed entry to limit the date series.
             SELECT MIN(day) - 1 AS day FROM CompletedDays
         ),
         DateSeries(d) AS (
             -- Generate a series of dates backwards from today.
             SELECT day FROM Today
             UNION ALL
             -- The recursion stops when it reaches the day before the first entry,
             -- or immediately if there are no completed entries at all (the JOIN fails).
             SELECT T.d - 1
             FROM DateSeries T JOIN StopDay S ON T.d > S.day
         ),
         FirstGap AS (
             -- Find the most recent day that is not in CompletedDays.
             -- This is the day the streak was broken.
             SELECT MAX(d) AS day
             FROM (
                 -- Find all generated dates that are not in the completed days list.
                 SELECT d FROM DateSeries
                 EXCEPT
                 SELECT day FROM CompletedDays
             )
         )
     -- The streak is the difference between today and the first day of the gap.
     -- If CompletedDays is empty, FirstGap will be today, resulting in a streak of 0.
     SELECT (SELECT day FROM Today) - FirstGap.day FROM FirstGap

org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.lambda$executeIfValid$1(ExecuteActionsTaskExecuter.java:135)
	at org.gradle.internal.Try$Failure.ifSuccessfulOrElse(Try.java:288)
	at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeIfValid(ExecuteActionsTaskExecuter.java:133)
	at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.execute(ExecuteActionsTaskExecuter.java:121)
	at <...>
        at
org.gradle.internal.execution.impl.DefaultExecutionEngine$1.execute(DefaultExecutionEngine.java:64)
	at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeIfValid(ExecuteActionsTaskExecuter.java:132)
	... 29 more
Caused by: java.lang.IllegalStateException: Failed to compile SqlCompoundSelectStmtImpl(COMPOUND_SELECT_STMT): [] :
WITH RECURSIVE
    Today AS (
        -- Get today's date as an epoch day number based on the local timezone.
        SELECT CAST(strftime('%s', 'now', 'localtime') * 1000 / 86400000 AS INTEGER) AS day
    ),
    CompletedDays AS (
        -- Get all distinct days with a positive entry.
        SELECT DISTINCT createdAt / 86400000 AS day
        FROM Entry
        WHERE pointsDelta > 0
    ),
    StopDay AS (
        -- Determine the day before the first-ever completed entry to limit the date series.
        SELECT MIN(day) - 1 AS day FROM CompletedDays
    ),
    DateSeries(d) AS (
        -- Generate a series of dates backwards from today.
        SELECT day FROM Today
        UNION ALL
        -- The recursion stops when it reaches the day before the first entry,
        -- or immediately if there are no completed entries at all (the JOIN fails).
        SELECT T.d - 1
        FROM DateSeries T JOIN StopDay S ON T.d > S.day
    ),
    FirstGap AS (
        -- Find the most recent day that is not in CompletedDays.
        -- This is the day the streak was broken.
        SELECT MAX(d) AS day
        FROM (
            -- Find all generated dates that are not in the completed days list.
            SELECT d FROM DateSeries
            EXCEPT
            SELECT day FROM CompletedDays
        )
    )
-- The streak is the difference between today and the first day of the gap.
-- If CompletedDays is empty, FirstGap will be today, resulting in a streak of 0.
SELECT (SELECT day FROM Today) - FirstGap.day FROM FirstGap
	at app.cash.sqldelight.core.compiler.SqlDelightCompilerKt.tryWithElement(SqlDelightCompiler.kt:234)
	at app.cash.sqldelight.core.compiler.QueriesTypeGenerator.generateType(QueriesTypeGenerator.kt:55)
	at app.cash.sqldelight.core.compiler.SqlDelightCompiler.writeQueries$sqldelight_compiler(SqlDelightCompiler.kt:172)
	at app.cash.sqldelight.core.compiler.SqlDelightCompiler.writeInterfaces(SqlDelightCompiler.kt:52)
	at app.cash.sqldelight.core.SqlDelightEnvironment.generateSqlDelightFiles$lambda$9(SqlDelightEnvironment.kt:165)
	at app.cash.sqldelight.core.SqlDelightEnvironment.forSourceFiles$lambda$5(SqlDelightEnvironment.kt:125)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.forSourceFiles$lambda$5(SqlCoreEnvironment.kt:162)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:219)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:216)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:216)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:216)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:216)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:216)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContentUnderDirectory(SqlCoreEnvironment.kt:216)
	at com.alecstrong.sql.psi.core.CoreFileIndex.iterateContent(SqlCoreEnvironment.kt:210)
	at com.alecstrong.sql.psi.core.SqlCoreEnvironment.forSourceFiles(SqlCoreEnvironment.kt:159)
	at app.cash.sqldelight.core.SqlDelightEnvironment.forSourceFiles(SqlDelightEnvironment.kt:120)
	at app.cash.sqldelight.core.SqlDelightEnvironment.forSqlFileBases(SqlDelightEnvironment.kt:364)
	at app.cash.sqldelight.core.SqlDelightEnvironment.generateSqlDelightFiles(SqlDelightEnvironment.kt:156)
	at app.cash.sqldelight.gradle.SqlDelightTask$GenerateInterfaces.execute(SqlDelightTask.kt:98)
	at org.gradle.workers.internal.DefaultWorkerServer.execute(DefaultWorkerServer.java:68)
	at org.gradle.workers.internal.AbstractClassLoaderWorker$1.create(AbstractClassLoaderWorker.java:54)
	at org.gradle.workers.internal.AbstractClassLoaderWorker$1.create(AbstractClassLoaderWorker.java:48)
	at org.gradle.internal.classloader.ClassLoaderUtils.executeInClassloader(ClassLoaderUtils.java:100)
	at org.gradle.workers.internal.AbstractClassLoaderWorker.executeInClassLoader(AbstractClassLoaderWorker.java:48)
	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.WorkerDaemonServer.run(WorkerDaemonServer.java:108)
	at org.gradle.workers.internal.WorkerDaemonServer.run(WorkerDaemonServer.java:77)
	at org.gradle.process.internal.worker.request.WorkerAction.lambda$run$1(WorkerAction.java:150)
	at org.gradle.process.internal.worker.child.WorkerLogEventListener.withWorkerLoggingProtocol(WorkerLogEventListener.java:41)
	at org.gradle.process.internal.worker.request.WorkerAction.lambda$run$2(WorkerAction.java:150)
	at org.gradle.internal.operations.CurrentBuildOperationRef.with(CurrentBuildOperationRef.java:84)
	at org.gradle.process.internal.worker.request.WorkerAction.run(WorkerAction.java:142)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:36)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:182)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:164)
	at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:414)
	at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:64)
	at org.gradle.internal.concurrent.AbstractManagedExecutor$1.run(AbstractManagedExecutor.java:47)
Caused by: java.lang.StackOverflowError
	at com.intellij.openapi.vfs.local.CoreLocalFileSystem.findFileByNioFile(CoreLocalFileSystem.java:26)
	at com.intellij.openapi.vfs.local.CoreLocalFileSystem.findFileByPath(CoreLocalFileSystem.java:31)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.virtualDirectoriesWithDependencies_delegate$lambda$1(SqlDelightEnvironment.kt:300)
	at kotlin.SynchronizedLazyImpl.getValue(LazyJVM.kt:86)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.getVirtualDirectoriesWithDependencies(SqlDelightEnvironment.kt:298)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.sourceFolders(SqlDelightEnvironment.kt:344)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.sourceFolders(SqlDelightEnvironment.kt:283)
	at app.cash.sqldelight.core.SqlDelightFileIndex$DefaultImpls.sourceFolders$default(SqlDelightFileIndex.kt:72)]]
Caused by: java.lang.StackOverflowError
	at com.intellij.openapi.vfs.local.CoreLocalFileSystem.findFileByNioFile(CoreLocalFileSystem.java:26)
	at com.intellij.openapi.vfs.local.CoreLocalFileSystem.findFileByPath(CoreLocalFileSystem.java:31)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.virtualDirectoriesWithDependencies_delegate$lambda$1(SqlDelightEnvironment.kt:300)
	at kotlin.SynchronizedLazyImpl.getValue(LazyJVM.kt:86)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.getVirtualDirectoriesWithDependencies(SqlDelightEnvironment.kt:298)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.sourceFolders(SqlDelightEnvironment.kt:344)
	at app.cash.sqldelight.core.SqlDelightEnvironment$FileIndex.sourceFolders(SqlDelightEnvironment.kt:283)

Gradle Build Script

N/A

Metadata

Metadata

Assignees

No one assigned

    Labels

    bug

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions