MariaDB "bigint unsigned value out of range"

Adatbázis: SQL, XML DB

Kedves tapasztalt fórumtársak!

A jelenség lecsupasztíva:

Adott egy tábla és benne sorok

create table T1 ( ID serial, primary key (ID) ) engine=InnoDB;
insert into T1 values
(1),(2),(3),(5);

Feladat: listázandók az ID-k, melléjük pedig a következő létezi ID érték (mint NextID), valamint a kettő különbsége (mint delta):

select b.ID,
               a.ID as NextID,
               a.ID - b.ID as delta
          from (select * from T1) as a,
               (select * from T1) as b
         where a.ID in (select min(ID)
                          from T1
                         where ID > b.ID
                       )
;

Elvárásszerűen működik. Probléma akkor van, ha allekérdezésbe teszem, és az eredménylistát szűrném az egynél nagyobb delta értékekre:

select *
  from (select b.ID,
               a.ID as NextID,
               a.ID - b.ID as delta
          from (select * from T1) as a,
               (select * from T1) as b
         where a.ID in (select min(ID)
                          from T1
                         where ID > b.ID
                       )
       ) as c
 where delta > 1;

Erre azt feleli, hogy ERROR 1690 (22003): BIGINT UNSIGNED value is out of range. Miért?

Köszi:
KEA.

( kruska v | 2024. 01. 19., p – 18:11 )

Próbáltad már castolni előjelesre?

CAST(a.ID AS SIGNED) - CAST(b.ID AS SIGNED) as delta

( bandy | 2024. 01. 19., p – 18:19 )

Ha megcseréled a delta-t kiszámító műveletet és delta < -1 -re szűrsz?

( NevemTeve | 2024. 01. 19., p – 18:36 )

Kezdetnek egy egyszerűsítés:

- from (select * from T1) as a, (select * from T1) as b
+ from T1 a, T1 b

( persicsb | 2024. 01. 19., p – 19:09 )

Gondolom azért, mert a

a.ID - b.ID as delta

értéke negatív lesz, az meg nem fér el előjel nélküli típusban

( csizmadia_zoltan v | 2024. 01. 20., szo – 09:53 )

Esetleg ha a WHERE helyett a HAVING delta > 1 -t próbálnád ki.

( Fisher v | 2024. 01. 20., szo – 11:07 )

Szóval... az gondolom megvan, hogy a chatgpt ad egy megoldást (működik is, nem úgy mint a fentiek), el is magyarázza, de egy mukkot nem értek belőle, szóval lehet hogy csak kamu.

( kea | 2024. 01. 20., szo – 23:47 )

Hm. a ...having delta>1 működik. De ettől még nem értem, mert a NextID mindig legalább eggyel nagyobb az ID-nél (a dolgok természetéből adódóan),
tehát a NextID-ID >= 1 kell legyen minden egyes sorra. Ez tényszerűen is világosan látszik, ha a belső lekérdezést külön futtatjuk. És igen, meg lehet csinálni másképpen is, ami zökkenő nélkül megcsinálja a feladatot. Viszont én megérteni szeretném, hogy mi történik a háttérben (és miért). Szóval ha erre lenne valami jó nyom, köszönném.

Jelenleg a max(id) az öt (és az is nyolc byte-on, előjel nélkül). A fenti parancsban nem merül föl a nextid-je, mert a feltétel ott már nem fog teljesülni (a legkisebb a nála nagyobbak közül) semmire. Másképpen csinálva az egészet meg oda NULL kerül. De hogy mehet mínuszba? Milyen belső kiértékelési, végrehajtási lépésben? Mégpedig olyanban, ami az eredeti lekérdezésben nem kerül elő, csak akkor, ha -- változatlan formában -- allekérdeésbe teszem, és where-rel szűrném. Ugyanez, ugyanott a where helyett having-gel megintcsak műkódik...

A fentebb írt egyszerűsítést is alkalmazva mi most a legegyszerűbb utasítás, ami mutatja a hibát?

És ha abban elvégzed az itt írt típuskonverziót?

Kieg: az nem segít, ha olyasmit idézel be, ami működik, olyat én is tudok, pl.:

create table T1 ( ID serial, primary key (ID) ) engine=InnoDB;
insert into T1 values
(1),(2),(3),(5);

select b.ID, a.ID as NextID, a.ID - b.ID as delta
from T1 a, T1 b
where a.ID in (select min(ID) from T1 where ID > b.ID);

Itt próbáltam: https://onecompiler.com/mysql/4223kvuas

create table T1 ( ID serial, primary key (ID) ) engine=InnoDB;
insert into T1 values
(1),(2),(3),(5);

select *
from (
select b.ID, a.ID as NextID, a.ID - b.ID as delta
from T1 a, T1 b
where a.ID = (select min(ID) from T1 where ID > b.ID)
) as c
where delta>1

neked aztan fura humorod van...

Köszönöm, közben és reprodukáltam: https://onecompiler.com/mysql/4224y8erc

create table T1 ( ID serial, primary key (ID) ) engine=InnoDB;
insert into T1 values (1),(2),(3),(5);

select * from (
select b.ID, a.ID as NextID, a.ID-b.ID as delta
from T1 a, T1 b
where a.ID in (select min(ID) from T1 where ID > b.ID)
) n where n.delta>=1;

eredménye:

ERROR 1690 (22003) at line 4:
BIGINT UNSIGNED value is out of range in '(`sandbox_db`.`a`.`ID` - `db_3zzvf6cjm_4224zg3vj`.`b`.`ID`)'

Elsőre azt mondanám, hogy az optimizer átrendezte a dolgot, a kivonás elvégzését a WHERE kiértékelése elé hozta.

teszteld úgy a megoldásodat, hogy előtte:
insert into T1 values (9), (7), (123), (42), (8);
vagy bármilyen random sorrendű(!) értékeket raksz a táblába. Ugyanis semmi nem garantálja, hogy a rowid alapján rendezve a sorokat az ID-k monoton növekedő sorrendben lesznek (egyébként meg elméletileg semmilyen garancia nincs arra, hogy a select eredménye sorrendet tekintve bármilyen rendezettséget mutasson)

Akkor fussunk neki mégegyszer... Honnan jönnek ezek az ID-k? Futnak-e párhuzamosan insert/update DML-ek? Lehetséges-e olyan, hogy egy insert/update DML rollback-re fut? A logikailag monoton növekedő és egyedi az az id létrehozásakor megvan, de a táblába már nem garantált, hogy ilyen sorrendben kerül bele, és erre nem is építhetsz.

A NextID amikor generálod, valóban legalább 1-gyel nagyobb, mint az előző. De vajon a DML, ami használja, az  hamarabb jut-e el a commit-ig, mint egy másik, ami vele párhuzamosan futva még egy korábbi ID-t rak be a táblába? És vajon a select kimenetében számíthatsz-e bármilyen garantált sorrendre?
 

De a tesztesetben szerintem igen. Ráadás: ha összevissza lennének az ID-k, a NextID (mint az ID-nél nagyobbak közül a legkisebb) akkor sem lehet egyenlő az ID-vel, és kisebb sem a belső where miatt. (Ha jobban belegondolunk, mivel PK, ha egyenlőség reprodukálható módon előfordulhatna, az nagy durranás lenne.)

( x-daemon | 2024. 01. 21., v – 20:45 )

Szerkesztve: 2024. 01. 21., v – 21:16 
ha in helyett = van, akkor jo. sqlsrv-n mukodik in-el is

select *
  from (select b.ID,
               a.ID as NextID,
               a.ID - b.ID as delta
          from (select * from T1) as a,
               (select * from T1) as b
         where a.ID = (select min(ID)
                          from T1
                         where ID > b.ID
                       )
       ) as c
 where delta > 1;

neked aztan fura humorod van...

az onecompiler.com/mysql-en probaltam ki, ahol NevemTeve is

ez van beirva es jol hozza:

create table T1 ( ID serial, primary key (ID) ) engine=InnoDB;
insert into T1 values
(1),(2),(3),(5);

select *
from (
select b.ID, a.ID as NextID, a.ID - b.ID as delta
from T1 a, T1 b
where a.ID = (select min(ID) from T1 where ID > b.ID)
) as c
where delta>1

a te szandekod valojaban az "=" volt, ez latszik a min(ID)-bol is, hogy csak 1 erteket vartal.

neked aztan fura humorod van...

( kea | 2024. 01. 21., v – 22:31 )

Esetemben: /usr/bin/mysql  Ver 15.1 Distrib 10.0.37-MariaDB, for Linux (x86_64) using readline 5.1

( kassaiviktor | 2024. 01. 22., h – 05:01 )

ez most komoly?

prog.hu megszünt? vagy mi van?

4 és fél éve csak vim-et használok. elsősorban azért, mert még nem jöttem rá, hogy kell kilépni belőle.

( gabrielakos v | 2025. 02. 26., sze – 10:41 )

Ami itt megy azért minden normális infó bsc-n oktatott sql tárgyból bukó járna, szerintem.

Gábriel Ákos