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

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.

Gábriel Ákos

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