Oracle 在SELECT子句中使用DISTINCT的PARTITION BY求和会产生意外结果

嗨，汤姆，
当查询中有DISTINCT子句时，我们会看到一个非常奇怪的问题，即PARTITION BY语句的总和。我可以用下面的例子来说明这个问题:
假设有一个具有以下结构的库存表:

创建表TEST_STOCK (item_id号，location_id号，数量号);

请注意，该表可以具有给定item_id和location_id组合的重复数据。

我们必须在我们的用户界面中的某个位置显示给定项目的总数量。我们的代码中有以下SQL (遗留代码):

选择不同的项目id，org_id，
总和 (数量) 超过 (按org_id，item_id分区) 作为sum_quantity
从测试 _ 库存ts
其中item_id = :ITEM_ID
和org_id = :ORG_ID;

这个查询工作正常。如果我们为绑定变量传递值1和1 (例如)，则sum_quantity列返回值10。

在运行时，我们将在上述查询中包含一个动态EXISTS子句，以过滤掉一些结果。由于EXISTS子句被添加到现有标准之上，我们希望结果的sum_quantity列中不应有增量。但是我们观察到sum_quantity列会根据EXISTS子句获得两倍，三倍甚至更多的值。例如，让我们考虑EXISTS子句是

存在 (从EMP_TEST中选择1，其中TS.item_id在 (从双联合中选择1从双联合中选择2))

使用上面的EXISTS子句，SUM_QUANTITY列的值乘以EMP_TEST表中的记录数。因此，如果EMP_TEST表中的reords数量为6，则该列显示的值为60。

如果我们从SELECT子句中删除DISTINCT子句，则结果看起来不错。

您能告诉我们这里缺少什么吗？

DMLs进入测试 _ 库存:

插入到TEST_STOCK值 (1,1，10)
插入到TEST_STOCK值 (1,2，9)
插入到TEST_STOCK值 (2,1，10)
插入到TEST_STOCK值 (2,2，9)

EMP_TEST的DDL和DMLs:

创建表EMP_TEST (emp_id号，dept_id号);
插入EMP_TEST值 (1,1)
插入EMP_TEST值 (2,1);
插入EMP_TEST值 (3,2);
插入EMP_TEST值 (4,2);

谢谢，
帕万。

嗯，确实如此!

create table TEST_STOCK (item_id number, org_id number, QUANTITY number); 
create table EMP_TEST (EMP_ID number, DEPT_ID number);

insert into TEST_STOCK values (1,1,10);
insert into TEST_STOCK values (1,2,9);
insert into TEST_STOCK values (2,1,10);
insert into TEST_STOCK values (2,2,9);

insert into EMP_TEST values (1,1);
insert into EMP_TEST values (2,1);
insert into EMP_TEST values (3,2);
INSERT INTO EMP_TEST VALUES (4,2);
commit;

var item_id number;
var org_id number;

exec :item_id := 1;
exec :org_id := 1;

SELECT DISTINCT item_id , org_id,
   SUM(quantity) OVER (PARTITION BY org_id,item_id) as sum_quantity
FROM test_stock ts
WHERE item_id = :ITEM_ID
AND org_id = :ORG_ID;

ITEM_ID  ORG_ID  SUM_QUANTITY  
1        1       10  

SELECT 
   item_id , org_id,
   SUM(quantity) OVER (PARTITION BY org_id,item_id) as sum_quantity
FROM test_stock ts
WHERE item_id = :ITEM_ID
AND org_id = :ORG_ID
and  EXISTS (
  SELECT 1 from EMP_TEST WHERE TS.item_id in (select 1 from DUAL union select 2 from DUAL)
);

ITEM_ID  ORG_ID  SUM_QUANTITY  
1        1       10 

SELECT 
   distinct
   item_id , org_id,
   SUM(quantity) OVER (PARTITION BY org_id,item_id) as sum_quantity
FROM test_stock ts
WHERE item_id = :ITEM_ID
AND org_id = :ORG_ID
and  EXISTS (
  SELECT 1 from emp_test WHERE TS.item_id in (select 1 from DUAL union select 2 from DUAL)
);

ITEM_ID  ORG_ID  SUM_QUANTITY  
1        1       40  

这似乎与错误8714580有关-来自具有任何子查询 (Doc ID 8714580.8) 的不同查询的错误结果。这是固定在12.1。

但是在你的SQL中有一个重要的皱纹:

您没有在子查询中加入emp_test!

SELECT 1 from emp_test WHERE TS.item_id in 

引用主表中的item_id，test_stock。没有对emp_test列的引用!所以我很难理解你的exists条款有什么目的...

无论如何，有两种解决方法:

-将子查询中的emp_test替换为单个行表，例如dual
-按照MOS注释中的建议，将/* no_unnest */ hint放在exists子句中:

SELECT 
   distinct
   item_id , org_id,
   SUM(quantity) OVER (PARTITION BY org_id,item_id) as sum_quantity
FROM test_stock ts
WHERE item_id = :ITEM_ID
AND org_id = :ORG_ID
and  EXISTS (
  SELECT 1 from dual WHERE TS.item_id in (select 1 from DUAL union select 2 from DUAL)
);

ITEM_ID  ORG_ID  SUM_QUANTITY  
1        1       10   

SELECT 
   distinct
   item_id , org_id,
   SUM(quantity) OVER (PARTITION BY org_id,item_id) as sum_quantity
FROM test_stock ts
WHERE item_id = :ITEM_ID
AND org_id = :ORG_ID
and  EXISTS (
  SELECT /*+ no_unnest */1 from emp_test WHERE TS.item_id in (select 1 from DUAL union select 2 from DUAL)
);         

ITEM_ID  ORG_ID  SUM_QUANTITY  
1        1       10