uptrace / bun

SQL-first Golang ORM
https://bun.uptrace.dev
BSD 2-Clause "Simplified" License
3.82k stars 231 forks source link

incorrect CTE generation #986

Open jurakontor opened 6 months ago

jurakontor commented 6 months ago

hello it looks like CTE (.With) doesn't generate proper sql query

I have following sql:

WITH ins AS ( INSERT INTO profile_source_on_chain (address, nick_name) VALUES ('0x72c9fb7ed19d3ce51cea5c56b3e023cd918baadf3', 'Sei Ape Club') ON CONFLICT (address, nick_name) DO NOTHING RETURNING id) SELECT id FROM ins UNION ALL SELECT id FROM profile_source_on_chain WHERE address = '0x72c9fb7ed19d3ce51cea5c56b3e023cd918baadf3' and nick_name = 'Sei Ape Club'; `

and i need to use bun to create same sql.

ProfileSourceOnChain is table with 3 columns: id (uuid), address , nickname varchar Have constraint (address , nickname)

` onChain := ProfileSourceOnChain{Address: "address",NickName:"nickname"}

    subq := tx.NewInsert().Model(&onChain).Returning("id").
        On("CONFLICT (address, nick_name) DO nothing")

    union := tx.NewSelect().Model((*dto2.ProfileSourceOnChain)(nil)).Column("id").
        Where("address = ?","address").
        Where("nick_name = ?","nickname")

        tx.NewSelect().UnionAll(union).Column("id").Table("ins").With("ins", subq).Scan(ctx, &r); `

the above code will generate

(WITH ins AS ( INSERT INTO profile_source_on_chain (address, nick_name) VALUES ('0x72c9fb7ed19d3ce51cea5c56b3e023cd918baadf3', 'Sei Ape Club') ON CONFLICT (address, nick_name) DO NOTHING RETURNING id) SELECT id FROM ins) UNION ALL SELECT id FROM profile_source_on_chain WHERE address = '0x72c9fb7ed19d3ce51cea5c56b3e023cd918baadf3' and nick_name = 'Sei Ape Club';

the issue with brackets at the beginning and after FROM ins. Execution of the sql gives an error:

ERROR: WITH clause containing a data-modifying statement must be at the top level

Need help with fixing this issue. thanks

github-actions[bot] commented 5 days ago

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.