drizzle_core/expr/
null.rs

1//! NULL handling: [`coalesce`], [`ifnull`], [`nullif`], [`greatest`], [`least`].
2//!
3//! All of these need arguments with compatible SQL types. Their results track
4//! nullability: [`coalesce`] is non-null as soon as one argument is non-null,
5//! while [`nullif`] is always nullable.
6
7use crate::sql::{SQL, Token};
8use crate::traits::SQLParam;
9use crate::types::Compatible;
10use crate::{MySQLDialect, PostgresDialect};
11
12use super::{AggregateKind, Expr, ExprSources, Null, Nullability, SQLExpr};
13use crate::scope::{Arg, Coalesce};
14
15/// Sources of a NULL-absorbing pair (`COALESCE(a, b)`): NULL only when both are.
16type FallbackSources<'a, V, A, B> = Coalesce<
17    Arg<<A as Expr<'a, V>>::Nullable, <A as ExprSources>::Sources>,
18    Arg<<B as Expr<'a, V>>::Nullable, <B as ExprSources>::Sources>,
19>;
20
21// =============================================================================
22// COALESCE Function
23// =============================================================================
24
25/// The first non-NULL of two values (`COALESCE(expr, default)`).
26///
27/// Both arguments must have compatible SQL types; the result has `expr`'s
28/// type. The result is non-null if either argument is non-null, so a nullable
29/// column with a non-null default becomes non-null. It is an aggregate if
30/// either argument is.
31///
32/// # Examples
33///
34/// ```rust
35/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
36/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
37/// # #[derive(Clone, Debug)] struct Value(String);
38/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
39/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
40/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
41/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
42/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
43/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
44/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
45/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
46/// // `email` is nullable; the result is not.
47/// let email = coalesce(users.email, "unknown");
48/// assert_eq!(email.sql(), r#"COALESCE ("users"."email", ?)"#);
49/// ```
50///
51/// # Type safety
52///
53/// The default must have a compatible type:
54///
55/// ```rust,compile_fail
56/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
57/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
58/// # #[derive(Clone, Debug)] struct Value(String);
59/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
60/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
61/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
62/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
63/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
64/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
65/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
66/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
67/// let wrong = coalesce(users.score, "none");
68/// ```
69#[allow(clippy::type_complexity)]
70pub fn coalesce<'a, V, E, D>(
71    expr: E,
72    default: D,
73) -> SQLExpr<
74    'a,
75    V,
76    E::SQLType,
77    <E::Nullable as Nullability>::And<D::Nullable>,
78    <E::Aggregate as AggregateKind>::Or<D::Aggregate>,
79    FallbackSources<'a, V, E, D>,
80>
81where
82    V: SQLParam + 'a,
83    E: Expr<'a, V>,
84    D: Expr<'a, V>,
85    E::SQLType: Compatible<D::SQLType>,
86    D::Nullable: Nullability,
87    D::Aggregate: AggregateKind,
88{
89    SQLExpr::new(SQL::func(
90        "COALESCE",
91        expr.into_expr_sql()
92            .push(Token::COMMA)
93            .append(default.into_expr_sql()),
94    ))
95}
96
97/// The first non-NULL of several values (`COALESCE(first, rest...)`).
98///
99/// `first` is separate so the list is never empty. `rest` is any iterator;
100/// its elements share one Rust type, and their SQL type must be compatible
101/// with `first`'s. The result has `first`'s type and is non-null if `first`
102/// or the `rest` element type is non-null (even when `rest` turns out to be
103/// empty).
104///
105/// # Examples
106///
107/// ```rust
108/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
109/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
110/// # #[derive(Clone, Debug)] struct Value(String);
111/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
112/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
113/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
114/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
115/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
116/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
117/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
118/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
119/// let email = coalesce_many(users.email, ["unknown", "n/a"]);
120/// assert_eq!(email.sql(), r#"COALESCE ("users"."email", ?, ?)"#);
121/// ```
122#[allow(clippy::type_complexity)]
123pub fn coalesce_many<'a, V, E, I>(
124    first: E,
125    rest: I,
126) -> SQLExpr<
127    'a,
128    V,
129    E::SQLType,
130    <E::Nullable as Nullability>::And<<I::Item as Expr<'a, V>>::Nullable>,
131    <E::Aggregate as AggregateKind>::Or<<I::Item as Expr<'a, V>>::Aggregate>,
132    FallbackSources<'a, V, E, I::Item>,
133>
134where
135    V: SQLParam + 'a,
136    E: Expr<'a, V>,
137    I: IntoIterator,
138    I::Item: Expr<'a, V>,
139    E::SQLType: Compatible<<I::Item as Expr<'a, V>>::SQLType>,
140    <I::Item as Expr<'a, V>>::Nullable: Nullability,
141    <I::Item as Expr<'a, V>>::Aggregate: AggregateKind,
142{
143    let mut sql = first.into_expr_sql();
144    for value in rest {
145        sql = sql.push(Token::COMMA).append(value.into_expr_sql());
146    }
147    SQLExpr::new(SQL::func("COALESCE", sql))
148}
149
150// =============================================================================
151// NULLIF Function
152// =============================================================================
153
154/// NULL when two values are equal, otherwise the first (`NULLIF(a, b)`).
155///
156/// Both arguments must have compatible SQL types. The result has the first
157/// argument's type and is always nullable.
158///
159/// # Examples
160///
161/// ```rust
162/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
163/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
164/// # #[derive(Clone, Debug)] struct Value(String);
165/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
166/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
167/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
168/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
169/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
170/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
171/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
172/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
173/// // Treat empty names as missing.
174/// let name = nullif(users.name, "");
175/// assert_eq!(name.sql(), r#"NULLIF ("users"."name", ?)"#);
176/// ```
177#[allow(clippy::type_complexity)]
178pub fn nullif<'a, V, E1, E2>(
179    expr1: E1,
180    expr2: E2,
181) -> SQLExpr<
182    'a,
183    V,
184    E1::SQLType,
185    Null,
186    <E1::Aggregate as AggregateKind>::Or<E2::Aggregate>,
187    (E1::Sources, E2::Sources),
188>
189where
190    V: SQLParam + 'a,
191    E1: Expr<'a, V>,
192    E2: Expr<'a, V>,
193    E1::SQLType: Compatible<E2::SQLType>,
194    E2::Aggregate: AggregateKind,
195{
196    SQLExpr::new(SQL::func(
197        "NULLIF",
198        expr1
199            .into_expr_sql()
200            .push(Token::COMMA)
201            .append(expr2.into_expr_sql()),
202    ))
203}
204
205// =============================================================================
206// IFNULL / NVL Function
207// =============================================================================
208
209/// The first value, or `default` when it is NULL (`IFNULL(expr, default)`).
210///
211/// Same typing as [`coalesce`]. `IFNULL` exists on SQLite and MySQL but not on
212/// PostgreSQL. This function is not restricted by dialect, so on PostgreSQL
213/// it compiles but the database rejects it; use [`coalesce`] there.
214///
215/// # Examples
216///
217/// ```rust
218/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
219/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
220/// # #[derive(Clone, Debug)] struct Value(String);
221/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
222/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
223/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
224/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
225/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
226/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
227/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
228/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
229/// let email = ifnull(users.email, "unknown");
230/// assert_eq!(email.sql(), r#"IFNULL ("users"."email", ?)"#);
231/// ```
232#[allow(clippy::type_complexity)]
233pub fn ifnull<'a, V, E, D>(
234    expr: E,
235    default: D,
236) -> SQLExpr<
237    'a,
238    V,
239    E::SQLType,
240    <E::Nullable as Nullability>::And<D::Nullable>,
241    <E::Aggregate as AggregateKind>::Or<D::Aggregate>,
242    FallbackSources<'a, V, E, D>,
243>
244where
245    V: SQLParam + 'a,
246    E: Expr<'a, V>,
247    D: Expr<'a, V>,
248    E::SQLType: Compatible<D::SQLType>,
249    D::Nullable: Nullability,
250    D::Aggregate: AggregateKind,
251{
252    SQLExpr::new(SQL::func(
253        "IFNULL",
254        expr.into_expr_sql()
255            .push(Token::COMMA)
256            .append(default.into_expr_sql()),
257    ))
258}
259
260// =============================================================================
261// GREATEST / LEAST
262// =============================================================================
263
264/// How `GREATEST` and `LEAST` treat NULL on a dialect.
265///
266/// PostgreSQL ignores NULL arguments, while MySQL returns NULL when either
267/// argument is NULL. SQLite has no such functions, so it does not implement
268/// this trait.
269#[diagnostic::on_unimplemented(
270    message = "GREATEST/LEAST are not available for this dialect",
271    label = "use a dialect-specific extrema expression"
272)]
273pub trait GreatestLeastPolicy<L: Nullability, R: Nullability> {
274    /// Nullability of the result.
275    type Nullable: Nullability;
276    /// How the operands' sources (each an [`Arg`]) combine.
277    type Sources<A, B>;
278}
279
280impl<L, R> GreatestLeastPolicy<L, R> for PostgresDialect
281where
282    L: Nullability,
283    R: Nullability,
284{
285    type Nullable = <L as Nullability>::And<R>;
286    type Sources<A, B> = Coalesce<A, B>;
287}
288
289impl<L, R> GreatestLeastPolicy<L, R> for MySQLDialect
290where
291    L: Nullability,
292    R: Nullability,
293{
294    type Nullable = <L as Nullability>::Or<R>;
295    type Sources<A, B> = (A, B);
296}
297
298/// The larger of two values (`GREATEST(left, right)`), on PostgreSQL and MySQL.
299///
300/// Both arguments must have compatible SQL types; the result has `left`'s
301/// type. On PostgreSQL, NULL arguments are ignored, so the result is NULL only
302/// when both are. On MySQL, the result is NULL when either argument is. SQLite
303/// has no `GREATEST`, so this does not compile for SQLite.
304///
305/// # Examples
306///
307/// ```rust
308/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
309/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
310/// # #[derive(Clone, Debug)] struct Value(String);
311/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
312/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
313/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
314/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
315/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
316/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
317/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
318/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
319/// let n = greatest(users.age, 18);
320/// assert_eq!(n.sql(), r#"GREATEST ("users"."age", $1)"#);
321/// ```
322#[allow(clippy::type_complexity)]
323pub fn greatest<'a, V, L, R>(
324    left: L,
325    right: R,
326) -> SQLExpr<
327    'a,
328    V,
329    L::SQLType,
330    <V::DialectMarker as GreatestLeastPolicy<L::Nullable, R::Nullable>>::Nullable,
331    <L::Aggregate as AggregateKind>::Or<R::Aggregate>,
332    <V::DialectMarker as GreatestLeastPolicy<L::Nullable, R::Nullable>>::Sources<
333        Arg<L::Nullable, L::Sources>,
334        Arg<R::Nullable, R::Sources>,
335    >,
336>
337where
338    V: SQLParam + 'a,
339    V::DialectMarker: GreatestLeastPolicy<L::Nullable, R::Nullable>,
340    L: Expr<'a, V>,
341    R: Expr<'a, V>,
342    L::SQLType: Compatible<R::SQLType>,
343    R::Nullable: Nullability,
344    R::Aggregate: AggregateKind,
345{
346    SQLExpr::new(SQL::func(
347        "GREATEST",
348        left.into_expr_sql()
349            .push(Token::COMMA)
350            .append(right.into_expr_sql()),
351    ))
352}
353
354/// The smaller of two values (`LEAST(left, right)`), on PostgreSQL and MySQL.
355///
356/// Both arguments must have compatible SQL types; the result has `left`'s
357/// type. On PostgreSQL, NULL arguments are ignored, so the result is NULL only
358/// when both are. On MySQL, the result is NULL when either argument is. SQLite
359/// has no `LEAST`, so this does not compile for SQLite.
360///
361/// # Examples
362///
363/// ```rust
364/// # use drizzle_core::dialect::{Dialect, DialectTypes, PostgresDialect as D};
365/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
366/// # #[derive(Clone, Debug)] struct Value(String);
367/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::PostgreSQL; type DialectMarker = D; }
368/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
369/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
370/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
371/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
372/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
373/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
374/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
375/// let n = least(users.age, 18);
376/// assert_eq!(n.sql(), r#"LEAST ("users"."age", $1)"#);
377/// ```
378#[allow(clippy::type_complexity)]
379pub fn least<'a, V, L, R>(
380    left: L,
381    right: R,
382) -> SQLExpr<
383    'a,
384    V,
385    L::SQLType,
386    <V::DialectMarker as GreatestLeastPolicy<L::Nullable, R::Nullable>>::Nullable,
387    <L::Aggregate as AggregateKind>::Or<R::Aggregate>,
388    <V::DialectMarker as GreatestLeastPolicy<L::Nullable, R::Nullable>>::Sources<
389        Arg<L::Nullable, L::Sources>,
390        Arg<R::Nullable, R::Sources>,
391    >,
392>
393where
394    V: SQLParam + 'a,
395    V::DialectMarker: GreatestLeastPolicy<L::Nullable, R::Nullable>,
396    L: Expr<'a, V>,
397    R: Expr<'a, V>,
398    L::SQLType: Compatible<R::SQLType>,
399    R::Nullable: Nullability,
400    R::Aggregate: AggregateKind,
401{
402    SQLExpr::new(SQL::func(
403        "LEAST",
404        left.into_expr_sql()
405            .push(Token::COMMA)
406            .append(right.into_expr_sql()),
407    ))
408}
drizzle_core/expr/null.rs

drizzle_core/expr/
null.rs
