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.

  • 1504 megtekintés

( 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.)

Nem olvastam végig mindent (belenéztem már  más is leírta), szerintem is:

létrehoztál egy táblát ID mezővel, ami serial , azaz bigint az ID

[SQL]select -1*ID as x from t1

[Err] 1690 - BIGINT UNSIGNED value is out of range in '-1 * `hup`.`t1`.`ID`'

x is bigint, nem megy. 

 

illetve van egy rész a t1 descartes  szorzata saját magával, delta negativ szám.

select b.ID,
               a.ID as NextID,
               a.ID - b.ID as delta
          from (select * from T1int) as a,
               (select * from T1int) as b

 

és ebből válogat a where

 

1    1    0
1    2    1
1    3    2
1    5    4
2    1    -1
2    2    0
2    3    1
2    5    3
3    1    -2
3    2    -1
3    3    0
3    5    2
5    1    -4
5    2    -3
5    3    -2
5    5    0

 

Szóval:

CREATE TABLE T1INT (
    ID INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

insert into T1 values (1),(2),(3),(5);

és lefut ami idáig nem:

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

https://blog.claryel.hu

Explain  parancs az  IN  esetében azt irja ki hogy használ join buffert , ha IN  helyett =  van akkor meg nincs join buffer, vagyis nem hozza létre az a x b tábla descartes szorzatát ami  szerintem a bigint hibát okozza. 
Sajnos semmilyen módszert  nem tudok, amivel bele lehetne látni a belső bufferba.
Illetve lehet vitatkozni, hogy lehet e bigint az ID mező, hiszen mysql dönti el a kiértékelés folyamatát, és innetől nem lehetsz  biztos hogy mindig ugyanúgy fogja csinálni. 

https://blog.claryel.hu

( 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.

zászló, zászló, szív

Köszönjük, Emese.

Felületes olvasók kedvéért: azt kellene kitalálni, hogy követett-e el hibát az optimizer, amikor a SELECT-listát olyan adatokra is kiszámolta, amikre nem teljesült a WHERE-feltétel? Ha az a válasz, hogy 'nem', akkor valószínűleg ez is jó:

SELECT 1/fld WHERE fld<>0; ===> Error: Division by zero

Ha Error: Division by zero-t ad akkor továbbra is rossz.
Kiprobáltam mysql-lel , az NULL értéket ad 1/0 -ra, az fld <>0  - t kezeli. 

De az eredeti kérdésre visszatérve, ID nem lehet bigint mert nem tételezheted fel azt az optimalizációt ami neked a legjobb.
A lekérés igy néz ki, hozd létre a x b descartes szrzatát és ebből válogass le. A másik az a feltételezés hogy ne hozz létre olyat ami nem  felel meg majd a where feltételnek. Nyilván ez a gyorsabb, de ez már egy előfeltétel. 

https://blog.claryel.hu

Ez dicséretes, de azért említsük meg, hogy a SELECT nem program, hanem specifikáció: 'kedves DB, keress olyan sorokat, amikre ezek a feltételek teljesülnek'

Szóval alapvetően nem jogtalan, hogy a DB-motor saját esze alapján kitalál valamilyen végrehajtási módot, most csak az a kérdés, hogy van-e valamilyen szabály arra, hogy pl. a SELECT-lista kiértékelése a WHERE ellenőrzése után kell következzen, vagy még ez sincs előírva.

Itt a  példa:

ha  IN van akkor bigint hiba, 

ha = , akkor nincs hiba 

 

..... from (select * from T1) as a, (select * from T1) as b where a.ID      IN     (select min(ID) from T1 where ID > b.ID )

Kérdés hogy millió  sornál mi történik ? 

Az IN-es verziónál hibának tekinti  pl kérdező is, , miért kerül elő negativ érték , hiszen beállította, azaz a  where feltétel nem engedi  a negatív értékeket.  Előfeltételezés.

https://blog.claryel.hu

( Raynes v | 2025. 03. 01., szo – 17:39 )

Szerkesztve: 2025. 03. 01., szo – 17:42

Ez a gikszer nem amiatt van, mert már a tábla létrehozásakor InnoDB-t adtál meg engine-nek? Mert a lekérdezések jónak tűnnek, SQLite-ban szépen le is futnak, és a várt eredményt szállítják le.

“Windows 95/98: 32 bit extension and a graphical shell for a 16 bit patch to an 8 bit operating system originally coded for a 4 bit microprocessor, written by a 2 bit company that can't stand 1 bit of competition.”

Szerimntem nem, futtasd le where nélkül, mindjárt nem lesz jó:

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
        
       ) as c
 

https://blog.claryel.hu

És ez se ha ID bigint unsigned,talán jobban látszik, hiába where > 0 mégis BIGINT UNSIGNED value is out of range hiba lesz. 
Pedig ott  a where delta >0 :

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  
           
       ) as c where delta >0
 

Mert nem program , ahol a where feltétel megfelel valamiféle if feltételnek, amivel kizárod a negativ értékeket. 
Megcsinálja az a és b halmaz szorzatát, abban meg ott vannak a negatív értékek.
 

https://blog.claryel.hu

Nem akarlak titeket elkeseríteni, de a SQLite-ban azok a lekérdezések is jók, amiket most írtatok ebben a szálban, hibaüzenet nélkül lefutnak, és azokat az eredményeket adják, amit elvár az ember:

sqlite> 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

       ) as c;
1|1|0
2|1|-1
3|1|-2
5|1|-4
1|2|1
2|2|0
3|2|-1
5|2|-3
1|3|2
2|3|1
3|3|0
5|3|-2
1|5|4
2|5|3
3|5|2
5|5|0
sqlite> select 1-id from T1;
0
-1
-2
-4

Azért mondom, hogy itt valami MariaDB vagy még inkább InnoDB engine specifikus korlátról, vagy adatkonverzióról van szó, ami a hibát okozza.

“Windows 95/98: 32 bit extension and a graphical shell for a 16 bit patch to an 8 bit operating system originally coded for a 4 bit microprocessor, written by a 2 bit company that can't stand 1 bit of competition.”

Akkor az sqllite csinál adatkonverziót, mysql meg nem. Van ugye t1 tábla ID mezője, ami serial, másképpen unsigned bigint. Csinálunk egy műveletet két unsigned biginttel akkor az eredmény is unsigned bigint mysql-ben. A negativ értékek miatt hibaüzenetet ad mysql, nincs konverzio, unsigned bigint forrás marad unsigned bigint. Ha mysql -ben ID integer , ugyanezt kapjuk mint sqllite -ban. 
Minden jó.

ID sqllite-ban is unsigned bigint ? 
De a probléma az volt hogy egy IN helyetti = jel változtatás eltüntette a hibát, (illetve egy where feltétel előhozta) mert az optimizer nem ugyanúgy hozta létre az eredményt, nem csinált join puffert , igy aztán nem volt hiba sem. IN esetén meg csinált, ami elsőre tényleg érthetetlen.

https://blog.claryel.hu

Igen, de a lényege a topiknak az volt, hogy mi okozza a hibát. Itt rá a válasz, adattípus konvertálása automatikus növeléskor. Ezen a nyomvonalon elindulhat a kérdező, hogy megoldja a problémát.

“Windows 95/98: 32 bit extension and a graphical shell for a 16 bit patch to an 8 bit operating system originally coded for a 4 bit microprocessor, written by a 2 bit company that can't stand 1 bit of competition.”

 Nincs  automatikus konvertálás. , ha lenne nem lenne hiba. A kiértékelés sorrendje más. Az a x b tábla (T1 önkapcsolása) descart szorzatában ott vannak a negativ értékek.  (te is listáztad) Attől száll el, mert delta is bigint.  A sqllite tábla nem játszik , (ott nem bigint a mező,)  itt most kifejezetten unsigned bigint mező van, nincs semmilyen konvertálás. Konvertálással minden verzió működik. A kérdező táblája unsigned bigint, ezen nem változtatunk.  Ha meg nincs join puffer, mert a where feltételt előbb jut "érvényre", nincs join buffer, nincs negatív érték, nincs hiba. Explainnel ennyi látszik.
 

A megoldás az hogy használj integer mezőt , legalábbis ebben a példában,  mert nem lehetsz benne biztos mikor jön a hibaüzenet . Lehet hogy 10 sornál nem , de lehet hogy 1000 nél már igen vagy csak 10 0000 -nél, mer megváltozik a kiértékelés,amire nincs hatásod.

SQLlite-tal ilyen hiba nem keletkezik. 

https://blog.claryel.hu