Skip to content

Window expression loses OVER clause when arithmetic is chained #606

Description

@1llu5ion

Describe the bug

In go-jet v2.15.0, chaining arithmetic after a window expression can remove the OVER clause from the left-hand operand.
This affects at least integer and float expressions:

ROW_NUMBER().
    OVER(ORDER_BY(Example.ID)).
    ADD(Int64(1))

Generated SQL:

ROW_NUMBER() + 1::bigint

PostgreSQL rejects this SQL:

pq: window function row_number requires an OVER clause
The same issue occurs with aggregate window expressions. When .SUB(...) is chained after .OVER(...), the left-hand expression loses its window clause while the right-hand expression keeps it.
An explicit wrapper works around the issue:

IntExp(
    ROW_NUMBER().OVER(ORDER_BY(Example.ID)),
).ADD(Int64(1))

This suggests that chained arithmetic uses the expression root from before the window expression was constructed.

Environment (please complete the following information):

  • OS: linux , macos
  • Database: PostgreSQL
  • Database driver: github.com/lib/pq v1.12.3
  • Jet version: github.com/go-jet/jet/v2 v2.15.0
  • Go version: go1.24.2

Code snippet
The example uses a generic table generated by Jet:

package main

import (
    "fmt"

    . "github.com/go-jet/jet/v2/postgres"
    . "example/generated/table"
)

func main() {
    stmt := Example.SELECT(
        ROW_NUMBER().
            OVER(ORDER_BY(Example.ID)).
            ADD(Int64(1)),
    )

    fmt.Println(stmt.DebugSql())
}

Actual SQL:

SELECT ROW_NUMBER() + 1::bigint
FROM example

Another reproduction using aggregate window expressions:

stmt := Example.SELECT(
    SUMf(Example.Debit).
        OVER(PARTITION_BY(Example.GroupID)).
        SUB(
            SUMf(Example.Credit).
                OVER(PARTITION_BY(Example.GroupID)),
        ),
)
fmt.Println(stmt.DebugSql())

Actual SQL:

SELECT
    SUM(example.debit)
    -
    SUM(example.credit) OVER (PARTITION BY example.group_id)
FROM example

No custom model logic is involved. Example is a normal table generated by the Jet generator with these relevant columns:

id
group_id
debit
credit

Expected behavior

The complete window expression should remain the left-hand operand when arithmetic is chained.
Expected ROW_NUMBER() SQL:

SELECT
    ROW_NUMBER() OVER (ORDER BY example.id) + 1::bigint
FROM example
Expected aggregate SQL:
SELECT
    SUM(example.debit) OVER (PARTITION BY example.group_id)
    -
    SUM(example.credit) OVER (PARTITION BY example.group_id)
FROM example
Explicit IntExp(...) or FloatExp(...)

wrappers should not be required to preserve the OVER clause.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions