CLNRest Bug in getPayments function - 'query failed with no such column: created_index'

[tioneb22]

Describe the bug

My CLN logs the below line when I try to view my activity:

2024-10-30T23:35:21.085Z **BROKEN** plugin-clnrest.py: Error: RPC call failed: method: sql, payload: {'query': \"select sp.payment_hash, sp.groupid, min(sp.status) as status, min(sp.destination) as destination, min(sp.created_at) as created_at, min(sp.description) as description, min(sp.bolt11) as bolt11, min(sp.bolt12) as b olt12, sum(case when sp.status = 'complete' then sp.amount_sent_msat else null end) as amount_sent_msat, sum(case when sp.status = 'complete' then sp.amount_msat e lse 0 end) as amount_msat, max(sp.payment_preimage) as preimage from sendpays sp group by sp.payment_hash, sp.groupid order by created_index desc limit 150\"}, err or: {'code': -1, 'message': 'query failed with no such column: created_index'} 2024-10-30T23:35:21.085Z **BROKEN** plugin-clnrest.py: Error: {error: {'code': -1, 'message': 'query failed with no such column: created_index'}}

Reproduce

Try to view activity when connected to CLN 23.08.

Versions:

• Zeus v0.9.1 (pulled from Google Play Store on Android)
• CLN 23.08 (using new clnrest.py)

ZEUS version

v0.9.1

Node interface

Core Lightning (CLNRest)

Network

Clearnet

Device

Samsung S10

Device operating system

Android 12

Log output

2024-10-30T23:35:21.085Z **BROKEN** plugin-clnrest.py: Error: RPC call failed: method: sql, payload: {'query': \"select sp.payment_hash, sp.groupid, min(sp.status)
 as status, min(sp.destination) as destination, min(sp.created_at) as created_at, min(sp.description) as description, min(sp.bolt11) as bolt11, min(sp.bolt12) as b
olt12, sum(case when sp.status = 'complete' then sp.amount_sent_msat else null end) as amount_sent_msat, sum(case when sp.status = 'complete' then sp.amount_msat e
lse 0 end) as amount_msat, max(sp.payment_preimage) as preimage from sendpays sp group by sp.payment_hash, sp.groupid order by created_index desc limit 150\"}, err
or: {'code': -1, 'message': 'query failed with no such column: created_index'}   
2024-10-30T23:35:21.085Z **BROKEN** plugin-clnrest.py: Error: {error: {'code': -1, 'message': 'query failed with no such column: created_index'}}

[kaloudis]

@tioneb22 I believe you need to upgrade to CLN 23.11 or higher to fix the issue

https://github.com/ElementsProject/lightning/blob/b327bd30c32c034a8350460066b9e7354bfa4ba1/doc/schemas/lightning-listsendpays.json#L96

[tioneb22]

Ah I see, I'll work on upgrading my node. Thanks!

[niteshbalusu11]

@tioneb22 I think the min version required is v24.02.
There are other things that'll break as well below this version.

[kaloudis]

@tioneb22 I think the min version required is v24.02. There are other things that'll break as well below this version.

We should potentially add a warning modal if a user connects with a lower version