SQL-CE Possible error?

Question

If I run this query, all values ​​are [data]. * issued as NULL. ignore * it's just a test

 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 

However, it works fine if I add 1=1 AND before each [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 

I could almost live with it as an invention, to make it work, but now it always runs. (After 6 minutes).

Am I getting attached? Or SQL CE from him rocker?

EDIT

If I cancel the conditions:

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

to

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

then I get a different behavior.

And if I use IN instead:

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

I have a different behavior.

None of the behaviors that change, and, of course, are not what I encoded.

Answer 1

I assume they are rather slower, but I received both of these requests:

 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) ; 

and

 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) ; 

EDIT With Compact 4, if that matters.

Answer 2

My solution is to abandon SQL-CE and make a memory connection on the client.

The only option that works is the latter, and the performance on real data was horrific due to a full scan of both tables.

 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.