# Duniter Squid bug for UD history custom SQL

**URL:** https://forum.duniter.org/t/duniter-squid-bug-for-ud-history-custom-sql/12625
**Category:** Indexers
**Tags:** subsquid, bug
**Created:** [28 October 2024 14:20 UTC](https://forum.duniter.org/t/duniter-squid-bug-for-ud-history-custom-sql/12625 "2024-10-28T14:20:05Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![HugoTrentesaux](https://forum.duniter.org/user_avatar/forum.duniter.org/hugotrentesaux/32/6396_2.png) [@HugoTrentesaux](https://forum.duniter.org/u/HugoTrentesaux)
#### Post date: [28 October 2024 14:20 UTC](https://forum.duniter.org/t/duniter-squid-bug-for-ud-history-custom-sql/12625/1 "2024-10-28T14:20:05Z")

</div>

In [Duniter Squid crash for out of range integer](https://forum.duniter.org/t/duniter-squid-crash-for-out-of-range-integer/12596), we had to use `BigInt` for Ud value instead of `number`, but now, the SQL function defined in [`udHistoryFunction_up.sql`](https://git.duniter.org/nodes/duniter-squid/-/blob/main/assets/sql/udHistoryFunction_up.sql?ref_type=heads) is not valid anymore and leads to database error in queries about UD. @poka do you have time to adapt this SQL query?

> **database logs**
>
> ```auto
> db-1 | 2024-10-28 14:09:19.743 UTC [2067474] ERROR: structure of query does not match function result type
> db-1 | 2024-10-28 14:09:19.743 UTC [2067474] DETAIL: Returned type numeric does not match expected type integer in column 2.
> db-1 | 2024-10-28 14:09:19.743 UTC [2067474] CONTEXT: SQL statement "SELECT
> db-1 | CAST('ud-' || identity_row.name || '-' || ud.block_number || '-' || ud.event_id AS character varying) AS id,
> db-1 | ud.amount,
> db-1 | ud.block_number,
> db-1 | b.timestamp,
> db-1 | identity_row.id AS identity_id
> db-1 | FROM
> db-1 | public.universal_dividend ud
> db-1 | JOIN public.block b ON ud.block_number = b.height
> db-1 | WHERE
> db-1 | EXISTS (
> db-1 | SELECT 1
> db-1 | FROM (
> db-1 | SELECT
> db-1 | me1.block_number as creation_block,
> db-1 | COALESCE(
> db-1 | (
> db-1 | SELECT me2.block_number
> db-1 | FROM public.membership_event me2
> db-1 | WHERE me2.identity_id = me1.identity_id
> db-1 | AND me2.event_type = 'Removal'
> db-1 | AND me2.block_number > me1.block_number
> db-1 | ORDER BY me2.block_number
> db-1 | LIMIT 1
> db-1 | ),
> db-1 | (SELECT MAX(block_number) FROM public.universal_dividend)
> db-1 | ) as removal_block
> db-1 | FROM public.membership_event me1
> db-1 | WHERE me1.identity_id = identity_row.id
> db-1 | AND me1.event_type = 'Creation'
> db-1 | ) as membership_periods
> db-1 | WHERE ud.block_number >= membership_periods.creation_block
> db-1 | AND ud.block_number <= membership_periods.removal_block
> db-1 | )"
> db-1 | PL/pgSQL function get_ud_history(identity) line 3 at RETURN QUERY
> db-1 | 2024-10-28 14:09:19.743 UTC [2067474] STATEMENT: SELECT coalesce((json_agg("root" )->0), 'null' ) AS "root" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.base"."id" AS "id", "_root.ar.root.transfersIssued"."transfersIssued" AS "transfersIssued", "_root.ar.root.transfersReceived"."transfersReceived" AS "transfersReceived", "_root.or.identity"."identity" AS "identity", "_root.or.linkedIdentity"."linkedIdentity" AS "linkedIdentity", "_root.ar.root.wasIdentity"."wasIdentity" AS "wasIdentity", 'Account' AS "__typename" ) AS "_e" ) ) AS "root" FROM (SELECT * FROM "public"."account" WHERE (("public"."account"."id") = (($5)::varchar)) ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.or.identity.base"."id" AS "id", "_root.or.identity.base"."name" AS "name", upper("_root.or.identity.base"."status" ) AS "status", "_root.or.identity.cf.udHistory"."udHistory" AS "udHistory", 'Identity' AS "__typename" ) AS "_e" ) ) AS "identity" FROM (SELECT * FROM "public"."identity" WHERE (("_root.base"."id") = ("account_id")) LIMIT 1 ) AS "_root.or.identity.base" LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("udHistory" ORDER BY "root.or.identity.cf.udHistory.pg.block_number" DESC NULLS FIRST), '[]' ) AS "udHistory" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.or.identity.cf.udHistory.base"."id" AS "id", "_root.or.identity.cf.udHistory.base"."timestamp" AS "timestamp", "_root.or.identity.cf.udHistory.base"."amount" AS "amount", "_root.or.identity.cf.udHistory.base"."block_number" AS "blockNumber", 'UdHistory' AS "__typename" ) AS "_e" ) ) AS "udHistory", "_root.or.identity.cf.udHistory.base"."block_number" AS "root.or.identity.cf.udHistory.pg.block_number" FROM (SELECT * FROM "public"."get_ud_history"("_root.or.identity.base") AS "_get_ud_history" WHERE (("_get_ud_history"."block_number") > (($4)::integer)) ORDER BY "block_number" DESC NULLS FIRST LIMIT 20 ) AS "_root.or.identity.cf.udHistory.base" ORDER BY "root.or.identity.cf.udHistory.pg.block_number" DESC NULLS FIRST ) AS "_root.or.identity.cf.udHistory" ) AS "_root.or.identity.cf.udHistory" ON ('true') ) AS "_root.or.identity" ON ('true') LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.or.linkedIdentity.base"."id" AS "id", "_root.or.linkedIdentity.base"."name" AS "name", 'Identity' AS "__typename" ) AS "_e" ) ) AS "linkedIdentity" FROM (SELECT * FROM "public"."identity" WHERE (("_root.base"."linked_identity_id") = ("id")) LIMIT 1 ) AS "_root.or.linkedIdentity.base" ) AS "_root.or.linkedIdentity" ON ('true') LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("transfersReceived" ORDER BY "root.ar.root.transfersReceived.pg.block_number" DESC NULLS FIRST), '[]' ) AS "transfersReceived" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersReceived.base"."id" AS "id", "_root.ar.root.transfersReceived.base"."from_id" AS "fromId", "_root.ar.root.transfersReceived.base"."amount" AS "amount", "_root.ar.root.transfersReceived.base"."timestamp" AS "timestamp", "_root.ar.root.transfersReceived.base"."block_number" AS "blockNumber", "_root.ar.root.transfersReceived.or.comment"."comment" AS "comment", 'Transfer' AS "__typename" ) AS "_e" ) ) AS "transfersReceived", "_root.ar.root.transfersReceived.base"."block_number" AS "root.ar.root.transfersReceived.pg.block_number" FROM (SELECT * FROM "public"."transfer" WHERE ((("_root.base"."id") = ("to_id")) AND (("public"."transfer"."block_number") > (($3)::integer))) ORDER BY "block_number" DESC NULLS FIRST LIMIT 10 ) AS "_root.ar.root.transfersReceived.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersReceived.or.comment.base"."id" AS "id", "_root.ar.root.transfersReceived.or.comment.base"."remark" AS "remark", upper("_root.ar.root.transfersReceived.or.comment.base"."type" ) AS "type", 'TxComment' AS "__typename" ) AS "_e" ) ) AS "comment" FROM (SELECT * FROM "public"."tx_comment" WHERE (("_root.ar.root.transfersReceived.base"."comment_id") = ("id")) LIMIT 1 ) AS "_root.ar.root.transfersReceived.or.comment.base" ) AS "_root.ar.root.transfersReceived.or.comment" ON ('true') ORDER BY "root.ar.root.transfersReceived.pg.block_number" DESC NULLS FIRST ) AS "_root.ar.root.transfersReceived" ) AS "_root.ar.root.transfersReceived" ON ('true') LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("transfersIssued" ORDER BY "root.ar.root.transfersIssued.pg.block_number" DESC NULLS FIRST), '[]' ) AS "transfersIssued" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersIssued.base"."id" AS "id", "_root.ar.root.transfersIssued.base"."to_id" AS "toId", "_root.ar.root.transfersIssued.base"."amount" AS "amount", "_root.ar.root.transfersIssued.base"."timestamp" AS "timestamp", "_root.ar.root.transfersIssued.base"."block_number" AS "blockNumber", "_root.ar.root.transfersIssued.or.comment"."comment" AS "comment", 'Transfer' AS "__typename" ) AS "_e" ) ) AS "transfersIssued", "_root.ar.root.transfersIssued.base"."block_number" AS "root.ar.root.transfersIssued.pg.block_number" FROM (SELECT * FROM "public"."transfer" WHERE ((("_root.base"."id") = ("from_id")) AND (("public"."transfer"."block_number") > (($2)::integer))) ORDER BY "block_number" DESC NULLS FIRST LIMIT 10 ) AS "_root.ar.root.transfersIssued.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersIssued.or.comment.base"."id" AS "id", "_root.ar.root.transfersIssued.or.comment.base"."remark" AS "remark", upper("_root.ar.root.transfersIssued.or.comment.base"."type" ) AS "type", 'TxComment' AS "__typename" ) AS "_e" ) ) AS "comment" FROM (SELECT * FROM "public"."tx_comment" WHERE (("_root.ar.root.transfersIssued.base"."comment_id") = ("id")) LIMIT 1 ) AS "_root.ar.root.transfersIssued.or.comment.base" ) AS "_root.ar.root.transfersIssued.or.comment" ON ('true') ORDER BY "root.ar.root.transfersIssued.pg.block_number" DESC NULLS FIRST ) AS "_root.ar.root.transfersIssued" ) AS "_root.ar.root.transfersIssued" ON ('true') LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("wasIdentity" ), '[]' ) AS "wasIdentity" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.wasIdentity.base"."id" AS "id", "_root.ar.root.wasIdentity.base"."next_id" AS "nextId", "_root.ar.root.wasIdentity.or.identity"."identity" AS "identity", 'ChangeOwnerKey' AS "__typename" ) AS "_e" ) ) AS "wasIdentity" FROM (SELECT * FROM "public"."change_owner_key" WHERE (("_root.base"."id") = ("previous_id")) ) AS "_root.ar.root.wasIdentity.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.wasIdentity.or.identity.base"."name" AS "name", 'Identity' AS "__typename" ) AS "_e" ) ) AS "identity" FROM (SELECT * FROM "public"."identity" WHERE (("_root.ar.root.wasIdentity.base"."identity_id") = ("id")) LIMIT 1 ) AS "_root.ar.root.wasIdentity.or.identity.base" ) AS "_root.ar.root.wasIdentity.or.identity" ON ('true') ) AS "_root.ar.root.wasIdentity" ) AS "_root.ar.root.wasIdentity" ON ('true') ) AS "_root"      
> 
> ```

(tracked in [#38](https://git.duniter.org/nodes/duniter-squid/-/issues/38))

---

<div class="post-metadata">

### Author: ![poka](https://forum.duniter.org/user_avatar/forum.duniter.org/poka/32/6827_2.png) [@poka](https://forum.duniter.org/u/poka)
#### Post date: [28 October 2024 21:40 UTC](https://forum.duniter.org/t/duniter-squid-bug-for-ud-history-custom-sql/12625/2 "2024-10-28T21:40:17Z")

</div>

![image](https://forum.duniter.org/uploads/default/original/2X/0/08569c9497ac80e624ff05626e030ef9aca4ad25.png)

> **[fix ud amount integer (a257cc76) · Commits · nodes / duniter-squid · GitLab](https://git.duniter.org/nodes/duniter-squid/-/commit/a257cc765f6c9a411f4082f5eda4232342251d1b)**
>
> A squid-based indexer for duniter-v2s.

 ![image](https://forum.duniter.org/uploads/default/original/2X/8/80ba91497ce3c313557d608a2f9b20bc3606a8dd.jpeg)

---

<div class="post-metadata">

### Author: ![HugoTrentesaux](https://forum.duniter.org/user_avatar/forum.duniter.org/hugotrentesaux/32/6396_2.png) [@HugoTrentesaux](https://forum.duniter.org/u/HugoTrentesaux)
#### Post date: [29 October 2024 10:34 UTC](https://forum.duniter.org/t/duniter-squid-bug-for-ud-history-custom-sql/12625/3 "2024-10-29T10:34:58Z")

</div>

**TL;DR** : il ne fallait pas cast le `bigint` en `int` (évidemment), mais bien changer le type de retour attendu par la fonction, donc le schéma de udHistory.

* * *

Chouette 🙂  
Je me doutais qu’il suffirait d’indiquer un type quelque part, et donc là c’est un simple cast, j’avais juste la flemme de chercher où le mettre \>\<, je dirais donc “mérci poka tes contribitions à duniter v2s!”.

Est-ce que tu veux mettre en ligne sur ton indexeur `https://gdev-indexer.p2p.legal/v1/graphql` ? Pour l’instant vu comme son gérées les migrations, ça implique de tout réindexer, alors qu’on pourrait simplement ajouter une migration pour mettre à jour la fonction. Si on regarde la table migrations :

 ![image](https://forum.duniter.org/uploads/default/original/2X/5/54e2e24801e3919839df05b5c5b85df52281431d.png)

_A priori_, il suffirait d’ajouter une nouvelle migration dans `/hasura/migrations`, mais vu que c’est généré, ce serait plutôt dans `/db/migrations`. À voir si c’est compatible avec le script `add_custom_squid_migrations.sh`.

* * *

[edit] bon bah non, je me suis réjouit trop vite, ça donne évidement l’erreur “ApolloError: integer out of range”, il reste au moins des choses à faire côté client. → mais là c’est bien côté serveur que ça pêche. Essaye de faire une requête en affichant les DU récents, ceux qui sont out of range, ça devrait planter.

> [@HugoTrentesaux](#):
>
> Je me doutais qu’il suffirait d’indiquer un type quelque part, et donc là c’est un simple cast

C’était effectivement une information de type, pas un cast. Si on fait un cast d’un numeric en integer, on se retrouve avec un out of range, c’est normal. Et l’information de type manquante était dans le schéma qui définissait le type de retour de la fonction. [à suivre]

---

<div class="post-metadata">

### Author: ![poka](https://forum.duniter.org/user_avatar/forum.duniter.org/poka/32/6827_2.png) [@poka](https://forum.duniter.org/u/poka)
#### Post date: [6 November 2024 17:56 UTC](https://forum.duniter.org/t/duniter-squid-bug-for-ud-history-custom-sql/12625/4 "2024-11-06T17:56:53Z")

</div>

> [@HugoTrentesaux](#):
>
> l’information de type manquante était dans le schéma

Oui clairement le plus long dans ma MR était d’écrire le bon prompt pour générer l’image…

Je savais bien en poussant le cast qui ça pèterait pour des non int, mais j’ai quand même poussé ainsi pour la beauté du geste.

A noter que je pense que le fix aurait était le bon avec Cursor en utilisant le context du workspace et pas juste le fichier SQL que j’avais ouvert. A tester pour la science. J’aime bien expérimenter ce genre de choses ^^