Hi all, I have a problem with join which drive me crazy :


3 tables AM_GRUPPI, AM_SGRUPPI, AM_FIGURE
AM_GRUPPI 1-->* AM_SGRUPPI 1-->* AM_FIGURE




the follow query returns 28 records


SELECT
t0.ID, t0.CODICE_SPK,
t1.ID, t1.CODICE_SPK,
t2.ID, t2.ISARTICOLO


FROM AM_GRUPPI t0 JOIN AM_SGRUPPI t1 ON t0.ID = t1.GRUPPO_ID
LEFT JOIN AM_FIGURE t2 ON t1.ID = t2.SGRUPPO_ID


WHERE t0.CODICE_SPK='27' and (t2.id IS NULL or t2.ISARTICOLO = 0)


Execution plan:
Field #01: AM_GRUPPI.ID Alias:ID Type:BIGINT
Field #02: AM_GRUPPI.CODICE_SPK Alias:CODICE_SPK Type:STRING(2)
Field #03: AM_SGRUPPI.ID Alias:ID Type:BIGINT
Field #04: AM_SGRUPPI.CODICE_SPK Alias:CODICE_SPK Type:STRING(3)
Field #05: AM_FIGURE.ID Alias:ID Type:BIGINT
Field #06: AM_FIGURE.ISARTICOLO Alias:ISARTICOLO Type:SMALLINT
PLAN JOIN (JOIN (T0 INDEX (UK_AM_GRUPPI_CODICE), T1 INDEX
(FK_AM_SGRUPPI_GRUPPO)), T2 INDEX (FK_AM_FIGURE_SGRUPPO))


Result (28 items):


28 27 342 PSW [null] [null]
28 27 343 POJ [null] [null]
28 27 344 PIW [null] [null]
28 27 345 POE 96 0
28 27 346 POF [null] [null]
28 27 347 POI 52 0
etc...


//---------------------------------------------
Removing the last table (LEFT JOIN AM_FIGURE), the query returns 29
records. One record more than previous query with left join.


SELECT
t0.ID, t0.CODICE_SPK,
t1.ID, t1.CODICE_SPK


FROM AM_GRUPPI t0 JOIN AM_SGRUPPI t1 ON t0.ID = t1.GRUPPO_ID


WHERE t0.CODICE_SPK='27'


Execution plan
Field #01: AM_GRUPPI.ID Alias:ID Type:BIGINT
Field #02: AM_GRUPPI.CODICE_SPK Alias:CODICE_SPK Type:STRING(2)
Field #03: AM_SGRUPPI.ID Alias:ID Type:BIGINT
Field #04: AM_SGRUPPI.CODICE_SPK Alias:CODICE_SPK Type:STRING(3)
PLAN JOIN (T0 INDEX (UK_AM_GRUPPI_CODICE), T1 INDEX (FK_AM_SGRUPPI_GRUPPO))


I think the first query should return 29 records.
I have already done a backup/restore cycle before testing.
So, I'm only tired or it's a real problem ?
Thank you in advance