Skip to content

The easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:

41.6 KB
Raw Blame
;; Isolated search scenarios shared by the in-place, app-db, and semantic tests.
;; Hits are compared as sets, not ranks.
;; A comparison inherits the scenario query and hits unless an engine gives an alternative {:query, :hits}.
;; Comparisons belong here only when at least one engine uses a different query.
;; A comparison's focus is a user's goal, and `:correct` the results that goal wants.
;; No engine need achieve them.
;; Each engine's query is the best one a user could plausibly type for that goal, so no alternative may score
;; worse than the scenario query.
;; Adapted queries use words a user could plausibly think of for the goal without knowing which cards exist:
;; the goal's own words, or common names for the thing, not words only the target card's text would suggest.
;; The user may also be an agent, which uses quotes and operators freely, so adapted queries may use any syntax
;; the engine supports.
;; The vector arm keeps cards within cosine distance 0.7 of the query.
;; With this model, wanted and unrelated cards overlap in distance, so the cutoff admits some unrelated cards and
;; drops some wanted ones.
;; Sometimes the wanted cards are still the nearest, and a looser cutoff would find them; sometimes an unrelated
;; card is nearer, and no cutoff can.
;; Semantic search returns the union of a keyword arm and a vector arm.
;; The keyword arm gives the appdb/PostgreSQL results for every scenario here, but not for every query.
;; With a language other than simple, appdb also indexes an unstemmed copy of the text and the semantic index
;; does not, so a half-typed word such as "shippi" finds "Shipping costs" on appdb but not in the keyword arm.
;; Both arms are recorded, but comparisons and statistics count only the vector arm.
;; In-place hits are what in-place returns on a PostgreSQL app DB.
;; Where another app DB returns something else, that app DB falls short, and metabase.search.query-semantics-test
;; records the difference.
[{:id     "multiword-defaults"
  :focus  "default multiword Boolean and cross-field matches"
  :config "simple"
  :query  "revenue region"
  :docs   {:A {:name "Revenue forecast"}
           :B {:name "Customers by region"}
           :C {:name "Revenue by region"}
           :D {:name "Quarterly revenue", :description "Split by sales region"}
           :E {:name "Product reviews"}}
  ;; In-place's OR favors recall; app-db's AND favors precision.
  ;; Neither default is universally better without knowing the user's intent.
  :expect {:in-place       [:A :B :C :D]
           :appdb-h2       [:C :D]
           :appdb-postgres [:C :D]
           :semantic       {:keyword [:C :D], :vector [:A :B :C :D]}}
  :comparisons
  ;; H2 cannot express OR, and in-place cannot express AND, so each falls back to a single word.
  [{:focus   "anything about revenue or regions"
    :correct [:A :B :C :D]
    :alternatives
    {:appdb-h2       {:query "revenue", :hits [:A :C :D]}
     :appdb-postgres {:query "revenue or region", :hits [:A :B :C :D]}}}
   {:focus   "revenue broken down by region"
    :correct [:C :D]
    :alternatives
    {:in-place {:query "revenue", :hits [:A :C :D]}
     :semantic {:query "revenue split by region", :hits [:A :B :C :D]}}}]}
 {:id     "infix-substring"
  :focus  "infix substring versus lexeme-start completion"
  :config "simple"
  :query  "rate"
  :docs   {:A {:name "Moderated product reviews"}
           :B {:name "Exchange rates"}
           :C {:name "Churn rate"}
           :D {:name "Average order value"}}
  ;; Infix matching finds "rate" in "Moderated"; lexeme-prefix matching avoids that likely false positive, but
  ;; cannot serve arbitrary substring lookup.
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:A :B :C]
           :appdb-postgres [:B :C]
           :semantic       {:keyword [:B :C], :vector [:B]}}}
 {:id     "two-substrings-one-word"
  :focus  "H2 satisfies two required substrings inside one word"
  :config "simple"
  :query  "sign up"
  :docs   {:A {:name "Signups by week"}
           :B {:name "Signed contracts"}
           :C {:name "Upgrades"}
           :D {:name "Sign up funnel"}}
  ;; H2 requires both substrings, not two separate words, which here happens to find the compound "Signups".
  ;; In-place needs only one substring; PostgreSQL's lexemes match only the separate-word card.
  :expect {:in-place       [:A :B :C :D]
           :appdb-h2       [:A :D]
           :appdb-postgres [:D]
           :semantic       {:keyword [:D], :vector [:A :B :D]}}}
 {:id     "last-word-prefix"
  :focus  "last-word prefix versus arbitrary substring"
  :config "simple"
  :query  "order sta"
  :docs   {:A {:name "Order status"}
           :B {:name "Order statistics"}
           :C {:name "Order installments"}
           :D {:name "Order volume"}
           :E {:name "Stalled order report"}}
  :expect {:in-place       [:A :B :C :D :E]
           :appdb-h2       [:A :B :C :E]
           :appdb-postgres [:A :B :E]
           :semantic       {:keyword [:A :B :E], :vector [:A :B :D :E]}}}
 {:id     "nonfinal-prefix"
  :focus  "only the final PostgreSQL word is prefix-completed"
  :config "simple"
  :query  "sub renewals"
  :docs   {:A {:name "Subscription renewals"}
           :B {:name "Sub renewals"}
           :C {:name "Subscription nonrenewals"}
           :D {:name "Renewals"}}
  :expect {:in-place       [:A :B :C :D]
           :appdb-h2       [:A :B :C]
           :appdb-postgres [:B]
           :semantic       {:keyword [:B], :vector [:A :B :C :D]}}}
 {:id     "or-operator"
  :focus  "unquoted or operator versus literal substring"
  :config "simple"
  :query  "refunds or returns"
  :docs   {:A {:name "Refunds"}
           :B {:name "Returns"}
           :C {:name "Refunds and returns"}
           :D {:name "Refunds or returns"}
           :E {:name "Store locations"}}
  ;; PostgreSQL parses `or` as an operator; H2 searches for the literal substring, and in-place matches any
  ;; word, including "or" in "Store".
  :expect {:in-place       [:A :B :C :D :E]
           :appdb-h2       [:D]
           :appdb-postgres [:A :B :C :D]
           :semantic       {:keyword [:A :B :C :D], :vector [:A :B :C :D]}}
  :comparisons
  [{:focus   "the card titled \"Refunds or returns\""
    :correct [:D]
    :alternatives
    {:in-place       {:query "or", :hits [:D :E]}
     :appdb-postgres {:query "\"refunds or returns\"", :hits [:D]}}}
   {:focus   "anything about refunds or returns"
    :correct [:A :B :C :D]
    :alternatives
    {:in-place {:query "refunds returns", :hits [:A :B :C :D]}
     :appdb-h2 {:query "refunds", :hits [:A :C :D]}}}]}
 {:id     "negation"
  :focus  "leading hyphen negation"
  :config "simple"
  :query  "orders -test"
  :docs   {:A {:name "Orders"}
           :B {:name "Test orders"}
           :C {:name "Test dashboard"}
           :D {:name "Orders -test"}}
  ;; Keyword negation excludes B, but it cannot veto the vector arm, which still retrieves it.
  ;; This is a current-behavior contract, not approval.
  :expect {:in-place       [:A :B :D]
           :appdb-h2       [:D]
           :appdb-postgres [:A]
           :semantic       {:keyword [:A], :vector [:A :B :D]}}
  :comparisons
  ;; Spelling out the exclusion changes nothing for the vector arm, except that "Test orders" becomes its
  ;; closest card.
  [{:focus   "orders, excluding test cards"
    :correct [:A]
    :alternatives
    {:in-place {:query "orders", :hits [:A :B :D]}
     :appdb-h2 {:query "orders", :hits [:A :B :D]}
     :semantic {:query "orders, not test data", :hits [:A :B :D]}}}]}
 {:id     "phrase-boundary"
  :focus  "ordered phrase, including the name/searchable-text boundary"
  :config "simple"
  :query  "\"active users\""
  :docs   {:A {:name "Active users"}
           :B {:name "Active paying users"}
           :C {:name "Users: active"}
           :D {:name "KPI: \"Active users\""}}
  :expect {:in-place       [:D]
           :appdb-h2       [:D]
           :appdb-postgres [:A :C :D]
           :semantic       {:keyword [:A :C :D], :vector [:A :B :C :D]}}
  :comparisons
  [{:focus   "cards named \"Active users\""
    :correct [:A :D]
    :alternatives
    {:in-place {:query "active users", :hits [:A :B :C :D]}
     :appdb-h2 {:query "active users", :hits [:A :B :C :D]}}}]}
 {:id     "and-literal"
  :focus  "unquoted and is discarded only by the tsquery parser"
  :config "simple"
  :query  "sales and marketing"
  :docs   {:A {:name "Sales from marketing campaigns"}
           :B {:name "Sales and marketing"}
           :C {:name "Sales and returns"}
           :D {:name "Marketing spend"}
           :E {:name "Revenue and costs"}
           :F {:name "Marketing and sales"}}
  :expect {:in-place       [:A :B :C :D :E :F]
           :appdb-h2       [:B :F]
           :appdb-postgres [:A :B :F]
           :semantic       {:keyword [:A :B :F], :vector [:A :B :C :F]}}
  :comparisons
  ;; PostgreSQL discards the `and` and requires both words, which is what this user wants.
  [{:focus   "cards covering both sales and marketing"
    :correct [:A :B :F]
    :alternatives
    {:in-place {:query "marketing", :hits [:A :B :D :F]}
     :appdb-h2 {:query "sales marketing", :hits [:A :B :F]}}}]}
 {:id     "english-stopword"
  :focus  "English stopword removal"
  :config "english"
  :query  "the office"
  :docs   {:A {:name "The Office episodes"}
           :B {:name "Office supplies"}
           :C {:name "Top shows of the year"}}
  ;; PostgreSQL's English dictionary drops "the".
  ;; That generally helps a natural-language query, but not a search intended to find literal text.
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:A]
           :appdb-postgres [:A :B]
           :semantic       {:keyword [:A :B], :vector [:A]}}
  :comparisons
  ;; A stopword the user typed is probably intentional, e.g. in a show title.
  ;; The English dictionary strips "the" from indexed documents too, so no PostgreSQL query, quoted or not,
  ;; can require it; excluding "supplies" is as close as it gets.
  [{:focus   "the show The Office, not office supplies"
    :correct [:A]
    :alternatives
    {:in-place       {:query "office", :hits [:A :B]}
     :appdb-postgres {:query "office -supplies", :hits [:A]}}}]}
 {:id     "simple-stopword"
  :focus  "same documents/query without English stopword removal"
  :config "simple"
  :query  "the office"
  :docs   {:A {:name "The Office episodes"}
           :B {:name "Office supplies"}
           :C {:name "Top shows of the year"}}
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:A]
           :appdb-postgres [:A]
           :semantic       {:keyword [:A], :vector [:A]}}}
 {:id     "english-stemming"
  :focus  "English stemming of a non-final word"
  :config "english"
  :query  "cancelled subscriptions"
  :docs   {:A {:name "Cancelled subscriptions"}
           :B {:name "Subscription cancellations"}
           :C {:name "Cancelled orders"}
           :D {:name "Active subscriptions"}
           :E {:name "Refund requests"}}
  ;; English stemming matches "Subscription cancellations" for "cancelled subscriptions".
  ;; This improves recall for inflections while broadening beyond exact-word matching.
  :expect {:in-place       [:A :C :D]
           :appdb-h2       [:A]
           :appdb-postgres [:A :B]
           :semantic       {:keyword [:A :B], :vector [:A :B :C :D]}}
  :comparisons
  ;; Substring engines need the shared stem typed out; for in-place, whose words are ORed, that stem alone.
  [{:focus   "subscription cancellations, however worded"
    :correct [:A :B]
    :alternatives
    {:in-place {:query "cancel", :hits [:A :B :C]}
     :appdb-h2 {:query "cancel subscription", :hits [:A :B]}
     :semantic {:query "subscriptions that were cancelled", :hits [:A :B :C :D]}}}]}
 {:id     "simple-no-stemming"
  :focus  "same documents/query with simple rather than English stemming"
  :config "simple"
  :query  "cancelled subscriptions"
  :docs   {:A {:name "Cancelled subscriptions"}
           :B {:name "Subscription cancellations"}
           :C {:name "Cancelled orders"}
           :D {:name "Active subscriptions"}
           :E {:name "Refund requests"}}
  :expect {:in-place       [:A :C :D]
           :appdb-h2       [:A]
           :appdb-postgres [:A]
           :semantic       {:keyword [:A], :vector [:A :B :C :D]}}}
 {:id     "english-stem-prefix"
  :focus  "English stems the final word before completing it"
  :config "english"
  :query  "shipping"
  :docs   {:A {:name "Shipped orders"}
           :B {:name "Shipments by carrier"}
           :C {:name "Shipping costs"}
           :D {:name "Ship dates"}
           :E {:name "Membership tiers"}
           :F {:name "Shopping carts"}}
  ;; PostgreSQL stems "shipping" to "ship" and completes it, which finds every shipping card and not "Membership".
  ;; Substring engines need the exact word.
  :expect {:in-place       [:C]
           :appdb-h2       [:C]
           :appdb-postgres [:A :B :C :D]
           :semantic       {:keyword [:A :B :C :D], :vector [:A :B :C :D]}}
  :comparisons
  ;; The shortest stem finds every shipping card on substring engines, and "Membership" too.
  [{:focus   "anything about shipping"
    :correct [:A :B :C :D]
    :alternatives
    {:in-place {:query "ship", :hits [:A :B :C :D :E]}
     :appdb-h2 {:query "ship", :hits [:A :B :C :D :E]}}}]}
 {:id     "simple-stem-prefix"
  :focus  "same final word with simple, which completes it unstemmed"
  :config "simple"
  :query  "shipping"
  :docs   {:A {:name "Shipped orders"}
           :B {:name "Shipments by carrier"}
           :C {:name "Shipping costs"}
           :D {:name "Ship dates"}
           :E {:name "Membership tiers"}
           :F {:name "Shopping carts"}}
  :expect {:in-place       [:C]
           :appdb-h2       [:C]
           :appdb-postgres [:C]
           :semantic       {:keyword [:C], :vector [:A :B :C :D]}}}
 {:id     "not-literal"
  :focus  "not is an ordinary word, not a negation operator"
  :config "simple"
  :query  "orders not shipped"
  :docs   {:A {:name "Orders shipped"}
           :B {:name "Orders not shipped"}
           :C {:name "Orders by day"}
           :D {:name "Not shipped yet"}}
  :expect {:in-place       [:A :B :C :D]
           :appdb-h2       [:B]
           :appdb-postgres [:B]
           :semantic       {:keyword [:B], :vector [:A :B :D]}}}
 {:id     "english-not-stopword"
  :focus  "same documents/query with English dropping not as a stopword"
  :config "english"
  :query  "orders not shipped"
  :docs   {:A {:name "Orders shipped"}
           :B {:name "Orders not shipped"}
           :C {:name "Orders by day"}
           :D {:name "Not shipped yet"}}
  ;; With "not" dropped, PostgreSQL asks only for "orders" and "shipped", so A matches too: the opposite of
  ;; what the user asked for.
  :expect {:in-place       [:A :B :C :D]
           :appdb-h2       [:B]
           :appdb-postgres [:A :B]
           :semantic       {:keyword [:A :B], :vector [:A :B :D]}}}
 {:id     "vector-only-match"
  :focus  "vector-only match enters semantic union without lexical overlap"
  :config "simple"
  :query  "customers"
  :docs   {:A {:name "Customers by signup date"}
           :B {:name "Client list"}
           :C {:name "Accounts receivable"}
           :D {:name "Product inventory"}}
  ;; B shares no word with the query but falls within the vector distance cutoff.
  ;; C is related too, but the model places it outside the cutoff.
  :expect {:in-place       [:A]
           :appdb-h2       [:A]
           :appdb-postgres [:A]
           :semantic       {:keyword [:A], :vector [:A :B]}}}
 {:id     "english-stopword-only"
  :focus  "a query consisting only of an English stopword"
  :config "english"
  :query  "the"
  :docs   {:A {:name "The Office ratings"}
           :B {:name "Theater ticket sales"}
           :C {:name "Other expenses"}
           :D {:name "Product inventory"}}
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:A :B :C]
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 {:id     "simple-stopword-only"
  :focus  "same stopword-only query with simple configuration"
  :config "simple"
  :query  "the"
  :docs   {:A {:name "The Office ratings"}
           :B {:name "Theater ticket sales"}
           :C {:name "Other expenses"}
           :D {:name "Product inventory"}}
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:A :B :C]
           :appdb-postgres [:A :B]
           :semantic       {:keyword [:A :B], :vector []}}}
 {:id     "percent-punctuation"
  :focus  "percent is literal in substring search but token punctuation in PostgreSQL"
  :config "simple"
  :query  "50%off"
  :docs   {:A {:name "SAVE50OFF redemptions"}
           :B {:name "50OFF redemptions"}
           :C {:name "50%OFF weekend sale"}
           :D {:name "50% off clearance"}
           :E {:name "OFF50 redemptions"}
           :F {:name "50-off sale"}
           :G {:name "50th anniversary sale"}}
  ;; PostgreSQL treats % as token punctuation; H2 and in-place require the literal character.
  ;; Tokenization suits prose, literal matching code-like text.
  ;; A and B would match if % were a LIKE wildcard.
  :expect {:in-place       [:C]
           :appdb-h2       [:C]
           :appdb-postgres [:C :D :F]
           :semantic       {:keyword [:C :D :F], :vector [:A :B :C :D :E :F :G]}}}
 {:id     "underscore-punctuation"
  :focus  "underscore is literal in substring search but token punctuation in PostgreSQL"
  :config "simple"
  :query  "user_id"
  :docs   {:A {:name "Events by user.id"}
           :B {:name "Orders by user_id"}
           :C {:name "Orders by userid"}
           :D {:name "Orders by user id"}
           :E {:name "Orders by user-id"}}
  ;; A and D would match if _ were a LIKE wildcard.
  :expect {:in-place       [:B]
           :appdb-h2       [:B]
           :appdb-postgres [:B :D :E]
           :semantic       {:keyword [:B :D :E], :vector [:A :B :C :D :E]}}}
 {:id     "percent-only"
  :focus  "percent alone matches only literal percent text in substring search"
  :config "simple"
  :query  "%"
  :docs   {:A {:name "Revenue by month"}
           :B {:name "Active customers"}
           :C {:name "Order volume"}
           :D {:name "Profiles 100% complete"}}
  :expect {:in-place       [:D]
           :appdb-h2       [:D]
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 {:id     "possessive-apostrophe"
  :focus  "an apostrophe splits a PostgreSQL word but is literal in substring search"
  :config "simple"
  :query  "customer's orders"
  :docs   {:A {:name "Customer's orders"}
           :B {:name "Customers orders"}
           :C {:name "Customer orders"}
           :D {:name "Orders"}}
  ;; PostgreSQL reads "customer's" as "customer" and "s", and only the possessive card has a lone "s".
  ;; H2 needs the apostrophe; in-place needs any one word.
  :expect {:in-place       [:A :B :C :D]
           :appdb-h2       [:A]
           :appdb-postgres [:A]
           :semantic       {:keyword [:A], :vector [:A :B :C :D]}}
  :comparisons
  ;; Prefix completion finds "Customers" from "customer"; H2 finds both words as substrings.
  [{:focus   "customer orders, however the possessive is written"
    :correct [:A :B :C]
    :alternatives
    {:in-place       {:query "customer", :hits [:A :B :C]}
     :appdb-h2       {:query "customer orders", :hits [:A :B :C]}
     :appdb-postgres {:query "customer", :hits [:A :B :C]}
     :semantic       {:query "customer", :hits [:A :B :C]}}}]}
 {:id     "year-in-range"
  :focus  "PostgreSQL reads the second year of a range as a negative number"
  :config "simple"
  :query  "2024"
  :docs   {:A {:name "Revenue 2024"}
           :B {:name "FY2024 revenue"}
           :C {:name "Revenue 2023-2024"}
           :D {:name "Ticket 20241 escalation"}
           :E {:name "Revenue 2023"}}
  ;; PostgreSQL parses "2023-2024" as "2023" and "-2024", and "FY2024" as one word, so it misses both.
  ;; Prefix completion still matches the ticket number, as substring search does.
  ;; The vector arm returns exactly the 2024 cards.
  :expect {:in-place       [:A :B :C :D]
           :appdb-h2       [:A :B :C :D]
           :appdb-postgres [:A :D]
           :semantic       {:keyword [:A :D], :vector [:A :B :C]}}
  :comparisons
  ;; Only the final word is prefix-completed, so putting "fy2024" last keeps the ticket number out.
  [{:focus   "cards about 2024"
    :correct [:A :B :C]
    :alternatives
    {:appdb-postgres {:query "2024 or fy2024", :hits [:A :B]}}}]}
 {:id     "accent-literal"
  :focus  "only the vector arm matches across an accent"
  :config "simple"
  :query  "cafe"
  :docs   {:A {:name "Café sales"}
           :B {:name "Cafe locations"}
           :C {:name "Cafeteria costs"}
           :D {:name "Coffee orders"}}
  ;; Lexical engines treat "é" as a different letter, so "cafe" misses "Café sales" but matches "Cafeteria".
  ;; On a MySQL or MariaDB app DB, in-place ignores accents and also finds "Café sales", which the test records.
  ;; The vector arm matches across the accent, and "Coffee orders" by meaning.
  :expect {:in-place       [:B :C]
           :appdb-h2       [:B :C]
           :appdb-postgres [:B :C]
           :semantic       {:keyword [:B :C], :vector [:A :B :D]}}
  :comparisons
  ;; Only the final PostgreSQL word is completed, so "cafe or café" keeps "Cafeteria" out.
  [{:focus   "cafés, with or without the accent"
    :correct [:A :B]
    :alternatives
    {:in-place       {:query "café cafe", :hits [:A :B :C]}
     :appdb-h2       {:query "caf", :hits [:A :B :C]}
     :appdb-postgres {:query "cafe or café", :hits [:A :B]}}}]}
 {:id     "hyphenated-compound"
  :focus  "PostgreSQL indexes a hyphenated word and its parts"
  :config "simple"
  :query  "e-commerce"
  :docs   {:A {:name "E-commerce orders"}
           :B {:name "Ecommerce revenue"}
           :C {:name "E commerce returns"}
           :D {:name "Commerce settings"}
           :E {:name "Email campaigns"}}
  ;; Each lexical engine finds only the hyphenated spelling; the vector arm finds all three.
  :expect {:in-place       [:A]
           :appdb-h2       [:A]
           :appdb-postgres [:A]
           :semantic       {:keyword [:A], :vector [:A :B :C]}}
  :comparisons
  ;; PostgreSQL also indexes "E-commerce" as "e" and "commerce", so "e commerce" matches it.
  ;; Substring engines need the shared part, which "Commerce settings" also has.
  [{:focus   "e-commerce, however spelled"
    :correct [:A :B :C]
    :alternatives
    {:in-place       {:query "commerce", :hits [:A :B :C :D]}
     :appdb-h2       {:query "commerce", :hits [:A :B :C :D]}
     :appdb-postgres {:query "e commerce or ecommerce", :hits [:A :B :C]}}}]}
 {:id     "camel-case-name"
  :focus  "PostgreSQL indexes a camelCase name as one word"
  :config "simple"
  :query  "revenue"
  :docs   {:A {:name "monthlyRevenue"}
           :B {:name "MonthlyRevenueByRegion"}
           :C {:name "monthly_revenue"}
           :D {:name "Monthly revenue"}
           :E {:name "Revenue"}}
  ;; Substring engines find "revenue" inside the camelCase names, and PostgreSQL does not.
  ;; Underscores separate PostgreSQL words, so it finds "monthly_revenue".
  :expect {:in-place       [:A :B :C :D :E]
           :appdb-h2       [:A :B :C :D :E]
           :appdb-postgres [:C :D :E]
           :semantic       {:keyword [:C :D :E], :vector [:A :C :D :E]}}}
 {:id     "english-phrase-stopword"
  :focus  "English stopword inside a positional phrase"
  :config "english"
  :query  "\"salt and pepper\""
  :docs   {:A {:name "Salt and pepper grinders"}
           :B {:name "Salt & pepper shakers"}
           :C {:name "Sea salt, black pepper"}
           :D {:name "Salt and black pepper"}
           :E {:name "Searches for \"salt and pepper\""}}
  :expect {:in-place       [:A :D :E]
           :appdb-h2       [:E]
           :appdb-postgres [:A :C :E]
           :semantic       {:keyword [:A :C :E], :vector [:A :B :C :D :E]}}
  :comparisons
  ;; PostgreSQL keeps the stopword's position, so any word can fill it and C matches.
  ;; The user wants the sets, including "Salt & pepper shakers", which no phrase with "and" finds.
  ;; Naming the items instead finds exactly the sets, except on H2, which cannot express OR and so names one.
  ;; Any query that mentions salt and pepper brings the vector arm every card.
  [{:focus   "salt-and-pepper sets"
    :correct [:A :B]
    :alternatives
    {:in-place       {:query "shaker grinder", :hits [:A :B]}
     :appdb-h2       {:query "shaker", :hits [:B]}
     :appdb-postgres {:query "shaker or grinder", :hits [:A :B]}
     :semantic       {:query "shakers and grinders", :hits [:A :B]}}}]}
 {:id     "simple-phrase-and"
  :focus  "same phrase with simple configuration retaining and"
  :config "simple"
  :query  "\"salt and pepper\""
  :docs   {:A {:name "Salt and pepper grinders"}
           :B {:name "Salt & pepper shakers"}
           :C {:name "Sea salt, black pepper"}
           :D {:name "Salt and black pepper"}
           :E {:name "Searches for \"salt and pepper\""}}
  :expect {:in-place       [:A :D :E]
           :appdb-h2       [:E]
           :appdb-postgres [:A :E]
           :semantic       {:keyword [:A :E], :vector [:A :B :C :D :E]}}}
 {:id     "unfinished-quote"
  :focus  "unfinished opening quote keeps phrase but completes final word"
  :config "simple"
  :query  "\"customer ref"
  :docs   {:A {:name "Customer referrals"}
           :B {:name "Customer refunds"}
           :C {:name "Customer partial refunds"}
           :D {:name "Search term: \"customer ref"}
           :E {:name "Customer ref lookup"}
           :F {:name "Search term: \"customer ref\""}}
  :expect {:in-place       [:A :B :C :D :E :F]
           :appdb-h2       [:D :F]
           :appdb-postgres [:A :B :D :E :F]
           :semantic       {:keyword [:A :B :D :E :F], :vector [:A :B :C :D :E :F]}}
  :comparisons
  [{:focus   "mid-typing the phrase \"customer ref...\""
    :correct [:A :B :D :E :F]
    :alternatives
    {:in-place {:query "customer ref", :hits [:A :B :C :D :E :F]}
     :appdb-h2 {:query "customer ref", :hits [:A :B :C :D :E :F]}}}]}
 {:id     "closed-quote"
  :focus  "closing quote disables final-word completion"
  :config "simple"
  :query  "\"customer ref\""
  :docs   {:A {:name "Customer referrals"}
           :B {:name "Customer refunds"}
           :C {:name "Customer partial refunds"}
           :D {:name "Search term: \"customer ref"}
           :E {:name "Customer ref lookup"}
           :F {:name "Search term: \"customer ref\""}}
  :expect {:in-place       [:D :F]
           :appdb-h2       [:F]
           :appdb-postgres [:D :E :F]
           :semantic       {:keyword [:D :E :F], :vector [:A :B :C :D :E :F]}}}
 {:id     "or-branch-prefix"
  :focus  "only final OR-branch term receives prefix completion"
  :config "simple"
  :query  "cust or rev"
  :docs   {:A {:name "Customers"}
           :B {:name "Cust lifetime value"}
           :C {:name "Revenue"}
           :D {:name "Reviews"}
           :E {:name "Prevented churn"}
           :F {:name "Customer revenue"}
           :G {:name "Revenue per customer or account"}
           :H {:name "Store locations"}}
  :expect {:in-place       [:A :B :C :D :E :F :G :H]
           :appdb-h2       [:G]
           :appdb-postgres [:B :C :D :F :G]
           :semantic       {:keyword [:B :C :D :F :G], :vector [:B]}}
  :comparisons
  ;; The typed query abbreviates both words, and PostgreSQL completes only the last, so it misses "Customers".
  ;; Spelling out "revenue" gives in-place and PostgreSQL exactly the goal; H2 cannot express OR.
  ;; "Reviews" also starts with "rev", but is not about revenue.
  [{:focus   "customer or revenue cards"
    :correct [:A :B :C :F :G]
    :alternatives
    {:in-place       {:query "cust revenue", :hits [:A :B :C :F :G]}
     :appdb-h2       {:query "cust", :hits [:A :B :F :G]}
     :appdb-postgres {:query "revenue or cust", :hits [:A :B :C :F :G]}
     :semantic       {:query "customer or revenue reports", :hits [:A :C :F :G]}}}]}
 {:id     "negated-prefix"
  :focus  "final negated term is also prefix-completed"
  :config "simple"
  :query  "revenue -est"
  :docs   {:A {:name "Revenue"}
           :B {:name "Revenue estimates"}
           :C {:name "Estimated revenue"}
           :D {:name "Revenue by west region"}
           :E {:name "Revenue -est"}
           :F {:name "Cost estimates"}}
  :expect {:in-place       [:A :B :C :D :E]
           :appdb-h2       [:E]
           :appdb-postgres [:A :D]
           :semantic       {:keyword [:A :D], :vector [:A :B :C :D :E]}}
  :comparisons
  [{:focus   "revenue, excluding estimates"
    :correct [:A :D]
    :alternatives
    {:in-place {:query "revenue", :hits [:A :B :C :D :E]}
     :appdb-h2 {:query "revenue", :hits [:A :B :C :D :E]}}}]}
 {:id     "vector-negation"
  :focus  "a natural-language exclusion pulls the vector arm toward the excluded cards"
  :config "simple"
  :query  "actual revenue, not estimates"
  :docs   {:A {:name "Revenue"}
           :B {:name "Revenue estimates"}
           :C {:name "Estimated revenue"}
           :D {:name "Revenue by west region"}
           :E {:name "Revenue -est"}
           :F {:name "Cost estimates"}}
  :expect {:in-place       [:B :F]
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector [:B :C :E]}}}
 {:id     "negation-only"
  :focus  "negation-only query is a broad PostgreSQL complement"
  :config "simple"
  :query  "-test"
  :docs   {:A {:name "Weekly revenue"}
           :B {:name "Test dashboard"}
           :C {:name "Revenue test"}
           :D {:name "Revenue -test"}
           :E {:name "Customer list"}}
  ;; PostgreSQL treats this as the complement of "test", returning otherwise unrelated rows.
  ;; That broad result is likely poor search UX, but is current.
  :expect {:in-place       [:D]
           :appdb-h2       [:D]
           :appdb-postgres [:A :E]
           :semantic       {:keyword [:A :E], :vector []}}}
 {:id     "like-escape-percent"
  :focus  "LIKE escape character and percent are both literal query text"
  :config "simple"
  :query  "!%"
  :docs   {:A {:name "Promo code SAVE!%"}
           :B {:name "Promo code SAVE%"}
           :C {:name "Promo code SAVE!"}
           :D {:name "Promo code SAVE"}}
  :expect {:in-place       [:A]
           :appdb-h2       [:A]
           :appdb-postgres []
           :semantic       {:keyword [], :vector [:A]}}}
 {:id     "or-only-operator"
  :focus  "operator-only or is literal for substring engines but empty for tsquery"
  :config "simple"
  :query  "or"
  :docs   {:A {:name "Refunds or returns"}
           :B {:name "Orders by month"}
           :C {:name "Store locations"}
           :D {:name "Revenue"}}
  ;; PostgreSQL sees only an operator, so it has no searchable lexeme, even when "or" is the start of "orders".
  ;; Literal substring engines still find "or", including its occurrences inside words.
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:A :B :C]
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 ;; Controls: "or" and "and" as words in ordinary titles.
 ;; No document contains the word inside a longer word; see or-only-operator for that trap.
 {:id     "or-word-control"
  :focus  "control: or as a literal word"
  :config "simple"
  :query  "or"
  :docs   {:A {:name "Refunds or returns"}
           :B {:name "Revenue"}
           :C {:name "Paid or trial users"}}
  :expect {:in-place       [:A :C]
           :appdb-h2       [:A :C]
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 {:id     "and-word-control"
  :focus  "control: and as a literal word"
  :config "simple"
  :query  "and"
  :docs   {:A {:name "Refunds and returns"}
           :B {:name "Revenue"}
           :C {:name "Salt and pepper grinders"}}
  :expect {:in-place       [:A :C]
           :appdb-h2       [:A :C]
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 ;; Quoting the word makes PostgreSQL search for it instead of parsing an operator.
 ;; Substring engines search for the quote marks too, so they find nothing.
 ;; "and" behaves the same way.
 {:id     "quoted-or-word"
  :focus  "quoted or is a word for PostgreSQL but literal quotes for substring search"
  :config "simple"
  :query  "\"or\""
  :docs   {:A {:name "Refunds or returns"}
           :B {:name "Revenue"}
           :C {:name "Paid or trial users"}}
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres [:A :C]
           :semantic       {:keyword [:A :C], :vector []}}}
 ;; English, the default on an English-locale instance with a PostgreSQL app db, drops "or" as a stopword, so
 ;; PostgreSQL cannot match it even when quoted.
 {:id     "english-quoted-or-word"
  :focus  "same quoted or with English dropping or as a stopword"
  :config "english"
  :query  "\"or\""
  :docs   {:A {:name "Refunds or returns"}
           :B {:name "Revenue"}
           :C {:name "Paid or trial users"}}
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 {:id     "field-term-agreement"
  :focus  "ordinary term matches either indexed text field"
  :config "simple"
  :query  "churn"
  :docs   {:A {:name "Monthly churn"}
           :B {:name "Retention cohorts", :description "Churn by signup month"}
           :C {:name "Order volume"}}
  ;; All four engines agree on an exact ordinary term in either field.
  :expect {:in-place       [:A :B]
           :appdb-h2       [:A :B]
           :appdb-postgres [:A :B]
           :semantic       {:keyword [:A :B], :vector [:A :B]}}}
 {:id     "multiword-agreement"
  :focus  "multiword terms present together, including across fields"
  :config "simple"
  :query  "revenue product"
  :docs   {:A {:name "Revenue by product"}
           :B {:name "Revenue", :description "Broken down by product line"}
           :C {:name "Customer list"}}
  ;; With no one-term-only document, OR and AND produce the same result.
  :expect {:in-place       [:A :B]
           :appdb-h2       [:A :B]
           :appdb-postgres [:A :B]
           :semantic       {:keyword [:A :B], :vector [:A :B]}}}
 {:id     "case-agreement"
  :focus  "letter case is ignored"
  :config "simple"
  :query  "REVENUE"
  :docs   {:A {:name "Revenue"}
           :B {:name "REVENUE BY REGION"}
           :C {:name "revenue forecast"}
           :D {:name "Orders"}}
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:A :B :C]
           :appdb-postgres [:A :B :C]
           :semantic       {:keyword [:A :B :C], :vector [:A :B :C]}}}
 {:id     "prefix-agreement"
  :focus  "final-word prefix without partial or infix distractors"
  :config "simple"
  :query  "order sta"
  :docs   {:A {:name "Order status"}
           :B {:name "Order statistics"}
           :C {:name "Customer list"}}
  ;; Last-word completion and substring matching coincide on these documents.
  :expect {:in-place       [:A :B]
           :appdb-h2       [:A :B]
           :appdb-postgres [:A :B]
           :semantic       {:keyword [:A :B], :vector [:A :B]}}}
 {:id     "operator-empty-agreement"
  :focus  "operator-only query without a literal occurrence"
  :config "simple"
  :query  "or"
  :docs   {:A {:name "Revenue"}
           :B {:name "Customers"}
           :C {:name "Daily signups"}}
  ;; PostgreSQL discards the operator; the substring engines find no literal.
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 {:id     "unmatched-term"
  :focus  "ordinary term absent from every document"
  :config "simple"
  :query  "inventory"
  :docs   {:A {:name "Revenue"}
           :B {:name "Customers"}}
  ;; Unlike operator-empty-agreement, every engine searches for a term but finds no match.
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 {:id     "lexical-and-vector-union"
  :focus  "lexical conjunction versus semantic vector union"
  :config "simple"
  :query  "mobile desktop"
  :docs   {:A {:name "Mobile sessions"}
           :B {:name "Desktop sessions"}
           :C {:name "Mobile vs desktop sessions"}
           :D {:name "Quarterly revenue"}}
  ;; App-db needs both words in one row.
  ;; The vector arm also retrieves each one-word row.
  :expect {:in-place       [:A :B :C]
           :appdb-h2       [:C]
           :appdb-postgres [:C]
           :semantic       {:keyword [:C], :vector [:A :B :C]}}}
 ;; Vector-arm precision and recall on one realistic catalogue.
 ;; In both scenarios an unrelated card is nearer the query than the wanted one, so no cutoff fixes them.
 {:id     "vector-noise"
  :focus  "the vector arm returns an unrelated card and misses the relevant one"
  :config "simple"
  :query  "sales pipeline"
  :docs   {:A {:name "Monthly sales"}
           :B {:name "Sales by region"}
           :C {:name "Deals closed"}
           :D {:name "Bookings by quarter"}
           :E {:name "Sales team headcount"}
           :F {:name "Salesforce sync errors"}
           :G {:name "Sales tax settings"}
           :H {:name "Support tickets"}}
  ;; "Sales team headcount" passes the cutoff; "Deals closed", the card this user wants, does not.
  ;; At 0.85, "Deals closed" is farther than five other cards, including "Support tickets".
  :expect {:in-place       [:A :B :E :F :G]
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector [:E]}}}
 {:id     "vector-paraphrase"
  :focus  "a paraphrase finds nothing, though \"Monthly sales\" is in the catalogue"
  :config "simple"
  :query  "how much did we sell"
  :docs   {:A {:name "Monthly sales"}
           :B {:name "Sales by region"}
           :C {:name "Deals closed"}
           :D {:name "Bookings by quarter"}
           :E {:name "Sales team headcount"}
           :F {:name "Salesforce sync errors"}
           :G {:name "Sales tax settings"}
           :H {:name "Support tickets"}}
  ;; "Monthly sales", at 0.86, is farther than "Sales team headcount", at 0.83.
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}
 ;; Queries whose words no card contains: a paraphrase, an acronym, another language, a misspelling.
 {:id     "vector-paraphrase-hit"
  :focus  "a paraphrase finds the card named for the idea"
  :config "simple"
  :query  "customers who stopped paying"
  :docs   {:A {:name "Churned customers"}
           :B {:name "Cancelled subscriptions"}
           :C {:name "New customers"}
           :D {:name "Customer lifetime value"}
           :E {:name "Support tickets"}
           :F {:name "Order volume"}}
  ;; In-place matches "customers"; app-db needs every word.
  :expect {:in-place       [:A :C]
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector [:A]}}
  :comparisons
  ;; Lexical engines need the jargon word.
  [{:focus   "churned customers"
    :correct [:A]
    :alternatives
    {:in-place       {:query "churn", :hits [:A]}
     :appdb-h2       {:query "churn", :hits [:A]}
     :appdb-postgres {:query "churn", :hits [:A]}}}]}
 {:id     "vector-acronym"
  :focus  "the vector arm expands an acronym"
  :config "simple"
  :query  "MRR"
  :docs   {:A {:name "Monthly recurring revenue"}
           :B {:name "Annual recurring revenue"}
           :C {:name "Monthly active users"}
           :D {:name "Revenue by product"}
           :E {:name "Marketing spend"}}
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector [:A :B]}}
  :comparisons
  ;; Spelling the acronym out is exact for app-db.
  ;; It moves the vector arm toward every monthly and revenue card, so semantic does best with "MRR".
  [{:focus   "monthly recurring revenue"
    :correct [:A]
    :alternatives
    {:in-place       {:query "recurring", :hits [:A :B]}
     :appdb-h2       {:query "monthly recurring revenue", :hits [:A]}
     :appdb-postgres {:query "monthly recurring revenue", :hits [:A]}}}]}
 {:id     "vector-multilingual"
  :focus  "the vector arm matches a query in another language"
  :config "simple"
  :query  "ventas mensuales"
  :docs   {:A {:name "Sales by region"}
           :B {:name "Revenue by region"}
           :C {:name "Customers by region"}
           :D {:name "Monthly sales"}
           :E {:name "Support tickets"}
           :F {:name "Product inventory"}}
  ;; The query shares no word or stem with any card, so lexical engines find nothing.
  ;; The vector arm returns "Monthly sales" at about 0.46, and "Sales by region" at 0.68, inside the cutoff.
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector [:A :D]}}}
 {:id     "vector-typo"
  :focus  "the vector arm tolerates a misspelling among correct words"
  :config "simple"
  :query  "reveune by month"
  :docs   {:A {:name "Revenue by month"}
           :B {:name "Revenue by region"}
           :C {:name "Orders by month"}
           :D {:name "Support tickets"}
           :E {:name "Customer reviews"}}
  ;; In-place matches the other words; app-db needs the misspelled one too.
  :expect {:in-place       [:A :B :C]
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector [:A :C]}}}
 {:id     "vector-typo-alone"
  :focus  "the cutoff drops the nearest cards for a lone misspelling"
  :config "simple"
  :query  "reveune"
  :docs   {:A {:name "Revenue by month"}
           :B {:name "Revenue by region"}
           :C {:name "Orders by month"}
           :D {:name "Support tickets"}
           :E {:name "Customer reviews"}}
  ;; Unlike vector-noise, the nearest cards are the wanted ones: both revenue cards sit at about 0.82, ahead of
  ;; "Customer reviews" at 0.88.
  ;; A looser cutoff would return them.
  :expect {:in-place       []
           :appdb-h2       []
           :appdb-postgres []
           :semantic       {:keyword [], :vector []}}}]