use super::fixtures::{plan_select_sql, try_plan_select_sql};
use crate::error::SqlError;
use crate::types::*;
const ARTICLES_ORDERS: &str = "FROM articles a JOIN orders o ON a.id = o.article_id";
fn debug(plan: &SqlPlan) -> String {
format!("{plan:?}")
}
fn column(table: &str, name: &str) -> String {
format!("Column {{ table: Some({table:?}), name: {name:?} }}")
}
fn bare_column(name: &str) -> String {
format!("Column {{ table: None, name: {name:?} }}")
}
fn assert_ambiguous(sql: &str, name: &str) {
match try_plan_select_sql(sql) {
Err(SqlError::AmbiguousColumn { column }) => assert_eq!(column, name, "{sql}"),
other => panic!("{sql}: expected AmbiguousColumn({name}), got {other:?}"),
}
}
#[test]
fn projected_columns_take_their_owners_qualifier() {
let plan = plan_select_sql(&format!("SELECT title, amount {ARTICLES_ORDERS}"));
let SqlPlan::Join { projection, .. } = plan else {
panic!("expected a join plan");
};
let names: Vec<String> = projection
.iter()
.map(|p| match p {
Projection::Column(name) => name.clone(),
other => panic!("expected a column projection, got {other:?}"),
})
.collect();
assert_eq!(names, vec!["a.title".to_string(), "o.amount".to_string()]);
}
#[test]
fn computed_projection_reads_qualified_columns() {
let plan = plan_select_sql(&format!(
"SELECT views * 2 AS twice, amount + 1 AS more {ARTICLES_ORDERS}"
));
let text = debug(&plan);
assert!(text.contains(&column("a", "views")), "{text}");
assert!(text.contains(&column("o", "amount")), "{text}");
}
#[test]
fn where_reads_qualified_columns() {
let plan = plan_select_sql(&format!(
"SELECT a.id {ARTICLES_ORDERS} WHERE amount > 5 AND views IS NULL"
));
let text = debug(&plan);
assert!(text.contains(&column("o", "amount")), "{text}");
assert!(text.contains(&column("a", "views")), "{text}");
assert!(!text.contains(&bare_column("amount")), "{text}");
}
#[test]
fn on_equi_keys_take_their_owners_qualifier() {
let plan = plan_select_sql("SELECT a.id FROM articles a JOIN orders o ON article_id = id");
let SqlPlan::Join { on, .. } = plan else {
panic!("expected a join plan");
};
assert_eq!(on, vec![("a.id".to_string(), "o.article_id".to_string())]);
}
#[test]
fn on_residual_reads_qualified_columns() {
let plan = plan_select_sql(
"SELECT a.id FROM articles a JOIN orders o ON a.id = o.article_id AND views < qty",
);
let SqlPlan::Join { condition, .. } = plan else {
panic!("expected a join plan");
};
let text = format!("{condition:?}");
assert!(text.contains(&column("a", "views")), "{text}");
assert!(text.contains(&column("o", "qty")), "{text}");
}
#[test]
fn group_keys_and_aggregate_arguments_take_their_owners_qualifier() {
let plan = plan_select_sql(&format!(
"SELECT title, SUM(amount), COUNT(*) FILTER (WHERE qty > 1) {ARTICLES_ORDERS} GROUP BY title"
));
let SqlPlan::Aggregate {
group_by,
aggregates,
..
} = plan
else {
panic!("expected an aggregate plan");
};
assert_eq!(
format!("{:?}", group_by[0]),
column("a", "title"),
"group key"
);
let text = format!("{aggregates:?}");
assert!(text.contains(&column("o", "amount")), "{text}");
assert!(text.contains(&column("o", "qty")), "{text}");
}
#[test]
fn self_aliased_group_key_takes_its_owners_qualifier() {
let plan = plan_select_sql(&format!(
"SELECT title AS title, COUNT(*) {ARTICLES_ORDERS} GROUP BY title"
));
let SqlPlan::Aggregate { group_by, .. } = plan else {
panic!("expected an aggregate plan");
};
assert_eq!(format!("{:?}", group_by[0]), column("a", "title"));
}
#[test]
fn having_reads_group_columns_by_bare_name() {
for having in ["title <> 'x'", "a.title <> 'x'"] {
let plan = plan_select_sql(&format!(
"SELECT title, SUM(amount) {ARTICLES_ORDERS} GROUP BY title HAVING {having}"
));
let SqlPlan::Aggregate { having, .. } = plan else {
panic!("expected an aggregate plan");
};
let text = format!("{having:?}");
assert!(text.contains(&bare_column("title")), "{text}");
assert!(!text.contains(&column("a", "title")), "{text}");
}
}
#[test]
fn having_aggregate_argument_takes_its_owners_qualifier() {
let plan = plan_select_sql(&format!(
"SELECT title {ARTICLES_ORDERS} GROUP BY title HAVING SUM(amount) > 10"
));
let SqlPlan::Aggregate { aggregates, .. } = plan else {
panic!("expected an aggregate plan");
};
let text = format!("{aggregates:?}");
assert!(text.contains(&column("o", "amount")), "{text}");
}
#[test]
fn order_by_over_a_join_sorts_by_the_qualified_column() {
let plan = plan_select_sql(&format!(
"SELECT title, amount AS paid {ARTICLES_ORDERS} ORDER BY amount, paid"
));
let SqlPlan::Subquery { sort_keys, .. } = plan else {
panic!("expected a post-processing tail, got {plan:?}");
};
assert_eq!(format!("{:?}", sort_keys[0].expr), column("o", "amount"));
assert_eq!(
format!("{:?}", sort_keys[1].expr),
bare_column("paid"),
"an output alias stays bare"
);
}
#[test]
fn order_by_over_a_join_aggregate_sorts_by_the_bare_group_column() {
let plan = plan_select_sql(&format!(
"SELECT title, SUM(amount) {ARTICLES_ORDERS} GROUP BY title ORDER BY a.title"
));
let SqlPlan::Aggregate { sort_keys, .. } = plan else {
panic!("expected an aggregate plan, got {plan:?}");
};
assert_eq!(format!("{:?}", sort_keys[0].expr), bare_column("title"));
}
#[test]
fn ambiguous_column_is_refused_in_every_clause() {
let two_open = "FROM orders o JOIN users u ON o.user_id = u.id";
assert_ambiguous(&format!("SELECT name {two_open}"), "name");
assert_ambiguous(&format!("SELECT o.id {two_open} WHERE name = 'x'"), "name");
assert_ambiguous(&format!("SELECT COUNT(*) {two_open} GROUP BY name"), "name");
assert_ambiguous(&format!("SELECT SUM(total) {two_open}"), "total");
assert_ambiguous(&format!("SELECT o.id {two_open} ORDER BY name"), "name");
assert_ambiguous(
"SELECT o.id FROM orders o JOIN users u ON o.user_id = u.id AND name = 'x'",
"name",
);
assert_ambiguous(
"SELECT o.id FROM orders o JOIN users u ON user_id = u.id",
"user_id",
);
}
#[test]
fn column_declared_by_both_relations_is_refused() {
assert_ambiguous(
"SELECT title FROM articles a JOIN articles b ON a.id = b.id",
"title",
);
}
#[test]
fn qualified_column_over_two_schemaless_relations_plans() {
let plan = plan_select_sql("SELECT u.name FROM orders o JOIN users u ON o.user_id = u.id");
let SqlPlan::Join { projection, .. } = plan else {
panic!("expected a join plan");
};
assert!(
matches!(&projection[0], Projection::Column(name) if name == "u.name"),
"{projection:?}"
);
}
#[test]
fn using_over_two_schemaless_relations_plans() {
let plan = plan_select_sql("SELECT o.id FROM orders o JOIN users u USING (tenant)");
let SqlPlan::Join { on, .. } = plan else {
panic!("expected a join plan");
};
assert_eq!(on, vec![("tenant".to_string(), "tenant".to_string())]);
}
#[test]
fn cross_join_plans_with_no_keys() {
let plan = plan_select_sql("SELECT a.title, o.amount FROM articles a CROSS JOIN orders o");
let SqlPlan::Join {
join_type,
on,
condition,
..
} = plan
else {
panic!("expected a join plan");
};
assert_eq!(join_type, JoinType::Cross);
assert!(on.is_empty());
assert!(condition.is_none());
}