Oracle natural join bug

Egyetemen legutóbbi félévben volt Adatbázisok I. nevű tárgy, ahol az Oracle nevű csodát használjuk gyakorlaton. Még a félév szorgalmi időszakában, ZH közben akadtam egy furcsaságra, nevezetesen hogy két szerintem ekvivalens lekérdezés különböző eredményeket adott. Furcsálltam a dolgot...

Aztán újra és újra lecsekkoltam, leredukáltam a példát egy minimálisra (ld. lejjebb), és akkor már elég egyértelmű volt, hogy ez egy bug, tekintve, hogy Postgresben futtatva mindez a helyes eredményt adja. Mindenesetre nagyon furcsán érzem most magam, szerintem egy piacvezető adatbáziskezelőben egy egyetemista nem kéne, hogy hibát találjon. :D És nem csak az egyetemen használt 12c-ben, hanem még a 19c-ben is ez a helyzet (a LiveSQL.oracle.com oldalon ki lehet próbálni). Beküldtem egy bugreportot a LiveSQL oldalon, de igazából lehet, hogy nem kéne ingyen javítgatnom a hulladék terméküket... :D

Na és akkor a bug:

Szerk.: hogyan lehet ebben a blogmotorban jelezni, hogy a listában eddig mutassa a bejegyzésemet?

Szerk. 2: done, köszönöm!

Ha két natural joinnak zárójellel jelzem a precedenciáját (ami egyébként nem számít, mert hát a natural join az összes inner joinnal együtt asszociatív. dehát a ZH közben nem volt időm ezen gondolkodni :) ), akkor mezők kiválasztása (szelekció) vagy aggregáció esetén több sort ad vissza, mint kéne. Explain Plan nézetben látszik, hogy a "külső" join feltételét elfelejti csekkolni, és simán cross joinnak veszi.

Nem értek hozzá, úgyhogy csak arra tudok gondolni, hogy a query optimizer túl okos akart lenni.

Az SQL és az eredmény:

DROP TABLE r

Table dropped.

DROP TABLE s

Table dropped.

DROP TABLE t

Table dropped.

CREATE TABLE r ( 
    r_id INTEGER, 
    r_data INTEGER 
)

Table created.

CREATE TABLE s ( 
    s_id INTEGER, 
    s_data INTEGER 
)

Table created.

CREATE TABLE t ( 
    r_id INTEGER, 
    s_id INTEGER, 
    t_data INTEGER 
)

Table created.

INSERT INTO r VALUES (2,4)

1 row(s) inserted.

INSERT INTO r VALUES (3,9)

1 row(s) inserted.

INSERT INTO s VALUES (2,8)

1 row(s) inserted.

INSERT INTO s VALUES (3,27)

1 row(s) inserted.

INSERT INTO t VALUES (2,2,1)

1 row(s) inserted.

-- Sima query működik a zárójelekkel:

SELECT 
    * 
FROM 
    r NATURAL JOIN (s NATURAL JOIN t)

R_ID	R_DATA	S_ID	S_DATA	T_DATA
2	4	2	8	1

-- Szelekcióval már nem működik:

SELECT 
    s_data 
FROM 
    r NATURAL JOIN (s NATURAL JOIN t)

S_DATA
8
8

-- Zárójelek nélkül viszont igen:

SELECT 
    s_data 
FROM 
    r NATURAL JOIN s NATURAL JOIN t

S_DATA
8

-- Ez rossz:

SELECT 
    COUNT(*) 
FROM 
    r NATURAL JOIN (s NATURAL JOIN t)

COUNT(*)
2

-- Ez jó:

SELECT 
    COUNT(*) 
FROM 
    r NATURAL JOIN s NATURAL JOIN t

COUNT(*)
1

staccato blogja
A hozzászóláshoz be kell jelentkezni
627 megtekintés

Vessetek meg, én Ora alatt nem joinoltam, vagyis nem kulcsszóval hanem where feltételben, valamint régen mintha nem is lett volna ajánlott. Outer joint meg lehetett + jellel is jelölni, de jó pár éve már MSSQL-t püfölök.

Színes vászon, színes vászon, fúj!

Kérem a Fiátot..

0 szavazat

A hozzászóláshoz be kell jelentkezni

Uristen.

Nem ajanlott max a FULL OUTER JOIN hasznalata lehetett performance okokbol. Ami ott legalabb van, de matematikailag levezetheto, hogy miert nem nagyon lehet nagysagrenddel gyorsabbra megirni.

0 szavazat

A hozzászóláshoz be kell jelentkezni

Nekem úgy rémlik, full outert eleve csak kulcsszóval lehetett, mert nem lehetett az egyenlőség vizsgálat mindkét operandusánál (+).

Amúgy azt megbeszéltük, hogy Ora-hoz nem értek :) (más kérdés, hogy így is időben és pontosan lefutott minden).

Ha már szaki vagy, meséld már el miért viselkedik úgy a két lekérdezés a témaindítónál, ahogy, mert én is kíváncsi vagyok.

Színes vászon, színes vászon, fúj!

Kérem a Fiátot..

0 szavazat

A hozzászóláshoz be kell jelentkezni

(Ez egy mellékszál, valamiért úgy alakult, hogy Oracle-s hagyományokban a JOIN-feltételt gyakran WHERE-feltételként fogalmazzák meg, hogy pl SELECT ... FROM oktato o,diak d,tanitja t WHERE o.id=t.oktato_id AND t.diak_id=d.id ettől ez még se nem gyorsabb, se nem különböző, mintha JOIN ... ON... lenne)

0 szavazat

A hozzászóláshoz be kell jelentkezni

A (+) operátor, bár még mindig működik, már az Oracle által sem ajánlott a használata - én pl. életemben nem használtam, csak régi lekérdezésekben találkozom vele.

0 szavazat

A hozzászóláshoz be kell jelentkezni

9.2-es Ora-n dolgoztam, az se most volt.

Színes vászon, színes vászon, fúj!

Kérem a Fiátot..

0 szavazat

A hozzászóláshoz be kell jelentkezni

Azt hiszem ora esteben pont hogy az volt az ajanlott,
mysql eseteben volt ajanlott a where .

manapsag nincs lenyeges kulombseg, izelsek a pofonok ..

Amit nem lehet megirni assemblyben, azt nem lehet megirni.

0 szavazat

A hozzászóláshoz be kell jelentkezni

Én azt gondolnám, hogy pont a zárójel nélküli a rossz. Ugyanis az r és s táblának nincs közös oszlopa, ezért szorzatot kapsz vissza, ami megtöbbszörözi a sorokat.

0 szavazat

A hozzászóláshoz be kell jelentkezni

Megtöbbszörözi valóban, de ha ezt utána összenaturaljoinolod a t-vel, azzal ennek két azonos nevű oszlopa lesz, és a példában mindkét oszlop csak egy kombinációban egyezik meg. Tehát a végső eredmény így is, úgy is egy sor.

0 szavazat

A hozzászóláshoz be kell jelentkezni

Ha jól emlékszek, az egyetemi tankönyv szerint a select halmazokat ad vissza duplikátumok nélkül, de minden adatbázis motor, amit eddig láttam, vígan duplikálta a sorokat. Ha egy bármilyen select dupla sorokat okozott, azt további join nem fogja eltüntetni, csak egy distinct kulcsszó.

0 szavazat

A hozzászóláshoz be kell jelentkezni

Én speciel megértem a témaindító frusztrációját, azért pl ha ott nem count hanem sum lenne, már tényleg nagyon nem lenne mindegy. Ugyanakkor nem érdemes sokat sem rugózni, ahogy megboldogult Munkás Pityu mondta az almás kapánál, jól kell fogni :)

Színes vászon, színes vászon, fúj!

Kérem a Fiátot..

1 szavazat

A hozzászóláshoz be kell jelentkezni

Szerk.: hogyan lehet ebben a blogmotorban jelezni, hogy a listában eddig mutassa a bejegyzésemet?

Úgy, hogy a bevezetőt a bevezetőbe, a többit pedig a törzsbe írod.

https://i.imgur.com/fC7iMP8.png

1904.04.08.
RIP Jákub.
neut @

2 szavazat

A hozzászóláshoz be kell jelentkezni

Köszi!

0 szavazat

A hozzászóláshoz be kell jelentkezni

Kicsit megváltoztattam a táblaneveket:

CREATE TABLE staccato_tanarok (
    r_id INTEGER,
    r_data INTEGER
);

CREATE TABLE staccato_diakok (
    s_id INTEGER,
    s_data INTEGER
);

CREATE TABLE staccato_tanitja (
    r_id INTEGER,
    s_id INTEGER,
    t_data INTEGER
);

INSERT INTO staccato_tanarok VALUES (102,4);
INSERT INTO staccato_tanarok VALUES (103,9);

INSERT INTO staccato_diakok VALUES (9002,8);
INSERT INTO staccato_diakok VALUES (9003,27);

INSERT INTO staccato_tanitja VALUES (102,9002,1);

Namostan az első, amit nem értek, hogy ez miért működik:

eredet@orcl> select * from staccato_tanarok natural join staccato_diakok;
R_ID R_DATA S_ID S_DATA
---- ------ ---- ------
 102      4 9002      8
 102      4 9003     27
 103      9 9002      8
 103      9 9003     27
4 rows selected (0.01 seconds)

Ennek szerintem vissza kellett volna dobnia azzal, hogy 'not applicable'

Mindenesetre az inkonzisztenciát látni vélem, pl:

eredet@orcl> select count(*) from staccato_tanitja natural join (staccato_tanarok natural join staccato_diakok);
COUNT(*)
--------
       4
1 row selected (0.00 seconds)

eredet@orcl> select count(*) from (staccato_tanarok natural join staccato_diakok) natural join staccato_tanitja;

COUNT(*)
--------
       1
1 row selected (0.00 seconds)

2 szavazat

A hozzászóláshoz be kell jelentkezni

"Ennek szerintem vissza kellett volna dobnia azzal, hogy 'not applicable'"

Írtam fentebb: natural join esetén, ha nincs közös oszlop, szorzatot kapsz vissza.

1 szavazat

A hozzászóláshoz be kell jelentkezni

Értem, hogy mi a működés, csak nem helyeslem... Meg kellene nézni, mit ír a szabvany. (Ha pl. azt, hogy ez UB, akkor már meg is van a válasz minden kérdésre.)

0 szavazat