Something went wrong. Try again.
My own corner of monopam
Something went wrong. Try again.
25 kB · 527 lines
OCaml
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528module E = Sql.Engine
let rows = function E.Rows r -> r | E.Done -> []
let cell = function | Catalog.Value.Int n -> Int64.to_string n | Catalog.Value.Text s -> s (* sqlite3's own rendering, so a ranking function's quotient is compared against the digits the shell printed rather than a rounded form *) | Catalog.Value.Float _ as v -> Catalog.Value.to_text v | Catalog.Value.Null -> "NULL" | Catalog.Value.Blob b -> b
let setup () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES (1,'x'), (2,'y'), (1,'z'), (3,'w')"); ignore (E.exec cat "CREATE TABLE u (k INTEGER, v TEXT)"); ignore (E.exec cat "INSERT INTO u VALUES (1,'p'), (2,'q'), (3,'r')"); ignore (E.exec cat "CREATE TABLE n (a INTEGER)"); ignore (E.exec cat "INSERT INTO n VALUES (3), (NULL), (1), (NULL), (2)"); cat
let show cat q = List.map (fun r -> String.concat "|" (List.map cell r)) (rows (E.exec cat q))
let check cat msg expected q = Alcotest.(check (list string)) msg expected (show cat q)
(* A window that names no column has nothing to project, and one whose partition holds every row still has to see them all. *)let test_no_input_columns () = let cat = setup () in check cat "row_number with no partition or order" [ "1|1"; "2|2"; "1|3"; "3|4" ] "SELECT a, row_number() OVER () FROM t"; check cat "a star count over a partition" [ "1|2"; "1|2"; "2|1"; "3|1" ] "SELECT a, count(*) OVER (PARTITION BY a) FROM t ORDER BY a, b"
(* Peers share a rank and a frame: rank counts the rows before the peer group, dense_rank counts the groups (windowfunctions.html sec.3). *)let test_ranking () = let cat = setup () in check cat "rank and dense_rank over a tied key" [ "1|1|1"; "1|1|1"; "2|3|2"; "3|4|3" ] "SELECT a, rank() OVER (ORDER BY a), dense_rank() OVER (ORDER BY a) FROM t \ ORDER BY a, b"; check cat "NULLs sort first, so they take the leading positions" [ "NULL|1"; "NULL|2"; "1|3"; "2|4"; "3|5" ] "SELECT a, row_number() OVER (ORDER BY a) FROM n ORDER BY 2"
(* The navigation functions index into the ordered partition. [last_value]'s default frame ends at the current peer group, which is why it tracks the row rather than naming the partition's last. *)let test_navigation () = let cat = setup () in check cat "first_value is the partition's first, last_value the frame's" [ "1|x|x"; "1|x|z"; "2|y|y"; "3|w|w" ] "SELECT a, first_value(b) OVER (PARTITION BY a ORDER BY b), last_value(b) \ OVER (PARTITION BY a ORDER BY b) FROM t ORDER BY a, b"; check cat "nth_value over the whole frame, and ntile's buckets" [ "1|z|1"; "1|z|1"; "2|z|2"; "3|z|2" ] "SELECT a, nth_value(b, 2) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED \ PRECEDING AND UNBOUNDED FOLLOWING), ntile(2) OVER (ORDER BY a) FROM t \ ORDER BY a, b"; check cat "lag and lead read a neighbour, falling back on the default" [ "1|0|2"; "2|1|1"; "1|2|3"; "3|1|0" ] "SELECT a, lag(a, 1, 0) OVER (), lead(a, 1, 0) OVER () FROM t"
(* An explicit ROWS frame slides with the row; an aggregate folds exactly the rows inside it, through the same fold a GROUP BY uses. *)let test_frames () = let cat = setup () in check cat "a sliding ROWS frame sums its three rows" [ "1|3"; "2|4"; "1|6"; "3|4" ] "SELECT a, sum(a) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t"; check cat "and an order-sensitive fold sees the frame in key order" [ "w|w"; "x|w-x"; "y|x-y"; "z|y-z" ] "SELECT b, group_concat(b, '-') OVER (ORDER BY b ROWS BETWEEN 1 PRECEDING \ AND CURRENT ROW) FROM t ORDER BY b"
(* The rows a window partitions can come from several sources. The lowering resolves [t.a] and [u.v] to their own cursors, while the post-pass holds the joined row on one, so this is the shape that needs the rebasing. *)let test_over_a_join () = let cat = setup () in check cat "a window over a two-table join" [ "1|p|2"; "1|p|2"; "2|q|1"; "3|r|1" ] "SELECT t.a, u.v, count(*) OVER (PARTITION BY t.a) FROM t JOIN u ON u.k = \ t.a ORDER BY t.a, u.v"
(* A window over a GROUP BY partitions the grouped rows, one per group, so the aggregate pass and the window pass compose: the inner "count(*)" counts each group and the outer "sum(...) OVER ..." runs over the groups. Written with quotes rather than odoc brackets because "(" followed by "*" opens a nested comment. *)let test_over_a_group_by () = let cat = setup () in check cat "the window runs over the grouped rows" [ "1|2|2"; "2|1|3"; "3|1|4" ] "SELECT a, count(*), sum(count(*)) OVER (ORDER BY a) FROM t GROUP BY a \ ORDER BY a"
let test_a_tie_in_a_grouped_window_sort () = let seeded n = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER)"); ignore (E.exec cat ("INSERT INTO t VALUES " ^ n)); cat in let four = seeded "(1),(1),(2),(3)" in let five = seeded "(1),(1),(2),(3),(4)" in let ranked dir = Fmt.str "SELECT a, count(*) FROM t GROUP BY a ORDER BY row_number() OVER (ORDER \ BY count(*) %s)" dir in check four "DESC turns the tied run round" [ "1|2"; "3|1"; "2|1" ] (ranked "DESC"); check four "ASC leaves it as the grouping put it" [ "2|1"; "3|1"; "1|2" ] (ranked "ASC"); check five "a third tied group is turned round with them" [ "1|2"; "4|1"; "3|1"; "2|1" ] (ranked "DESC"); check five "and left alone with them" [ "2|1"; "3|1"; "4|1"; "1|2" ] (ranked "ASC"); (* the same DESC key over an ungrouped scan keeps the rows as they came *) check five "an ungrouped scan under a DESC key is not turned round" [ "1|1"; "1|2"; "2|3"; "3|4"; "4|5" ] "SELECT a, row_number() OVER (ORDER BY 0 DESC) FROM t"
let test_a_window_reads_a_bare_column () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE c (k INTEGER, v TEXT, x INTEGER)"); ignore (E.exec cat "INSERT INTO c VALUES (1,'a',10), (1,'b',20), (2,'c',30), (2,'d',40)"); check cat "the group answers a bare column from its first row" [ "1|a"; "2|c" ] "SELECT k, v FROM c GROUP BY k"; check cat "and answers one inside a window's argument the same way" [ "1|40"; "2|40" ] "SELECT k, sum(x) OVER () FROM c GROUP BY k"; check cat "whatever the fold over the frame is" [ "1|30"; "2|30" ] "SELECT k, max(x) OVER () FROM c GROUP BY k"; check cat "including one that joins its arguments" [ "1|a,c"; "2|a,c" ] "SELECT k, group_concat(v) OVER () FROM c GROUP BY k"; check cat "a navigation function reads it too" [ "1|a"; "2|a" ] "SELECT k, first_value(v) OVER () FROM c GROUP BY k"; check cat "from the neighbouring group's row" [ "1|NULL"; "2|a" ] "SELECT k, lag(v) OVER () FROM c GROUP BY k"; check cat "a PARTITION BY key is a reference like any other" [ "1|10"; "2|30" ] "SELECT k, sum(x) OVER (PARTITION BY v) FROM c GROUP BY k"; check cat "and so is an ORDER BY key" [ "1|1"; "2|2" ] "SELECT k, row_number() OVER (ORDER BY v) FROM c GROUP BY k"; (* The two places a window may not stand. sqlite3 answers "misuse of window function sum()" for the HAVING and "RAISE() may only be used within a trigger-program" for the RAISE, so neither reads a group's row at all. *) Alcotest.check_raises "a window in a HAVING is refused" (Failure "engine: SELECT k, count(k) FROM c GROUP BY k HAVING (sum(x) OVER () > \ 0) is not supported yet") (fun () -> ignore (E.exec cat "SELECT k, count(k) FROM c GROUP BY k HAVING sum(x) OVER () > 0")); Alcotest.check_raises "and a RAISE never reaches one" (Catalog.Error.Prepare "RAISE() may only be used within a trigger-program") (fun () -> ignore (E.exec cat "SELECT k, RAISE(ABORT, v) FROM c GROUP BY k"))
(* The select list, DISTINCT, ORDER BY and LIMIT around a window are the post-pass's second projection, not the scan's. *)let test_result_clauses () = let cat = setup () in check cat "the expressions around a window are projected too" [ "2|2"; "4|1" ] "SELECT DISTINCT a * 2, count(*) OVER (PARTITION BY a) FROM t WHERE a < 3 \ ORDER BY 1 LIMIT 2"; check cat "and [*] expands to the scanned columns, never the window's" [ "3|w|1"; "1|x|2"; "2|y|3"; "1|z|4" ] "SELECT *, row_number() OVER (ORDER BY b) FROM t ORDER BY b"
let test_percent_rank_and_cume_dist () = let cat = setup () in check cat "over peer groups of an ordered partition" [ "1|1|0.0|0.5"; "1|1|0.0|0.5"; "2|3|0.66666666666666663|0.75"; "3|4|1.0|1.0"; ] "SELECT a, rank() OVER (ORDER BY a), percent_rank() OVER (ORDER BY a), \ cume_dist() OVER (ORDER BY a) FROM t ORDER BY a, b"; check cat "a one-row partition is 0.0 and 1.0, not a division by zero" [ "1|0.0|0.5"; "1|1.0|1.0"; "2|0.0|1.0"; "3|0.0|1.0" ] "SELECT a, percent_rank() OVER (PARTITION BY a ORDER BY b), cume_dist() \ OVER (PARTITION BY a ORDER BY b) FROM t ORDER BY a, b"; check cat "with no ORDER BY the partition is one peer group" [ "1|0.0|1.0"; "1|0.0|1.0"; "2|0.0|1.0"; "3|0.0|1.0" ] "SELECT a, percent_rank() OVER (), cume_dist() OVER () FROM t ORDER BY a, b"; check cat "NULLs are peers of each other" [ "NULL|0.0|0.4"; "NULL|0.0|0.4"; "1|0.5|0.6"; "2|0.75|0.8"; "3|1.0|1.0" ] "SELECT a, percent_rank() OVER (ORDER BY a), cume_dist() OVER (ORDER BY a) \ FROM n ORDER BY a"; check cat "and an explicit frame does not move either answer" [ "1|0.0|0.5"; "1|0.0|0.5"; "2|0.66666666666666663|0.75"; "3|1.0|1.0" ] "SELECT a, percent_rank() OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND \ CURRENT ROW), cume_dist() OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND \ CURRENT ROW) FROM t ORDER BY a, b"
let test_partitions_come_out_in_key_order () = let cat = Catalog.of_mem [] in let exec q = ignore (E.exec cat q) in exec "CREATE TABLE p (y TEXT, n INTEGER)"; exec "INSERT INTO p VALUES ('c',1), ('a',2), ('b',3), ('a',4)"; exec "CREATE TABLE k (p TEXT, q INTEGER)"; exec "INSERT INTO k VALUES ('b',2), ('a',3), ('b',1), ('a',1)"; exec "CREATE TABLE m (x)"; exec "INSERT INTO m VALUES ('b'), (2), (NULL), (1.5), (x'41'), ('a')"; exec "CREATE TABLE d (x)"; exec "INSERT INTO d VALUES (1), (1.0), (1)"; check cat "a partition's key decides where it lands, not when it was seen" [ "a|2|1"; "a|4|2"; "b|3|1"; "c|1|1" ] "SELECT y, n, row_number() OVER (PARTITION BY y) FROM p"; check cat "several keys order lexicographically" [ "a|1|1"; "a|3|1"; "b|1|1"; "b|2|1" ] "SELECT p, q, row_number() OVER (PARTITION BY p, q) FROM k"; check cat "the window's own ORDER BY orders within a partition" [ "a|3|1"; "a|1|2"; "b|2|1"; "b|1|2" ] "SELECT p, q, row_number() OVER (PARTITION BY p ORDER BY q DESC) FROM k"; check cat "keys of different storage classes take the value order" [ "null|NULL|1"; "real|1.5|1"; "integer|2|1"; "text|a|1"; "text|b|1"; "blob|A|1"; ] "SELECT typeof(x), x, row_number() OVER (PARTITION BY x) FROM m"; check cat "and two spellings of one value are one partition" [ "integer|1"; "real|2"; "integer|3" ] "SELECT typeof(x), row_number() OVER (PARTITION BY x) FROM d"
let test_partition_reads_a_collating_sequence () = let cat = Catalog.of_mem [] in let exec q = ignore (E.exec cat q) in exec "CREATE TABLE p (x TEXT)"; exec "INSERT INTO p VALUES ('a'), ('B'), ('A'), ('b'), ('c')"; exec "CREATE TABLE r (x TEXT)"; exec "INSERT INTO r VALUES ('a '), ('b'), ('a'), ('b ')"; check cat "BINARY keeps the five spellings apart, in byte order" [ "A|1"; "B|1"; "a|1"; "b|1"; "c|1" ] "SELECT x, row_number() OVER (PARTITION BY x) FROM p"; check cat "NOCASE folds them into three, in the folded order" [ "a|1"; "A|2"; "B|1"; "b|2"; "c|1" ] "SELECT x, row_number() OVER (PARTITION BY x COLLATE NOCASE) FROM p"; check cat "and a fold over the partition sees every row of it" [ "a|2"; "A|2"; "B|2"; "b|2"; "c|1" ] "SELECT x, count(x) OVER (PARTITION BY x COLLATE NOCASE) FROM p"; check cat "the window's ORDER BY keeps its own sequence" [ "A|1"; "a|2"; "B|1"; "b|2"; "c|1" ] "SELECT x, row_number() OVER (PARTITION BY x COLLATE NOCASE ORDER BY x) \ FROM p"; check cat "RTRIM folds a trailing space, and orders by the trimmed key" [ "[a ]|1"; "[a]|2"; "[b]|1"; "[b ]|2" ] "SELECT '['||x||']', row_number() OVER (PARTITION BY x COLLATE RTRIM) FROM \ r"; Alcotest.check_raises "a name no sequence carries is refused" (Failure "no such collation sequence: FOO") (fun () -> ignore (E.exec cat "SELECT count(x) OVER (PARTITION BY x COLLATE FOO) FROM p"))
let test_a_window_key_reads_its_whole_operand () = let cat = Catalog.of_mem [] in let exec q = ignore (E.exec cat q) in exec "CREATE TABLE p (x TEXT)"; exec "INSERT INTO p VALUES ('a'), ('B'), ('A'), ('b'), ('c')"; let folded = [ "a|1"; "A|2"; "B|1"; "b|2"; "c|1" ] in check cat "a sequence under a CAST still partitions" folded "SELECT x, row_number() OVER (PARTITION BY CAST(x COLLATE NOCASE AS TEXT)) \ FROM p"; check cat "as does one over the CAST" folded "SELECT x, row_number() OVER (PARTITION BY CAST(x AS TEXT) COLLATE NOCASE) \ FROM p"; check cat "one on a concatenation's right operand" folded "SELECT x, row_number() OVER (PARTITION BY x || '' COLLATE NOCASE) FROM p"; check cat "and one on its left" folded "SELECT x, row_number() OVER (PARTITION BY (x COLLATE NOCASE) || '') FROM p"; check cat "and one inside a function's arguments" folded "SELECT x, row_number() OVER (PARTITION BY substr(x COLLATE NOCASE, 1)) \ FROM p"; check cat "the leftmost wins, even naming the default" [ "A|1"; "B|1"; "a|1"; "b|1"; "c|1" ] "SELECT x, row_number() OVER (PARTITION BY (x COLLATE BINARY) || (x \ COLLATE NOCASE)) FROM p"; check cat "the window's ORDER BY reads its key the same way" [ "a|a,A"; "A|a,A"; "B|a,A,B,b"; "b|a,A,B,b"; "c|a,A,B,b,c" ] "SELECT x, group_concat(x) OVER (ORDER BY CAST(x COLLATE NOCASE AS TEXT)) \ FROM p"; Alcotest.check_raises "a name no sequence carries is refused there too" (Failure "no such collation sequence: FOO") (fun () -> ignore (E.exec cat "SELECT x, row_number() OVER (PARTITION BY CAST(x COLLATE FOO AS \ TEXT)) FROM p"))
let test_window_reachable_only_from_order_by () = let cat = setup () in check cat "a window nothing projects still orders the result" [ "1"; "2"; "1"; "3" ] "SELECT a FROM t ORDER BY row_number() OVER ()"; check cat "its own ORDER BY is the rank the sort then reverses" [ "z"; "y"; "x"; "w" ] "SELECT b FROM t ORDER BY row_number() OVER (ORDER BY b) DESC"; check cat "and a LIMIT applies after that sort" [ "z"; "y" ] "SELECT b FROM t ORDER BY row_number() OVER (ORDER BY b) DESC LIMIT 2"; check cat "a partitioned fold is a sort key like any other" [ "w"; "y"; "x"; "z" ] "SELECT b FROM t ORDER BY count(a) OVER (PARTITION BY a), b"; check cat "the same window in both places is one computation" [ "1|4"; "2|3"; "1|2"; "3|1" ] "SELECT a, row_number() OVER (ORDER BY b) FROM t ORDER BY row_number() \ OVER (ORDER BY b) DESC"; check cat "and the grouped rows take one too" [ "3|1"; "2|1"; "1|2" ] "SELECT a, count(*) FROM t GROUP BY a ORDER BY row_number() OVER (ORDER BY \ a DESC)"
let test_window_refuses_distinct () = let cat = setup () in let refused q = Alcotest.check_raises q (Catalog.Error.Prepare "DISTINCT is not supported for window functions") (fun () -> ignore (E.exec cat q)) in refused "SELECT count(DISTINCT a) OVER () FROM t"; refused "SELECT sum(DISTINCT a) OVER (ORDER BY a) FROM t"; refused "SELECT group_concat(DISTINCT b) OVER (PARTITION BY a) FROM t"; refused "SELECT count(DISTINCT a) FILTER (WHERE a > 1) OVER () FROM t"; check cat "a FILTER without an OVER still folds distinct arguments" [ "2" ] "SELECT count(DISTINCT a) FILTER (WHERE a > 1) FROM t"
let test_a_window_call_is_resolved () = let cat = setup () in let refused sentence q = Alcotest.check_raises q (Catalog.Error.Prepare sentence) (fun () -> ignore (E.exec cat q)) in let count name = "wrong number of arguments to function " ^ name ^ "()" in refused (count "lag") "SELECT lag() OVER () FROM t"; refused (count "first_value") "SELECT first_value() OVER () FROM t"; refused (count "nth_value") "SELECT nth_value(a) OVER () FROM t"; refused (count "ntile") "SELECT ntile() OVER () FROM t"; refused (count "row_number") "SELECT row_number(a) OVER () FROM t"; refused "no such function: nosuchwin" "SELECT nosuchwin(a) OVER () FROM t"; check cat "a name at no argument is the arity it has" [ "1|1"; "2|2"; "1|3"; "3|4" ] "SELECT a, row_number() OVER () FROM t"; check cat "and one at its widest" [ "1|0"; "2|1"; "1|2"; "3|1" ] "SELECT a, lag(a, 1, 0) OVER () FROM t"; check cat "and one that takes exactly two" [ "1|2"; "2|2"; "1|2"; "3|2" ] "SELECT a, nth_value(a, 2) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND \ UNBOUNDED FOLLOWING) FROM t"
(* A window's head names a window function or an aggregate. A scalar function in its place is refused while the statement is prepared, whatever its argument count, with the sentence of sqlite/src/resolve.c ("%#T() may not be used as a window function"). Every sentence is the sqlite3 3.54.0 CLI's. *)let test_a_scalar_is_no_window_head () = let cat = setup () in let refused name q = Alcotest.check_raises q (Catalog.Error.Prepare (name ^ "() may not be used as a window function")) (fun () -> ignore (E.exec cat q)) in refused "abs" "SELECT abs(a) OVER () FROM t"; refused "abs" "SELECT abs() OVER () FROM t"; refused "max" "SELECT max(a, b) OVER () FROM t"; refused "upper" "SELECT upper(b) OVER (ORDER BY a) FROM t"; check cat "an aggregate over the same shape still answers" [ "1|3"; "2|3"; "1|3"; "3|3" ] "SELECT a, max(a) OVER () FROM t"
(* A window function written as an ordinary call, with no OVER, is refused as a misuse at a count the window table registers it at, and as a count error at any other (sqlite/src/resolve.c, the TK_FUNCTION case). Every sentence is the sqlite3 3.54.0 CLI's. *)let test_a_window_function_needs_over () = let cat = setup () in let refused sentence q = Alcotest.check_raises q (Catalog.Error.Prepare sentence) (fun () -> ignore (E.exec cat q)) in let misuse name = "misuse of window function " ^ name ^ "()" in let count name = "wrong number of arguments to function " ^ name ^ "()" in refused (misuse "row_number") "SELECT row_number() FROM t"; refused (misuse "lag") "SELECT lag(a) FROM t"; refused (misuse "ntile") "SELECT ntile(2) FROM t"; refused (misuse "row_number") "SELECT a FROM t WHERE row_number() > 0"; refused (count "row_number") "SELECT row_number(a) FROM t"; refused (count "lag") "SELECT lag() FROM t"
let test_a_comparing_fold_over_a_frame_reads_a_sequence () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE c (k INTEGER, v TEXT)"); ignore (E.exec cat "CREATE TABLE d (k INTEGER, v TEXT COLLATE NOCASE)"); List.iter (fun t -> ignore (E.exec cat (Fmt.str "INSERT INTO %s VALUES (1,'Bob'), (1,'alice'), (2,'Carol'), \ (2,'dave')" t))) [ "c"; "d" ]; check cat "no sequence compares the bytes" [ "Bob|Bob"; "Carol|Bob"; "alice|Bob"; "dave|Bob" ] "SELECT v, min(v) OVER () FROM c ORDER BY v"; check cat "NOCASE folds the case over the whole partition" [ "Bob|alice"; "Carol|alice"; "alice|alice"; "dave|alice" ] "SELECT v, min(v COLLATE NOCASE) OVER () FROM c ORDER BY v"; check cat "and at the other end of the same order" [ "Bob|dave"; "Carol|dave"; "alice|dave"; "dave|dave" ] "SELECT v, max(v COLLATE NOCASE) OVER () FROM c ORDER BY v"; check cat "the whole argument is searched" [ "Bob|alice"; "Carol|alice"; "alice|alice"; "dave|alice" ] "SELECT v, min(CAST(v COLLATE NOCASE AS TEXT)) OVER () FROM c ORDER BY v"; check cat "each partition folds under it" [ "1|Bob|alice"; "2|Carol|Carol"; "1|alice|alice"; "2|dave|Carol" ] "SELECT k, v, min(v COLLATE NOCASE) OVER (PARTITION BY k) FROM c ORDER BY v"; check cat "and so does each frame" [ "Bob|Bob"; "Carol|Bob"; "alice|alice"; "dave|alice" ] "SELECT v, min(v COLLATE NOCASE) OVER (ORDER BY v ROWS BETWEEN UNBOUNDED \ PRECEDING AND CURRENT ROW) FROM c ORDER BY v"; check cat "a fold that orders nothing spends no sequence" (List.map (fun v -> v ^ "|Bob,alice,Carol,dave") [ "Bob"; "Carol"; "alice"; "dave" ]) "SELECT v, group_concat(v COLLATE NOCASE) OVER () FROM c ORDER BY v"; check cat "a declared sequence governs a fold that names none" [ "alice|alice"; "Bob|alice"; "Carol|alice"; "dave|alice" ] "SELECT v, min(v) OVER () FROM d ORDER BY v"
let test_a_window_key_reads_a_declared_sequence () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE pc (g TEXT COLLATE NOCASE, n INTEGER)"); ignore (E.exec cat "INSERT INTO pc VALUES ('a',1), ('A',2), ('b',3)"); ignore (E.exec cat "CREATE TABLE d (k INTEGER, v TEXT COLLATE NOCASE)"); ignore (E.exec cat "INSERT INTO d VALUES (1,'Bob'), (1,'alice'), (2,'Carol'), (2,'dave')"); check cat "a declared sequence folds the cases into one partition" [ "a|1|2"; "A|2|2"; "b|3|1" ] "SELECT g, n, count(g) OVER (PARTITION BY g) FROM pc ORDER BY n"; check cat "so every row of the partition sees the others" [ "a|1|1,2"; "A|2|1,2"; "b|3|3" ] "SELECT g, n, group_concat(n) OVER (PARTITION BY g) FROM pc ORDER BY n"; check cat "an explicit BINARY wins over the declared sequence" [ "a|1|1"; "A|2|1"; "b|3|1" ] "SELECT g, n, count(g) OVER (PARTITION BY g COLLATE BINARY) FROM pc ORDER \ BY n"; check cat "a partition it folded is one partition to number within" [ "a|1|1"; "A|2|2"; "b|3|1" ] "SELECT g, n, row_number() OVER (PARTITION BY g ORDER BY n) FROM pc ORDER \ BY n"; check cat "an ORDER BY key reads it too, with no COLLATE written" [ "alice|1"; "Bob|2"; "Carol|3"; "dave|4" ] "SELECT v, row_number() OVER (ORDER BY v) AS rn FROM d ORDER BY rn"; check cat "DESC reverses the declared order like any other" [ "dave|1"; "Carol|2"; "Bob|3"; "alice|4" ] "SELECT v, row_number() OVER (ORDER BY v DESC) AS rn FROM d ORDER BY rn"; check cat "and an explicit BINARY wins there as well" [ "Bob|1"; "Carol|2"; "alice|3"; "dave|4" ] "SELECT v, row_number() OVER (ORDER BY v COLLATE BINARY) AS rn FROM d \ ORDER BY rn"; check cat "the order it puts the rows in is the order each frame grows in" [ "Bob|alice,Bob"; "Carol|alice,Bob,Carol"; "alice|alice"; "dave|alice,Bob,Carol,dave"; ] "SELECT v, group_concat(v) OVER (ORDER BY v) AS g FROM d ORDER BY v \ COLLATE BINARY"
let suite = ( "window", [ Alcotest.test_case "a window reading no input column" `Quick test_no_input_columns; Alcotest.test_case "ranking over peer groups" `Quick test_ranking; Alcotest.test_case "navigation within the partition" `Quick test_navigation; Alcotest.test_case "explicit frames" `Quick test_frames; Alcotest.test_case "a window over a join" `Quick test_over_a_join; Alcotest.test_case "a window over a GROUP BY" `Quick test_over_a_group_by; Alcotest.test_case "a tie in a grouped window sort" `Quick test_a_tie_in_a_grouped_window_sort; Alcotest.test_case "a window reads a bare column" `Quick test_a_window_reads_a_bare_column; Alcotest.test_case "the result clauses around a window" `Quick test_result_clauses; Alcotest.test_case "percent_rank and cume_dist" `Quick test_percent_rank_and_cume_dist; Alcotest.test_case "partitions come out in key order" `Quick test_partitions_come_out_in_key_order; Alcotest.test_case "a partition key reads a collating sequence" `Quick test_partition_reads_a_collating_sequence; Alcotest.test_case "a window key reads its whole operand" `Quick test_a_window_key_reads_its_whole_operand; Alcotest.test_case "a window reachable only from ORDER BY" `Quick test_window_reachable_only_from_order_by; Alcotest.test_case "a window call refuses DISTINCT" `Quick test_window_refuses_distinct; Alcotest.test_case "a window call's name and arity are resolved" `Quick test_a_window_call_is_resolved; Alcotest.test_case "a scalar function is no window head" `Quick test_a_scalar_is_no_window_head; Alcotest.test_case "a window function needs OVER" `Quick test_a_window_function_needs_over; Alcotest.test_case "a comparing fold over a frame reads a sequence" `Quick test_a_comparing_fold_over_a_frame_reads_a_sequence; Alcotest.test_case "a window key reads a declared sequence" `Quick test_a_window_key_reads_a_declared_sequence; ] )