Oracle 减查询。如果顶部 SQL 和底部 SQL 不包含 NULL，如何获得 NULL 的结果？

Question

这个 SQL 如何...

CREATE TABLE NewTable AS
    SELECT A,B,C FROM Table1
    minus
    SELECT A, B, C From Table2

...创建一个 A 列中包含 NULL 值的新表 当 Table1 或 Table2 的 A 列中都没有 NULL 值时？

但另一方面，这个 SQL...

SELECT * FROM
(
   SELECT A,B,C FROM Table1
    minus
    SELECT A, B, C From Table2
) 
WHERE A IS NULL 

不返回任何行！

看来是不一致啊！

我认为这是 Oracle 中的一个错误。

当然，真正的 SQL 要复杂得多，但我相信这准确地说明了问题的本质。

更新

这是实际的SQL：

我执行了这个语句：

CREATE TABLE MyMinus
AS
select 
*
FROM
---begin main query 
(
SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM dw_mgr.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM dw_mgr.po_distributions_curr_fct b,
                 dw_mgr.po_lines_curr_fct,
                 dw_mgr.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
             AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
             AND dw_mgr.po_lines_curr_fct.f_cuic =
                                              dw_mgr.po_header_curr_fct.f_cuic
             AND dw_mgr.po_lines_curr_fct.f_header_id =
                                         dw_mgr.po_header_curr_fct.f_header_id
             AND dw_mgr.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND dw_mgr.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

MINUS 

SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM arch_fct.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM arch_fct.po_distributions_curr_fct b,
                 arch_fct.po_lines_curr_fct,
                 arch_fct.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
             AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
             AND arch_fct.po_lines_curr_fct.f_cuic =
                                            arch_fct.po_header_curr_fct.f_cuic
             AND arch_fct.po_lines_curr_fct.f_header_id =
                                       arch_fct.po_header_curr_fct.f_header_id
             AND arch_fct.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND arch_fct.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

) 

然后是这个。请注意，具有 NULL 值 F_DISTRIBUTION_ID 的行已插入到创建的表中。

SELECT COUNT(*) from MyMinus WHERE F_DISTRIBUTION_ID IS NULL

--17 行

然而，当我执行此操作时：

select 
*
FROM
---begin main query 
(
SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM dw_mgr.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM dw_mgr.po_distributions_curr_fct b,
                 dw_mgr.po_lines_curr_fct,
                 dw_mgr.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
             AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
             AND dw_mgr.po_lines_curr_fct.f_cuic =
                                              dw_mgr.po_header_curr_fct.f_cuic
             AND dw_mgr.po_lines_curr_fct.f_header_id =
                                         dw_mgr.po_header_curr_fct.f_header_id
             AND dw_mgr.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND dw_mgr.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

MINUS 

SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM arch_fct.po_distributions_curr_fct a
 WHERE EXISTS (
          SELECT 1
            FROM arch_fct.po_distributions_curr_fct b,
                 arch_fct.po_lines_curr_fct,
                 arch_fct.po_header_curr_fct
           WHERE a.ROWID = b.ROWID
             AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
             AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
             AND arch_fct.po_lines_curr_fct.f_cuic =
                                            arch_fct.po_header_curr_fct.f_cuic
             AND arch_fct.po_lines_curr_fct.f_header_id =
                                       arch_fct.po_header_curr_fct.f_header_id
             AND arch_fct.po_header_curr_fct.f_header_creation_date <
                                      ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                  -48)
             AND arch_fct.po_header_curr_fct.f_po_status IN
                                                 ('CLOSED', 'FINALLY CLOSED'))

) 
WHERE

f_distribution_id is null

我得到 0 行。

为什么将记录插入到临时表中似乎会引入具有 NULL DIST ID 的行？

这个负查询 SQL 是由自定义数据归档程序动态生成的，它尝试验证应该归档在 DW_MGR 模式中的数据实际上是否已复制到 ARCH_FCT（归档）模式中。它返回差异，其中包括 17 条记录，其中 MyMinus 临时表中的 F_DISTRIBUTION_ID 与源 DW_MG.PO_DISTRIBUTIONS_CURR_FCT 表中的记录不匹配，因为它们为 NULL。因此，归档过程是在发现差异时设计的。问题是为什么会有差异，即当 NULL 值不在 SOURCE PO_DISTRIBUTIONS_CURR_FCT 表中时，它们如何进入 MyMinus 表？

编辑：

具有 Oracle META 访问权限的人可以在 Oracle bug 之后发布有关 thd 的信息吗？我被转介给他们，但我在公司里找到了一个人，他可以告诉我我们的支持 ID 号是什么。我最终会知道的，但如果能早点知道就好了。如果您不想发布它，请考虑以下错误参考作为我的问题的潜在相关信息：

Bug 8209309: MINUS IS SHOWING DIFFERENCES WITH CTAS + INSERT 
Bug 7834950: WRONG RESULTS WITH MINUS OPERATOR

Answer 1

别再破坏你的排骨了。这是一个 Oracle 错误。我将向您证明：

首先，它必须是第一个为 DISTRIBUTION ID 返回 NULL 的 SQL，因此隔离该 SQL，我们将其称为“SQL1”。

好的，为了讨论起见，让我们简化 SQL1 并假设它具有以下格式：

CREATE TABLE TempTable AS 
SELECT
   F_DISTRIBUTION_ID,
   FIELD2,
   FIELD3,...FIELD99

FROM WHATEVER 
WHERE WHATEVER

然后，您会发现，当您执行此命令时，您会发现具有 NULL DIST ID 的行：

SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL
--Some positive number of rows returned.

如果 Oracle 不是一坨屎，您可以更改所选字段的数量，以便仅选择 F_DISTRIBUTION_ID，并且当您计算 F_DISTRIBUTION_ID 为 NULL 值的行数时，您会得到相同的结果，对吧？正确的！但事实并非如此，因为 Oracle 是一只不可靠的恐龙。

试试这个：

CREATE TABLE TempTable AS 
SELECT
   F_DISTRIBUTION_ID
FROM WHATEVER 
WHERE WHATEVER

SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL

我打赌你会返回 0 行。

现在，打电话给 Microsoft 并告诉他们您想要升级到 SQL Server 2008 R2。

Answer 2

首先，我将摆脱 ROWID 到 ROWID 的连接。
然后我会让表 aiases 唯一（不在减号上方的查询和减号下方的查询中重复使用“a”和“b”）。

最后，我会查看这 17 行，并尝试在“dw_mgr.po_distributions_curr_fct”中找到匹配的记录，并使用 DUMP(F_DISTRIBUTION_ID) 查看列值中是否有任何奇怪的地方。

Answer 3

一般不应该。

唯一可能的情况是，如果您具有一些高级安全功能（细粒度访问控制），优化器可以通过这些功能看到 table1/table2 中的 A 不能为空，因此返回零行，但 FGAC 会启动以阻止您看到实际的行数。通过返回 null 来获取列中的值。

---

编辑。
“通过 [虚拟专用数据库] 列屏蔽行为，所有行都会显示，甚至那些引用敏感列的行也是如此。但是，敏感列显示为 NULL 值。”

http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/vpd.htm# i1014682

Answer 4

我认为 F_DISTRIBUTION_ID 插入 MyMinus 时可能为 NULL 的唯一方法是，如果它在第一个查询中以某种方式返回 NULL。

重现这一点（在 9i 和 10g 上）：

SQL> INSERT INTO table1 VALUES (NULL, 2, 3);

1 row created.

SQL> INSERT INTO table2 VALUES (1, 2, 3);

1 row created.

SQL> SELECT * 
  2  FROM (
  3    SELECT a, b, c FROM table1
  4    MINUS
  5    SELECT a, b, c FROM table2);

         A          B          C
---------- ---------- ----------
                    2          3

但是，关于查询在单独运行时不返回任何行......这是另一回事。一个错误不会让我感到惊讶......但是你尝试过删除那些存在吗？当然，有很多不同的方法，但也许所有这些子查询都会导致内存中发生一些有趣的事情。

例如：

SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM dw_mgr.po_distributions_curr_fct a
       dw_mgr.po_lines_curr_fct,
       dw_mgr.po_header_curr_fct
  WHERE a.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
    AND a.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
    AND dw_mgr.po_lines_curr_fct.f_cuic = dw_mgr.po_header_curr_fct.f_cuic
    AND dw_mgr.po_lines_curr_fct.f_header_id = dw_mgr.po_header_curr_fct.f_header_id
    AND dw_mgr.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48)
    AND dw_mgr.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')
MINUS 
SELECT expenditure_item_date, expenditure_org, expenditure_type,
       f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
       f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
       f_company_code, f_cost_center_num, f_cuic, f_currency_code,
       f_destination_type_code, f_distribution_id, f_distribution_num,
       f_exchange_rate, f_extract_date, f_gl_account,
       f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
       f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
       f_project, f_project_num, f_promised_date, f_quantity_billed,
       f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
       f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
       f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
  FROM arch_fct.po_distributions_curr_fct a,
       arch_fct.po_lines_curr_fct,
       arch_fct.po_header_curr_fct
 WHERE a.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
   AND a.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
   AND arch_fct.po_lines_curr_fct.f_cuic = arch_fct.po_header_curr_fct.f_cuic
   AND arch_fct.po_lines_curr_fct.f_header_id = arch_fct.po_header_curr_fct.f_header_id
   AND arch_fct.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48)
   AND arch_fct.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')   

Answer 5

我遇到了一个可能相关的问题，该问题最初是使用一堆复杂视图时的 MINUS 问题，并且存在。我将其范围缩小到可能的优化器问题 - 您可以通过使用“upper(F_DISTRIBUTION_ID) IS NULL”之类的内容来阻止优化器搞乱基于“F_DISTRIBUTION_ID IS NULL”的事情来解决它。

在这些情况下，为错误报告创建一个简化的测试用例几乎是不可能的——它可能只发生在非常特定的场景中（毕竟 MINUS 不会被完全破坏）。对于我的问题，我根本无法减少查询并且它仍然发生。

仅供参考，我的问题是一个查询，它基本上将一堆内容连接到源表上，称之为“员工”。我在 Employee 的主键上有一个 where 子句 - 如果我执行了 where EmployeeId = foo，它将返回一个额外的行，其中不应该有空值（来自内部连接的表中的列） - 如果我这样做a where upper(EmployeeId) = foo 那么我会得到正确的结果。显然，EmployeeId 值来自与谓词匹配的所有行中的同一单元格 - 因此这显然是一个错误。

Answer 6

我刚刚遇到同样的错误。并建立一个 if else 条件来获取这些值 :D

表 1

A      !      B

null          35

---

'if else' 条件 :P

select 
Case
WHEN  a."Add" >= '0' THEN to_number(a."Add" - b."Cease" )

ELSE (b."Cease")*-1
End  as "X"
from table 1

答案

-35