diff options
| -rw-r--r-- | lib/analysers/filters.ml | 22 | ||||
| -rw-r--r-- | tests/analyser_filters.ml | 2 | ||||
| -rw-r--r-- | tests/analyser_query_test.ml | 45 | ||||
| -rw-r--r-- | tests/sql_db.ml | 31 |
4 files changed, 86 insertions, 14 deletions
diff --git a/lib/analysers/filters.ml b/lib/analysers/filters.ml index 15e8cda..cd47e27 100644 --- a/lib/analysers/filters.ml +++ b/lib/analysers/filters.ml @@ -36,9 +36,8 @@ let print : let cte_index = match cte.ImporterSyntax.CTE.group with | Some expression -> - begin - if acc.has_previous then Chunk.add_string query ", " - else Chunk.add_string query "WITH " + begin if acc.has_previous then Chunk.add_string query ", " + else Chunk.add_string query "WITH " end; Chunk.add_string query "filter"; Chunk.add_string query (string_of_int n); @@ -59,13 +58,16 @@ let print : Chunk.add_string query ".group_function" end; - begin - match cte.ImporterSyntax.CTE.filters with - | [] -> () - | _ -> - Chunk.add_string query " WHERE "; - Chunk.add_expressions ~sep:"\nAND " ~repr:(Printers.path ~conf) - query cte.ImporterSyntax.CTE.filters + begin match cte.ImporterSyntax.CTE.filters with + | [] -> () + | _ -> + (* If there is a previous group, just add the AND clause in the query *) + let () = + if not acc.has_previous then Chunk.add_string query " WHERE " + else Chunk.add_string query " AND " + in + Chunk.add_expressions ~sep:"\nAND " ~repr:(Printers.path ~conf) + query cte.ImporterSyntax.CTE.filters end; Chunk.add_string query ")\n"; Some acc.n diff --git a/tests/analyser_filters.ml b/tests/analyser_filters.ml index 9a54bde..851efb8 100644 --- a/tests/analyser_filters.ml +++ b/tests/analyser_filters.ml @@ -179,7 +179,7 @@ let group_with_group () = let tests = Alcotest. - ( "filters", + ( __FILE__, [ test_case "No filters" `Quick empty_predicates; test_case "Simple filter" `Quick simple_filter; diff --git a/tests/analyser_query_test.ml b/tests/analyser_query_test.ml index 37a748b..0af6d42 100644 --- a/tests/analyser_query_test.ml +++ b/tests/analyser_query_test.ml @@ -227,7 +227,7 @@ WHERE filter0.group_function|} This generate a CTE expression in order to evaluate the group before loading the results from the query. *) let filter_group2 = - "Test filter_group" >:: fun _ -> + "Test filter_group2" >:: fun _ -> let c col = Expr.path ImportDataTypes.Path.{ alias = None; column = col } in let conf = { @@ -254,6 +254,46 @@ WHERE (COALESCE('source'.'col_3',0)=0) AND filter0.group_function|} Alcotest.check Alcotest.string "" expected contents.q +(** Test a group filter, followed by a simple clause and another group : The + simple clause is evaluated after the first group, but before the last one *) +let filter_group3 = + "Test filter_group3" >:: fun _ -> + let c col = Expr.path ImportDataTypes.Path.{ alias = None; column = col } in + let conf = + { + conf with + columns = [ c 1 ]; + filters = + [ + Expr.(max (c 3) [ c 1 ] [ c 1 ]); + Expr.equal (c 3) Expr.integer_zero; + Expr.(max (c 3) [ c 1 ] [ c 1 ]); + ]; + } + in + let contents, _ = ImportAnalyser.Query.select conf in + + let expected = + {|WITH filter0 AS (SELECT source.id, (LAST_VALUE('source'.'col_3') OVER (PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS group_function +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file') +, filter1 AS (SELECT source.id, (LAST_VALUE('source'.'col_3') OVER (PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS group_function +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file' +INNER JOIN 'filter0' ON filter0.id = source.id +WHERE filter0.group_function AND (COALESCE('source'.'col_3',0)=0)) +SELECT 'source'.'col_1' AS result_0 +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.'col_1')) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.'col_1')) = 'last_file'.'key_last_file' +INNER JOIN 'filter1' ON filter1.id = source.id +WHERE filter1.group_function|} + in + + Alcotest.check Alcotest.string "" expected contents.q + (** Add an external with a filter. Ensure the predicate is reported in the query. *) let external_filter = @@ -349,9 +389,10 @@ let test_suit = prepare_insert; filter_group; filter_group2; + filter_group3; external_filter; order_by; group_by; ] -let tests = "analyser_query_test" >::: test_suit +let tests = __FILE__ >::: test_suit diff --git a/tests/sql_db.ml b/tests/sql_db.ml index 34f5b12..3c6a749 100644 --- a/tests/sql_db.ml +++ b/tests/sql_db.ml @@ -328,6 +328,34 @@ filters = [ ] ~expected:(Ok [ [| ImportDataTypes.Value.Integer 1 |] ]) +let filter_group_expression_and_group = + run_test "filter group and expression then group" + ~configuration: + {|[source] +name = "source_name" +file = "source_file" + +[sheet] +columns = [ + ":A", +] + +filters = [ + "max(:B, [], [:B]) <> :B", + ":B", + "max(:B, [], [:B]) <> :B", +] + +|} + ~input: + ImportDataTypes.Value. + [ + [ (0, Integer 1); (1, Integer 100) ]; + [ (0, Integer 2); (1, Integer 150) ]; + [ (0, Integer 3); (1, Integer 200) ]; + ] + ~expected:(Ok [ [| ImportDataTypes.Value.Integer 1 |] ]) + let test_suit = [ simple_extraction; @@ -340,6 +368,7 @@ let test_suit = filter_expression_and_group; filter_group_and_expression; filter_group_and_group; + filter_group_expression_and_group; ] -let tests = "sql_db" >::: test_suit +let tests = __FILE__ >::: test_suit |