Something went wrong. Try again.
My own corner of monopam
Something went wrong. Try again.
100 kB · 2129 lines
OCaml
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130module Q = Sql.Querymodule Lexer = Sql.Lexermodule E = Sql.Enginemodule Ast = Sql.Astmodule Value = Catalog.Valueopen Catalog.Value
let select sql = match Lexer.parse_statements sql with | Ok [ Ast.Select s ] -> s | Ok _ -> Fmt.failwith "not a single SELECT: %s" sql | Error e -> Fmt.failwith "parse error: %s" e
let run cat sql = Sql.Engine.run_select (Sql.Engine.catalog cat) (select sql) [||]
let row = Alcotest.testable (Fmt.Dump.list Value.pp) (List.equal Value.equal)let rows = Alcotest.list row
(* A filtered, ordered, arithmetic-projected single-table scan, seeded through the Spi-based engine. *)let test_select () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES (1, 10), (3, 30), (2, 20)"); Alcotest.check rows "SELECT a, a + b WHERE a >= 2 ORDER BY a DESC" [ [ Int 3L; Int 33L ]; [ Int 2L; Int 22L ] ] (run cat "SELECT a, a + b FROM t WHERE a >= 2 ORDER BY a DESC")
(* An inner join resolved across two tables. *)let test_join () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE a (k INTEGER, x TEXT)"); ignore (E.exec cat "CREATE TABLE b (k INTEGER, y TEXT)"); ignore (E.exec cat "INSERT INTO a VALUES (1, 'ax'), (2, 'a2')"); ignore (E.exec cat "INSERT INTO b VALUES (1, 'by'), (2, 'b2')"); Alcotest.check rows "a JOIN b ON a.k = b.k" [ [ Text "ax"; Text "by" ]; [ Text "a2"; Text "b2" ] ] (run cat "SELECT a.x, b.y FROM a JOIN b ON a.k = b.k ORDER BY a.k")
(* A recursive CTE: the seed and each delta arm run through the injected subquery runner ({!Sql.Engine.run_select} over the same catalog), counting 1..5 by semi-naive evaluation. *)let test_recursive_cte () = let cat = Catalog.of_mem [] in Alcotest.check rows "WITH RECURSIVE c(n) counts to 5" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ]; [ Int 4L ]; [ Int 5L ] ] (run cat "WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n + 1 FROM c WHERE \ n < 5) SELECT n FROM c")
(* A compound select: UNION dedups across the two arms and orders the result. *)let test_compound () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (n INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES (1), (2), (2), (3)"); Alcotest.check rows "SELECT n FROM t UNION VALUES (3), (4)" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ]; [ Int 4L ] ] (run cat "SELECT n FROM t UNION SELECT * FROM (VALUES (3), (4)) ORDER BY n")
(* An unknown column is a loud failure, not an empty result -- seed a row so the projection actually evaluates the bad reference. *)let test_unknown_column () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES (1)"); Alcotest.check_raises "unknown column fails" (Catalog.Error.Prepare "no such column: nope") (fun () -> ignore (run cat "SELECT nope FROM t"))
let seed_alias_table () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (x TEXT, y TEXT, n INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES ('a','P',1),('a','Q',2),('b','P',3),('b','Q',4)"); cat
(* A GROUP BY naming an alias no input column shadows groups by that alias's expression: two y values, two rows of two. *)let test_group_by_alias () = let cat = seed_alias_table () in Alcotest.check rows "GROUP BY g resolves the alias g = y" [ [ Text "P"; Int 2L ]; [ Text "Q"; Int 2L ] ] (run cat "SELECT y AS g, count(*) FROM t GROUP BY g ORDER BY g"); Alcotest.check rows "an aliased expression groups by the expression" [ [ Int 10L; Int 1L ]; [ Int 20L; Int 1L ]; [ Int 30L; Int 1L ]; [ Int 40L; Int 1L ]; ] (run cat "SELECT n * 10 AS m, count(*) FROM t GROUP BY m ORDER BY m")
(* When the alias collides with a real input column, the input column wins: the groups are t.x ('a' and 'b'), each of two rows, not the two y values. *)let test_group_by_alias_shadowed_by_column () = let cat = seed_alias_table () in Alcotest.check rows "GROUP BY x groups by the input column t.x, not the alias" [ [ Text "P"; Int 2L ]; [ Text "P"; Int 2L ] ] (run cat "SELECT y AS x, count(*) AS c FROM t GROUP BY x ORDER BY t.x")
(* ORDER BY has the opposite precedence: the alias wins over the input column, so this orders by y, pairing 'P' rows before 'Q' rows. *)let test_order_by_alias_beats_column () = let cat = seed_alias_table () in Alcotest.check rows "ORDER BY x uses the alias x = y" [ [ Text "P"; Int 1L ]; [ Text "P"; Int 3L ]; [ Text "Q"; Int 2L ]; [ Text "Q"; Int 4L ]; ] (run cat "SELECT y AS x, n FROM t ORDER BY x, n")
let seed_correlated_alias_tables () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES (1,10), (2,20), (3,30)"); ignore (E.exec cat "CREATE TABLE u (b INTEGER)"); ignore (E.exec cat "INSERT INTO u VALUES (2), (3)"); ignore (E.exec cat "CREATE TABLE w (x INTEGER)"); ignore (E.exec cat "INSERT INTO w VALUES (2)"); cat
let test_an_alias_reaches_a_subquery_body () = let cat = seed_correlated_alias_tables () in let two_three = [ [ Int 2L ]; [ Int 3L ] ] in let all_three = [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] in Alcotest.check rows "an EXISTS body reads the outer alias" two_three (run cat "SELECT a+0 AS x FROM t WHERE EXISTS (SELECT 1 FROM u WHERE b = x)"); Alcotest.check rows "a scalar subquery body does too" two_three (run cat "SELECT a+0 AS x FROM t WHERE (SELECT count(b) FROM u WHERE b = x) > 0"); Alcotest.check rows "so does a membership's subquery" [ [ Int 2L ] ] (run cat "SELECT a+0 AS x FROM t WHERE 2 IN (SELECT b FROM u WHERE b = x)"); Alcotest.check rows "and one two scopes down" two_three (run cat "SELECT a+0 AS x FROM t WHERE EXISTS (SELECT 1 FROM u WHERE EXISTS \ (SELECT 1 FROM u AS v WHERE v.b = x))"); Alcotest.check rows "the subquery's own FROM shadows it" all_three (run cat "SELECT a+0 AS x FROM t WHERE EXISTS (SELECT 1 FROM w WHERE x = 2)"); Alcotest.check rows "the subquery's own alias shadows it in its WHERE" all_three (run cat "SELECT a+0 AS x FROM t WHERE EXISTS (SELECT b AS x FROM u WHERE x = 2)"); Alcotest.check rows "a shadowing FROM two scopes down shadows it there" all_three (run cat "SELECT a+0 AS x FROM t WHERE EXISTS (SELECT 1 FROM u WHERE EXISTS \ (SELECT 1 FROM w WHERE w.x = x))")
(* A GROUP BY name that is neither an input column nor an alias still fails loud; the alias fallback must not turn a typo into an empty grouping. *)let test_group_by_unknown_name () = let cat = seed_alias_table () in Alcotest.check_raises "GROUP BY nope fails" (Catalog.Error.Prepare "no such column: nope") (fun () -> ignore (run cat "SELECT y AS g, count(*) FROM t GROUP BY nope"))
let nocase_catalog () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE c (v TEXT COLLATE NOCASE)"); ignore (E.exec cat "INSERT INTO c VALUES ('Alice')"); Sql.Engine.catalog cat
let test_declared_collation () = let cat = nocase_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let alice = [ [ Text "Alice" ] ] in Alcotest.check rows "a top-level comparison" alice (run "SELECT v FROM c WHERE v = 'alice'"); Alcotest.check rows "a comparison inside CASE" alice (run "SELECT v FROM c WHERE (CASE WHEN v = 'alice' THEN 1 ELSE 0 END) = 1"); Alcotest.check rows "a comparison inside a function argument" alice (run "SELECT v FROM c WHERE coalesce(v = 'alice', 0) = 1"); Alcotest.check rows "a comparison in the select list" [ [ Int 1L ] ] (run "SELECT v = 'alice' FROM c"); Alcotest.check rows "a comparison in HAVING" alice (run "SELECT v FROM c GROUP BY v HAVING v = 'alice'")
let test_declared_collation_through_cast () = let cat = nocase_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let alice = [ [ Text "Alice" ] ] in Alcotest.check rows "a CAST of the column keeps its collation" alice (run "SELECT v FROM c WHERE CAST(v AS TEXT) = 'alice'"); Alcotest.check rows "and so does a CAST of a CAST" alice (run "SELECT v FROM c WHERE CAST(CAST(v AS TEXT) AS TEXT) = 'alice'"); Alcotest.check rows "on the right-hand side too" alice (run "SELECT v FROM c WHERE 'alice' = CAST(v AS TEXT)"); Alcotest.check rows "an IN list tests membership under it" alice (run "SELECT v FROM c WHERE CAST(v AS TEXT) IN ('alice')"); Alcotest.check rows "an explicit COLLATE still wins over it" [] (run "SELECT v FROM c WHERE CAST(v AS TEXT) COLLATE BINARY = 'alice'")
(* Ordering under the same rule, on a table whose two rows sort one way under NOCASE and the other under BINARY: sqlite3 3.54.0 answers a,B for a bare column and for a CAST of it, and B,a only when BINARY is spelled out. *)let test_declared_collation_orders_through_cast () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE c (v TEXT COLLATE NOCASE)"); ignore (E.exec cat "INSERT INTO c VALUES ('B'), ('a')"); let cat = Sql.Engine.catalog cat in let run sql = Sql.Engine.run_select cat (select sql) [||] in let nocase = [ [ Text "a" ]; [ Text "B" ] ] in Alcotest.check rows "ORDER BY the column sorts under its collation" nocase (run "SELECT v FROM c ORDER BY v"); Alcotest.check rows "ORDER BY a CAST of it sorts the same way" nocase (run "SELECT v FROM c ORDER BY CAST(v AS TEXT)"); Alcotest.check rows "an explicit BINARY sorts the other way" [ [ Text "B" ]; [ Text "a" ] ] (run "SELECT v FROM c ORDER BY v COLLATE BINARY")
let unary_plus_catalog () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (v TEXT, a INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES ('Alice', 1)"); let q = Sql.Engine.catalog cat in { q with Q.collation = (fun _ col -> if col = "v" then Some "NOCASE" else None); }
let test_unary_plus_keeps_collation () = let cat = unary_plus_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let alice = [ [ Text "Alice" ] ] in Alcotest.check rows "unary + keeps the column's declared sequence" alice (run "SELECT v FROM t WHERE +v = 'alice'"); Alcotest.check rows "and so does a pile of them" alice (run "SELECT v FROM t WHERE ++v = 'alice'"); Alcotest.check rows "on the right-hand side too" alice (run "SELECT v FROM t WHERE 'alice' = +v"); Alcotest.check rows "an IN list tests membership under it" alice (run "SELECT v FROM t WHERE +v IN ('alice')"); Alcotest.check rows "an explicit BINARY wins over the declared sequence" [] (run "SELECT v FROM t WHERE (v COLLATE BINARY) = 'alice'"); Alcotest.check rows "including through an IN list" [] (run "SELECT v FROM t WHERE (v COLLATE BINARY) IN ('alice')")
let test_unary_plus_strips_affinity () = let cat = unary_plus_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in Alcotest.check rows "unary + leaves nothing to coerce the text operand" [ [ Int 0L ] ] (run "SELECT +a = '1' FROM t"); Alcotest.check rows "an explicit COLLATE keeps the column's affinity" [ [ Int 1L ] ] (run "SELECT (a COLLATE BINARY) = '1' FROM t"); Alcotest.check rows "a bare column keeps it too" [ [ Int 1L ] ] (run "SELECT a = '1' FROM t"); Alcotest.check rows "two numeric operands never needed it" [ [ Int 1L ] ] (run "SELECT +a = 1 FROM t"); Alcotest.check rows "the same, through an IN list" [ [ Int 0L ] ] (run "SELECT +a IN ('1') FROM t")
(* datatype3.html sec.7.1 rule 1 asks whether an operand "has an explicit collating function assignment", not whether its outermost node is one: a postfix COLLATE under a CAST or a unary + still names the comparison's sequence, and still wins over the column's declared one. *)let test_explicit_collation_below_the_top_node () = let cat = unary_plus_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let alice = [ [ Text "Alice" ] ] in Alcotest.check rows "a COLLATE inside a CAST still names the sequence" alice (run "SELECT v FROM t WHERE CAST(v COLLATE NOCASE AS TEXT) = 'alice'"); Alcotest.check rows "an inner BINARY beats the column's NOCASE" [] (run "SELECT v FROM t WHERE CAST(v COLLATE BINARY AS TEXT) = 'alice'"); Alcotest.check rows "a COLLATE under a unary + names it too" [] (run "SELECT v FROM t WHERE +(v COLLATE BINARY) = 'alice'")
let collate_subexpression_catalog () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE c (a TEXT, b TEXT)"); ignore (E.exec cat "INSERT INTO c VALUES ('Alice', 'ALICE')"); Sql.Engine.catalog cat
let test_explicit_collation_in_any_subexpression () = let cat = collate_subexpression_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let yes = [ [ Int 1L ] ] and no = [ [ Int 0L ] ] in Alcotest.check rows "a COLLATE under a concatenation names the sequence" yes (run "SELECT (a COLLATE NOCASE) || '' = b FROM c"); Alcotest.check rows "on the concatenation's right operand too" yes (run "SELECT ('' || a COLLATE NOCASE) = b FROM c"); Alcotest.check rows "a COLLATE in a CASE branch names it" yes (run "SELECT (CASE WHEN 1 THEN a COLLATE NOCASE END) = b FROM c"); Alcotest.check rows "a COLLATE in a function argument names it" yes (run "SELECT substr(a COLLATE NOCASE, 1) = b FROM c"); Alcotest.check rows "a COLLATE in a CASE's own test names it" yes (run "SELECT a = (CASE WHEN (b COLLATE NOCASE) = 'x' THEN 'zz' ELSE b END) \ FROM c"); Alcotest.check rows "the left operand is searched to any depth first" no (run "SELECT (a COLLATE BINARY) || '' = b COLLATE NOCASE FROM c"); Alcotest.check rows "so a deep left assignment beats a shallow right one" yes (run "SELECT a || ('' COLLATE NOCASE) = b COLLATE BINARY FROM c"); Alcotest.check rows "nothing is carried out of a subquery" no (run "SELECT (SELECT a COLLATE NOCASE FROM c) = b FROM c"); Alcotest.check rows "an IN list's own COLLATE does not count" no (run "SELECT a IN (b COLLATE NOCASE, 'zz') FROM c"); Alcotest.check rows "one inside the IN operand does" yes (run "SELECT (a || '' COLLATE NOCASE) IN (b) FROM c")
let single_element_in_catalog () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (v TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES ('abc'), ('ABC')"); Sql.Engine.catalog cat
let test_a_single_constant_element_in_is_an_equality () = let cat = single_element_in_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let yes = [ [ Int 1L ] ] and no = [ [ Int 0L ] ] in Alcotest.check rows "the lone candidate's COLLATE governs the comparison" yes (run "SELECT 'ABC' IN ('abc' COLLATE NOCASE)"); Alcotest.check rows "a second candidate leaves it a membership again" no (run "SELECT 'ABC' IN ('abc' COLLATE NOCASE, 'zzz')"); Alcotest.check rows "with no COLLATE the equality answers as the membership did" no (run "SELECT 'ABC' IN ('abc')"); Alcotest.check rows "NOT IN is the negated equality" no (run "SELECT 'ABC' NOT IN ('abc' COLLATE NOCASE)"); Alcotest.check rows "a NULL left operand still answers NULL" [ [ Text "null" ] ] (run "SELECT typeof(NULL IN ('abc' COLLATE NOCASE))"); Alcotest.check rows "and so does a NULL candidate" [ [ Text "null" ] ] (run "SELECT typeof('ABC' IN (NULL))"); Alcotest.check rows "a deterministic call over constants is constant" yes (run "SELECT 'ABC' IN (lower('ABC') COLLATE NOCASE)"); Alcotest.check rows "so is a CAST of one" yes (run "SELECT 'ABC' IN (CAST('abc' AS TEXT) COLLATE NOCASE)"); Alcotest.check rows "a bound parameter is constant too" yes (Sql.Engine.run_select cat (select "SELECT 'ABC' IN (?1 COLLATE NOCASE)") [| Text "abc" |]); Alcotest.check rows "a call reading a column is not constant" [ [ Text "abc"; Int 0L ]; [ Text "ABC"; Int 0L ] ] (run "SELECT v, 'ABC' IN (lower(v) COLLATE NOCASE) FROM t"); Alcotest.check rows "and neither is the column itself" [ [ Text "abc"; Int 0L ]; [ Text "ABC"; Int 1L ] ] (run "SELECT v, 'ABC' IN (v COLLATE NOCASE) FROM t"); Alcotest.check rows "a non-deterministic call is not constant" no (run "SELECT 'ABC' IN (CASE WHEN random() < 0 THEN 'abc' ELSE 'abc' END \ COLLATE NOCASE)"); Alcotest.check rows "nor is a subquery" no (run "SELECT 'ABC' IN ((SELECT 'abc') COLLATE NOCASE)"); Alcotest.check rows "the plain spellings are unmoved" [ [ Int 1L; Int 0L; Int 0L; Int 0L ] ] (run "SELECT 1 IN (1), 'a' IN ('b'), '1' IN (1), 1 IN ('1')"); Alcotest.check rows "a WHERE reads the rewritten comparison too" [ [ Text "abc" ]; [ Text "ABC" ] ] (run "SELECT v FROM t WHERE v IN ('abc' COLLATE NOCASE)"); Alcotest.check rows "an aggregate is not constant" no (run "SELECT 'ABC' IN (max(v) COLLATE NOCASE) FROM t"); Alcotest.check rows "not even one folding a constant" no (run "SELECT 'ABC' IN (max('abc') COLLATE NOCASE) FROM t"); Alcotest.check rows "the argument count tells the scalar of that name apart" yes (run "SELECT 'ABC' IN (max('abc','abc') COLLATE NOCASE)"); Alcotest.check rows "a window function is not constant either" [ [ Int 0L ]; [ Int 0L ] ] (run "SELECT 'ABC' IN (max(v) OVER () COLLATE NOCASE) FROM t")
let declared_nocase_catalog () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE n (v TEXT, w TEXT)"); ignore (E.exec cat "INSERT INTO n VALUES ('Alice', 'ALICE')"); let q = Sql.Engine.catalog cat in { q with Q.collation = (fun _ col -> if col = "v" then Some "NOCASE" else None); }
let test_explicit_collation_outranks_a_declared_one () = let cat = declared_nocase_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let yes = [ [ Int 1L ] ] and no = [ [ Int 0L ] ] in Alcotest.check rows "with none explicit the column's sequence decides" yes (run "SELECT v = w FROM n"); Alcotest.check rows "an explicit one at the top node beats it" no (run "SELECT v = (w COLLATE BINARY) FROM n"); Alcotest.check rows "and so does one a level down in the same operand" no (run "SELECT v = (w COLLATE BINARY) || '' FROM n"); Alcotest.check rows "the declared sequence loses from either side" no (run "SELECT v || '' = (w COLLATE BINARY) FROM n")
let test_explicit_collation_in_any_subexpression_of_a_key () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE d (s TEXT)"); ignore (E.exec cat "INSERT INTO d VALUES ('a'), ('A')"); ignore (E.exec cat "CREATE TABLE o (s TEXT)"); ignore (E.exec cat "INSERT INTO o VALUES ('a'), ('B'), ('c')"); let run sql = Sql.Engine.run_select (Sql.Engine.catalog cat) (select sql) [||] in Alcotest.check rows "DISTINCT dedups by binary value without one" [ [ Text "a" ]; [ Text "A" ] ] (run "SELECT DISTINCT s || '' FROM d"); Alcotest.check rows "and under NOCASE with one buried in the key" [ [ Text "a" ] ] (run "SELECT DISTINCT (s COLLATE NOCASE) || '' FROM d"); Alcotest.check rows "GROUP BY splits the two spellings without one" [ [ Int 1L ]; [ Int 1L ] ] (run "SELECT count(s) FROM d GROUP BY s || ''"); Alcotest.check rows "and buckets them together with one" [ [ Int 2L ] ] (run "SELECT count(s) FROM d GROUP BY (s COLLATE NOCASE) || ''"); Alcotest.check rows "ORDER BY sorts by byte without one" [ [ Text "B" ]; [ Text "a" ]; [ Text "c" ] ] (run "SELECT s FROM o ORDER BY s || ''"); Alcotest.check rows "and case-insensitively with one" [ [ Text "a" ]; [ Text "B" ]; [ Text "c" ] ] (run "SELECT s FROM o ORDER BY (s COLLATE NOCASE) || ''")
let test_a_distinct_collation_is_indexed_by_projected_column () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t3 (a, b, c)"); ignore (E.exec cat "INSERT INTO t3 VALUES (1,'x','p'), (1,'X','p')"); let run sql = Sql.Engine.run_select (Sql.Engine.catalog cat) (select sql) [||] in Alcotest.check rows "the sequence folds the column it is written on" [ [ Int 1L; Text "x" ] ] (run "SELECT DISTINCT a, b COLLATE NOCASE FROM t3"); let both = [ [ Int 1L; Text "x"; Text "p"; Text "p" ]; [ Int 1L; Text "X"; Text "p"; Text "p" ]; ] in Alcotest.check rows "spelled out, it changes no answer on the column it names" both (run "SELECT DISTINCT a, b, c, c COLLATE NOCASE FROM t3"); Alcotest.check rows "and a star projecting the same three values keeps both" both (run "SELECT DISTINCT *, c COLLATE NOCASE FROM t3")
let nocase_mixed_case_catalog () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (Nm TEXT COLLATE NOCASE)"); ignore (E.exec cat "INSERT INTO t VALUES ('abc'), ('ABC')"); Sql.Engine.catalog cat
let test_declared_collation_ignores_identifier_case () = let cat = nocase_mixed_case_catalog () in let run sql = Sql.Engine.run_select cat (select sql) [||] in let two = [ [ Int 2L ] ] in Alcotest.check rows "GROUP BY spelled as declared" two (run "SELECT count(*) FROM t GROUP BY Nm"); Alcotest.check rows "GROUP BY spelled in another case" two (run "SELECT count(*) FROM t GROUP BY nm"); Alcotest.check rows "WHERE spelled in another case" two (run "SELECT count(*) FROM t WHERE nm = 'ABC'"); Alcotest.check rows "a qualifier spelled in another case than its alias" two (run "SELECT count(*) FROM t AS T WHERE t.nm = 'ABC'")
let test_rowid_needs_a_relation_with_one () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER, c TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES (1,10,'x'), (2,20,'y'), (3,30,'z')"); let ids = [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] in Alcotest.check rows "a base table has one" ids (run cat "SELECT rowid FROM t"); Alcotest.check rows "qualified too" ids (run cat "SELECT t.rowid FROM t"); Alcotest.check rows "and under each of its names" ids (run cat "SELECT oid FROM t"); let rejects what msg sql = Alcotest.check_raises what (Catalog.Error.Prepare msg) (fun () -> ignore (run cat sql)) in rejects "a materialised CTE has none" "no such column: rowid" "WITH e AS (SELECT a, b, c FROM t) SELECT rowid FROM e"; rejects "qualified by the CTE's own name" "no such column: e.rowid" "WITH e AS (SELECT a, b, c FROM t) SELECT e.rowid FROM e"; rejects "a VALUES CTE has none either" "no such column: rowid" "WITH v(x) AS (VALUES (1),(2)) SELECT rowid FROM v"; rejects "nor does a derived table" "no such column: rowid" "SELECT rowid FROM (SELECT a FROM t)"; rejects "an alias of the name is reported as written" "no such column: _rowid_" "WITH e AS (SELECT a FROM t) SELECT _rowid_ FROM e"; rejects "and an aggregate over it is refused too" "no such column: rowid" "WITH e AS (SELECT a FROM t) SELECT count(rowid) FROM e"
let test_hoisting_reads_names_as_identifiers () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER)"); ignore (E.exec cat "CREATE TABLE u (c INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES (1)"); ignore (E.exec cat "INSERT INTO u VALUES (7)"); let qcat = E.catalog cat in let hoisted sql = Sql.Render.select (Q.hoist_subqueries ~run:(fun s -> E.run_select qcat s [||]) qcat (select sql)) in Alcotest.(check string) "the schema's own spelling folds to the value" "SELECT a FROM t WHERE (a = 7)" (hoisted "SELECT a FROM t WHERE a = (SELECT max(c) FROM u)"); Alcotest.(check string) "and so does the capitalised one" "SELECT a FROM t WHERE (a = 7)" (hoisted "SELECT a FROM t WHERE a = (SELECT max(C) FROM u)"); Alcotest.(check string) "a qualifier spelled in another case folds too" "SELECT a FROM t WHERE (a = 7)" (hoisted "SELECT a FROM t WHERE a = (SELECT max(U.c) FROM u)"); (* a column of the OUTER query is free wherever it is spelled, so that subquery stays as written and is run per row *) Alcotest.(check string) "an outer column is left to the runner" "SELECT a FROM t WHERE (a = (SELECT max(c) FROM u WHERE (c = T.A)))" (hoisted "SELECT a FROM t WHERE a = (SELECT max(c) FROM u WHERE c = T.A)")
let test_cte_defined_in_terms_of_itself () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t1 (a INTEGER, b INTEGER, c TEXT)"); ignore (E.exec cat "INSERT INTO t1 VALUES (1,10,'x'), (2,20,'y'), (3,30,'z')"); let circular what name sql = Alcotest.check_raises what (Failure ("circular reference: " ^ name)) (fun () -> ignore (run cat sql)) in circular "a body that is not a compound" "t1" "WITH t1 AS (SELECT a FROM t1) SELECT a FROM t1"; circular "the message does not depend on what the body cannot resolve" "t1" "WITH t1 AS (SELECT a, c FROM t1 WHERE b >= 20) SELECT a FROM t1"; circular "the name matched as a SQL identifier" "t1" "WITH t1 AS (SELECT a FROM T1) SELECT a FROM t1"; circular "named through a derived table" "t1" "WITH t1 AS (SELECT a FROM (SELECT a FROM t1)) SELECT a FROM t1"; circular "named from a scalar subquery" "t1" "WITH t1 AS (SELECT (SELECT count(a) FROM t1) AS n) SELECT n FROM t1"; circular "named from an EXISTS in the body's WHERE" "t1" "WITH t1 AS (SELECT 1 AS x WHERE EXISTS (SELECT 1 FROM t1)) SELECT x FROM \ t1"; circular "a compound whose seed names it" "r" "WITH r AS (SELECT * FROM r UNION ALL SELECT 1) SELECT x FROM r"; (* the one self-reference that has a fixpoint still runs *) let three = [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] in Alcotest.check rows "a compound whose arm names it recurses" three (run cat "WITH r AS (SELECT 1 AS x UNION ALL SELECT x+1 FROM r WHERE x<3) SELECT \ x FROM r"); Alcotest.check rows "under UNION as well" three (run cat "WITH r AS (SELECT 1 AS x UNION SELECT x+1 FROM r WHERE x<3) SELECT x \ FROM r"); (* nothing reads it, so nothing resolves it *) Alcotest.check rows "an unread circular body raises nothing" [ [ Int 1L ] ] (run cat "WITH t1 AS (SELECT a FROM t1) SELECT 1"); Alcotest.check rows "nor does an unread body that cannot resolve at all" [ [ Int 1L ] ] (run cat "WITH x AS (SELECT nope FROM t1) SELECT 1"); (* Naming which columns the relation publishes is resolution too, so it waits for a reader like the rows do. A [*] needs the source's column list, and asking for one the schema does not have raises: here a table-valued function, which raises on every backend, and equally a plain missing table on a store whose [columns] raises rather than answering none. *) Alcotest.check rows "nor does an unread star over a source that is not there" [ [ Int 1L ] ] (run cat "WITH x AS (SELECT * FROM nosuchfn(1)) SELECT 1")
let test_nested_with_shadows_rather_than_cycles () = let cat = Catalog.of_mem [] in let circular what name sql = Alcotest.check_raises what (Failure ("circular reference: " ^ name)) (fun () -> ignore (run cat sql)) in Alcotest.check rows "an inner WITH of the same name shadows the outer" [ [ Int 1L ] ] (run cat "WITH e AS (WITH e AS (SELECT 1) SELECT * FROM e) SELECT * FROM e"); Alcotest.check rows "and it shadows from inside a derived table" [ [ Int 2L ] ] (run cat "WITH e AS (SELECT * FROM (WITH e AS (SELECT 2) SELECT * FROM e)) \ SELECT * FROM e"); Alcotest.check rows "and from inside a scalar subquery" [ [ Int 3L ] ] (run cat "WITH e AS (SELECT (SELECT * FROM (WITH e AS (SELECT 3) SELECT * FROM \ e))) SELECT * FROM e"); Alcotest.check rows "a shadowed body is still a compound" [ [ Int 1L ]; [ Int 9L ] ] (run cat "WITH e AS (WITH e AS (SELECT 1) SELECT * FROM e UNION ALL SELECT 9) \ SELECT * FROM e"); (* an inner WITH binding some other name shadows nothing *) circular "an inner WITH of another name leaves the reference outer" "e" "WITH e AS (WITH f AS (SELECT 1) SELECT * FROM e) SELECT * FROM e"; circular "and a body naming itself with no inner WITH still has no fixpoint" "e" "WITH e AS (SELECT * FROM e) SELECT * FROM e"; circular "as does a cycle through two members" "a" "WITH a AS (SELECT * FROM b), b AS (SELECT * FROM a) SELECT * FROM a"; Alcotest.check rows "and the one self-reference with a fixpoint still runs" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c WHERE \ x<3) SELECT * FROM c")
let test_an_unread_nested_with_is_never_expanded () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER, c TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES (1,10,'x'), (2,20,'y'), (3,30,'z')"); Alcotest.check rows "an unread inner body naming the relation is not a cycle" [ [ Int 1L ] ] (run cat "WITH e AS (WITH x AS (SELECT * FROM e) SELECT 1) SELECT * FROM e"); Alcotest.check rows "nor is one beside a member the body does read" [ [ Int 5L ] ] (run cat "WITH e AS (WITH x AS (SELECT * FROM e), y AS (SELECT 5) SELECT * FROM \ y) SELECT * FROM e"); Alcotest.check rows "nor one under a derived table in a scalar subquery" [ [ Int 1L ] ] (run cat "WITH e AS (SELECT (SELECT 1 FROM (WITH x AS (SELECT * FROM e) SELECT \ 4))) SELECT * FROM e"); Alcotest.check rows "nor one that would close a cycle through two members" [ [ Int 7L ] ] (run cat "WITH a AS (WITH z AS (SELECT * FROM b) SELECT 7), b AS (SELECT * FROM \ a) SELECT * FROM a"); Alcotest.check_raises "the same body read is the cycle it looks like" (Failure "circular reference: e") (fun () -> ignore (run cat "WITH e AS (WITH x AS (SELECT * FROM e) SELECT * FROM x) SELECT * \ FROM e"))
let test_a_shadowed_reference_is_not_this_relation () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER, c TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES (1,10,'x'), (2,20,'y'), (3,30,'z')"); Alcotest.check rows "the inner relation is what the body reads" [ [ Int 9L ] ] (run cat "WITH e AS (SELECT a FROM t), g AS (WITH e AS (SELECT 9 AS a) SELECT a \ FROM e) SELECT a FROM g"); Alcotest.check rows "and so it is under a derived table" [ [ Int 9L ] ] (run cat "WITH e AS (SELECT a FROM t) SELECT a FROM (WITH e AS (SELECT 9 AS a) \ SELECT a FROM e)"); Alcotest.check rows "a shadowed name closes no cycle" [ [ Int 3L ] ] (run cat "WITH e AS (WITH f AS (SELECT a FROM t) SELECT a FROM f), f AS (SELECT \ a FROM e) SELECT count(a) FROM f")
let test_the_whole_with_list_is_in_scope () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t1 (a INTEGER, b INTEGER, c TEXT)"); ignore (E.exec cat "INSERT INTO t1 VALUES (1,10,'x'), (2,20,'y'), (3,30,'z')"); let circular what name sql = Alcotest.check_raises what (Failure ("circular reference: " ^ name)) (fun () -> ignore (run cat sql)) in Alcotest.check rows "a body reads a CTE declared after it" [ [ Int 3L ] ] (run cat "WITH e AS (SELECT a FROM f), f AS (SELECT a FROM t1) SELECT count(a) \ FROM e"); Alcotest.check rows "and does so where nothing is flattened" [ [ Int 9L ] ] (run cat "WITH e AS (SELECT a FROM f), f AS (SELECT a FROM t1) SELECT count(x.a) \ FROM e x JOIN e y"); Alcotest.check rows "the one read later may be the recursive one" [ [ Int 3L ] ] (run cat "WITH s AS (SELECT x FROM r), r AS (SELECT 1 AS x UNION ALL SELECT x+1 \ FROM r WHERE x<3) SELECT count(x) FROM s"); circular "two bodies reading each other" "e" "WITH e AS (SELECT a FROM f), f AS (SELECT a FROM e) SELECT count(a) FROM e"; circular "the same cycle entered at the other end" "f" "WITH e AS (SELECT a FROM f), f AS (SELECT a FROM e) SELECT count(a) FROM f"; circular "the name re-entered, not the name the reader spelled" "e" "WITH g AS (SELECT a FROM e), e AS (SELECT a FROM f), f AS (SELECT a FROM \ e) SELECT count(a) FROM g"; circular "a recursive arm reaching itself through another member" "r" "WITH RECURSIVE r AS (SELECT 1 AS x UNION ALL SELECT x+1 FROM g WHERE \ x<3), g AS (SELECT x FROM r) SELECT count(x) FROM r"; Alcotest.check rows "an unread cycle raises nothing" [ [ Int 1L ] ] (run cat "WITH e AS (SELECT a FROM f), f AS (SELECT a FROM e) SELECT 1")
(* sqlite.org/lang_select.html: a select-stmt is an optional WITH clause, then select-cores joined by compound-operators, then the ORDER BY and LIMIT; a select-core has no WITH of its own. lang_with.html: the common table expressions of that clause are in scope for the whole statement, so every arm of the compound reads them, wherever the select-stmt stands: at the top, in FROM, or as an expression subquery. Each expected value is what sqlite3 3.54.0 answers on the same rows. *)let test_a_with_scopes_over_every_arm () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE c (code TEXT, pop INTEGER)"); ignore (E.exec cat "INSERT INTO c VALUES ('a', 10), ('b', 20), ('c', 30)"); ignore (E.exec cat "CREATE TABLE t (a INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES (1), (2)"); let check what expected sql = Alcotest.check rows what expected (run cat sql) in let text = List.map (fun s -> [ Text s ]) in let p = "WITH p AS (SELECT code FROM c WHERE pop > 10) " in check "the first arm reads it" (text [ "b"; "c"; "x" ]) (p ^ "SELECT code FROM p UNION ALL SELECT 'x'"); check "a later arm alone reads it" (text [ "x"; "b"; "c" ]) (p ^ "SELECT 'x' UNION ALL SELECT code FROM p"); check "every arm reads it" (text [ "b"; "c"; "b"; "c" ]) (p ^ "SELECT code FROM p UNION ALL SELECT code FROM p"); check "under EXCEPT" (text [ "a" ]) (p ^ "SELECT code FROM c EXCEPT SELECT code FROM p"); check "the compound's ORDER BY sorts what every arm read" (text [ "c"; "c"; "b"; "b" ]) (p ^ "SELECT code FROM p UNION ALL SELECT code FROM p ORDER BY code DESC"); (* a WITH binding the name of a table hides the table from every arm *) check "it shadows a table in every arm" [ [ Int 9L ]; [ Int 9L ] ] "WITH t AS (SELECT 9 AS a) SELECT a FROM t UNION ALL SELECT a FROM t"; check "a recursive one whose body is a compound" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ]; [ Int 10L ]; [ Int 20L ]; [ Int 30L ]; ] "WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n < 3) \ SELECT n FROM r UNION ALL SELECT n*10 FROM r"; check "the compound in FROM" [ [ Int 4L ] ] ("SELECT count(*) FROM (" ^ p ^ "SELECT code FROM p UNION ALL SELECT code FROM p)"); check "a scalar subquery whose later arm reads it" (text [ "x" ]) ("SELECT (" ^ p ^ "SELECT 'x' UNION ALL SELECT code FROM p)"); check "an IN subquery" (text [ "b"; "c" ]) ("SELECT code FROM c WHERE code IN (" ^ p ^ "SELECT 'z' UNION SELECT code FROM p)"); check "an EXISTS subquery" [ [ Int 1L ]; [ Int 2L ] ] ("SELECT a FROM t WHERE EXISTS (" ^ p ^ "SELECT 'x' WHERE 0 UNION ALL SELECT code FROM p)"); check "a correlated subquery" [ [ Int 1L; Int 2L ]; [ Int 2L; Int 1L ] ] "SELECT a, (WITH q AS (SELECT pop FROM c) SELECT count(*) FROM q WHERE pop \ > t.a * 10 UNION ALL SELECT max(pop) FROM q WHERE pop > t.a * 10 ORDER BY \ 1 LIMIT 1) FROM t"; check "an alias in a later arm's WHERE" (text [ "c" ]) "SELECT code FROM c WHERE code IN (WITH q AS (SELECT code AS k FROM c) \ SELECT 'z' UNION SELECT k AS j FROM q WHERE j > 'b')"; check "a subquery nested in a later arm" [ [ Int 5L ] ] "SELECT (WITH q AS (SELECT 5 AS x) SELECT 1 UNION ALL SELECT (SELECT x \ FROM q) ORDER BY 1 DESC LIMIT 1)"; check "and one nested in a subquery's single core" [ [ Int 5L ] ] "SELECT (WITH q AS (SELECT 5 AS x) SELECT (SELECT x FROM q))"; check "a recursive one in a subquery" [ [ Int 3L ] ] "SELECT (WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r \ WHERE n < 3) SELECT 0 UNION ALL SELECT n FROM r ORDER BY 1 DESC LIMIT 1)"; check "a WITH inside an arm's FROM" [ [ Int 1L ]; [ Int 2L ] ] "SELECT x FROM (WITH q AS (SELECT 1 AS x) SELECT x FROM q) UNION ALL \ SELECT 2"
(* The placements the grammar has no production for, refused as sqlite3 3.54.0 refuses them: a select-core takes no WITH and no parentheses, and a WITH inside an arm's FROM ends with that subquery. *)let test_a_with_placement_the_grammar_refuses () = let cat = Catalog.of_mem [] in let syntax what near sql = match Lexer.parse_statements sql with | Ok _ -> Alcotest.failf "%s: parsed %s" what sql | Error e -> Alcotest.(check string) what (Fmt.str "near %S: syntax error" near) e in syntax "a WITH on a later arm" "WITH" "SELECT 1 UNION ALL WITH q AS (SELECT 2) SELECT * FROM q"; syntax "a second WITH after the first arm" "WITH" "WITH p AS (SELECT 1) SELECT * FROM p UNION ALL WITH q AS (SELECT 2) \ SELECT * FROM q"; syntax "a parenthesised later arm" "(" "SELECT 1 UNION ALL (WITH q AS (SELECT 2) SELECT * FROM q)"; syntax "a parenthesised first arm" "(" "(SELECT 1) UNION ALL SELECT 2"; Alcotest.check_raises "an arm's own WITH is not the sibling's" (Failure "no such table: q") (fun () -> ignore (run cat "SELECT x FROM (WITH q AS (SELECT 1 AS x) SELECT x FROM q) UNION \ ALL SELECT x FROM q"))
let test_a_cycle_names_where_it_was_entered () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER)"); let circular what name sql = Alcotest.check_raises what (Failure ("circular reference: " ^ name)) (fun () -> ignore (run cat sql)) in circular "entered at the member declared first" "a" "WITH a AS (SELECT * FROM b), b AS (SELECT * FROM a) SELECT * FROM a"; circular "and at the one declared second" "b" "WITH a AS (SELECT * FROM b), b AS (SELECT * FROM a) SELECT * FROM b"; circular "the declaration order decides nothing" "a" "WITH b AS (SELECT * FROM a), a AS (SELECT * FROM b) SELECT * FROM a"; circular "at either end of it" "b" "WITH b AS (SELECT * FROM a), a AS (SELECT * FROM b) SELECT * FROM b"; circular "a cycle through three members names the one re-entered" "c" "WITH a AS (SELECT * FROM b), b AS (SELECT * FROM c), c AS (SELECT * FROM \ a) SELECT * FROM c"; circular "which the reader chooses by entering there" "b" "WITH a AS (SELECT * FROM b), b AS (SELECT * FROM c), c AS (SELECT * FROM \ a) SELECT * FROM b"; Alcotest.check rows "and a cycle nothing reads raises nothing" [ [ Int 1L ] ] (run cat "WITH a AS (SELECT * FROM b), b AS (SELECT * FROM a), z AS (SELECT 1) \ SELECT * FROM z")
let seed_flatten_table () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER, c TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES (1,10,'x'), (2,20,'y'), (3,30,'z')"); cat
let flattened cat sql = Q.flatten_ctes (Sql.Engine.catalog cat) (select sql)
let cte_names (s : Ast.select) = List.map (fun (c : Ast.cte) -> c.Ast.name) s.ctes
let check_sql = Alcotest.(check string)let check_ctes = Alcotest.(check (list string))
(* The reported defect: a CTE read once from the outer FROM becomes the base table under the CTE's own name, and the reader's predicate joins the body's. The predicate now sits on a base table, which is what lets an index serve it.*)let test_flatten_single_use () = let cat = seed_flatten_table () in let f = flattened cat "WITH e AS (SELECT a, b FROM t) SELECT count(*) FROM e WHERE a = 2" in check_ctes "the CTE is gone" [] (cte_names f); check_sql "spliced into the reader" "SELECT count(*) FROM t e WHERE (a = 2)" (Sql.Render.select f); Alcotest.check rows "same answer as the flat query" [ [ Int 1L ] ] (run cat "WITH e AS (SELECT a, b FROM t) SELECT count(*) FROM e WHERE a = 2")
(* The body's WHERE follows it into the reader, ANDed with the reader's own, and a reference the body qualified by the base table's own name is re-qualified to the CTE's name so it still resolves. *)let test_flatten_merges_predicates () = let cat = seed_flatten_table () in let f = flattened cat "WITH e AS (SELECT a, b FROM t AS u WHERE u.b >= 20) SELECT a FROM e \ WHERE a < 3" in check_ctes "the CTE is gone" [] (cte_names f); check_sql "both predicates, body re-qualified" "SELECT a FROM t e WHERE ((e.b >= 20) AND (a < 3))" (Sql.Render.select f); Alcotest.check rows "only row 2" [ [ Int 2L ] ] (run cat "WITH e AS (SELECT a, b FROM t AS u WHERE u.b >= 20) SELECT a FROM e \ WHERE a < 3")
(* The flattened alias carries the base table's whole column list, so a [*] over a CTE that projected a subset is expanded to that subset first. Widening it would return columns the CTE never had. *)let test_flatten_star_keeps_the_cte_columns () = let cat = seed_flatten_table () in let f = flattened cat "WITH e AS (SELECT a, b FROM t) SELECT * FROM e WHERE a = 2" in check_sql "[*] expanded to the CTE's columns" "SELECT e.a, e.b FROM t e WHERE (a = 2)" (Sql.Render.select f); Alcotest.check rows "two columns, not three" [ [ Int 2L; Int 20L ] ] (run cat "WITH e AS (SELECT a, b FROM t) SELECT * FROM e WHERE a = 2")
let test_no_flatten_multi_use () = let cat = seed_flatten_table () in let sql = "WITH e AS (SELECT a, b FROM t) SELECT count(*) FROM e AS p JOIN e AS q ON \ p.a = q.a" in check_ctes "both references are spliced" [] (cte_names (flattened cat sql)); check_sql "each under its own alias" "SELECT count(*) FROM t p JOIN t q ON (p.a = q.a)" (Sql.Render.select (flattened cat sql)); Alcotest.check rows "self-join of three rows" [ [ Int 3L ] ] (run cat sql); let fenced = "WITH e AS MATERIALIZED (SELECT a, b FROM t) SELECT count(*) FROM e AS p \ JOIN e AS q ON p.a = q.a" in check_ctes "AS MATERIALIZED keeps the relation" [ "e" ] (cte_names (flattened cat fenced)); Alcotest.check rows "and answers the same" [ [ Int 3L ] ] (run cat fenced)
(* A reference the body cannot be spliced into -- a later compound arm, a subquery -- keeps the relation, and a relation that is built anyway is what every reader reads. Splicing the body into the readers that could take it would read the base table once for them and once more to build the relation for the rest. sqlite3 3.54.0 splices the body into every arm and so scans the table once per arm. *)let test_no_flatten_beside_a_reader_it_cannot_take () = let cat = seed_flatten_table () in let kept what expected sql = let f = flattened cat sql in check_ctes what [ "e" ] (cte_names f); check_sql what (Sql.Render.select (select sql)) (Sql.Render.select f); Alcotest.check rows what expected (run cat sql) in kept "a later arm reads it" [ [ Int 2L ]; [ Int 3L ]; [ Int 2L ]; [ Int 3L ] ] "WITH e AS (SELECT a, b FROM t WHERE b >= 20) SELECT a FROM e UNION ALL \ SELECT a FROM e"; kept "a subquery reads it" [ [ Int 2L ]; [ Int 3L ] ] "WITH e AS (SELECT a, b FROM t WHERE b >= 20) SELECT a FROM e WHERE a IN \ (SELECT a FROM e)"
(* sqlite3's constraints on the body: not an aggregate, not DISTINCT, no LIMIT, no ORDER BY, not compound. Each keeps the CTE materialised. *)let test_no_flatten_body_constraints () = let cat = seed_flatten_table () in let refused what sql expected = check_ctes what [ "e" ] (cte_names (flattened cat sql)); Alcotest.check rows what expected (run cat sql) in refused "an aggregate body" "WITH e AS (SELECT c, count(*) AS n FROM t GROUP BY c) SELECT n FROM e \ WHERE c = 'y'" [ [ Int 1L ] ]; refused "a DISTINCT body" "WITH e AS (SELECT DISTINCT a FROM t) SELECT count(*) FROM e WHERE a >= 2" [ [ Int 2L ] ]; refused "a LIMIT body" "WITH e AS (SELECT a FROM t ORDER BY a LIMIT 2) SELECT count(*) FROM e" [ [ Int 2L ] ]; refused "an ORDER BY body" "WITH e AS (SELECT a FROM t ORDER BY a) SELECT count(*) FROM e" [ [ Int 3L ] ]; refused "a compound body" "WITH e AS (SELECT a FROM t UNION SELECT 9) SELECT count(*) FROM e" [ [ Int 4L ] ]; refused "a body projecting an expression" "WITH e AS (SELECT a + b AS s FROM t) SELECT count(*) FROM e WHERE s > 15" [ [ Int 2L ] ]
(* sqlite3's constraint (22): a recursive CTE is never flattened, since its body reads the relation being defined. *)let test_no_flatten_recursive () = let cat = seed_flatten_table () in let sql = "WITH RECURSIVE n(i) AS (VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < \ 5) SELECT sum(i) FROM n" in check_ctes "the CTE stays" [ "n" ] (cte_names (flattened cat sql)); Alcotest.check rows "1..5 sums to 15" [ [ Int 15L ] ] (run cat sql)
(* A CTE read from a later CTE's body flattens into that body; walking the list backwards then leaves the chain collapsed in one pass. *)let test_flatten_chained_ctes () = let cat = seed_flatten_table () in let sql = "WITH x AS (SELECT a, b FROM t), y AS (SELECT a, b FROM x WHERE b >= 20) \ SELECT a FROM y WHERE a < 3" in let f = flattened cat sql in check_ctes "both CTEs are gone" [] (cte_names f); check_sql "collapsed to one scan" "SELECT a FROM t y WHERE ((y.b >= 20) AND (a < 3))" (Sql.Render.select f); Alcotest.check rows "only row 2" [ [ Int 2L ] ] (run cat sql)
let seed_join_tables cat = ignore (E.exec cat "CREATE TABLE u (a INTEGER, nm TEXT)"); ignore (E.exec cat "INSERT INTO u VALUES (1,'one'), (2,'two'), (4,'four')"); cat
(* An inner join's operand: the body's predicate is ANDed onto the reader's WHERE, which is where an inner join's ON lands anyway. *)let test_flatten_inner_join_operand () = let cat = seed_join_tables (seed_flatten_table ()) in let sql = "WITH e AS (SELECT a, b FROM t WHERE b >= 20) SELECT u.nm, e.b FROM u JOIN \ e ON e.a = u.a ORDER BY u.a" in let f = flattened cat sql in check_ctes "the CTE is gone" [] (cte_names f); check_sql "spliced into the join operand" "SELECT u.nm, e.b FROM u JOIN t e ON (e.a = u.a) WHERE (e.b >= 20) ORDER \ BY u.a" (Sql.Render.select f); Alcotest.check rows "only the matching row" [ [ Text "two"; Int 20L ] ] (run cat sql)
(* The operand a LEFT JOIN pads: the body's predicate belongs to that join's ON. In the reader's WHERE it would turn an unmatched row into a dropped one, and the row set says which happened. *)let test_flatten_left_join_operand () = let cat = seed_join_tables (seed_flatten_table ()) in let sql = "WITH e AS (SELECT a, b FROM t WHERE b >= 20) SELECT u.nm, e.b FROM u LEFT \ JOIN e ON e.a = u.a ORDER BY u.a" in let f = flattened cat sql in check_ctes "the CTE is gone" [] (cte_names f); check_sql "the body's predicate joins the ON, not the WHERE" "SELECT u.nm, e.b FROM u LEFT JOIN t e ON ((e.a = u.a) AND (e.b >= 20)) \ ORDER BY u.a" (Sql.Render.select f); Alcotest.check rows "the LEFT JOIN keeps its unmatched rows" [ [ Text "one"; Null ]; [ Text "two"; Int 20L ]; [ Text "four"; Null ] ] (run cat sql)
(* A RIGHT or FULL join pads the operands to its left, the leading one included, so a body predicate moved to the reader's WHERE drops the padded rows: this query answers three rows and the WHERE form answers one. sqlite3 refuses the right operand of a RIGHT join outright (26) and needs the whole ON machinery for the left one, so both shapes stay materialised here. *)let test_no_flatten_right_or_full_join () = let cat = seed_join_tables (seed_flatten_table ()) in let padded = [ [ Text "one"; Null ]; [ Text "two"; Int 20L ]; [ Text "four"; Null ] ] in let right = "WITH e AS (SELECT a, b, c FROM t WHERE b >= 20) SELECT u.nm, e.b FROM e \ RIGHT JOIN u ON e.a = u.a ORDER BY u.a" in check_ctes "the CTE stays under a RIGHT join" [ "e" ] (cte_names (flattened cat right)); Alcotest.check rows "the RIGHT join keeps its unmatched rows" padded (run cat right); let full = "WITH e AS (SELECT a, b, c FROM t WHERE b >= 20) SELECT u.nm, e.b FROM e \ FULL OUTER JOIN u ON e.a = u.a ORDER BY u.a, e.b" in check_ctes "the CTE stays under a FULL join" [ "e" ] (cte_names (flattened cat full)); Alcotest.check rows "the FULL join keeps both sides' unmatched rows" ([ Null; Int 30L ] :: padded) (run cat full)
(* sqlite3's (3d): the reader may not be DISTINCT when the CTE is the operand an outer join pads. *)let test_no_flatten_distinct_over_left_join () = let cat = seed_join_tables (seed_flatten_table ()) in let sql = "WITH e AS (SELECT a, b FROM t WHERE b >= 20) SELECT DISTINCT e.b FROM u \ LEFT JOIN e ON e.a = u.a ORDER BY e.b" in check_ctes "the CTE stays" [ "e" ] (cte_names (flattened cat sql)); Alcotest.check rows "unmatched NULL and the one match" [ [ Null ]; [ Int 20L ] ] (run cat sql)
(* A NATURAL / USING join is about the operands' common column names, so a splice that publishes a different column list under the alias changes which columns the join equates. A CTE exposing exactly its base table's columns changes nothing and still flattens. *)let test_no_flatten_natural_when_narrowing () = let cat = seed_join_tables (seed_flatten_table ()) in let narrowing = "WITH e AS (SELECT a FROM t WHERE b >= 20) SELECT count(*) FROM u NATURAL \ JOIN e" in check_ctes "a narrowing CTE stays" [ "e" ] (cte_names (flattened cat narrowing)); Alcotest.check rows "one row joins on a" [ [ Int 1L ] ] (run cat narrowing); let using = "WITH e AS (SELECT a FROM t WHERE b >= 20) SELECT count(*) FROM u JOIN e \ USING (a)" in check_ctes "so does a narrowing CTE under USING" [ "e" ] (cte_names (flattened cat using)); Alcotest.check rows "one row joins on a" [ [ Int 1L ] ] (run cat using)
(* A CTE that narrows its base table may still be spliced into a reader that joins: what the blanket refusal was guarding is a name the widened alias would capture, and only a name the base table carries can be captured. [*] expands to the reader's own scope with the CTE's exposed columns in the spliced position, so it lists what it listed before. *)let test_flatten_narrowing_cte_with_join () = let cat = seed_join_tables (seed_flatten_table ()) in let sql = "WITH e AS (SELECT a FROM t WHERE b >= 20) SELECT nm FROM e JOIN u ON u.a \ = e.a" in let f = flattened cat sql in check_ctes "the CTE is gone" [] (cte_names f); check_sql "spliced, and the other operand's name still resolves" "SELECT nm FROM t e JOIN u ON (u.a = e.a) WHERE (e.b >= 20)" (Sql.Render.select f); Alcotest.check rows "the one joined row" [ [ Text "two" ] ] (run cat sql); let star = "WITH e AS (SELECT a FROM t WHERE b >= 20) SELECT * FROM u JOIN e ON e.a = \ u.a" in let g = flattened cat star in check_ctes "the CTE is gone" [] (cte_names g); check_sql "[*] keeps the CTE's one column" "SELECT u.a, u.nm, e.a FROM u JOIN t e ON (e.a = u.a) WHERE (e.b >= 20)" (Sql.Render.select g); Alcotest.check rows "two columns of u, one of e" [ [ Int 2L; Text "two"; Int 2L ] ] (run cat star)
let test_no_flatten_captured_name () = let cat = seed_flatten_table () in ignore (E.exec cat "CREATE TABLE w (a INTEGER, nm TEXT, c TEXT)"); ignore (E.exec cat "INSERT INTO w VALUES (1,'one','W1'), (2,'two','W2'), (4,'f','W4')"); let sql = "WITH e AS (SELECT a FROM t WHERE b >= 20) SELECT c FROM e JOIN w ON w.a = \ e.a" in check_ctes "the CTE stays" [ "e" ] (cte_names (flattened cat sql)); Alcotest.check rows "c is still w's" [ [ Text "W2" ] ] (run cat sql); let shared = "WITH e AS (SELECT a FROM t WHERE c = 'y') SELECT nm FROM e JOIN w ON w.a \ = e.a" in let f = flattened cat shared in check_ctes "the CTE is gone" [] (cte_names f); check_sql "the body's predicate moves qualified" "SELECT nm FROM t e JOIN w ON (w.a = e.a) WHERE (e.c = 'y')" (Sql.Render.select f); Alcotest.check rows "and answers the joined row" [ [ Text "two" ] ] (run cat shared)
(* The re-aliased base table carries the implicit rowid the CTE relation has not, so a reader naming one would resolve after the splice against a column that was not there before. sqlite3 rejects the name outright ("no such column: rowid"), resolving before it flattens; refusing the splice at least keeps the answer the one the materialised relation gives. *)let test_no_flatten_rowid_read () = let cat = seed_flatten_table () in let sql = "WITH e AS (SELECT a, b, c FROM t) SELECT rowid FROM e" in check_ctes "the CTE stays" [ "e" ] (cte_names (flattened cat sql)); let qualified = "WITH e AS (SELECT a, b, c FROM t) SELECT count(e.rowid) FROM e" in check_ctes "qualified by the alias too" [ "e" ] (cte_names (flattened cat qualified))
(* The flattened alias carries the base table's whole column list, so a reader that names a column the CTE projected away would resolve after flattening where it did not before. That keeps the CTE materialised, and the reference stays the loud failure it was. *)let test_no_flatten_hidden_column () = let cat = seed_flatten_table () in let sql = "WITH e AS (SELECT a FROM t) SELECT count(*) FROM e WHERE b > 15" in check_ctes "the CTE stays" [ "e" ] (cte_names (flattened cat sql)); Alcotest.check_raises "the hidden column still fails" (Catalog.Error.Prepare "no such column: b") (fun () -> ignore (run cat sql))
let test_a_read_cte_body_is_resolved () = let cat = seed_flatten_table () in let refuses what name sql = Alcotest.check_raises what (Catalog.Error.Prepare ("no such column: " ^ name)) (fun () -> ignore (run cat sql)) in refuses "a reader projecting no column of the CTE reads it all the same" "nosuch" "WITH e AS (SELECT nosuch FROM t) SELECT count(*) FROM e"; refuses "a projected name beside a good one" "nosuch" "WITH e AS (SELECT a, nosuch FROM t) SELECT a FROM e"; refuses "a qualified one is named in full" "t.nosuch" "WITH e AS (SELECT t.nosuch FROM t) SELECT count(*) FROM e"; refuses "the body's predicate is the body's to resolve" "nosuch" "WITH e AS (SELECT a FROM t WHERE nosuch > 1) SELECT count(*) FROM e"; refuses "read through [*], the name is still the body's bare one" "nosuch" "WITH e AS (SELECT nosuch FROM t) SELECT * FROM e"; Alcotest.check rows "a body nothing reads raises nothing" [ [ Int 1L ] ] (run cat "WITH e AS (SELECT nosuch FROM t) SELECT 1")
let test_a_body_over_a_sibling_cte_is_resolved () = let cat = seed_flatten_table () in let refuses what name sql = Alcotest.check_raises what (Catalog.Error.Prepare ("no such column: " ^ name)) (fun () -> ignore (run cat sql)) in let good = "WITH x AS (SELECT a FROM t), y AS (SELECT a FROM x) SELECT count(*) FROM y" in check_ctes "a body over a sibling still collapses the chain" [] (cte_names (flattened cat good)); Alcotest.check rows "and counts the three rows of t" [ [ Int 3L ] ] (run cat good); refuses "a name neither the sibling nor its table publishes" "nosuch" "WITH x AS (SELECT a FROM t), y AS (SELECT nosuch FROM x) SELECT count(*) \ FROM y"; refuses "a name the sibling's projection dropped" "b" "WITH x AS (SELECT a FROM t), y AS (SELECT b FROM x) SELECT count(*) FROM y"; refuses "qualified by the sibling, and reported in full" "x.b" "WITH x AS (SELECT a FROM t), y AS (SELECT x.b FROM x) SELECT count(*) \ FROM y"; refuses "the body's predicate reads the sibling too" "b" "WITH x AS (SELECT a FROM t), y AS (SELECT a FROM x WHERE b > 1) SELECT \ count(*) FROM y"
(* Placeholders are numbered before flattening and the rewrite moves the body's predicate without renumbering, so each [?] keeps the index it was given. The two bindings are chosen so a swap would answer three rows instead of one. *)let test_flatten_keeps_parameter_indices () = let cat = seed_flatten_table () in let sql = "WITH e AS (SELECT a, b FROM t WHERE b >= ?) SELECT a FROM e WHERE a < ?" in let numbered = match Sql.Param_number.number_params (Ast.Select (select sql)) with | Ast.Select s -> s | _ -> Alcotest.fail "not a SELECT" in let f = Q.flatten_ctes (Sql.Engine.catalog cat) numbered in check_ctes "the CTE is gone" [] (cte_names f); check_sql "both placeholders survive the splice" "SELECT a FROM t e WHERE ((e.b >= ?1) AND (a < ?2))" (Sql.Render.select f); Alcotest.check rows "b >= 20 AND a < 3" [ [ Int 2L ] ] (Sql.Engine.run_select (Sql.Engine.catalog cat) numbered [| Int 20L; Int 3L |]); Alcotest.check rows "read the other way round it would be three rows" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] (Sql.Engine.run_select (Sql.Engine.catalog cat) numbered [| Int 3L; Int 20L |])
(* A splice into a join operand moves the body's predicate the same way, so the placeholders keep the indices they were numbered with there too. The two bindings are chosen so a swap answers two rows instead of one. *)let test_flatten_join_keeps_parameter_indices () = let cat = seed_join_tables (seed_flatten_table ()) in let sql = "WITH e AS (SELECT a, b FROM t WHERE b >= ?) SELECT u.nm FROM u JOIN e ON \ e.a = u.a WHERE u.a < ? ORDER BY u.a" in let numbered = match Sql.Param_number.number_params (Ast.Select (select sql)) with | Ast.Select s -> s | _ -> Alcotest.fail "not a SELECT" in let f = Q.flatten_ctes (Sql.Engine.catalog cat) numbered in check_ctes "the CTE is gone" [] (cte_names f); check_sql "both placeholders survive the splice" "SELECT u.nm FROM u JOIN t e ON (e.a = u.a) WHERE ((e.b >= ?1) AND (u.a < \ ?2)) ORDER BY u.a" (Sql.Render.select f); Alcotest.check rows "b >= 20 AND u.a < 3" [ [ Text "two" ] ] (Sql.Engine.run_select (Sql.Engine.catalog cat) numbered [| Int 20L; Int 3L |]); Alcotest.check rows "read the other way round it would be two rows" [ [ Text "one" ]; [ Text "two" ] ] (Sql.Engine.run_select (Sql.Engine.catalog cat) numbered [| Int 3L; Int 20L |])
type probe = { mutable seeks : int; mutable rows_read : int }
(* [t] with an index on [a]: [index_scan] answers a top-level equality on it, [best_index_for] names it, and every read is counted. *)let probed_catalog () = let cat = seed_join_tables (seed_flatten_table ()) in let base = Sql.Engine.catalog cat in let p = { seeks = 0; rows_read = 0 } in let known n = if not (List.mem n [ "t"; "u" ]) then Fmt.failwith "no such table: %s" n in let q = { base with Q.rows_rowid = (fun n -> known n; let rs = base.Q.rows_rowid n in p.rows_read <- p.rows_read + List.length rs; rs); index_scan = (fun n eqs -> known n; match (n, List.assoc_opt "a" eqs) with | "t", Some v -> p.seeks <- p.seeks + 1; let keep (_, r) = match r with a :: _ when Value.equal a v -> Some r | _ -> None in let rs = List.filter_map keep (base.Q.rows_rowid n) in p.rows_read <- p.rows_read + List.length rs; Some ("INDEX t_a", rs) | _ -> None); (* the access path as EXPLAIN spells it after USING, which for a backend whose only path is a named index is that name behind INDEX *) best_index_for = (fun n cols -> known n; if n = "t" && List.mem "a" cols then Some { Catalog.Spi.path = "INDEX t_a"; unique = false; bound = 1; rows_per_key = None; } else None); } in (q, p)
let test_explain_is_the_executed_plan () = let q, p = probed_catalog () in let plan sql = Q.explain q (select sql) in let ran sql = Sql.Engine.run_select q (select sql) [||] in (* An index on the equated column: the plan says SEARCH, and the engine must read through the index rather than the relation. *) Alcotest.(check string) "an indexed equality searches" "SEARCH t USING INDEX t_a" (plan "SELECT c FROM t WHERE a = 2"); Alcotest.check rows "and answers the row" [ [ Text "y" ] ] (ran "SELECT c FROM t WHERE a = 2"); Alcotest.(check int) "one seek" 1 p.seeks; Alcotest.(check int) "and one row read, not the table" 1 p.rows_read; (* No index covers b, so both halves say scan. *) let q, p = probed_catalog () in Alcotest.(check string) "an unindexed equality scans" "SCAN t" (Q.explain q (select "SELECT c FROM t WHERE b = 20")); Alcotest.check rows "and answers the row" [ [ Text "y" ] ] (Sql.Engine.run_select q (select "SELECT c FROM t WHERE b = 20") [||]); Alcotest.(check int) "no seek" 0 p.seeks; Alcotest.(check int) "the whole table read" 3 p.rows_read
let test_explain_renders_the_path_the_catalog_names () = let q, _ = probed_catalog () in let rowid_seek = { q with Q.best_index_for = (fun _ _ -> Some { Catalog.Spi.path = "INTEGER PRIMARY KEY (rowid=?)"; unique = true; bound = 1; rows_per_key = Some 1; }); } in Alcotest.(check string) "a rowid seek names no index" "SEARCH t USING INTEGER PRIMARY KEY (rowid=?)" (Q.explain rowid_seek (select "SELECT c FROM t WHERE a = 2")); Alcotest.(check string) "and a named index carries its own keyword" "SEARCH t USING INDEX t_a" (Q.explain q (select "SELECT c FROM t WHERE a = 2"))
(* The implicit rowid comes from the relation, not from an index, so a query reading it cannot be answered by a seek -- and the plan must say so. *)let test_explain_a_rowid_read_scans () = let q, p = probed_catalog () in Alcotest.(check string) "a rowid read scans" "SCAN t" (Q.explain q (select "SELECT rowid FROM t WHERE a = 2")); Alcotest.check rows "and the rowid is the row's" [ [ Int 2L ] ] (Sql.Engine.run_select q (select "SELECT rowid FROM t WHERE a = 2") [||]); Alcotest.(check int) "no seek" 0 p.seeks
let test_explain_a_constant_subquery_seeks () = let q, p = probed_catalog () in let sql = "SELECT c FROM t WHERE a = (SELECT max(a) FROM u WHERE a < 4)" in Alcotest.(check string) "a closed subquery keys a lookup" "SEARCH t USING INDEX t_a" (Q.explain q (select sql)); Alcotest.check rows "and the seek answers the row" [ [ Text "y" ] ] (Sql.Engine.run_select q (select sql) [||]); Alcotest.(check int) "one seek" 1 p.seeks; (* u is read whole for the subquery (3 rows); t is not read at all, only sought, which returns the single matching row *) Alcotest.(check int) "and t is not scanned" 4 p.rows_read; (* the subquery runs once, not once per key lookup and again for the predicate it was folded into *) let q, p = probed_catalog () in Alcotest.check rows "a literal subquery too" [ [ Text "y" ] ] (Sql.Engine.run_select q (select "SELECT c FROM t WHERE a = (SELECT 2)") [||]); Alcotest.(check int) "one seek" 1 p.seeks; Alcotest.(check int) "and one row read" 1 p.rows_read; (* a correlated subquery has no single value, so it keys nothing *) let q, _ = probed_catalog () in Alcotest.(check string) "a correlated subquery scans" "SCAN t" (Q.explain q (select "SELECT c FROM t WHERE a = (SELECT max(u.a) FROM u WHERE u.a = t.b)")); Alcotest.(check string) "and so does an unindexed column, whatever keys it" "SCAN t" (Q.explain q (select "SELECT c FROM t WHERE b = (SELECT max(a) FROM u)"))
let test_explain_lists_sources_in_from_order () = let q, _ = probed_catalog () in Alcotest.(check string) "the leading source seeks, the operand hashes" "SEARCH t USING INDEX t_a\nSCAN u" (Q.explain q (select "SELECT u.nm FROM t JOIN u ON u.a = t.a WHERE t.a = 2")); Alcotest.(check string) "and the joined source seeks in the order written" "SCAN u\nSEARCH t USING INDEX t_a" (Q.explain q (select "SELECT u.nm FROM u JOIN t ON u.a = t.a WHERE t.a = 2"))
(* A joined source with an index is read through it, not rescanned: [u] is read whole once and each of its rows seeks [t]. Both halves are asserted together -- a plan that says SEARCH while the count says the whole relation is the failure this exists to catch. *)let test_a_joined_source_seeks () = let q, p = probed_catalog () in let sql = "SELECT u.nm FROM u JOIN t ON u.a = t.a WHERE t.a = 2" in Alcotest.check rows "the join answers its row" [ [ Text "two" ] ] (Sql.Engine.run_select q (select sql) [||]); Alcotest.(check int) "t is sought, not scanned" 1 p.seeks; (* u whole (3 rows) plus the one row t's key selects *) Alcotest.(check int) "and only its key's row is read" 4 p.rows_read
(* A SQL identifier is case-insensitive, so how a query spells a column cannot decide the access path it takes: matching the query's spelling against the schema's byte for byte lost the index with no error and no plan. *)let test_a_seek_key_ignores_identifier_case () = let q, p = probed_catalog () in Alcotest.(check string) "another spelling takes the same path" "SEARCH t USING INDEX t_a" (Q.explain q (select "SELECT c FROM t WHERE T.A = 2")); Alcotest.check rows "and answers the same row" [ [ Text "y" ] ] (Sql.Engine.run_select q (select "SELECT c FROM t WHERE T.A = 2") [||]); Alcotest.(check int) "through one seek" 1 p.seeks; Alcotest.(check int) "reading one row" 1 p.rows_read
(* Equality is transitive: [u.a = t.a AND u.a = 2] says [t.a = 2] as surely as it says either conjunct. Without the derivation only the source the pin is written against gets a key, so which of two interchangeable spellings the query used decided whether the join cost the product. *)let test_a_pin_reaches_the_other_end_of_the_join_key () = let q, p = probed_catalog () in let sql = "SELECT u.nm FROM t JOIN u ON u.a = t.a WHERE u.a = 2" in Alcotest.(check string) "the pin on the joined source seeks the leading one" "SEARCH t USING INDEX t_a\nSCAN u" (Q.explain q (select sql)); Alcotest.check rows "and the rows are the join's" [ [ Text "two" ] ] (Sql.Engine.run_select q (select sql) [||]); Alcotest.(check int) "one seek" 1 p.seeks
let test_explain_scans_a_materialised_cte () = let q, _ = probed_catalog () in Alcotest.(check string) "a grouping body scans" "SCAN e" (Q.explain q (select "WITH e AS (SELECT a, count(*) AS n FROM t GROUP BY a) SELECT \ count(*) FROM e")); Alcotest.(check string) "AS MATERIALIZED scans, once per reference" "SCAN e\nSCAN e" (Q.explain q (select "WITH e AS MATERIALIZED (SELECT a, b FROM t) SELECT count(*) FROM e \ AS p JOIN e AS r ON p.a = r.a")); Alcotest.(check string) "a VALUES CTE scans" "SCAN v" (Q.explain q (select "WITH v(x) AS (VALUES (1),(2)) SELECT count(*) FROM v")); (* Flattened, the same CTE is described by the base table's own access path. *) Alcotest.(check string) "a single-use CTE explains as the flat query" "SEARCH t USING INDEX t_a" (Q.explain q (select "WITH e AS (SELECT a, b FROM t) SELECT b FROM e WHERE a = 2"))
(* Each source's access path as one line, the way {!Sql.Plan.pp} writes it: a probe names the column it hashes on and the earlier cursor's column it is answered against, which is what says the equality was seen at all. *)let access_descr (a : Sql.Plan.access) = match a with | Sql.Plan.Scan -> "scan" | Sql.Plan.Seek { table; path; _ } -> Fmt.str "seek %s using %s" table path.Sql.Plan.descr | Sql.Plan.Probe { key; src; pos; _ } -> Fmt.str "probe %d on %d.%d" key src pos | Sql.Plan.Pair { key; other; other_key; _ } -> Fmt.str "pair %d with %d.%d" key other other_key
let test_plan_sources_binds_the_statements_own_ctes () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER)"); ignore (E.exec cat "INSERT INTO t VALUES (1, 10), (2, 20)"); let q = E.catalog cat in let s = select "WITH e AS MATERIALIZED (SELECT a, b FROM t) SELECT count(1) FROM e AS p \ JOIN e AS r ON p.a = r.a" in let bound = Q.materialize_ctes q [||] s.Ast.ctes in Alcotest.(check (list string)) "the CTE publishes a and b" [ "a"; "b" ] (bound.Q.columns "e"); Alcotest.(check (list (pair string (list string)))) "and both sources publish them" [ ("p", [ "a"; "b" ]); ("r", [ "a"; "b" ]) ] (Sql.Plan.layout (Q.resolve_env bound) ~bound:[ ("e", bound.Q.columns "e") ] s); Alcotest.(check (list string)) "over that layout the join equality plans as a probe" [ "scan"; "probe 0 on 0.0" ] (List.map access_descr (Q.plan_sources bound s)); Alcotest.(check (list string)) "and the statement's own WITH clause gets there unaided" [ "scan"; "probe 0 on 0.0" ] (List.map access_descr (Q.plan_sources q s))
let explained_catalog () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE c (k INTEGER PRIMARY KEY, v TEXT)"); ignore (E.exec cat "INSERT INTO c VALUES (1, 'a'), (2, 'b')"); E.catalog cat
let test_explain_resolves_before_it_describes () = let q = explained_catalog () in let refused label exn sql = Alcotest.check_raises label exn (fun () -> ignore (Q.explain q (select sql))) in let unknown_col label name sql = refused label (Catalog.Error.Prepare ("no such column: " ^ name)) sql in refused "a name of no table" (Failure "no such table: nosuchtable") "SELECT k FROM nosuchtable"; unknown_col "a select-list name of no column" "nosuchcol" "SELECT nosuchcol FROM c"; unknown_col "a qualified one" "c.nosuchcol" "SELECT c.nosuchcol FROM c"; unknown_col "one in the WHERE" "nosuchcol" "SELECT k FROM c WHERE nosuchcol = 1"; unknown_col "one in the GROUP BY" "nosuchcol" "SELECT k FROM c GROUP BY nosuchcol"; unknown_col "one in the HAVING" "nosuchcol" "SELECT max(k) FROM c GROUP BY v HAVING nosuchcol > 1"; unknown_col "one in the ORDER BY" "nosuchcol" "SELECT k FROM c ORDER BY nosuchcol"; (* The arity complaint carries no explain guard, so it stands where the unknown name does not. *) refused "a function at an arity it has not got" (Catalog.Error.Prepare "wrong number of arguments to function length()") "SELECT length(k, v) FROM c"
(* The controls, which are the whole of what the refusal must leave standing: a name of no function is the one refusal EXPLAIN suspends, and every name that does resolve -- through the rowid slot, through a select-list alias, through an ordinal -- reaches the planner as written. sqlite3 3.54.0 describes all of these rather than refusing them. *)let test_explain_describes_what_resolves () = let q = explained_catalog () in let plan label sql = Alcotest.(check string) label "SCAN c" (Q.explain q (select sql)) in plan "an unknown function still describes the scan" "SELECT nosuchfn(k) FROM c"; plan "and one in the WHERE too" "SELECT k FROM c WHERE nosuchfn(k) = 1"; plan "a resolving statement answers its plan" "SELECT v FROM c WHERE v = 'a'"; plan "the implicit rowid is a name the layout publishes" "SELECT rowid FROM c"; plan "a select-list alias is one the ORDER BY may name" "SELECT v AS w FROM c ORDER BY w"; plan "and so is an ordinal" "SELECT k FROM c ORDER BY 1"; (* A subquery reads sources of its own, which the layout does not describe. *) plan "a subquery's own names are its own" "SELECT k FROM c WHERE v IN (SELECT v FROM c)"
let test_numeric_literal_forms () = let cat = Catalog.of_mem [] in let one sql v = Alcotest.check rows sql [ [ v ] ] (run cat sql) in one "SELECT 5." (Float 5.0); one "SELECT .5" (Float 0.5); one "SELECT typeof(5.)" (Text "real"); one "SELECT typeof(.5)" (Text "real"); one "SELECT 5.e2" (Float 500.0); one "SELECT .5e1" (Float 5.0); one "SELECT 5. + 1" (Float 6.0); one "SELECT 1 + .5" (Float 1.5); one "SELECT -.5" (Float (-0.5)); one "SELECT 1." (Float 1.0); (* a bare [.] is still the qualifier dot, not a number *) ignore (E.exec cat "CREATE TABLE q (a INTEGER)"); ignore (E.exec cat "INSERT INTO q VALUES (7)"); Alcotest.check rows "SELECT q.a FROM q" [ [ Int 7L ] ] (run cat "SELECT q.a FROM q")
let test_arithmetic_nan_is_null () = let cat = Catalog.of_mem [] in let one sql v = Alcotest.check rows sql [ [ v ] ] (run cat sql) in one "SELECT 1e400 - 1e400" Null; one "SELECT typeof(1e400 - 1e400)" (Text "null"); one "SELECT typeof(1e400 / 1e400)" (Text "null"); one "SELECT typeof(-1e400 * 0)" (Text "null"); one "SELECT typeof(1e400 + -1e400)" (Text "null"); one "SELECT 1e400 * 1e400" (Float Float.infinity); one "SELECT typeof(1e400 * 1e400)" (Text "real"); one "SELECT typeof(1e400)" (Text "real"); (* division by zero was already NULL, and stays so *) one "SELECT typeof(0.0 / 0.0)" (Text "null"); one "SELECT typeof(1.0 / 0.0)" (Text "null"); (* An INSERT ... VALUES tuple is projected by {!Sql.Engine.eval_const}, so a written value follows the same rule as a selected one and no NaN is ever stored. *) let const sql = match (select ("SELECT " ^ sql)).Ast.cols with | [ { Ast.expr; _ } ] -> E.eval_const (Sql.Engine.catalog cat) [||] expr | _ -> Fmt.failwith "not a single expression: %s" sql in let value = Alcotest.testable Value.pp Value.equal in Alcotest.check value "a written value stores no NaN either" Null (const "1e400 - 1e400"); Alcotest.check value "and keeps an infinity" (Float Float.infinity) (const "1e400 * 1e400")
let chain_tables () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE l (k INTEGER, v TEXT)"); ignore (E.exec cat "CREATE TABLE r (k INTEGER, w TEXT)"); ignore (E.exec cat "CREATE TABLE c (k INTEGER, u TEXT)"); ignore (E.exec cat "INSERT INTO l VALUES (1, 'l1'), (2, 'l2'), (2, 'l2b'), (3, 'l3'), \ (NULL, 'lnull')"); ignore (E.exec cat "INSERT INTO r VALUES (2, 'r2'), (2, 'r2b'), (3, 'r3'), (4, 'r4'), \ (NULL, 'rnull')"); ignore (E.exec cat "INSERT INTO c VALUES (1, 'c1'), (3, 'c3'), (3, 'c3b'), (5, 'c5'), \ (NULL, 'cnull')"); cat
(* Two LEFT joins in a row. [l1] has no [r] and still meets [c1]; [lnull] has neither and is emitted once, padded on both. The k=2 rows pair four ways in the first join and none of the four finds a [c]. *)let test_chain_left_left () = Alcotest.check rows "l LEFT JOIN r LEFT JOIN c" [ [ Text "l1"; Null; Text "c1" ]; [ Text "l2"; Text "r2"; Null ]; [ Text "l2"; Text "r2b"; Null ]; [ Text "l2b"; Text "r2"; Null ]; [ Text "l2b"; Text "r2b"; Null ]; [ Text "l3"; Text "r3"; Text "c3" ]; [ Text "l3"; Text "r3"; Text "c3b" ]; [ Text "lnull"; Null; Null ]; ] (run (chain_tables ()) "SELECT l.v, r.w, c.u FROM l LEFT JOIN r ON l.k = r.k LEFT JOIN c ON \ c.k = l.k ORDER BY l.v, r.w, c.u")
let test_chain_inner_right () = Alcotest.check rows "the leading ON runs inside the RIGHT join" [ [ Null; Null; Text "c1" ]; [ Text "l3"; Text "r3"; Text "c3" ]; [ Text "l3"; Text "r3"; Text "c3b" ]; [ Null; Null; Text "c5" ]; [ Null; Null; Text "cnull" ]; ] (run (chain_tables ()) "SELECT l.v, r.w, c.u FROM l JOIN r ON l.k = r.k RIGHT JOIN c ON c.k = \ l.k ORDER BY c.u, l.v, r.w"); Alcotest.check rows "the same predicate as a WHERE keeps two rows" [ [ Text "l3"; Text "r3"; Text "c3" ]; [ Text "l3"; Text "r3"; Text "c3b" ]; ] (run (chain_tables ()) "SELECT l.v, r.w, c.u FROM l, r RIGHT JOIN c ON c.k = l.k WHERE l.k = \ r.k ORDER BY c.u, l.v, r.w")
(* A LEFT join then a FULL one: both ends of the second join keep their unmatched rows, and each is reported exactly once. *)let test_chain_left_full () = Alcotest.check rows "l LEFT JOIN r FULL JOIN c" [ [ Null; Null; Text "c5" ]; [ Null; Null; Text "cnull" ]; [ Text "l1"; Null; Text "c1" ]; [ Text "l2"; Text "r2"; Null ]; [ Text "l2"; Text "r2b"; Null ]; [ Text "l2b"; Text "r2"; Null ]; [ Text "l2b"; Text "r2b"; Null ]; [ Text "l3"; Text "r3"; Text "c3" ]; [ Text "l3"; Text "r3"; Text "c3b" ]; [ Text "lnull"; Null; Null ]; ] (run (chain_tables ()) "SELECT l.v, r.w, c.u FROM l LEFT JOIN r ON l.k = r.k FULL JOIN c ON \ c.k = l.k ORDER BY l.v, r.w, c.u")
(* A GROUP BY over a chain groups the rows the null-fills produced, so a padded row is a group member with a NULL in it and [count] of that column is zero.*)let test_chain_group_by () = Alcotest.check rows "count(c.u) per l.v over the chain" [ [ Text "l1"; Int 1L ]; [ Text "l2"; Int 0L ]; [ Text "l2b"; Int 0L ]; [ Text "l3"; Int 2L ]; [ Text "lnull"; Int 0L ]; ] (run (chain_tables ()) "SELECT l.v, count(c.u) FROM l LEFT JOIN r ON l.k = r.k LEFT JOIN c ON \ c.k = l.k GROUP BY l.v ORDER BY l.v")
let test_chain_on_names_a_later_table () = let expected = [ [ Text "l3"; Text "r3"; Text "c3" ]; [ Text "l3"; Text "r3"; Text "c3b" ]; ] in Alcotest.check rows "the ON reaching forward" expected (run (chain_tables ()) "SELECT l.v, r.w, c.u FROM l JOIN r ON c.k = l.k JOIN c ON c.k = r.k \ ORDER BY l.v, r.w, c.u"); Alcotest.check rows "and the same predicate as a WHERE" expected (run (chain_tables ()) "SELECT l.v, r.w, c.u FROM l, r, c WHERE c.k = l.k AND c.k = r.k ORDER \ BY l.v, r.w, c.u")
(* A window function runs over rows whose sources are already joined, so the post-pass holds one cursor where the lowering named three. The inner joins' [ON]s have to survive that rebasing: dropped, they would widen the frame to the whole cross product. *)let test_window_over_an_inner_chain () = Alcotest.check rows "count(*) OVER () sees the joined rows only" [ [ Text "l3"; Int 2L ]; [ Text "l3"; Int 2L ] ] (run (chain_tables ()) "SELECT l.v, count(*) OVER () FROM l JOIN r ON l.k = r.k JOIN c ON c.k \ = l.k ORDER BY l.v, c.u")
let natural_tables () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, x TEXT)"); ignore (E.exec cat "CREATE TABLE u (a INTEGER, c INTEGER)"); ignore (E.exec cat "CREATE TABLE v (a INTEGER, c INTEGER, y TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES (1, 't1'), (2, 't2')"); ignore (E.exec cat "INSERT INTO u VALUES (1, 10), (2, 20)"); ignore (E.exec cat "INSERT INTO v VALUES (1, 10, 'v1')"); cat
(* [v] shares [a] with [t] and [c] with [u], so its [ON] equates each against the source that has it, and [*] lists neither of its copies. *)let test_natural_join_of_three () = Alcotest.check rows "t NATURAL JOIN u NATURAL JOIN v" [ [ Int 1L; Text "t1"; Int 10L; Text "v1" ] ] (run (natural_tables ()) "SELECT * FROM t NATURAL JOIN u NATURAL JOIN v")
(* [USING (a)] names one column, so [c] is not equated and both copies of it stay in the projection -- the second join's [a] is the only one hidden. *)let test_using_join_of_three () = Alcotest.check rows "t JOIN u USING(a) JOIN v USING(a)" [ [ Int 1L; Text "t1"; Int 10L; Int 10L; Text "v1" ] ] (run (natural_tables ()) "SELECT * FROM t JOIN u USING (a) JOIN v USING (a)")
(* The same chain outer: [t2] finds its [u] row and no [v] row, and the NATURAL LEFT join preserves it with the joined source's columns NULL. *)let test_natural_left_join_of_three () = Alcotest.check rows "t NATURAL LEFT JOIN u NATURAL LEFT JOIN v" [ [ Int 1L; Text "t1"; Int 10L; Text "v1" ]; [ Int 2L; Text "t2"; Int 20L; Null ]; ] (run (natural_tables ()) "SELECT * FROM t NATURAL LEFT JOIN u NATURAL LEFT JOIN v ORDER BY a")
let joined_star_tables () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE l (k INTEGER, v TEXT)"); ignore (E.exec cat "CREATE TABLE r (k INTEGER, w TEXT)"); ignore (E.exec cat "INSERT INTO l VALUES (1, 'a'), (2, 'b')"); ignore (E.exec cat "INSERT INTO r VALUES (1, 'p'), (2, 'q')"); cat
let test_a_grouped_star_publishes_the_joins_columns () = let cat = joined_star_tables () in let three = [ [ Int 1L; Text "a"; Text "p" ]; [ Int 2L; Text "b"; Text "q" ] ] in Alcotest.check rows "ungrouped, the join's common column is published once" three (run cat "SELECT * FROM l NATURAL JOIN r ORDER BY k"); Alcotest.check rows "and grouping the same rows changes no column" three (run cat "SELECT * FROM l NATURAL JOIN r GROUP BY k ORDER BY k"); Alcotest.check rows "a USING list names the same column and hides the same one" three (run cat "SELECT * FROM l JOIN r USING (k) GROUP BY k ORDER BY k")
let disjoint_tables () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE t (a INTEGER, b TEXT)"); ignore (E.exec cat "CREATE TABLE u (c INTEGER, d TEXT)"); ignore (E.exec cat "INSERT INTO t VALUES (1, 'x'), (2, 'y'), (3, 'z')"); ignore (E.exec cat "INSERT INTO u VALUES (9, 'w')"); cat
let test_a_natural_join_with_nothing_in_common_crosses () = let cat = disjoint_tables () in Alcotest.check rows "no common column leaves the product" [ [ Int 1L; Text "x"; Int 9L; Text "w" ]; [ Int 2L; Text "y"; Int 9L; Text "w" ]; [ Int 3L; Text "z"; Int 9L; Text "w" ]; ] (run cat "SELECT * FROM t NATURAL JOIN u ORDER BY a")
let test_a_using_column_absent_from_an_operand () = let cat = disjoint_tables () in Alcotest.check_raises "a USING name one operand has not got says so" (Catalog.Error.Prepare "cannot join using column a - column not present in both tables") (fun () -> ignore (run cat "SELECT * FROM t JOIN u USING (a)"))
let coalesce_tables () = let cat = Catalog.of_mem [] in ignore (E.exec cat "CREATE TABLE a (c INTEGER, x TEXT)"); ignore (E.exec cat "CREATE TABLE b (c INTEGER, y TEXT)"); ignore (E.exec cat "INSERT INTO a VALUES (1, 'ax')"); ignore (E.exec cat "INSERT INTO b VALUES (1, 'by'), (3, 'b3')"); cat
let test_using_column_a_right_join_preserves () = let cat = coalesce_tables () in Alcotest.check rows "SELECT * lists the join's column once, coalesced" [ [ Int 1L; Text "ax"; Text "by" ]; [ Int 3L; Null; Text "b3" ] ] (run cat "SELECT * FROM a RIGHT JOIN b USING (c) ORDER BY c"); Alcotest.check rows "a bare c is the join's column" [ [ Int 1L ]; [ Int 3L ] ] (run cat "SELECT c FROM a RIGHT JOIN b USING (c) ORDER BY c"); Alcotest.check rows "and a qualified a.c is the padded operand's" [ [ Null ]; [ Int 1L ] ] (run cat "SELECT a.c FROM a RIGHT JOIN b USING (c) ORDER BY a.c"); (* the WHERE runs after the null-fill, so it sees the coalesce too *) Alcotest.check rows "a bare c in the WHERE reads the same column" [ [ Text "b3" ] ] (run cat "SELECT b.y FROM a RIGHT JOIN b USING (c) WHERE c = 3")
(* A FULL join preserves both operands, so it pads the owner as a RIGHT one does; NATURAL is the same rule with the column list read off the names. *)let test_using_column_a_full_join_preserves () = let cat = coalesce_tables () in Alcotest.check rows "FULL JOIN USING" [ [ Int 1L; Text "ax"; Text "by" ]; [ Int 3L; Null; Text "b3" ] ] (run cat "SELECT * FROM a FULL JOIN b USING (c) ORDER BY c"); Alcotest.check rows "NATURAL RIGHT JOIN" [ [ Int 1L ]; [ Int 3L ] ] (run cat "SELECT c FROM a NATURAL RIGHT JOIN b ORDER BY c"); (* a LEFT or inner join never pads the owner, so its copy is the answer and no coalesce is called for *) Alcotest.check rows "a LEFT JOIN keeps the owner's copy" [ [ Int 1L; Text "ax"; Text "by" ] ] (run cat "SELECT * FROM a LEFT JOIN b USING (c) ORDER BY c")
let test_a_recursive_limit_bounds_the_table () = let cat = Catalog.of_mem [] in Alcotest.check rows "the series idiom answers exactly the LIMIT's rows" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ]; [ Int 4L ]; [ Int 5L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c LIMIT 5) \ SELECT x FROM c"); Alcotest.check rows "a fixpoint reached first answers fewer" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c WHERE x<3 \ LIMIT 10) SELECT x FROM c"); Alcotest.check rows "LIMIT 0 keeps not even the seed" [] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c LIMIT 0) \ SELECT x FROM c"); Alcotest.check rows "the reader filters the bounded table" [ [ Int 4L ]; [ Int 5L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c LIMIT 5) \ SELECT x FROM c WHERE x>3")
let test_a_recursive_offset_drops_from_the_front () = let cat = Catalog.of_mem [] in Alcotest.check rows "LIMIT 5 OFFSET 2 fills seven and publishes five" [ [ Int 3L ]; [ Int 4L ]; [ Int 5L ]; [ Int 6L ]; [ Int 7L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c LIMIT 5 \ OFFSET 2) SELECT x FROM c"); Alcotest.check rows "a negative LIMIT bounds nothing, the OFFSET still drops" [ [ Int 3L ]; [ Int 4L ]; [ Int 5L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c WHERE x<5 \ LIMIT -1 OFFSET 2) SELECT x FROM c")
let test_a_recursive_union_dedups_across_the_rounds () = let cat = Catalog.of_mem [] in Alcotest.check rows "a cycling recursion stops at the rows it has seen" [ [ Int 1L ]; [ Int 2L ]; [ Int 0L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION SELECT (x+1)%3 FROM c) SELECT x \ FROM c"); Alcotest.check rows "the seed's own duplicates fold into one row" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] (run cat "WITH RECURSIVE c(x) AS (VALUES(1),(1) UNION SELECT x+1 FROM c WHERE \ x<3) SELECT x FROM c"); Alcotest.check rows "UNION ALL keeps every one of them" [ [ Int 1L ]; [ Int 1L ]; [ Int 2L ]; [ Int 2L ]; [ Int 3L ]; [ Int 3L ] ] (run cat "WITH RECURSIVE c(x) AS (VALUES(1),(1) UNION ALL SELECT x+1 FROM c \ WHERE x<3) SELECT x FROM c"); Alcotest.check rows "the LIMIT counts the rows the dedup kept" [ [ Int 1L ]; [ Int 2L ]; [ Int 3L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION SELECT (x+1)%4 FROM c LIMIT 3) \ SELECT x FROM c")
let test_except_and_intersect_over_duplicates () = let cat = Catalog.of_mem [] in Alcotest.check rows "EXCEPT is the difference of the two key sets" [ [ Int 1L ] ] (run cat "SELECT * FROM (VALUES(1),(1),(2)) EXCEPT SELECT * FROM (VALUES(2),(2))"); Alcotest.check rows "INTERSECT keeps one row per shared key" [ [ Int 2L ]; [ Int 3L ] ] (run cat "SELECT * FROM (VALUES(1),(1),(2),(3)) INTERSECT SELECT * FROM \ (VALUES(2),(2),(3))"); Alcotest.check rows "an integer and the real equal to it are one key" [] (run cat "SELECT * FROM (VALUES(1)) EXCEPT SELECT * FROM (VALUES(1.0))"); Alcotest.check rows "and the row kept is the left one" [ [ Int 1L ] ] (run cat "SELECT * FROM (VALUES(1)) INTERSECT SELECT * FROM (VALUES(1.0))")
let test_a_bounded_recursion_costs_what_it_produces () = let cat = Catalog.of_mem [] in Alcotest.check rows "twenty thousand rows, and every one of them" [ [ Int 20000L; Int 1L; Int 20000L ] ] (run cat "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c LIMIT \ 20000) SELECT count(*), min(x), max(x) FROM c")
(* The cap is the catalog's: stated at a thousand rows here, so the refusal is met in milliseconds rather than after the default million. *)let test_an_unbounded_recursion_is_refused_at_the_cap () = let cat = Sql.Engine.catalog ~recursion_cap:1_000 (Catalog.of_mem []) in Alcotest.check_raises "the reader's LIMIT does not bound the recursion" (Failure "recursive CTE exceeded 1000 rows") (fun () -> ignore (Sql.Engine.run_select cat (select "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c) \ SELECT x FROM c LIMIT 5") [||]))
let test_order_by_in_a_recursive_select_is_refused () = let cat = Catalog.of_mem [] in Alcotest.check_raises "the queue order is not ours to give" (Catalog.Error.Prepare "engine: ORDER BY in the recursive select of t is not supported yet") (fun () -> ignore (run cat "WITH RECURSIVE t(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM t WHERE \ x<3 UNION ALL SELECT x*10 FROM t WHERE x<30 ORDER BY 1 LIMIT 10) \ SELECT x FROM t"))
let suite = ( "query", [ Alcotest.test_case "scan / filter / project / order" `Quick test_select; Alcotest.test_case "numeric literal forms (5. and .5)" `Quick test_numeric_literal_forms; Alcotest.test_case "arithmetic NaN is NULL" `Quick test_arithmetic_nan_is_null; Alcotest.test_case "inner join" `Quick test_join; Alcotest.test_case "recursive CTE" `Quick test_recursive_cte; Alcotest.test_case "compound UNION" `Quick test_compound; Alcotest.test_case "unknown column fails" `Quick test_unknown_column; Alcotest.test_case "GROUP BY resolves a select-list alias" `Quick test_group_by_alias; Alcotest.test_case "GROUP BY prefers an input column to an alias" `Quick test_group_by_alias_shadowed_by_column; Alcotest.test_case "ORDER BY prefers an alias to an input column" `Quick test_order_by_alias_beats_column; Alcotest.test_case "an alias reaches a subquery body" `Quick test_an_alias_reaches_a_subquery_body; Alcotest.test_case "GROUP BY on an unknown name fails" `Quick test_group_by_unknown_name; Alcotest.test_case "a declared COLLATE reaches every comparison" `Quick test_declared_collation; Alcotest.test_case "a declared COLLATE ignores identifier case" `Quick test_declared_collation_ignores_identifier_case; Alcotest.test_case "rowid needs a relation that has one" `Quick test_rowid_needs_a_relation_with_one; Alcotest.test_case "hoisting reads names as identifiers" `Quick test_hoisting_reads_names_as_identifiers; Alcotest.test_case "a CTE defined in terms of itself" `Quick test_cte_defined_in_terms_of_itself; Alcotest.test_case "a nested WITH shadows rather than cycles" `Quick test_nested_with_shadows_rather_than_cycles; Alcotest.test_case "an unread nested WITH is never expanded" `Quick test_an_unread_nested_with_is_never_expanded; Alcotest.test_case "a shadowed reference is not this relation" `Quick test_a_shadowed_reference_is_not_this_relation; Alcotest.test_case "a WITH scopes over every arm" `Quick test_a_with_scopes_over_every_arm; Alcotest.test_case "a WITH placement the grammar refuses" `Quick test_a_with_placement_the_grammar_refuses; Alcotest.test_case "the whole WITH list is in scope for each member" `Quick test_the_whole_with_list_is_in_scope; Alcotest.test_case "a cycle names where it was entered" `Quick test_a_cycle_names_where_it_was_entered; Alcotest.test_case "a CAST of a column keeps its COLLATE" `Quick test_declared_collation_through_cast; Alcotest.test_case "a CAST of a column sorts under its COLLATE" `Quick test_declared_collation_orders_through_cast; Alcotest.test_case "unary + keeps the operand's COLLATE" `Quick test_unary_plus_keeps_collation; Alcotest.test_case "unary + strips the operand's affinity" `Quick test_unary_plus_strips_affinity; Alcotest.test_case "an explicit COLLATE below the top node counts" `Quick test_explicit_collation_below_the_top_node; Alcotest.test_case "an explicit COLLATE in any subexpression counts" `Quick test_explicit_collation_in_any_subexpression; Alcotest.test_case "a sort or group key counts one too" `Quick test_explicit_collation_in_any_subexpression_of_a_key; Alcotest.test_case "a DISTINCT sequence is indexed by projected column" `Quick test_a_distinct_collation_is_indexed_by_projected_column; Alcotest.test_case "a single constant IN element is an equality" `Quick test_a_single_constant_element_in_is_an_equality; Alcotest.test_case "an explicit COLLATE outranks a declared one" `Quick test_explicit_collation_outranks_a_declared_one; Alcotest.test_case "a single-use CTE is spliced into its reader" `Quick test_flatten_single_use; Alcotest.test_case "flattening ANDs the body's predicate in" `Quick test_flatten_merges_predicates; Alcotest.test_case "flattening keeps the CTE's column list" `Quick test_flatten_star_keeps_the_cte_columns; Alcotest.test_case "a multi-use CTE stays materialised" `Quick test_no_flatten_multi_use; Alcotest.test_case "a CTE a reader cannot take stays whole" `Quick test_no_flatten_beside_a_reader_it_cannot_take; Alcotest.test_case "the body constraints refuse flattening" `Quick test_no_flatten_body_constraints; Alcotest.test_case "a recursive CTE stays materialised" `Quick test_no_flatten_recursive; Alcotest.test_case "a chain of single-use CTEs collapses" `Quick test_flatten_chained_ctes; Alcotest.test_case "a CTE in inner-join position is spliced" `Quick test_flatten_inner_join_operand; Alcotest.test_case "a LEFT JOIN operand's predicate moves to the ON" `Quick test_flatten_left_join_operand; Alcotest.test_case "a RIGHT or FULL join refuses flattening" `Quick test_no_flatten_right_or_full_join; Alcotest.test_case "a DISTINCT reader over a LEFT JOIN refuses" `Quick test_no_flatten_distinct_over_left_join; Alcotest.test_case "a narrowing CTE refuses NATURAL / USING" `Quick test_no_flatten_natural_when_narrowing; Alcotest.test_case "a narrowing CTE flattens into a joining reader" `Quick test_flatten_narrowing_cte_with_join; Alcotest.test_case "a name the widened alias would capture refuses" `Quick test_no_flatten_captured_name; Alcotest.test_case "a rowid read through the alias refuses" `Quick test_no_flatten_rowid_read; Alcotest.test_case "a column the CTE hid stays hidden" `Quick test_no_flatten_hidden_column; Alcotest.test_case "a body the reader reads is resolved" `Quick test_a_read_cte_body_is_resolved; Alcotest.test_case "a body over a sibling CTE is resolved" `Quick test_a_body_over_a_sibling_cte_is_resolved; Alcotest.test_case "flattening keeps parameter indices" `Quick test_flatten_keeps_parameter_indices; Alcotest.test_case "a join splice keeps parameter indices" `Quick test_flatten_join_keeps_parameter_indices; Alcotest.test_case "explain is the plan the engine runs" `Quick test_explain_is_the_executed_plan; Alcotest.test_case "explain renders the path the catalog names" `Quick test_explain_renders_the_path_the_catalog_names; Alcotest.test_case "explain scans for a rowid read" `Quick test_explain_a_rowid_read_scans; Alcotest.test_case "a constant subquery keys a seek" `Quick test_explain_a_constant_subquery_seeks; Alcotest.test_case "explain lists sources in FROM order" `Quick test_explain_lists_sources_in_from_order; Alcotest.test_case "a joined source seeks" `Quick test_a_joined_source_seeks; Alcotest.test_case "a seek key ignores identifier case" `Quick test_a_seek_key_ignores_identifier_case; Alcotest.test_case "a pin reaches the other end of the join key" `Quick test_a_pin_reaches_the_other_end_of_the_join_key; Alcotest.test_case "explain scans a materialised CTE" `Quick test_explain_scans_a_materialised_cte; Alcotest.test_case "plan_sources binds the statement's own CTEs" `Quick test_plan_sources_binds_the_statements_own_ctes; Alcotest.test_case "explain resolves before it describes" `Quick test_explain_resolves_before_it_describes; Alcotest.test_case "explain describes what resolves" `Quick test_explain_describes_what_resolves; Alcotest.test_case "chain: LEFT then LEFT" `Quick test_chain_left_left; Alcotest.test_case "chain: inner then RIGHT" `Quick test_chain_inner_right; Alcotest.test_case "chain: LEFT then FULL" `Quick test_chain_left_full; Alcotest.test_case "chain: GROUP BY over the null-fills" `Quick test_chain_group_by; Alcotest.test_case "chain: an ON naming a later table" `Quick test_chain_on_names_a_later_table; Alcotest.test_case "chain: a window over an inner chain" `Quick test_window_over_an_inner_chain; Alcotest.test_case "NATURAL over three sources" `Quick test_natural_join_of_three; Alcotest.test_case "USING over three sources" `Quick test_using_join_of_three; Alcotest.test_case "NATURAL LEFT over three sources" `Quick test_natural_left_join_of_three; Alcotest.test_case "a grouped star publishes the join's columns" `Quick test_a_grouped_star_publishes_the_joins_columns; Alcotest.test_case "a NATURAL join with nothing in common" `Quick test_a_natural_join_with_nothing_in_common_crosses; Alcotest.test_case "a USING column one operand has not got" `Quick test_a_using_column_absent_from_an_operand; Alcotest.test_case "the column a RIGHT join's USING preserves" `Quick test_using_column_a_right_join_preserves; Alcotest.test_case "the column a FULL join's USING preserves" `Quick test_using_column_a_full_join_preserves; Alcotest.test_case "a recursive LIMIT bounds the table" `Quick test_a_recursive_limit_bounds_the_table; Alcotest.test_case "a recursive OFFSET drops from the front" `Quick test_a_recursive_offset_drops_from_the_front; Alcotest.test_case "a recursive UNION dedups across the rounds" `Quick test_a_recursive_union_dedups_across_the_rounds; Alcotest.test_case "EXCEPT and INTERSECT over duplicates" `Quick test_except_and_intersect_over_duplicates; Alcotest.test_case "a bounded recursion costs what it produces" `Quick test_a_bounded_recursion_costs_what_it_produces; Alcotest.test_case "an unbounded recursion is refused at the cap" `Quick test_an_unbounded_recursion_is_refused_at_the_cap; Alcotest.test_case "ORDER BY in a recursive select is refused" `Quick test_order_by_in_a_recursive_select_is_refused; ] )