DB2 - 选择计数（*）在子查询上无法正常工作
以下是 2查询应该带来相同的结果，但不幸的是，情况并非如此。 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实际上是 946 （第二查询是右）。
我猜DB2不支持 count 子查询，两次（public.pessoa）加上的组。
你们经历了同样的情况吗？ 的语法第一个查询不正确？还是DB2中有一个错误？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论