SQL-CE 可能存在错误吗？

Question

如果我运行此查询，所有 [data].* 值都会显示为 NULL。 忽略*，这只是一个测试

SELECT
  [map].*,
  [data].*
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
    ON  (([data].level1_id = [map].level1_id) OR ([data].level1_id = 0))
    AND (([data].level2_id = [map].level2_id) OR ([data].level2_id = 0))
    AND (([data].level3_id = [map].level3_id) OR ([data].level3_id = 0))
    AND (([data].level4_id = [map].level4_id) OR ([data].level4_id = 0))
    AND (([data].level5_id = [map].level5_id) OR ([data].level5_id = 0))
    AND (([data].level6_id = [map].level6_id) OR ([data].level6_id = 0))
    AND ([data].metric_id = 22)
WHERE
  [map].level1_id = 22

但是，如果我在每个[之前添加1=1 AND，它就可以正常工作data].levelX_id = 0!?!?

SELECT
  [map].*,
  [data].*
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
    ON  (([data].level1_id = [map].level1_id) OR (1=1 AND [data].level1_id = 0))
    AND (([data].level2_id = [map].level2_id) OR (1=1 AND [data].level2_id = 0))
    AND (([data].level3_id = [map].level3_id) OR (1=1 AND [data].level3_id = 0))
    AND (([data].level4_id = [map].level4_id) OR (1=1 AND [data].level4_id = 0))
    AND (([data].level5_id = [map].level5_id) OR (1=1 AND [data].level5_id = 0))
    AND (([data].level6_id = [map].level6_id) OR (1=1 AND [data].level6_id = 0))
    AND ([data].metric_id = 22)
WHERE
  [map].level1_id = 22

我几乎可以接受它作为一个软糖来让它工作，但现在它需要永远运行。 （从不到一秒到 6 分钟后“仍在运行”。）

我被绑定了吗？还是 SQL CE 已经失效了？

编辑

如果我颠倒条件：

    AND (([data].level6_id = [map].level6_id) OR ([data].level6_id = 0))

那么

    AND (([data].level6_id = 0) OR ([data].level6_id = [map].level6_id))

我会得到不同的行为。

如果我使用 IN 代替：

    AND ([data].level6_id IN ([map].level6_id,0))

我仍然会得到不同的行为。

没有任何行为有意义，当然也不是我编码的。

Answer 1

我猜这些可能会慢一些，但我让这两个查询都可以工作：

SELECT
    *
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
      ON  ([data].level1_id = [map].level1_id OR [data].level1_id*1 = 0)
      AND ([data].level2_id = [map].level2_id OR [data].level2_id*1 = 0)
      AND ([data].level3_id = [map].level3_id OR [data].level3_id*1 = 0)
      AND ([data].level4_id = [map].level4_id OR [data].level4_id*1 = 0)
      AND ([data].level5_id = [map].level5_id OR [data].level5_id*1 = 0)
      AND ([data].level6_id = [map].level6_id OR [data].level6_id*1 = 0)
;

并且

SELECT
    *
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
      ON  ([data].level1_id = [map].level1_id OR cast([data].level1_id as int) = 0)
      AND ([data].level2_id = [map].level2_id OR cast([data].level2_id as int) = 0)
      AND ([data].level3_id = [map].level3_id OR cast([data].level3_id as int) = 0)
      AND ([data].level4_id = [map].level4_id OR cast([data].level4_id as int) = 0)
      AND ([data].level5_id = [map].level5_id OR cast([data].level5_id as int) = 0)
      AND ([data].level6_id = [map].level6_id OR cast([data].level6_id as int) = 0)
;

使用 Compact 4 进行编辑（如果有影响的话）。

Answer 2

我的解决方案是放弃 SQL-CE 并在客户端的内存中进行连接。

唯一有效的选项是最后一个，由于对两个表的全面扫描，实际数据的性能令人震惊。

CREATE TABLE f_normalised_report_data (
    level1_id       INT,
    level2_id       INT,
    level3_id       INT,
    level4_id       INT,
    level5_id       INT,
    level6_id       INT,
    metric_id       INT,
    value           MONEY,
    PRIMARY KEY (
        level1_id,
        level2_id,
        level3_id,
        level4_id,
        level5_id,
        level6_id,
        metric_id
    )
)
;

CREATE TABLE f_normalised_report_hierarchy (
    level1_id       INT,
    level2_id       INT,
    level3_id       INT,
    level4_id       INT,
    level5_id       INT,
    level6_id       INT,
    PRIMARY KEY (
        level1_id,
        level2_id,
        level3_id,
        level4_id,
        level5_id,
        level6_id
    )
)
;

INSERT INTO f_normalised_report_hierarchy SELECT 1, 2, 3, 4, 5, 6;
INSERT INTO f_normalised_report_hierarchy SELECT 1, 2, 3, 4, 5, 7;

INSERT INTO f_normalised_report_data      SELECT 1, 2, 3, 0, 5, 6, 22, 999;
INSERT INTO f_normalised_report_data      SELECT 1, 2, 3, 0, 5, 7, 22, 911;

SELECT
    *
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
      ON  ([data].level1_id = [map].level1_id OR [data].level1_id = 0)
      AND ([data].level2_id = [map].level2_id OR [data].level2_id = 0)
      AND ([data].level3_id = [map].level3_id OR [data].level3_id = 0)
      AND ([data].level4_id = [map].level4_id OR [data].level4_id = 0)
      AND ([data].level5_id = [map].level5_id OR [data].level5_id = 0)
      AND ([data].level6_id = [map].level6_id OR [data].level6_id = 0)
;
-- The above query gives me this...
-- 1, 2, 3, 4, 5, 6, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
-- 1, 2, 3, 4, 5, 7, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

SELECT
    *
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
      ON  ([data].level1_id = 0 OR [data].level1_id = [map].level1_id)
      AND ([data].level2_id = 0 OR [data].level2_id = [map].level2_id)
      AND ([data].level3_id = 0 OR [data].level3_id = [map].level3_id)
      AND ([data].level4_id = 0 OR [data].level4_id = [map].level4_id)
      AND ([data].level5_id = 0 OR [data].level5_id = [map].level5_id)
      AND ([data].level6_id = 0 OR [data].level6_id = [map].level6_id)
;
-- The above query gives me this...
-- 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 0, 6, 22, 999
-- 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 0, 7, 22, 911
-- 1, 2, 3, 4, 5, 7, 1, 2, 3, 4, 0, 6, 22, 999
-- 1, 2, 3, 4, 5, 7, 1, 2, 3, 4, 0, 7, 22, 911


SELECT
    *
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
      ON  ([data].level1_id IN ([map].level1_id, 0))
      AND ([data].level2_id IN ([map].level2_id, 0))
      AND ([data].level3_id IN ([map].level3_id, 0))
      AND ([data].level4_id IN ([map].level4_id, 0))
      AND ([data].level5_id IN ([map].level5_id, 0))
      AND ([data].level6_id IN ([map].level6_id, 0))
;
-- The above query gives me this...
-- 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 0, 6, 22, 999
-- 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 0, 7, 22, 911
-- 1, 2, 3, 4, 5, 7, 1, 2, 3, 4, 0, 6, 22, 999
-- 1, 2, 3, 4, 5, 7, 1, 2, 3, 4, 0, 7, 22, 911


SELECT
    *
FROM
  f_normalised_report_hierarchy     AS [map]
LEFT JOIN
  f_normalised_report_data          AS [data]
      ON  ([data].level1_id = [map].level1_id OR (1=1 AND [data].level1_id = 0))
      AND ([data].level2_id = [map].level2_id OR (1=1 AND [data].level2_id = 0))
      AND ([data].level3_id = [map].level3_id OR (1=1 AND [data].level3_id = 0))
      AND ([data].level4_id = [map].level4_id OR (1=1 AND [data].level4_id = 0))
      AND ([data].level5_id = [map].level5_id OR (1=1 AND [data].level5_id = 0))
      AND ([data].level6_id = [map].level6_id OR (1=1 AND [data].level6_id = 0))
;
-- The above query gives me this...
-- 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 0, 6, 22, 999
-- 1, 2, 3, 4, 5, 7, 1, 2, 3, 4, 0, 7, 22, 911
--
-- Which is correct, but performance was blown to smitherines.