Diesel allows invalid SQL with subquery that uses ungrouped column from outer query #4755
Description
Found this out while writing a query that uses a subquery. It's invalid SQL but diesel compiles OK and fails at runtime.
Versions
- Rust: rustc 1.89.0 (29483883e 2025-08-04)
- Diesel:
diesel = { version = "2.1", features = ["postgres"] } - Database: Postgresql 17
- Operating System MacOS Sequoia 15.4.1
Feature Flags
- diesel:
postgres
Problem Description
Diesel compiles an invalid SQL query.
What is the expected output?
Compile error.
What is the actual output?
thread 'main' panicked at src/main.rs:63:26:
called `Result::unwrap()` on an `Err` value: DatabaseError(Unknown, "subquery uses ungrouped column \"parent.id\" from outer query")
Steps to reproduce
use diesel::prelude::*;
diesel::table! {
parents (id) {
id -> Integer,
}
}
diesel::table! {
childs (id) {
id -> Integer,
parent_id -> Integer,
amount -> Integer,
}
}
diesel::joinable!(childs -> parents (parent_id));
diesel::allow_tables_to_appear_in_same_query!(parents, childs);
fn create_tables(conn: &mut PgConnection) -> QueryResult<()> {
use diesel::dsl::sql_query;
sql_query(
r#"
CREATE TABLE IF NOT EXISTS parents (
id SERIAL PRIMARY KEY
)
"#,
)
.execute(conn)?;
sql_query(
r#"
CREATE TABLE IF NOT EXISTS childs (
id SERIAL PRIMARY KEY,
parent_id INTEGER REFERENCES parents(id),
amount INTEGER
)
"#,
)
.execute(conn)?;
Ok(())
}
fn bug(conn: &mut PgConnection) -> QueryResult<(i64, i64)> {
parents::table
.select((
diesel::dsl::count_distinct(parents::id),
childs::table
.filter(childs::parent_id.eq(parents::id))
.select(diesel::dsl::sum(childs::amount).assume_not_null())
.single_value()
.assume_not_null(),
))
.first(conn)
}
fn main() {
let mut conn =
PgConnection::establish("postgresql://user:password@localhost:5432/diesel_test").unwrap();
create_tables(&mut conn).unwrap();
bug(&mut conn).unwrap();
}
Checklist
- I have already looked over the issue tracker and the discussion forum for similar possible closed issues.
- This issue can be reproduced on Rust's stable channel. (Your issue will be
closed if this is not the case) - This issue can be reproduced without requiring a third party crate