Firebird 5.0.2 64 bits on Windows 11 - UPDATE OR INSERT INTO with RETURNING clause

[zedalaye]

Hello,

Given this table definition :

CREATE TABLE MAGASINS (
    UID                        UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
    CODE                       REFERENCE /* REFERENCE = CHAR(12) NOT NULL */,
    NOM                        DESCRIPTION NOT NULL /* DESCRIPTION = VARCHAR(200) */,
    RESPONSABLE                TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    ADRESSE                    TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    CODE_POSTAL                CODE_POSTAL NOT NULL /* CODE_POSTAL = CHAR(8) */,
    VILLE                      TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    PAYS                       TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    TELEPHONE                  TELEPHONE NOT NULL /* TELEPHONE = VARCHAR(20) */,
    EMAIL                      TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    TVA                        TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    SIRET                      TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    APE                        TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    RCS                        TEXTE_80 NOT NULL /* TEXTE_80 = VARCHAR(80) */,
    LOGO                       IMAGE /* IMAGE = BLOB SUB_TYPE BINARY SEGMENT SIZE 80 */,
    NOTE_FACTURE               VARCHAR(5000),
    LIEU_CHEQUE                CHAR(35),
    CODE_CLIENT_CRT            VARCHAR(15),
    HEURE_CLOTURE_QUOTIDIENNE  TIME,
    DATE_CLOTURE_EXERCICE      DATE,
    PAPIER_ENTETE              BOOLEEN /* BOOLEEN = BOOLEAN DEFAULT false NOT NULL */,
    SUFFIXE_NUMEROTATION       VARCHAR(5),
    FORME_JURIDIQUE            TEXTE_80 /* TEXTE_80 = VARCHAR(80) */,
    CAPITAL_SOCIAL             MONTANT /* MONTANT = DECIMAL(10,4) DEFAULT 0.00 */,
    CENTRAL                    BOOLEEN /* BOOLEEN = BOOLEAN DEFAULT false NOT NULL */,
    OPTION_TVA                 INTEGER DEFAULT 0 NOT NULL,
    REGIME_IMPOTS              INTEGER DEFAULT 0 NOT NULL
);

ALTER TABLE MAGASINS ADD CONSTRAINT MAGASINS_PK PRIMARY KEY (UID);

CREATE UNIQUE INDEX MAGASINS_CODE_IDX ON MAGASINS (CODE);

I want to run this query returnuing both OLD and NEW values like this (for logging purposes) :

    update or insert into MAGASINS(
      UID, CODE, NOM, RESPONSABLE, ADRESSE, CODE_POSTAL, VILLE, PAYS,
      TELEPHONE, EMAIL,
      TVA, SIRET, APE, RCS, FORME_JURIDIQUE, CAPITAL_SOCIAL,
      HEURE_CLOTURE_QUOTIDIENNE, DATE_CLOTURE_EXERCICE,
      PAPIER_ENTETE, SUFFIXE_NUMEROTATION,
      OPTION_TVA, REGIME_IMPOTS
    )
    values (
      ?, ?, ?, ?, ?, ?, ?, ?,
      ?, ?,
      ?, ?, ?, ?, ?, ?,
      ?, ?,
      ?, ?,
      ?, ?
    )
    matching (UID)
    returning
      UID,
      OLD.CODE as "OLD.CODE",
      NEW.CODE as "NEW.CODE",
      OLD.NOM as "OLD.NOM",
      NEW.NOM as "NEW.NOM",
      OLD.RESPONSABLE as "OLD.RESPONSABLE",
      NEW.RESPONSABLE as "NEW.RESPONSABLE",
      OLD.ADRESSE as "OLD.ADRESSE",
      NEW.ADRESSE as "NEW.ADRESSE",
      OLD.CODE_POSTAL as "OLD.CODE_POSTAL",
      NEW.CODE_POSTAL as "NEW.CODE_POSTAL",
      OLD.VILLE as "OLD.VILLE",
      NEW.VILLE as "NEW.VILLE",
      OLD.PAYS as "OLD.PAYS",
      NEW.PAYS as "NEW.PAYS",
      OLD.TELEPHONE as "OLD.TELEPHONE",
      NEW.TELEPHONE as "NEW.TELEPHONE",
      OLD.EMAIL as "OLD.EMAIL",
      NEW.EMAIL as "NEW.EMAIL",
      OLD.TVA as "OLD.TVA",
      NEW.TVA as "NEW.TVA",
      OLD.SIRET as "OLD.SIRET",
      NEW.SIRET as "NEW.SIRET",
      OLD.APE as "OLD.APE",
      NEW.APE as "NEW.APE",
      OLD.RCS as "OLD.RCS",
      NEW.RCS as "NEW.RCS",
      OLD.FORME_JURIDIQUE as "OLD.FORME_JURIDIQUE",
      NEW.FORME_JURIDIQUE as "NEW.FORME_JURIDIQUE",
      OLD.CAPITAL_SOCIAL as "OLD.CAPITAL_SOCIAL",
      NEW.CAPITAL_SOCIAL as "NEW.CAPITAL_SOCIAL",
      OLD.HEURE_CLOTURE_QUOTIDIENNE as "OLD.HEURE_CLOTURE_QUOTIDIENNE",
      NEW.HEURE_CLOTURE_QUOTIDIENNE as "NEW.HEURE_CLOTURE_QUOTIDIENNE",
      OLD.DATE_CLOTURE_EXERCICE as "OLD.DATE_CLOTURE_EXERCICE",
      NEW.DATE_CLOTURE_EXERCICE as "NEW.DATE_CLOTURE_EXERCICE",
      OLD.PAPIER_ENTETE as "OLD.PAPIER_ENTETE",
      NEW.PAPIER_ENTETE as "NEW.PAPIER_ENTETE",
      OLD.SUFFIXE_NUMEROTATION as "OLD.SUFFIXE_NUMEROTATION",
      NEW.SUFFIXE_NUMEROTATION as "NEW.SUFFIXE_NUMEROTATION",
      OLD.OPTION_TVA as "OLD.OPTION_TVA",
      NEW.OPTION_TVA as "NEW.OPTION_TVA",
      OLD.REGIME_IMPOTS as "OLD.REGIME_IMPOTS",
      NEW.REGIME_IMPOTS as "NEW.REGIME_IMPOT"

When I run the query for the first time and the table is empty, it works as expected.
Then, when I run the query again with the very same parameters, I get this error :

---------------------------
Uibfb5
---------------------------
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 1, actual 3
Arithmetic overflow or division by zero has occurred.
GDS Code: 335544321 - SQL Code: -802 - Error Code: 1.
---------------------------
OK   
---------------------------

As it worked "like a charm" in Firebird 3.0, could this be a regression ?

I tried to cast all parameters in the returning clause : cast(NEW.CODE as VARCHAR(12)) as "NEW.CODE" with no success.

The very same error appears when the query is run with the returning clause in IBExpert.

Thank you for your help, regards

--
Pierre Y.

[mrotteveel]

Can you reproduce it with only one or two columns?

[zedalaye]

With the same table definition, this query fails the same way (in IBExpert) :

update or insert into MAGASINS(UID, CODE)
values (x'B961790496A042E9957F03A4D1C86726', 'CODE')
matching (UID) 
returning
  UID,
  OLD.CODE as "OLD.CODE",
  NEW.CODE as "NEW.CODE"

[zedalaye]

Same with a simpler table definition :

CREATE TABLE MAGASINS$TMP (
    UID   CHAR(16) CHARACTER SET OCTETS NOT NULL,
    CODE  CHAR(12) NOT NULL
)

(Run the query twice)

[zedalaye]

Interesting (?) fact : if I remove the returning clause for OLD.CODE, the query works as expected :

update or insert into MAGASINS$TMP(UID, CODE)
values (x'B961790496A042E9957F03A4D1C86726', 'CODE')
matching (UID) 
returning
  UID,
  NEW.CODE as "NEW.CODE"

[mrotteveel]

This sounds like a bug to me.

[zedalaye]

So I filled an issue: #8616

[zedalaye]

Fixed in recent Firebird 5.0.3 snapshots