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

( bennyh | 2020. 08. 08., szo – 20:22 )

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.

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.  

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

( joco01 v | 2020. 08. 08., szo – 23:32 )

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

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

( neutrino v | 2020. 08. 09., v – 09:32 )

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

Aláírás _Franko_ miatt törölve.
neut @

( NevemTeve | 2020. 08. 09., v – 16:06 )

Szerkesztve: 2020. 08. 09., v – 16:16

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)

Hát ez elgondolkodtatott, mert én ezt csak a diasorunkból "tudom", de pl. az Oracle elég szűkszavú a művelet szemantikáját illetően:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/S…

SQL szabványhoz meg nem férek hozzá. De pl. a Postgres leírása elég egyértelmű:

https://www.postgresql.org/docs/10/queries-table-expressions.html#QUERI…