// Copyright (c) 2023-2026 ParadeDB, Inc. // // This file is part of ParadeDB - Postgres for Search and Analytics // // This program is free software: you can redistribute it and/or modify // it under the terms of the GNU Affero General Public License as published by // the Free Software Foundation, either version 3 of the License, or // (at your option) any later version. // // This program is distributed in the hope that it will be useful // but WITHOUT ANY WARRANTY; without even the implied warranty of // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the // GNU Affero General Public License for more details. // // You should have received a copy of the GNU Affero General Public License // along with this program. If not, see . use tests::fixtures::querygen::distinctgen::arb_distinct_mode; use tests::fixtures::querygen::groupbygen::arb_group_by; use tests::fixtures::querygen::joingen::{JoinType, arb_joins, arb_semi_joins}; use tests::fixtures::querygen::mutationgen::churn_setup; use tests::fixtures::querygen::numericgen::arb_numeric_expr; use tests::fixtures::querygen::orderbygen::arb_joinscan_order_parts; use tests::fixtures::querygen::pagegen::arb_paging_exprs; use tests::fixtures::querygen::pdbagggen::{ arb_pdb_agg_join, arb_pdb_agg_single_table, arb_pdb_agg_window, }; use tests::fixtures::querygen::wheregen::Expr as WhereExpr; use tests::fixtures::querygen::wheregen::arb_wheres; use tests::fixtures::querygen::windowgen::arb_window_targets; use tests::fixtures::querygen::{ Column, IndexExpression, PgGucs, QuerySide, Sides, arb_joins_and_wheres, compare_outcome_retrying, compare_outcome_retrying_on, compare_plan_retrying, compare_plan_retrying_on, generated_queries_setup, }; use tests::fixtures::*; use futures::executor::block_on; use lockfree_object_pool::MutexObjectPool; use proptest::prelude::*; use rstest::*; use sqlx::{PgConnection, Row}; #[cfg(feature = "dst")] #[test] fn antithesis_sdk_initializes() { ensure_dst_init(); } /// Proptest configuration shared by every generator test in this file. Under `dst`, proptest /// draws its randomness from the Antithesis SDK, so the platform controls and branches the /// entropy stream; otherwise this is just `Config::default()`. fn qgen_proptest_config() -> proptest::test_runner::Config { #[allow(unused_mut)] let mut config = proptest::test_runner::Config::default(); #[cfg(feature = "dst")] { config.rng_algorithm = proptest::test_runner::RngAlgorithm::Antithesis; // No shrinking: Antithesis controls the entropy stream and fault schedule, so a replay // sees a different history and the minimized input is meaningless. config.max_shrink_iters = 0; config.source_file = Some("tests/tests/qgen.rs"); } config } /// Report one case outcome as a per-test Antithesis property, then collapse it to a proptest /// result. The single `Always` assertion doubles as a reachability check: cataloged, it fails as /// unreached if the generator never produces a case. No-op under a plain `cargo test`. macro_rules! qgen_oracle { ($prop:literal, $outcome:expr) => {{ let outcome = $outcome; ensure_dst_init(); match &outcome { tests::fixtures::querygen::CaseOutcome::Match => { dst::assert_always!(true, $prop, &serde_json::json!({})); } tests::fixtures::querygen::CaseOutcome::Failure(e) => { dst::assert_always!( false, $prop, &serde_json::json!({ "detail": e.to_string() }) ); } // compare_outcome_retrying resolves every Transient by retrying, so this arm is // defensive. tests::fixtures::querygen::CaseOutcome::Transient(_, e) => { dst::assert_always!( false, $prop, &serde_json::json!({ "detail": e.to_string() }) ); } } outcome.into_test_result() }}; } const COLUMNS: &[Column] = &[ Column::new("id", "SERIAL8", "'4'") .primary_key() .groupable({ true }), Column::new("uuid", "UUID", "'550e8400-e29b-41d4-a716-446655440000'") .groupable({ true }) .random_generator_sql("rpad(lpad((random() * 2147483647)::integer::text, 10, '0'), 32, '0')::uuid"), Column::new("name", "TEXT", "'bob'") .index_expression(IndexExpression::Literal) .random_generator_sql( "(ARRAY ['alice', 'bob', 'cloe', 'sally', 'brandy', 'brisket', 'anchovy']::text[])[(floor(random() * 7) + 1)::int]" ), Column::new("color", "VARCHAR", "'blue'") .whereable(true) .index_expression(IndexExpression::Literal) .random_generator_sql( "(ARRAY ['red', 'green', 'blue', 'orange', 'purple', 'pink', 'yellow', NULL]::text[])[(floor(random() * 8) + 1)::int]" ), Column::new("age", "INTEGER", "'20'") .random_generator_sql("(floor(random() * 100) + 1)"), Column::new("op_expr_col", "INTEGER", "'3'") .whereable(false) .groupable(true) .index_expression(IndexExpression::Multiply { factor: 10 }) .random_generator_sql("(floor(random() * 100) + 1)"), Column::new("quantity", "INTEGER", "'7'") .whereable(true) .random_generator_sql("CASE WHEN random() < 0.1 THEN NULL ELSE (floor(random() * 100) + 1)::int END"), Column::new("price", "NUMERIC(10,2)", "'99.99'") .groupable( true ) .random_generator_sql("(random() * 1000 + 10)::numeric(10,2)"), // Additional NUMERIC columns for testing Numeric64 vs NumericBytes storage Column::new("small_numeric", "NUMERIC(5,2)", "'12.34'") .groupable(false) .random_generator_sql("(random() * 100)::numeric(5,2)"), Column::new("int_numeric", "NUMERIC(10,0)", "'12345'") .groupable(false) .random_generator_sql("(floor(random() * 1000000))::numeric(10,0)"), Column::new("high_scale", "NUMERIC(18,6)", "'123.456789'") .groupable(false) .random_generator_sql("(random() * 10000)::numeric(18,6)"), Column::new("big_numeric", "NUMERIC", "'12345.67890'") .groupable(false) .random_generator_sql("(random() * 100000)::numeric"), Column::new("rating", "INTEGER", "'4'") .indexed({ // Marked un-indexed in order to test heap-filter pushdown. false }) .random_generator_sql("(floor(random() * 5) + 1)::int"), Column::new("category", "TEXT", "'electronics'") .whereable(false) .index_expression(IndexExpression::Upper) .random_generator_sql( "(ARRAY ['electronics', 'clothing', 'food', 'books', 'toys', 'sports', 'home']::text[])[(floor(random() * 7) + 1)::int]" ), Column::new("literal_normalized", "TEXT", "'Hello World'") .whereable({ // literal_normalized lowercases text, so BM25 @@@ would match case-insensitively // while PostgreSQL = does exact matching. This causes test failures when comparing // results, so we exclude it from WHERE clause testing. false }) .groupable(false) .index_expression(IndexExpression::LiteralNormalized) .random_generator_sql( "(ARRAY ['Hello World', 'HELLO WORLD', 'hello world', 'HeLLo WoRLD', 'GOODBYE WORLD', 'goodbye world']::text[])[(floor(random() * 6) + 1)::int]" ), Column::new("metadata", "JSONB", "'{\"brand\": \"apple\", \"rating\": 4}'") .whereable(false) .groupable(false) .index_expression(IndexExpression::UnicodeWordsColumnar) .random_generator_sql( "CASE (floor(random() * 5))::int WHEN 0 THEN NULL WHEN 1 THEN '{}'::jsonb ELSE jsonb_build_object( 'brand', (ARRAY ['apple', 'samsung', 'sony', 'lg']::text[])[(floor(random() * 4) + 1)::int], 'rating', CASE WHEN random() < 0.2 THEN NULL ELSE (floor(random() * 5) + 1)::int END, 'details', jsonb_build_object( 'score', CASE WHEN random() < 0.2 THEN NULL ELSE (floor(random() * 200) - 100) / 4.0 END ) ) END" ), Column::new("tags", "TEXT[]", "ARRAY['alpha', 'beta']::text[]") .whereable(false) .groupable(false) .index_expression(IndexExpression::Literal) .random_generator_sql( "(CASE (floor(random() * 5) + 1)::int \ WHEN 1 THEN ARRAY['alpha', 'beta']::text[] \ WHEN 2 THEN ARRAY['gamma']::text[] \ WHEN 3 THEN ARRAY['delta', 'epsilon', 'zeta']::text[] \ WHEN 4 THEN NULL \ ELSE ARRAY[]::text[] \ END)", ), Column::new("embedding", "vector(3)", "'[1,1,1]'") .whereable(false) .groupable(false) .orderable(false) .index_expression(IndexExpression::VectorCosine) .random_generator_sql( "ARRAY[(floor(random() * 10) + 1)::float4, (floor(random() * 10) + 1)::float4, (floor(random() * 10) + 1)::float4]::vector(3)", ), ]; fn columns_named(names: Vec<&'static str>) -> Vec { COLUMNS .iter() .filter(|c| names.contains(&c.name)) .cloned() .collect() } #[derive(Clone, Copy, Debug)] enum SubqueryKind { Exists, In, } #[derive(Clone, Copy, Debug)] enum SubqueryPolarity { Positive, Negated, } /// How an `EXISTS` subquery reads the outer row's column. #[derive(Clone, Copy, Debug)] enum SubqueryCorrelation { /// `inner.col = outer.col` Var, /// `inner.col = (SELECT outer.col)`: the value arrives through an InitPlan, which a /// parallel plan has to hand its workers on every rescan. InitPlan, } #[derive(Clone, Debug)] struct GeneratedSubquery { kind: SubqueryKind, polarity: SubqueryPolarity, correlation: SubqueryCorrelation, column: &'static str, inner_where_expr: WhereExpr, paging_exprs: String, } impl GeneratedSubquery { fn to_sql(&self, op: &str, outer_table_name: &str, inner_table_name: &str) -> String { let base = match self.kind { SubqueryKind::Exists => format!( "EXISTS (\ SELECT 1 FROM {inner_table_name} \ WHERE {inner_table_name}.{column} = {outer_value} \ AND {} {}\ )", self.inner_where_expr.to_sql(op), self.paging_exprs, column = self.column, outer_value = match self.correlation { SubqueryCorrelation::Var => format!("{outer_table_name}.{}", self.column), SubqueryCorrelation::InitPlan => { format!("(SELECT {outer_table_name}.{})", self.column) } }, ), SubqueryKind::In => format!( "{outer_table_name}.{column} IN (\ SELECT {column} FROM {inner_table_name} WHERE {} {}\ )", self.inner_where_expr.to_sql(op), self.paging_exprs, column = self.column, ), }; match (self.kind, self.polarity) { (_, SubqueryPolarity::Positive) => base, (SubqueryKind::Exists, SubqueryPolarity::Negated) => format!("NOT {base}"), (SubqueryKind::In, SubqueryPolarity::Negated) => format!("NOT ({base})"), } } } /// /// Tests all join configurations against small tables (important for joins that produce /// cartesian products or expansive results). /// /// When the generated query is within the subset guaranteed to be plannable by ParadeDB's /// custom scan (INNER/OUTER joins without Cartesian products, LIMIT present, and GUC enabled), /// this test explicitly verifies via EXPLAIN that PostgreSQL selected ParadeDB Join Scan /// (or Aggregate Scan). For all queries, it verifies exact result correctness and parity /// against PostgreSQL. /// #[rstest] #[case::clean(false)] #[case::churn(true)] #[tokio::test] async fn generated_joins_small(database: Db, #[case] churn_on: bool) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let tables_and_sizes = [("users", 10), ("products", 10), ("orders", 10)]; let tables = tables_and_sizes .iter() .map(|(table, _)| table) .collect::>(); let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let setup_sql = churn_setup(&pool, setup_sql, &tables_and_sizes, COLUMNS, churn_on); let where_and_join_columns = columns_named(vec!["id", "name", "color", "age", "uuid", "tags"]); proptest!(qgen_proptest_config(), |( ((join, where_expr, cross_rel), distinct_mode, mut order_parts, window_targets, pdb_agg) in arb_joins_and_wheres( any::(), tables, &where_and_join_columns, ).prop_flat_map(|(join, where_expr, cross_rel)| { let used_tables = join .used_tables() .into_iter() .map(str::to_string) .collect::>(); let pdb_agg = proptest::option::of(arb_pdb_agg_window( used_tables.clone(), join.unnested_fields(), )); ( Just((join, where_expr, cross_rel)), arb_distinct_mode(used_tables.clone(), COLUMNS), arb_joinscan_order_parts(used_tables.clone(), false), arb_window_targets(used_tables), pdb_agg, ) }), limit in proptest::option::of(1..=50usize), offset in proptest::option::of(0..=10usize), gucs in any::(), )| { let join_clause = join.to_sql(); let used_tables = join.used_tables(); // Assert that JoinScan or AggregateScan was actually used. // With JoinScan running at UPPERREL_FINAL, all generated join shapes (inner, outer, // cross, multi-table, DISTINCT, lateral unnest, and expression ordering) // are plannable by ParadeDB's custom scan whenever LIMIT is present, except when // outer joins combine with null-testing predicates (`IS NULL`, `IS NOT NULL`, or // negated equivalents). PostgreSQL's optimizer (`reduce_outer_joins`) converts such // outer joins into anti-joins (`JOIN_ANTI`) and prunes the inner relation. When join keys // or projections reference columns from the pruned relation with no output-visible // equivalent, JoinScan intentionally declines to plan (see `rewrite_pruned_join_keys`). let expect_custom_scan = gucs.join_custom_scan && limit.is_some() && window_targets.iter().all(|t| t.is_absorbable()) // DISTINCT + window declines: the window expression is itself a // DISTINCT column, so the query's distinct pathkeys always // contain it and JoinScan's ORDER BY resolution refuses the path // ("unsupported ORDER BY expression shape"). && !(distinct_mode.is_distinct() && !window_targets.is_empty()) && !(!join.has_only_inner() && where_expr.has_null_predicate()); // `pdb.agg()` has no PostgreSQL fallback, so a JoinScan that declines is an // error, not a second path to compare. Its window form joins the target // list only where the scan has to engage, and not under DISTINCT, where // a jsonb target entry declines. The planner hook also raises an error // for a `pdb.agg()` window whose WHERE clause needs a heap filter while // filter pushdown is off. PostgreSQL never runs it: the spec's // `GROUP BY` over the same join is the oracle for the document. let pdb_agg = pdb_agg.filter(|_| { expect_custom_scan && gucs.filter_pushdown && !distinct_mode.is_distinct() }); let mut target_cols = vec![ format!("{}.id", used_tables[0]), format!("{}.name", used_tables[0]), ]; if distinct_mode.is_distinct() { for table in &used_tables[1..] { let col = format!("{}.id", table); if !target_cols.contains(&col) { target_cols.push(col); } } if let Some(expr) = distinct_mode.expression() { let expr_sql = expr.to_sql(); if !target_cols.contains(&expr_sql) { target_cols.push(expr_sql); } } // Ensure every expression in order_parts appears in SELECT DISTINCT // to satisfy PostgreSQL's requirement that ORDER BY expressions must appear in the SELECT list. for part in &order_parts { if !target_cols.contains(part) { target_cols.push(part.clone()); } } } for target in &window_targets { target_cols.push(target.to_sql()); } for alias in join.unnest_aliases() { target_cols.push(alias.to_string()); } let target_cols_str = target_cols.join(", "); let distinct_kw = if distinct_mode.is_distinct() { "DISTINCT " } else { "" }; let from = format!("SELECT {distinct_kw}{target_cols_str} {join_clause}"); // The document goes last, where `window_rows` reads it. let bm25_from = match &pdb_agg { Some(agg) => format!( "SELECT {distinct_kw}{target_cols_str}, {} OVER () {join_clause}", agg.call() ), None => from.clone(), }; let (where_pg, where_bm25) = match &cross_rel { Some(cr) => ( format!("({}) AND ({})", where_expr.to_sql(" = "), cr.to_sql()), format!("({}) AND ({})", where_expr.to_sql("@@@"), cr.to_sql()), ), None => (where_expr.to_sql(" = "), where_expr.to_sql("@@@")), }; for alias in join.unnest_aliases() { order_parts.push(alias.to_string()); } let order_by = order_parts.join(", "); let offset_clause = match offset { Some(o) if o > 0 => format!(" OFFSET {o}"), _ => "".to_string(), }; let limit_clause = match limit { Some(l) => format!("LIMIT {l}{offset_clause}"), None => "".to_string(), }; let pg_query = format!("{from} WHERE {where_pg} ORDER BY {order_by} {limit_clause}"); let bm25_query = format!("{bm25_from} WHERE {where_bm25} ORDER BY {order_by} {limit_clause}"); let pdb_window = pdb_agg .as_ref() .map(|agg| (agg, agg.pg_query(&join_clause, &where_pg))); if expect_custom_scan { // A leftover PostgreSQL WindowAgg node would mean the scan // engaged without absorbing the window aggregates (#5637). let forbidden: &[&str] = if window_targets.is_empty() && pdb_agg.is_none() { &[] } else { &["WindowAgg"] }; qgen_oracle!( "qgen: generated_joins_small - ParadeDB custom scan planned", compare_plan_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, &["ParadeDB Join Scan", "ParadeDB Aggregate Scan"], forbidden, ) )?; } // A `pdb.agg()` document takes the typed path too: only the candidate's // rows carry it, so the rows are compared without it and the document // on its own. let typed_rows = !window_targets.is_empty() || pdb_agg.is_some(); qgen_oracle!("qgen: generated_joins_small - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, |query, side, conn| { let rows = query.fetch_dynamic_result(conn)?; // The document on the candidate's rows against the buckets of the // PostgreSQL `GROUP BY`. With no row there is no document to compare. let mut buckets = match &pdb_window { Some((agg, pg_buckets_query)) if !rows.is_empty() => { if side.is_candidate() { agg.window_rows(&rows)? } else { agg.rows(pg_buckets_query.as_str().fetch_dynamic_result(conn)?)? } } _ => Vec::new(), }; buckets.sort(); let document_cols = usize::from(side.is_candidate() && pdb_window.is_some()); let mut row_strings: Vec = rows .into_iter() .map(|row| { use sqlx::Row; let id: i64 = row.try_get(0).unwrap_or(0); if !typed_rows { return format!("{:020}|{:?}", id, row); } // Typed, fixed-precision formatting when window // aggregates are projected: AVG decodes through // Float64/decimal encodings on the scan side while // PostgreSQL computes exact NUMERIC averages, so the // raw row Debug representation would differ in // insignificant digits. let mut parts = Vec::new(); for i in 0..row.len() - document_cols { let part = if let Ok(v) = row.try_get::(i) { v.to_string() } else if let Ok(v) = row.try_get::(i) { v.to_string() } else if let Ok(v) = row.try_get::(i) { v.to_string() } else if let Ok(v) = row.try_get::(i) { format!("{v:.6}") } else if let Ok(v) = row.try_get::(i) { format!("{v:.6}") } else if let Ok(v) = row.try_get::(i) { v } else { // NULLs (and any unhandled type) format // identically on both sides. "NULL".to_string() }; parts.push(part); } format!("{:020}|{}", id, parts.join("|")) }) .collect(); row_strings.sort(); Ok((row_strings, buckets)) } ))?; }); } #[rstest] #[case::clean(false)] #[case::churn(true)] #[tokio::test] async fn generated_single_relation(database: Db, #[case] churn_on: bool) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let table_name = "users"; let tables_and_sizes = [(table_name, 10)]; let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let setup_sql = churn_setup(&pool, setup_sql, &tables_and_sizes, COLUMNS, churn_on); proptest!(qgen_proptest_config(), |( where_expr in arb_wheres( vec![table_name], COLUMNS, ), gucs in any::(), target in prop_oneof![Just("COUNT(*)"), Just("id")], )| { qgen_oracle!("qgen: generated_single_relation - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &format!("SELECT {target} FROM {table_name} WHERE {}", where_expr.to_sql(" = ")), &format!("SELECT {target} FROM {table_name} WHERE {}", where_expr.to_sql("@@@")), &gucs, &pool, &setup_sql, |query, _, conn| { let mut rows = query.fetch_result::<(i64,)>(conn)?; rows.sort(); Ok(rows) } ))?; }); } /// /// Property test for GROUP BY aggregates with ORDER BY and LIMIT/OFFSET /// - ensures equivalence between PostgreSQL and bm25 behavior /// #[rstest] #[case::clean(false)] #[case::churn(true)] #[tokio::test] async fn generated_group_by_aggregates(database: Db, #[case] churn_on: bool) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let table_name = "users"; let tables_and_sizes = [(table_name, 50)]; let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let setup_sql = churn_setup(&pool, setup_sql, &tables_and_sizes, COLUMNS, churn_on); // Columns that can be used for grouping (must be columnar indexed) let where_columns: Vec<_> = COLUMNS .iter() .filter(|col| col.is_whereable) .cloned() .collect(); let mut grouping_columns: Vec<_> = COLUMNS .iter() .filter(|col| col.is_groupable) .filter(|col| col.index_expression.is_none_or(|e| e.indexes_bare_column())) .map(|col| col.name.to_string()) .collect(); grouping_columns.extend( [ // FuncExpr matching an index expression "upper(category)", // OpExpr; pair this with the IndexExpression variant from earlier, "op_expr_col * 10", // JSON projections "metadata->>'brand'", "metadata->'brand'", // TODO(issue #7207) //"(metadata->>'rating')::bigint", // TODO(issue #6834): numeric JSON `->>` grouping isn't pushed down exactly //"metadata->'details'->>'score'", ] .map(String::from), ); proptest!(qgen_proptest_config(), |( text_where_expr in arb_wheres( vec![table_name], &where_columns, ), numeric_where_expr in arb_wheres( vec![table_name], &columns_named(vec!["age", "price", "rating"]), ), group_by_expr in arb_group_by(grouping_columns.clone(), vec![ "COUNT(*)", "SUM(price)", "AVG(price)", "MIN(quantity)", "MAX(quantity)", "SUM(age)", "AVG(age)", "COUNT(metadata->>'brand')", "COUNT((metadata->>'rating')::bigint)", "SUM((metadata->>'rating')::bigint)", "AVG((metadata->>'rating')::bigint)", "MIN((metadata->>'rating')::bigint)", "MAX((metadata->>'rating')::bigint)", "COUNT(COALESCE((metadata->>'rating')::bigint, 0))", "SUM(COALESCE((metadata->>'rating')::bigint, -1))", "SUM(COALESCE((metadata->>'rating')::bigint, 7))", "SUM((metadata->'details'->>'score')::double precision)", "AVG((metadata->'details'->>'score')::double precision)", "MIN((metadata->'details'->>'score')::double precision)", "MAX((metadata->'details'->>'score')::double precision)", "AVG(COALESCE((metadata->'details'->>'score')::double precision, 1.5))", ]), limit in prop::option::of(5..21_usize), offset in prop::option::of(0..4_usize), gucs in any::(), )| { let select_list = group_by_expr.to_select_list(); let group_by_clause = group_by_expr.to_sql(); let order_by_and_offset_clause: String = if group_by_expr.group_by_columns.is_empty() { String::new() } else { let order_by_items: Vec = group_by_expr.group_by_columns .iter() .map(|item| {format!("{item} ASC NULLS LAST")}) .collect(); // only apply OFFSET when there are grouping columns, otherwise // we'd offset the single aggregate row let offset_clause = offset .map(|value| format!(" OFFSET {value}")) .unwrap_or_default(); format!("ORDER BY {}{offset_clause}", order_by_items.join(", ")) }; let limit_clause = limit .map(|value| format!(" LIMIT {value}")) .unwrap_or_default(); // Create combined WHERE clause for PostgreSQL using = operator let pg_where_clause = format!( "({}) AND ({})", text_where_expr.to_sql(" = "), numeric_where_expr.to_sql(" < ") ); // Create combined WHERE clause for BM25 using appropriate operators let bm25_where_clause = format!( "({}) AND ({})", text_where_expr.to_sql("@@@"), numeric_where_expr.to_sql(" < ") ); let pg_query = format!( "SELECT {select_list} FROM {table_name} WHERE {pg_where_clause} {group_by_clause} {order_by_and_offset_clause}{limit_clause}", ); let bm25_query = format!( "SELECT {select_list} FROM {table_name} WHERE {bm25_where_clause} {group_by_clause} {order_by_and_offset_clause}{limit_clause}", ); // Custom result comparator for GROUP BY results let compare_results = |query: &str, _: QuerySide, conn: &mut PgConnection| -> Result, sqlx::Error> { // Fetch all rows as dynamic results and convert to string representation let rows = query.fetch_dynamic_result(conn)?; let string_rows: Vec = rows .into_iter() .map(|row| { // Convert entire row to a string representation for comparison let mut row_string = String::new(); for i in 0..row.len() { if i > 0 { row_string.push('|'); } // Try to get value as different types, converting to string let value_str = if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { format!("{val:.6}") } else if let Ok(val) = row.try_get::(i) { format!("{val:.6}") } else if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { val } else { "NULL".to_string() }; row_string.push_str(&value_str); } row_string }) .collect(); Ok(string_rows) }; qgen_oracle!("qgen: generated_group_by_aggregates - ParadeDB result matches PostgreSQL", compare_outcome_retrying(&pg_query, &bm25_query, &gucs, &pool, &setup_sql, compare_results))?; }); } #[rstest] #[tokio::test] async fn generated_paging_small(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let table_name = "users"; let setup_sql = generated_queries_setup(&pool, &[(table_name, 1000)], COLUMNS); proptest!(qgen_proptest_config(), |( where_expr in arb_wheres(vec![table_name], &columns_named(vec!["name"])), paging_exprs in arb_paging_exprs(table_name, vec!["name", "color", "age", "quantity"], vec!["id", "uuid"], 100), gucs in any::(), )| { qgen_oracle!("qgen: generated_paging_small - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &format!("SELECT id FROM {table_name} WHERE {} {paging_exprs}", where_expr.to_sql(" = ")), &format!("SELECT id FROM {table_name} WHERE {} {paging_exprs}", where_expr.to_sql("@@@")), &gucs, &pool, &setup_sql, |query, _, conn| query.fetch_result::<(i64,)>(conn), ))?; }); } /// Generates paging expressions on a large table, which was necessary to reproduce /// https://github.com/paradedb/tantivy/pull/51 /// /// TODO: Explore whether this could use https://github.com/paradedb/paradedb/pull/2681 /// to use a large segment count rather than a large table size. #[rstest] #[tokio::test] async fn generated_paging_large(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let table_name = "users"; let setup_sql = generated_queries_setup(&pool, &[(table_name, 100000)], COLUMNS); proptest!(qgen_proptest_config(), |( paging_exprs in arb_paging_exprs(table_name, vec![], vec!["uuid"], 100), gucs in any::(), )| { qgen_oracle!("qgen: generated_paging_large - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &format!("SELECT uuid::text FROM {table_name} WHERE name = 'bob' {paging_exprs}"), &format!("SELECT uuid::text FROM {table_name} WHERE name === 'bob' {paging_exprs}"), &gucs, &pool, &setup_sql, |query, _, conn| query.fetch_result::<(String,)>(conn), ))?; }); } #[rstest] #[case::clean(false)] #[case::churn(true)] #[tokio::test] async fn generated_subquery(database: Db, #[case] churn_on: bool) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let outer_table_name = "products"; let inner_table_name = "orders"; let tables_and_sizes = [(outer_table_name, 10), (inner_table_name, 10)]; let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let setup_sql = churn_setup(&pool, setup_sql, &tables_and_sizes, COLUMNS, churn_on); proptest!(qgen_proptest_config(), |( outer_where_expr in arb_wheres( vec![outer_table_name], COLUMNS, ), inner_where_expr in arb_wheres( vec![inner_table_name], COLUMNS, ), // `rating` is not indexed, so its predicate runs as a heap filter. subquery_column in proptest::sample::select(&["name", "color", "age", "rating"]), subquery_kind in prop_oneof![Just(SubqueryKind::Exists), Just(SubqueryKind::In)], subquery_polarity in prop_oneof![ Just(SubqueryPolarity::Positive), Just(SubqueryPolarity::Negated), ], subquery_correlation in prop_oneof![ Just(SubqueryCorrelation::Var), Just(SubqueryCorrelation::InitPlan), ], // Bounded by the inner table's size: an `OFFSET` past its rows makes the `EXISTS` false // whatever the scan returns. paging_exprs in arb_paging_exprs(inner_table_name, vec!["name", "color", "age"], vec!["id", "uuid"], tables_and_sizes[1].1), mut gucs in any::(), )| { // TODO(#6814): once Postgres pulls the EXISTS up into a semi join, the InitPlan form is a // SubPlan in the join filter, which the DataFusion aggregate cannot evaluate ("SubPlan // found with no parent plan"). if matches!(subquery_correlation, SubqueryCorrelation::InitPlan) { gucs.aggregate_custom_scan = false; } let subquery = GeneratedSubquery { kind: subquery_kind, polarity: subquery_polarity, correlation: subquery_correlation, column: subquery_column, inner_where_expr, paging_exprs, }; let pg = format!( "SELECT COUNT(*) FROM {outer_table_name} \ WHERE {} AND {}", subquery.to_sql(" = ", outer_table_name, inner_table_name), outer_where_expr.to_sql(" = "), ); let bm25 = format!( "SELECT COUNT(*) FROM {outer_table_name} \ WHERE {} AND {}", subquery.to_sql("@@@", outer_table_name, inner_table_name), outer_where_expr.to_sql("@@@"), ); qgen_oracle!("qgen: generated_subquery - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &pg, &bm25, &gucs, &pool, &setup_sql, |query, _, conn| query.fetch_one_result::<(i64,)>(conn), ))?; }); } /// Property test for aggregate-on-join via DataFusion: ensures equivalence between /// PostgreSQL native aggregation and ParadeDB's DataFusion aggregate backend. /// /// This test randomly combines: /// - 2 or 3 table INNER, LEFT, or FULL joins (equi, non-equi, and mixed conditions) /// - BM25 predicates (@@@ on outer table) /// - GROUP BY with 0-2 grouping columns across all joined tables (including JSONB projections) /// - Regular, statistical, and DISTINCT aggregates: COUNT(*), SUM, AVG, MIN, MAX, STDDEV, /// VARIANCE, COUNT(DISTINCT), SUM(DISTINCT), AVG(DISTINCT) across all joined tables /// - Optional HAVING clauses /// /// Verifies both plan selection (`DataFusion Physical Plan` when at least one join /// step survives PostgreSQL's `remove_useless_joins`, or single-table `ParadeDB Aggregate Scan` /// when all steps collapse and no HAVING / DISTINCT / STDDEV / VARIANCE aggregate declines /// Tantivy) and result equivalence against PostgreSQL's native aggregation. #[rstest] #[case::clean(false)] #[case::churn(true)] #[tokio::test] async fn generated_aggregate_join(database: Db, #[case] churn_on: bool) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); // Three tables for 2-way and 3-way join testing let tables_and_sizes = [("users", 50), ("products", 50), ("orders", 50)]; let all_tables: Vec<&str> = tables_and_sizes.iter().map(|(table, _)| *table).collect(); let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let setup_sql = churn_setup(&pool, setup_sql, &tables_and_sizes, COLUMNS, churn_on); // Text columns for BM25 WHERE clauses let text_columns = columns_named(vec!["name"]); // Columns for join keys let join_key_columns = columns_named(vec!["id", "age"]); // Base column names for GROUP BY (must be columnar fast fields) let base_grouping_cols: Vec<&str> = COLUMNS .iter() .filter(|col| col.is_groupable && col.is_whereable) .filter(|col| col.index_expression.is_none_or(|e| e.indexes_bare_column())) .map(|col| col.name) .collect(); proptest!(qgen_proptest_config(), |( (join_expr, group_by_expr) in (2..=3usize).prop_flat_map(|num_tables| { let tables_for_join = all_tables[..num_tables].to_vec(); let base_grouping_cols = base_grouping_cols.clone(); arb_joins( prop_oneof![Just(JoinType::Inner), Just(JoinType::Left), Just(JoinType::Full)], tables_for_join, &join_key_columns, ) .prop_flat_map(move |join_expr| { let used_tables = join_expr.used_tables(); let mut grouping_columns = Vec::new(); let mut aggregates = vec!["COUNT(*)".to_string()]; for table in &used_tables { for col in &base_grouping_cols { grouping_columns.push(format!("{table}.{col}")); } // Group by text and JSONB representations grouping_columns.push(format!("{table}.metadata->>'brand'")); grouping_columns.push(format!("{table}.metadata->'brand'")); grouping_columns.push(format!("{table}.op_expr_col * 10")); aggregates.extend([ format!("SUM({table}.age)"), format!("AVG({table}.age)"), format!("MIN({table}.quantity)"), format!("MAX({table}.quantity)"), format!("STDDEV({table}.age)"), format!("VARIANCE({table}.age)"), format!("COUNT(DISTINCT {table}.age)"), format!("SUM(DISTINCT {table}.age)"), format!("AVG(DISTINCT {table}.age)"), ]); } ( Just(join_expr), arb_group_by(grouping_columns, aggregates), ) }) }), // Outer table BM25 predicate outer_bm25 in arb_wheres(vec![all_tables[0]], &text_columns), mut gucs in any::(), )| { let join_clause = join_expr.to_sql(); let select_list = group_by_expr.to_select_list(); let group_by_clause = group_by_expr.to_sql(); // Build WHERE clauses let bm25_where = outer_bm25.to_sql("@@@"); let pg_where = outer_bm25.to_sql(" = "); // PostgreSQL native query let pg_query = format!( "SELECT {select_list} {join_clause} WHERE {pg_where} {group_by_clause}" ); // BM25 query with aggregate custom scan enabled let bm25_query = format!( "SELECT {select_list} {join_clause} WHERE {bm25_where} {group_by_clause}" ); // GUCs: enable both join and aggregate custom scans gucs.aggregate_custom_scan = true; gucs.join_custom_scan = true; gucs.custom_scan = true; // Check whether PostgreSQL's `reduce_outer_joins` and `remove_useless_joins` prune // every joined table, reducing the query from a join (`RELOPT_JOINREL`) to a // single-table scan (`RELOPT_BASEREL`) on `users`. let collapses_to_single_table = join_expr.collapses_to_single_table( outer_bm25.forces_nonnullable(), |table| { let prefix = format!("{table}."); select_list.contains(&prefix) || group_by_clause.contains(&prefix) }, ); let expected_plan: Option<&[&str]> = if !collapses_to_single_table { Some(&["DataFusion Physical Plan"]) } else if group_by_expr.having.is_none() && !select_list.contains("DISTINCT") && !select_list.contains("STDDEV(") && !select_list.contains("VARIANCE(") { Some(&["ParadeDB Aggregate Scan"]) } else { None }; if let Some(expected_plan) = expected_plan { qgen_oracle!( "qgen: generated_aggregate_join - ParadeDB aggregate custom scan planned", compare_plan_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, expected_plan, &[], ) )?; } qgen_oracle!("qgen: generated_aggregate_join - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, |query, _, conn| { let rows = query.fetch_dynamic_result(conn)?; let mut string_rows: Vec = rows .into_iter() .map(|row| { let mut row_string = String::new(); for i in 0..row.len() { if i > 0 { row_string.push('|'); } let value_str = if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { format!("{val:.6}") } else if let Ok(val) = row.try_get::(i) { format!("{val:.6}") } else if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { val.to_string() } else if let Ok(val) = row.try_get::(i) { val } else { "NULL".to_string() }; row_string.push_str(&value_str); } row_string }) .collect(); string_rows.sort(); Ok(string_rows) }, ))?; }); } /// /// Property test for numeric pushdown - ensures equivalence between PostgreSQL and BM25 behavior /// for numeric comparison operators (=, <, <=, >, >=, BETWEEN). /// /// Tests both Numeric64 (precision <= 18) and NumericBytes (unlimited precision) storage types. /// #[rstest] #[tokio::test] async fn generated_numeric_pushdown(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let table_name = "users"; // Use more rows to get better coverage of value ranges let setup_sql = generated_queries_setup(&pool, &[(table_name, 100)], COLUMNS); // Numeric columns for testing - includes both Numeric64 and NumericBytes storage types let numeric_columns = columns_named(vec![ "price", // NUMERIC(10,2) - Numeric64 "small_numeric", // NUMERIC(5,2) - Numeric64 "int_numeric", // NUMERIC(10,0) - Numeric64 (integer-like) "high_scale", // NUMERIC(18,6) - Numeric64 with high scale "big_numeric", // NUMERIC - NumericBytes (unlimited precision) "age", // INTEGER - for comparison ]); proptest!(qgen_proptest_config(), |( numeric_expr in arb_numeric_expr(vec![table_name], &numeric_columns), gucs in any::(), )| { // Both queries use the same SQL since numeric comparison operators // are handled identically - the pushdown happens internally in BM25 let where_clause = numeric_expr.to_sql(); // We need a BM25 predicate to trigger the custom scan // Use an OR clause to match all possible name values in the test data let bm25_predicate = format!( "{table_name}.name @@@ pdb.all()" ); // PostgreSQL query: uses only the numeric predicate let pg_query = format!( "SELECT id FROM {table_name} WHERE {where_clause} ORDER BY id" ); // BM25 query: combines BM25 predicate with numeric pushdown let bm25_query = format!( "SELECT id FROM {table_name} WHERE {bm25_predicate} AND {where_clause} ORDER BY id" ); qgen_oracle!("qgen: generated_numeric_pushdown - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, |query, _, conn| { let mut rows = query.fetch_result::<(i64,)>(conn)?; rows.sort(); Ok(rows) }, ))?; }); } /// /// Property test for JoinScan SEMI and ANTI joins. /// /// Fuzzes between: /// - SEMI join via `IN (SELECT ...)` subquery /// - ANTI join via `NOT EXISTS (SELECT ... WHERE correlated)` with `IS NOT NULL` /// /// PostgreSQL only triggers an anti join plan with `NOT EXISTS`, not `NOT IN` /// (due to NULL semantics). An `IS NOT NULL` condition on the join column is /// also required for the anti join optimization. /// /// This complements `generated_joins_small` and verifies both: /// - `paradedb.enable_join_custom_scan = false`: no ParadeDB Join Scan is used /// - `paradedb.enable_join_custom_scan = true`: ParadeDB Join Scan is used #[rstest] #[tokio::test] async fn generated_join_semi_like(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); // Use varied table sizes to test both when the left side is the largest source // and when the right side is the largest source (which now forces the partition // to the left side anyway for SEMI/ANTI correctness). let tables_and_sizes = [ ("users", 500), ("products", 120), ("orders", 40), ("logs", 1000), ]; let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let all_tables = vec!["users", "products", "orders", "logs"]; let join_key_columns = vec!["id", "age", "uuid"]; let search_terms = vec![ "alice", "bob", "cloe", "sally", "brandy", "brisket", "anchovy", ]; proptest!(qgen_proptest_config(), |( semi_join in arb_semi_joins(all_tables.clone(), join_key_columns.clone()), inner_term in proptest::sample::select(search_terms.clone()), is_anti_join in proptest::bool::ANY, limit in 1..=50usize, nested_join in proptest::option::of(arb_semi_joins(all_tables.clone(), join_key_columns.clone())), nested_term in proptest::sample::select(search_terms.clone()), nested_is_anti in proptest::bool::ANY, gucs in any::(), )| { let outer = semi_join.outer_table(); let inner = semi_join.inner_table(); let join_col = semi_join.join_column(); // Skip nested cases where the nested inner table collides with the outer tables, // since that would create a self-join which changes the semantics. let nested = nested_join.as_ref().and_then(|nj| { let nested_inner = nj.inner_table(); if nested_inner != outer && nested_inner != inner && nj.outer_table() != nj.inner_table() { Some((nj, &nested_term, nested_is_anti)) } else { None } }); // Build the subquery clause parameterized by operator. // SEMI: IN (SELECT ...), ANTI: IS NOT NULL AND NOT EXISTS (SELECT 1 ... WHERE correlated) // PostgreSQL only uses an anti join plan with NOT EXISTS (not NOT IN) and // requires IS NOT NULL on the join column. let subquery_clause = |op: &str| { let nested_clause = if let Some((nj, nterm, nis_anti)) = &nested { let mid = inner; let deep = nj.inner_table(); let ncol = nj.join_column(); if *nis_anti { format!( " AND {mid}.{ncol} IS NOT NULL AND NOT EXISTS (\ SELECT 1 FROM {deep} \ WHERE {deep}.{ncol} = {mid}.{ncol} \ AND {deep}.name {op} '{nterm}'\ )" ) } else { format!( " AND {mid}.{ncol} IN (\ SELECT {deep}.{ncol} FROM {deep} \ WHERE {deep}.name {op} '{nterm}'\ )" ) } } else { String::new() }; if is_anti_join { format!( "{outer}.{join_col} IS NOT NULL AND NOT EXISTS (\ SELECT 1 FROM {inner} \ WHERE {inner}.{join_col} = {outer}.{join_col} \ AND {inner}.name {op} '{inner_term}'\ {nested_clause}\ )" ) } else { format!( "{outer}.{join_col} IN (\ SELECT {inner}.{join_col} FROM {inner} \ WHERE {inner}.name {op} '{inner_term}'\ {nested_clause}\ )" ) } }; let pg_where = format!("TRUE AND {}", subquery_clause(" = ")); let bm25_where = format!("{outer}.id @@@ pdb.all() AND {}", subquery_clause("@@@")); let pg_query = format!( "SELECT {outer}.id, {outer}.name \ FROM {outer} \ WHERE {pg_where} \ ORDER BY {outer}.id \ LIMIT {limit}" ); let bm25_query = format!( "SELECT {outer}.id, {outer}.name \ FROM {outer} \ WHERE {bm25_where} \ ORDER BY {outer}.id \ LIMIT {limit}" ); qgen_oracle!("qgen: generated_joinscan_semi_like - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, |query, _, conn| query.fetch_result::<(i64, String)>(conn), ))?; }); } /// /// Property test for numeric precision preservation. /// /// Tests that high-precision numeric values (which would lose precision if converted to f64) /// are correctly matched in BM25 queries. This specifically tests the Numeric64 storage /// type with values that have more than 15-16 significant digits (f64's precision limit). /// /// Example: 123456789012345678 and 123456789012345679 are distinct in NUMERIC(18,0) /// but would be indistinguishable if converted to f64. /// #[rstest] #[tokio::test] async fn generated_numeric_precision(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let table_name = "precision_test"; // Custom setup for precision testing - uses NUMERIC(18,0) which stores as Numeric64 // but with values that exceed f64's precision let precision_columns: &[Column] = &[ Column::new("id", "SERIAL8", "'1'") .primary_key() .groupable(true), Column::new("name", "TEXT", "'test'") .index_expression(IndexExpression::Literal) .random_generator_sql("'test'"), Column::new("big_int", "NUMERIC(18,0)", "'123456789012345678'") .groupable(false) // Generate high-precision values that differ only in lower digits // These values would collide if converted to f64 .random_generator_sql( "(ARRAY [123456789012345678, 123456789012345679, 123456789012345680, 999999999999999998, 999999999999999999]::numeric[])[(floor(random() * 5) + 1)::int]" ), ]; let setup_sql = generated_queries_setup(&pool, &[(table_name, 50)], precision_columns); // High-precision test values that would be indistinguishable in f64 let precision_test_values = vec![ "123456789012345678", "123456789012345679", "123456789012345680", "999999999999999998", "999999999999999999", ]; proptest!(qgen_proptest_config(), |( test_value in proptest::sample::select(precision_test_values), gucs in any::(), )| { // PostgreSQL query - should find exact matches only let pg_query = format!( "SELECT COUNT(*) FROM {table_name} WHERE big_int = {test_value}" ); // BM25 query - should produce identical results // Use 'test' as the name value since all rows have name = 'test' let bm25_query = format!( "SELECT COUNT(*) FROM {table_name} WHERE name === 'test' AND big_int = {test_value}" ); qgen_oracle!("qgen: generated_numeric_precision - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, |query, _, conn| Ok(query.fetch_one_result::<(i64,)>(conn)?.0), ))?; }); } /// /// Property test for numeric range queries with precision preservation. /// /// Tests that range queries (>, <, >=, <=, BETWEEN) on high-precision numeric values /// produce correct results without precision loss. /// #[rstest] #[tokio::test] async fn generated_numeric_range_precision(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let table_name = "range_precision_test"; // Custom setup for range precision testing let precision_columns: &[Column] = &[ Column::new("id", "SERIAL8", "'1'") .primary_key() .groupable(true), Column::new("name", "TEXT", "'test'") .index_expression(IndexExpression::Literal) .random_generator_sql("'test'"), Column::new("big_int", "NUMERIC(18,0)", "'100'") .groupable(false) // Generate sequential high-precision values .random_generator_sql("(floor(random() * 100) + 123456789012345600)::numeric(18,0)"), ]; let setup_sql = generated_queries_setup(&pool, &[(table_name, 100)], precision_columns); // Range boundaries that would collide in f64 let range_bounds = vec![ ("123456789012345650", "123456789012345660"), ("123456789012345670", "123456789012345680"), ("123456789012345690", "123456789012345700"), ]; proptest!(qgen_proptest_config(), |( (low, high) in proptest::sample::select(range_bounds), gucs in any::(), )| { // PostgreSQL query - range filter let pg_query = format!( "SELECT COUNT(*) FROM {table_name} WHERE big_int >= {low} AND big_int < {high}" ); // BM25 query - should produce identical results // Use 'test' as the name value since all rows have name = 'test' let bm25_query = format!( "SELECT COUNT(*) FROM {table_name} WHERE name === 'test' AND big_int >= {low} AND big_int < {high}" ); qgen_oracle!("qgen: generated_numeric_range_precision - ParadeDB result matches PostgreSQL", compare_outcome_retrying( &pg_query, &bm25_query, &gucs, &pool, &setup_sql, |query, _, conn| Ok(query.fetch_one_result::<(i64,)>(conn)?.0), ))?; }); } /// Property test for `pdb.agg()` over joins: the buckets and metrics the DataFusion backend /// assembles must equal the rows of the equivalent SQL `GROUP BY` run by PostgreSQL, which is /// the oracle since `pdb.agg()` itself has no native fallback. Covers nested `terms`, a /// `size` cut under the default count order, NULL buckets, NUMERIC metrics, `cardinality`, /// a SQL `GROUP BY` beside the call, and MPP when the parallel GUCs are on. /// TODO: Consider merging this property test with `generated_aggregate_join` in the future. #[rstest] #[tokio::test] async fn generated_pdb_agg_join(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let tables_and_sizes = [("users", 50), ("products", 50), ("orders", 50)]; let all_tables: Vec = tables_and_sizes .iter() .map(|(table, _)| table.to_string()) .collect(); let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let where_columns = columns_named(vec!["name", "color"]); let join_key_columns = columns_named(vec!["id", "age"]); proptest!(qgen_proptest_config(), |( (join_expr, agg, wheres) in arb_pdb_agg_join(all_tables.clone(), &join_key_columns, &where_columns), mut gucs in any::(), )| { let join_clause = join_expr.to_sql(); let pg_query = agg.pg_query(&join_clause, &wheres.pg_where()); let bm25_query = agg.pdb_query(&join_clause, &wheres.bm25_where()); // `pdb.agg()` over a join only runs on the DataFusion backend. gucs.aggregate_custom_scan = true; gucs.join_custom_scan = true; gucs.custom_scan = true; let pg_outer_query = agg.pg_outer_query(&join_clause, &wheres.pg_where()); if agg.combine_across_groups { qgen_oracle!( "qgen: generated_pdb_agg_join - ParadeDB Aggregate Scan planned for pdb.combine_agg", compare_plan_retrying( &pg_outer_query, &bm25_query, &gucs, &pool, &setup_sql, &["ParadeDB Aggregate Scan"], &["WindowAgg"], ) )?; } if agg.combine_across_groups || !agg.outer_aggs.is_empty() { qgen_oracle!( "qgen: generated_pdb_agg_join - outer aggregates match PostgreSQL", compare_outcome_retrying( &pg_outer_query, &bm25_query, &gucs, &pool, &setup_sql, |query, side, conn| { let rows = query.fetch_dynamic_result(conn)?; let mut outer = agg.outer_rows(rows, side.is_candidate())?; if agg.group_limit.is_none() { outer.sort(); } Ok(outer) }, ) )?; } let baseline_query = if agg.combine_across_groups { &pg_outer_query } else { &pg_query }; qgen_oracle!( "qgen: generated_pdb_agg_join - pdb.agg() buckets match PostgreSQL GROUP BY", compare_outcome_retrying( baseline_query, &bm25_query, &gucs, &pool, &setup_sql, |query, side, conn| { let rows = query.fetch_dynamic_result(conn)?; let mut buckets = if agg.combine_across_groups { if rows.is_empty() { return Ok(Vec::new()); } if side.is_candidate() { agg.window_rows(&rows)? } else { agg.rows(pg_query.as_str().fetch_dynamic_result(conn)?)? } } else { agg.rows(rows)? }; buckets.sort(); Ok(buckets) }, ) )?; }); } /// The same single-table `pdb.agg()` answered by Tantivy and by DataFusion. The documents must /// be equal as they are: bucket order, `sum_other_doc_count`, NULL buckets, and metric values /// alike. A grouped query sorted by an aggregate under a `LIMIT` is routed to DataFusion whatever /// the planner's group estimate, and the limit sits above any group count, so it cuts nothing. #[rstest] #[tokio::test] async fn generated_pdb_agg_single_table(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let setup_sql = generated_queries_setup(&pool, &[("users", 50)], COLUMNS); let text_columns = columns_named(vec!["name"]); proptest!(qgen_proptest_config(), |( outer_bm25 in arb_wheres(vec!["users".to_string()], &text_columns), agg in arb_pdb_agg_single_table(), mut gucs in any::(), )| { let group = agg.outer_group.as_deref().expect("a single-table spec sits beside a GROUP BY"); let where_bm25 = outer_bm25.to_sql("@@@"); let (tantivy_query, datafusion_query) = if agg.combine_across_groups { let tantivy = format!("SELECT {} FROM users WHERE {where_bm25}", agg.call()); let df = format!( "SELECT {group}, COUNT(*), COUNT(*) OVER (), pdb.combine_agg({}) OVER () \ FROM users WHERE {where_bm25} GROUP BY {group} \ ORDER BY COUNT(*) DESC, {group} ASC NULLS LAST LIMIT 10", agg.call() ); (tantivy, df) } else { let tantivy = format!( "SELECT {group}, COUNT(*), {} FROM users WHERE {where_bm25} GROUP BY {group}", agg.call(), ); let df = format!("{tantivy} ORDER BY COUNT(*) DESC LIMIT 1000"); (tantivy, df) }; gucs.aggregate_custom_scan = true; gucs.custom_scan = true; let sides = Sides { baseline: gucs.set(), candidate: gucs.set(), }; // A spec that doesn't lower leaves the candidate on Tantivy too, and the comparison // below would then prove nothing. qgen_oracle!("qgen: generated_pdb_agg_single_table - candidate runs on DataFusion", compare_plan_retrying_on( &sides, &tantivy_query, &datafusion_query, &pool, &setup_sql, &["DataFusion Physical Plan"], &["WindowAgg"], ))?; let group_check_query = format!( "SELECT 1 FROM users WHERE {where_bm25} GROUP BY {group} LIMIT 1" ); qgen_oracle!("qgen: generated_pdb_agg_single_table - both backends answer pdb.agg() alike", compare_outcome_retrying_on( &sides, &tantivy_query, &datafusion_query, &pool, &setup_sql, |query, side, conn| { if agg.combine_across_groups { if !side.is_candidate() { let has_groups = !group_check_query.as_str().fetch_dynamic_result(conn)?.is_empty(); if !has_groups { return Ok(Vec::new()); } let rows = query.fetch_dynamic_result(conn)?; let doc: serde_json::Value = rows[0].try_get(0)?; return Ok(vec![(String::new(), doc)]); } let rows = query.fetch_dynamic_result(conn)?; if rows.is_empty() { return Ok(Vec::new()); } let docs = agg.documents(rows)?; let first = docs[0].1.clone(); for (_, doc) in &docs[1..] { if *doc != first { return Err(sqlx::Error::Protocol( "pdb.combine_agg(pdb.agg()) OVER () rows carry different documents".into(), )); } } Ok(vec![(String::new(), first)]) } else { let mut documents = agg.documents(query.fetch_dynamic_result(conn)?)?; documents.sort_by(|a, b| a.0.cmp(&b.0)); Ok(documents) } }, ))?; }); } #[derive(Clone, Copy, Debug)] enum RankJoinOrderKind { FastField, Score(usize), VectorDistance { query: [u8; 3], table_idx: usize }, } /// Property test for top-K scored, fast-field-ordered, and vector-distance-ordered `INNER` and /// `SEMI` joins comparing `HashJoinExec` (`paradedb.enable_rank_join = off`) against /// `RankJoinExec` (`paradedb.enable_rank_join = on`, `paradedb.rank_join_min_rows = 0`). #[rstest] #[tokio::test] async fn generated_rank_join(database: Db) { let pool = MutexObjectPool::::new( move || block_on(async { database.connection().await }), |_| {}, ); let tables_and_sizes = [("users", 40), ("products", 40), ("orders", 40)]; let all_tables: Vec = tables_and_sizes .iter() .map(|(table, _)| table.to_string()) .collect(); let setup_sql = generated_queries_setup(&pool, &tables_and_sizes, COLUMNS); let where_columns = columns_named(vec!["name", "color"]); let base_where_columns = columns_named(vec!["name", "color", "age", "quantity"]); let join_key_columns = columns_named(vec!["id", "age", "name", "price", "big_numeric", "uuid"]); let join_and_where_strategy = (2..=all_tables.len()).prop_flat_map(move |num_tables| { let joined = all_tables[..num_tables].to_vec(); let table_wheres: Vec<_> = joined .iter() .map(|t| arb_wheres(vec![t.clone()], &where_columns).boxed()) .collect(); let extra_base_where = proptest::option::of(arb_wheres(vec![joined[0].clone()], &base_where_columns)); ( arb_joins(Just(JoinType::Inner), joined.clone(), &join_key_columns), table_wheres, extra_base_where, arb_joinscan_order_parts(joined, false), ) }); let order_kind_strategy = prop_oneof![ 1 => Just(RankJoinOrderKind::FastField), 1 => Just(RankJoinOrderKind::Score(1)), 1 => Just(RankJoinOrderKind::Score(usize::MAX)), 2 => (1..=5u8, 1..=5u8, 1..=5u8, 0..3usize) .prop_map(|(x, y, z, table_idx)| RankJoinOrderKind::VectorDistance { query: [x, y, z], table_idx, }), ]; proptest!(qgen_proptest_config(), |( (join, table_wheres, extra_base_where, order_parts) in join_and_where_strategy, semi_exists in proptest::option::of(proptest::bool::ANY), disjunctive in proptest::bool::ANY, base_where_is_pushdown_filter in proptest::bool::ANY, project_score_and_subquery in proptest::bool::ANY, order_kind in order_kind_strategy, extra_vector_tiebreaker in proptest::bool::ANY, multiply_scores in proptest::bool::ANY, descending in proptest::bool::ANY, limit in 1..=25usize, offset in 0..=10usize, mut gucs in any::(), )| { let dir = if descending { "DESC" } else { "ASC" }; let join_clause = join.to_sql(); let used_tables = join.used_tables(); let first_table = used_tables[0]; let (from_prefix, where_clauses, visible_tables, mut order_parts, use_disjunctive) = if let Some(((outer, inner, join_col), use_exists)) = join.as_equi_pair().zip(semi_exists) { let outer_where = table_wheres[0].to_sql("@@@"); let inner_where = table_wheres[1].to_sql("@@@"); let subq = if use_exists { format!( "EXISTS (SELECT 1 FROM {inner} WHERE {inner}.{join_col} = {outer}.{join_col} AND ({inner_where}))" ) } else { format!( "{outer}.{join_col} IN (SELECT {inner}.{join_col} FROM {inner} WHERE {inner_where})" ) }; ( format!("FROM {outer}"), format!("({outer_where}) AND {subq}"), &used_tables[..1], vec![format!("{outer}.id")], false, ) } else { let where_sep = if disjunctive { " OR " } else { " AND " }; let where_clauses = table_wheres .iter() .map(|w| format!("({})", w.to_sql("@@@"))) .collect::>() .join(where_sep); ( join_clause.clone(), where_clauses, &used_tables[..], order_parts, disjunctive, ) }; let where_bm25 = match &extra_base_where { Some(base_w) => { let base_op = if base_where_is_pushdown_filter { " = " } else { "@@@" }; format!("({}) AND ({where_clauses})", base_w.to_sql(base_op)) } None => where_clauses, }; // Non-equi / keyless steps decline vector distance ORDER BY in JoinScan; fall // back to FastField ordering on keyless join shapes so every vector-ordered // case exercises JoinScan. let effective_order = match order_kind { RankJoinOrderKind::VectorDistance { .. } if join.has_keyless_step() => { RankJoinOrderKind::FastField } other => other, }; let is_vector_order = matches!(effective_order, RankJoinOrderKind::VectorDistance { .. }); let n_scored = match effective_order { RankJoinOrderKind::VectorDistance { .. } => 0, RankJoinOrderKind::FastField if !use_disjunctive => 0, RankJoinOrderKind::FastField => visible_tables.len(), RankJoinOrderKind::Score(n) => { if use_disjunctive { visible_tables.len() } else { n.min(visible_tables.len()) } } }; let (select_table, order_by) = match effective_order { RankJoinOrderKind::VectorDistance { query: [x, y, z], table_idx, } => { let vec_table = visible_tables[table_idx % visible_tables.len()]; let mut parts = vec![format!( "{vec_table}.embedding <=> '[{x},{y},{z}]'::vector ASC" )]; if extra_vector_tiebreaker { parts.push(format!("{vec_table}.age {dir}")); } parts.push(format!("{vec_table}.id {dir}")); (vec_table, parts.join(", ")) } RankJoinOrderKind::FastField | RankJoinOrderKind::Score(_) => { let score_sep = if !use_disjunctive && multiply_scores { " * " } else { " + " }; let lead_order = if n_scored == 0 { format!("{first_table}.quantity {dir}") } else { let combined = visible_tables[..n_scored] .iter() .map(|t| format!("pdb.score({t}.id)")) .collect::>() .join(score_sep); format!("{combined} {dir}") }; order_parts.insert(0, lead_order); (first_table, order_parts.join(", ")) } }; let select_id_expr = if project_score_and_subquery && n_scored > 0 { format!( "({select_table}.id + COALESCE((SELECT MAX(age) FROM {first_table}), 0) + (pdb.score({select_table}.id) * 0.0)::bigint)::bigint" ) } else { format!("{select_table}.id") }; let bm25_query = format!( "SELECT {select_id_expr}, {select_table}.name {from_prefix} \ WHERE {where_bm25} \ ORDER BY {order_by} \ LIMIT {limit} OFFSET {offset}" ); gucs.join_custom_scan = true; gucs.custom_scan = true; if is_vector_order { // Vector distance ORDER BY requires `enable_filter_pushdown` when local scalar/heap // predicates are present so `RankJoinExec` and `HashJoinExec` push identical index queries. gucs.filter_pushdown = true; } let base_gucs = gucs.set(); let sides = Sides { baseline: format!( "{base_gucs}\ SET paradedb.vector_cluster_max_probe TO 1.0;\n\ SET paradedb.enable_rank_join TO off;\n" ), candidate: format!( "{base_gucs}\ SET paradedb.vector_cluster_max_probe TO 1.0;\n\ SET paradedb.enable_rank_join TO on;\n\ SET paradedb.rank_join_min_rows TO 0;\n" ), }; qgen_oracle!( "qgen: generated_rank_join - RankJoinExec matches HashJoinExec", compare_outcome_retrying_on( &sides, &bm25_query, &bm25_query, &pool, &setup_sql, |query, _, conn| query.fetch_result::<(i64, String)>(conn), ) )?; let wheres_eligible = if use_disjunctive { table_wheres .iter() .all(WhereExpr::is_pure_or_indexed_search) } else { table_wheres .iter() .all(WhereExpr::is_positive_indexed_search) }; let base_where_eligible = match &extra_base_where { None => true, Some(base_w) if base_where_is_pushdown_filter => base_w.is_pushdown_filter_shape(), Some(base_w) => !use_disjunctive && base_w.is_positive_indexed_search(), }; if !join.has_keyless_step() && !join_clause.contains(" AND ") && !join_clause.contains(".name =") && !join_clause.contains(".big_numeric =") && base_where_eligible && (n_scored == 0 || descending) && !gucs.parallel_workers && gucs.filter_pushdown && wheres_eligible { let forbidden_candidate: &[&str] = if is_vector_order { &["Sort Key:"] } else { &[] }; qgen_oracle!( "qgen: generated_rank_join - RankJoinExec planned", compare_plan_retrying_on( &sides, &bm25_query, &bm25_query, &pool, &setup_sql, &["RankJoinExec"], forbidden_candidate, ) )?; if is_vector_order { let baseline_plan_sides = Sides { baseline: sides.candidate.clone(), candidate: sides.baseline.clone(), }; qgen_oracle!( "qgen: generated_rank_join - vector HashJoinExec + lazy_sort planned", compare_plan_retrying_on( &baseline_plan_sides, &bm25_query, &bm25_query, &pool, &setup_sql, &["lazy_sort="], &["Sort Key:", "RankJoinExec"], ) )?; } } }); }