The easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:
TypeScript analyticsbibusiness-intelligencebusinessintelligenceclojuredashboarddatadata-analysisdata-visualizationdatabasemetabasemysqlpostgrespostgresqlreportingslacksql-editorvisualization
;; 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 []}}}]