SqlDelight code generator is not producing code for ROW_NUM function

[yamilmedina]

SQLDelight Version

2.0.2

Gradle Version

8.8

Kotlin Version

1.9.23

Dialect

SQLite

AGP Version

8.5.2

Describe the Bug

Seems to be a bug within SqlDelight 2.0.2

This is the query that is facing issues:

getNotificationsMessages:
WITH NumberedMessages AS (
    SELECT
        -- other fields
        ROW_NUMBER() OVER (PARTITION BY m.conversation_id ORDER BY m.creation_date DESC) AS row_num
    FROM
        Message m
    JOIN
        User u ON m.sender_user_id = u.qualified_id
    JOIN
        Conversation c ON m.conversation_id = c.qualified_id
    LEFT JOIN
        MessageAssetContent ac ON m.id = ac.message_id AND m.conversation_id = ac.conversation_id
    LEFT JOIN
        MessageTextContent tc ON m.id = tc.message_id AND m.conversation_id = tc.conversation_id
    WHERE
        -- Criteria
)
SELECT
    -- some fields
FROM
    NumberedMessages
WHERE
   row_num <= 10
ORDER BY
    date DESC;

However, the ROW_NUMBER() OVER ... field is removed by SqlDelight code generator:

WITH NumberedMessages AS (
    SELECT
        -- more fields
        row_num -- It completely removed the rest of the query here
    FROM
        Message m
    JOIN
        User u ON m.sender_user_id = u.qualified_id
    JOIN
        Conversation c ON m.conversation_id = c.qualified_id
    LEFT JOIN

[!NOTE] On version 2.0.1 it is working as expected, generating the full ROW_NUMBER() OVER field.

Stacktrace

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such column: row_num)
 
 org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such column: row_num)
 	at app//org.sqlite.core.DB.newSQLException(DB.java:1179)
 	at app//org.sqlite.core.DB.newSQLException(DB.java:1190)
 	at app//org.sqlite.core.DB.throwex(DB.java:1150)
 	at app//org.sqlite.core.NativeDB.prepare_utf8(Native Method)
 	at app//org.sqlite.core.NativeDB.prepare(NativeDB.java:132)
 	at app//org.sqlite.core.DB.prepare(DB.java:264)
 	at app//org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:46)
 ...

[griffio]

📸 The snapshot version seems to work 2.1.0-SNAPSHOT - in a small sample app Previously I fixed an issue with Sqlite Windows as they didn't really work https://github.com/sqldelight/sqldelight/pull/5406

📓 Using minSdk = 30 as Window function support was first added to SQLite with release 3.25.- SqlDelight uses the minSdk to select the sqlite dialect version.

Generated source

public class NotificationsQueries(
  driver: SqlDriver,
) : TransacterImpl(driver) {
  public fun `get`(): Query<Long> = Query(-813_273_273, arrayOf("Message", "User", "Conversation",
      "MessageAssetContent", "MessageTextContent"), driver, "Notifications.sq", "get", """
  |WITH NumberedMessages AS (
  |     SELECT
  |         ROW_NUMBER() OVER (PARTITION BY m.conversation_id ORDER BY m.creation_date DESC) AS row_num
  |     FROM
  |         Message m
  |     JOIN
  |         User u ON m.sender_user_id = u.qualified_id
  |     JOIN
  |         Conversation c ON m.conversation_id = c.qualified_id
  |     LEFT JOIN
  |         MessageAssetContent ac ON m.id = ac.message_id AND m.conversation_id = ac.conversation_id
  |     LEFT JOIN
  |         MessageTextContent tc ON m.id = tc.message_id AND m.conversation_id = tc.conversation_id
  | )
  | SELECT
  |     NumberedMessages.row_num
  | FROM
  |     NumberedMessages
  | WHERE
  |    row_num <= 10
  | ORDER BY date  DESC
  """.trimMargin()) { cursor ->
    cursor.getLong(0)!!
  }```

[vitorhugods]

Just confirming that this has been fixed in 2.1.0