1 year ago
#384409
Matheus Santz
DB2 - Select count(*) not working properly on subquery
Here follows 2 querys that should bring the same result, but unfortunately thats not the case. They have just 1 different line:
1.
Result = 403
SELECT count(*)
FROM (SELECT CONSULTORA.ID CONSULTORA_ID, FIDC.ID FIDC_ID
FROM public.PESSOA CONSULTORA, public.PESSOA FIDC, public.PESSOA_NEO4J pnj
WHERE ((pnj.CNPJ = CONSULTORA.CPF_CNPJ AND pnj.FK_ID_FIDC = FIDC.ID)
OR (pnj.CNPJ = FIDC.CPF_CNPJ AND pnj.FK_ID_FACTORING = CONSULTORA.ID))
AND CONSULTORA.TIPO = 'EC'
AND CONSULTORA.TIPO_ENTIDADE_CREDITO IN ('FACTORING', 'SECURITIZADORA')
AND FIDC.TIPO = 'EC'
AND FIDC.TIPO_ENTIDADE_CREDITO = 'FIDC'
GROUP BY CONSULTORA.ID, FIDC.ID)
Result = 946
SELECT count(*)
FROM (SELECT CONSULTORA.ID CONSULTORA_ID, FIDC.ID FIDC_ID
FROM public.PESSOA CONSULTORA, public.PESSOA FIDC, public.PESSOA_NEO4J pnj
WHERE ((pnj.CNPJ = CONSULTORA.CPF_CNPJ AND pnj.FK_ID_FIDC = FIDC.ID)
OR (pnj.CNPJ = FIDC.CPF_CNPJ AND pnj.FK_ID_FACTORING = CONSULTORA.ID))
AND CONSULTORA.TIPO = 'EC'
AND CONSULTORA.TIPO_ENTIDADE_CREDITO IN ('FACTORING', 'SECURITIZADORA')
AND FIDC.TIPO = 'EC'
AND FIDC.TIPO_ENTIDADE_CREDITO = 'FIDC'
GROUP BY CONSULTORA.ID, FIDC.ID
ORDER BY CONSULTORA.ID, FIDC.ID) -- Different Line
Execution Plan [1]:
Return 1 1.000 162.709
Group By 2 1.000 162.708
Table Queue 3 1.000 162.708
Group By 4 0.000 162.688
Group By 5 0.000 162.688
Table Scan 6 0.000 162.688
Sort 7 0.000 162.688
Nested loop Join 8 0.023 162.687
Fetch 20 137,293.000 14.305
[NULL] TA: PUBLIC.PESSOA_NEO4J [NULL] 137,293.000 [NULL]
RID Scan 21 0.026 14.121
Sort 24 0.026 7.061
Index scan 25 137,293.000 7.060
[NULL] IX: DB2INST1.PESSOA_NEO4J_CNPJ_IDX3 [NULL] 137,293.000 [NULL]
Sort 22 0.000 7.061
Index scan 23 137,293.000 7.060
[NULL] IX: DB2INST1.PESSOA_NEO4J_CNPJ_IDX2 [NULL] 137,293.000 [NULL]
Rebalance rows between SMP subagents 9 0.023 141.520
Hash Join 10 0.235 141.520
Fetch 16 9,083,719.000 70.776
[NULL] TA: PUBLIC.PESSOA [NULL] 9,083,719.000 [NULL]
RID Scan 17 1,742.000 8.379
Sort 18 1,742.000 8.379
Index scan 19 9,083,719.000 7.886
[NULL] IX: DB2INST1.PESSOA_TIPO_IDX [NULL] 9,083,719.000 [NULL]
Rebalance rows between SMP subagents 11 0.235 70.744
Fetch 12 9,083,719.000 70.743
[NULL] TA: PUBLIC.PESSOA [NULL] 9,083,719.000 [NULL]
RID Scan 13 1,742.000 8.379
Sort 14 1,742.000 8.379
Index scan 15 9,083,719.000 7.886
[NULL] IX: DB2INST1.PESSOA_TIPO_IDX [NULL] 9,083,719.000 [NULL]
Execution Plan [2]
Return 1 1.000 162.708
Group By 2 0.000 162.708
Group By 3 0.000 162.708
Table Queue 4 0.000 162.708
Group By 5 0.000 162.688
Nested loop Join 6 0.023 162.688
Fetch 19 137,293.000 14.305
[NULL] TA: PUBLIC.PESSOA_NEO4J [NULL] 137,293.000 [NULL]
RID Scan 20 0.026 14.121
Sort 23 0.026 7.061
Index scan 24 137,293.000 7.060
[NULL] IX: DB2INST1.PESSOA_NEO4J_CNPJ_IDX3 [NULL] 137,293.000 [NULL]
Sort 21 0.000 7.061
Index scan 22 137,293.000 7.060
[NULL] IX: DB2INST1.PESSOA_NEO4J_CNPJ_IDX2 [NULL] 137,293.000 [NULL]
Table Scan 7 0.023 141.521
Sort 8 0.023 141.520
Hash Join 9 0.235 141.520
Fetch 15 9,083,719.000 70.776
[NULL] TA: PUBLIC.PESSOA [NULL] 9,083,719.000 [NULL]
RID Scan 16 1,742.000 8.379
Sort 17 1,742.000 8.379
Index scan 18 9,083,719.000 7.886
[NULL] IX: DB2INST1.PESSOA_TIPO_IDX [NULL] 9,083,719.000 [NULL]
Rebalance rows between SMP subagents 10 0.235 70.744
Fetch 11 9,083,719.000 70.743
[NULL] TA: PUBLIC.PESSOA [NULL] 9,083,719.000 [NULL]
RID Scan 12 1,742.000 8.379
Sort 13 1,742.000 8.379
Index scan 14 9,083,719.000 7.886
[NULL] IX: DB2INST1.PESSOA_TIPO_IDX [NULL] 9,083,719.000 [NULL]
Running only the subquery, the row count is actually 946 (so the second query is right).
I guess DB2 does not support count over subquery with same table twice (public.PESSOA) plus GROUP BY.
Do you guys went through the same situation? Is the syntax of first query incorrect? Or probably there is a bug in DB2?
group-by
count
subquery
db2-luw
0 Answers
Your Answer