Operations on tuples/multiple columns (e.g. row comparison)

[oeed]

I asked on Gitter the other day about queries such as the following, what MySQL coins 'row comparison' (see more here). This query essentially matches rows/tuples in their entirety against the selected IN query columns.

SELECT * FROM table_a
WHERE (table_a.col_1, table_a.col_2) IN (
    SELECT table_b.col_1, table_b.col_2
    FROM table_b
)

I've started hitting up another situation where that would be useful, using multiple columns in COUNT(DISTINCT ...) queries, such as the following, which counts unique combinations of the two columns.

SELECT COUNT(DISTINCT table_a.col_1, table_a.col_2) FROM table_a;

Ideally, I'd like to do the following:

table_1::table.filter((table_a::col_1, table_a::col_2).eq_any(table_b::table.select((table_b.col_1, table_b.col_2))))

table_1::table.select(count_distinct((table_a::col_1, table_a::col_2)))

Most of these functions are only implemented for the SingleValue trait, what I'd like to propose is another trait called, for example, TupleValue that represents multiple columns, i.e. would be implemented for all (A, B, C, ...) where A: SingleValue, B: SingleValue, ..., for all T: SingleValue. This trait would then be used in places where multiple columns are possible.

I am happy to implement such changes if realistic in complexity, breadth and if actually desired. I'm essentially also unsure of how destructive this would be. If it's more appropriate, I can just make such functionality with personal structs/trait in my own code (as I've done with the row comparison case).

[weiznich]

Thanks for writing this proposal. I think you describe two distinct things here:

For COUNT(DISTINCT …) it should be not that hard to modify it to accept tuples as arguments as well. We would need to adjust this implementation:

diesel/diesel/src/expression/count.rs

Lines 99 to 158 in 7e2cd73

	pub fn count_distinct<T, E>(expr: E) -> CountDistinct<T, E::Expression>
	where
	T: SqlType + SingleValue,
	E: AsExpression<T>,
	{
	CountDistinct {
	expr: expr.as_expression(),
	_marker: PhantomData,
	}
	}
	#[derive(Debug, Clone, Copy, QueryId, DieselNumericOps)]
	#[doc(hidden)]
	pub struct CountDistinct<T, E> {
	expr: E,
	_marker: PhantomData<T>,
	}
	impl<T, E> Expression for CountDistinct<T, E>
	where
	T: SqlType + SingleValue,
	E: Expression,
	{
	type SqlType = BigInt;
	}
	impl<T, E, GB> ValidGrouping<GB> for CountDistinct<T, E>
	where
	T: SqlType + SingleValue,
	{
	type IsAggregate = is_aggregate::Yes;
	}
	impl<T, E, QS> SelectableExpression<QS> for CountDistinct<T, E>
	where
	Self: AppearsOnTable<QS>,
	E: SelectableExpression<QS>,
	{
	}
	impl<T, E, QS> AppearsOnTable<QS> for CountDistinct<T, E>
	where
	Self: Expression,
	E: AppearsOnTable<QS>,
	{
	}
	impl<T, E, DB> QueryFragment<DB> for CountDistinct<T, E>
	where
	T: SqlType + SingleValue,
	DB: Backend,
	E: QueryFragment<DB>,
	{
	fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
	out.push_sql("COUNT(DISTINCT ");
	self.expr.walk_ast(out.reborrow())?;
	out.push_sql(")");
	Ok(())
	}
	}

and essentially remove the SingleValue bounds there and see if we sometimes need to replace it with something else. It might be that it should just work.

The general expression part is more complicated as we would need to restrict it to a specific set of methods, although we likely could do something like with the sql type specific expression method extensions like TextExpressionMethods. That means we would have a TupleExpressionMethods trait that only contains those functions that are valid for a tuple and is implemented for various tuple sizes.

Both features would be happily accepted as contribution if they contain a reasonable set of (doc-)tests + documentation. In both cases I cannot really comment on the complexity of the implementation as that would require starting the implementation itself.

[dullbananas]

I really want to be able to use the type dsl::Eq<(a1, b1), (a2, b2)> instead of dsl::And<dsl::Eq<a1, a2>, dsl::Eq<b1, b2>>.

[weiznich]

As written above: We are happy to accept a PR for that. Otherwise its discouraged to add essentially "I want to have this feature too" comments to existing feature requests.

[fenollp]

Here is a lazy workaround for the 2-tuple case

• (A,B) > (1, 2) <=> (A = 1 and B > 2) or A > 1
• proof: https://dbfiddle.uk/qdHc6Xj0

[dullbananas]

Directly comparing tuples sometimes results in better use of indexes in the query plan.

[Bigbigcheese]

Sorry to necro a seemingly dead thread but all roads seem to lead here on this topic.

I wish to do something like this:
SELECT v FROM t WHERE (a, b) IN ((a1, b1), (a2, b2), (an, bn));`

Has anything come of this topic in terms of added functionality, or is there another way of doing this?

[oeed]

I made some private extensions to support this. I should PR them at some stage but I just don't have a ton of time to properly polish them. This is the code I've used for them, someone's welcome to take it and PR based from it (i.e. consider all this MIT).

There are two versions, I can't recall why, perhaps I wrote one for MySQL and another for Postgres. I'm not actually using row_tuples_eq_any anywhere, don't recall why. Worth noting DB in the code specifically refers to Pg.

The usage examples I've found are a little odd, I think the only reason ComparisonRows was needed was because properly implementing it on tuples would require it be done in diesel itself.

pub fn with_ids(
    ids: Vec<(DynamicCreativeVersionID, MediaOwnerID)>,
  ) -> dynamic_creative_version_review_schema::BoxedQuery<'static, DB> {
    Self::query().filter(
      (
        dynamic_creative_version_review_schema::version_id,
        dynamic_creative_version_review_schema::media_owner_id,
      )
        .row_eq_any(ComparisonRows::new(ids)),
    )
  }

(
  schema::external_id,
  schema::media_owner_id,
)
  .row_eq_any(
    hidden_unmapped_site_schema::table
      .select((
        hidden_unmapped_site_schema::external_id,
        hidden_unmapped_site_schema::media_owner_id,
      ))
      .filter(hidden_unmapped_site_schema::source.eq(source)),
  )

Implementations

use diesel::{
  backend::{sql_dialect, Backend, SqlDialect},
  expression::{
    array_comparison::{AsInExpression, InExpression},
    ValidGrouping,
  },
  prelude::*,
  query_builder::{AstPass, QueryFragment, QueryId},
  sql_types::{Bool, SqlType},
};

/// TODO: look as PR'ing this https://github.com/diesel-rs/diesel/discussions/3723
pub trait TupleRowComparisonExpressionMethod: Expression + Sized {
  /// A custom expression method to allow row comparison queries.
  ///
  /// For example:
  /// ```sql
  /// SELECT * FROM unmapped_site_creative
  /// WHERE (external_id, media_owner_id) NOT IN (
  ///   SELECT external_id, media_owner_id
  ///   FROM hidden_unmapped_site
  /// );
  /// ```
  ///
  /// See: https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/subquery-restrictions.html
  fn row_tuples_eq_any<V>(self, values: V) -> RowComparison<Self, V::InExpression>
  where
    Self::SqlType: SqlType,
    V: AsInExpression<Self::SqlType>,
  {
    RowComparison {
      left: self,
      values: values.as_in_expression(),
    }
  }
}

impl<T: Expression> TupleRowComparisonExpressionMethod for T {}

pub struct RowComparison<T, U> {
  left: T,
  values: U,
}

impl<T, U> Expression for RowComparison<T, U>
where
  T: Expression,
  U: Expression<SqlType = T::SqlType>,
{
  type SqlType = Bool;
}

impl<T, U, DB> QueryFragment<DB> for RowComparison<T, U>
where
  DB: Backend,
  Self: QueryFragment<DB, DB::ArrayComparison>,
{
  fn walk_ast<'b>(&'b self, pass: AstPass<'_, 'b, DB>) -> QueryResult<()> {
    <Self as QueryFragment<DB, DB::ArrayComparison>>::walk_ast(self, pass)
  }
}

impl<T, U, GB> ValidGrouping<GB> for RowComparison<T, U>
where
  T: Expression,
  U: Expression<SqlType = T::SqlType> + ValidGrouping<GB>,
{
  type IsAggregate = <U as ValidGrouping<GB>>::IsAggregate;
}

impl<T, U, QS> SelectableExpression<QS> for RowComparison<T, U>
where
  T: Expression,
  RowComparison<T, U>: AppearsOnTable<QS>,
  U: Expression<SqlType = T::SqlType> + SelectableExpression<QS>,
{
}

impl<T, U, QS> AppearsOnTable<QS> for RowComparison<T, U>
where
  T: Expression,
  RowComparison<T, U>: Expression,
  U: Expression<SqlType = T::SqlType> + SelectableExpression<QS>,
{
}

impl<T, U, DB> QueryFragment<DB, sql_dialect::array_comparison::AnsiSqlArrayComparison> for RowComparison<T, U>
where
  DB: Backend + SqlDialect<ArrayComparison = sql_dialect::array_comparison::AnsiSqlArrayComparison>,
  T: QueryFragment<DB>,
  U: QueryFragment<DB> + InExpression,
{
  fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
    if self.values.is_empty() {
      out.push_sql("1=0");
    }
    else {
      out.push_sql("(");
      self.left.walk_ast(out.reborrow())?;
      out.push_sql(") IN (");
      self.values.walk_ast(out.reborrow())?;
      out.push_sql(")");
    }
    Ok(())
  }
}

impl<T, U> QueryId for RowComparison<T, U> {
  type QueryId = ();

  const HAS_STATIC_QUERY_ID: bool = false;
}

use std::marker::PhantomData;

use diesel::{
  expression::{
    array_comparison::{AsInExpression, InExpression},
    AsExpression, TypedExpressionType, ValidGrouping,
  },
  prelude::*,
  query_builder::{AstPass, QueryFragment, QueryId},
  sql_types::{Bool, SingleValue, SqlType},
};

use crate::connection::DB;

// this could probably be done in a nicer way. it was originally written for MySQL,
// but postgres has slightly more natural support for such a query
/// TODO: look as PR'ing this https://github.com/diesel-rs/diesel/discussions/3723
pub trait RowComparisonExpressionMethod: Expression + Sized {
  /// A custom expression method to allow row comparison queries.
  ///
  /// For example:
  /// ```sql
  /// SELECT * FROM unmapped_site_creative
  /// WHERE (external_id, media_owner_id) NOT IN (
  ///   SELECT external_id, media_owner_id
  ///   FROM hidden_unmapped_site
  /// );
  /// ```
  ///
  /// See: https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/subquery-restrictions.html
  fn row_eq_any<V>(self, values: V) -> RowComparison<Self, V::InExpression>
  where
    Self::SqlType: SqlType,
    V: AsInExpression<Self::SqlType>,
  {
    RowComparison {
      left: self,
      values: values.as_in_expression(),
    }
  }
}

impl<T: Expression> RowComparisonExpressionMethod for T {}

pub struct RowComparison<T, U> {
  left: T,
  values: U,
}

impl<T, U> Expression for RowComparison<T, U>
where
  T: Expression,
  U: Expression<SqlType = T::SqlType>,
{
  type SqlType = Bool;
}


impl<T, U, GB> ValidGrouping<GB> for RowComparison<T, U>
where
  T: Expression,
  U: Expression<SqlType = T::SqlType> + ValidGrouping<GB>,
{
  type IsAggregate = <U as ValidGrouping<GB>>::IsAggregate;
}

impl<T, U, QS> SelectableExpression<QS> for RowComparison<T, U>
where
  T: Expression,
  RowComparison<T, U>: AppearsOnTable<QS>,
  U: Expression<SqlType = T::SqlType> + SelectableExpression<QS>,
{
}

impl<T, U, QS> AppearsOnTable<QS> for RowComparison<T, U>
where
  T: Expression,
  RowComparison<T, U>: Expression,
  U: Expression<SqlType = T::SqlType> + SelectableExpression<QS>,
{
}

impl<T, U> QueryFragment<DB> for RowComparison<T, U>
where
  T: QueryFragment<DB>,
  U: QueryFragment<DB> + InExpression,
{
  fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
    if self.values.is_empty() {
      out.push_sql("1=0");
    }
    else {
      out.push_sql("(");
      self.left.walk_ast(out.reborrow())?;
      out.push_sql(") IN (");
      self.values.walk_ast(out.reborrow())?;
      out.push_sql(")");
    }
    Ok(())
  }
}

impl<T, U> QueryId for RowComparison<T, U> {
  type QueryId = ();

  const HAS_STATIC_QUERY_ID: bool = false;
}


pub struct ComparisonRows<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  inner: Vec<T::Expression>,
  _phantom: PhantomData<ST>,
}


impl<T, ST> ComparisonRows<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  pub fn new(rows: Vec<T>) -> Self {
    ComparisonRows {
      inner: rows.into_iter().map(|row| row.as_row_expression()).collect(),
      _phantom: PhantomData,
    }
  }
}

impl<T, ST> InExpression for ComparisonRows<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  type SqlType = ST;

  fn is_empty(&self) -> bool {
    self.inner.is_empty()
  }

  fn is_array(&self) -> bool {
    ST::IS_ARRAY
  }
}


impl<T, ST> AsInExpression<ST> for ComparisonRows<T, ST>
where
  T: AsRowExpression<ST>,
  ST: SqlType + TypedExpressionType,
{
  type InExpression = Self;

  fn as_in_expression(self) -> Self::InExpression {
    self
  }
}


impl<T, ST, GB> ValidGrouping<GB> for ComparisonRows<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
  T::Expression: ValidGrouping<GB>,
{
  type IsAggregate = <T::Expression as ValidGrouping<GB>>::IsAggregate;
}

impl<T, ST> Expression for ComparisonRows<T, ST>
where
  ST: TypedExpressionType + SqlType,
  T: AsRowExpression<ST>,
{
  type SqlType = ST;
}

impl<T, ST, QS> SelectableExpression<QS> for ComparisonRows<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
  ComparisonRows<T, ST>: AppearsOnTable<QS>,
  T::Expression: Expression<SqlType = ST> + SelectableExpression<QS>,
{
}

impl<T, ST, QS> AppearsOnTable<QS> for ComparisonRows<T, ST>
where
  ST: TypedExpressionType + SqlType,
  T: AsRowExpression<ST>,
  T::Expression: AppearsOnTable<QS>,
{
}


impl<T, ST> QueryFragment<DB> for ComparisonRows<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
    out.unsafe_to_cache_prepared();
    let mut first = true;
    for value in &self.inner {
      if first {
        first = false;
      }
      else {
        out.push_sql(", ");
      }
      out.push_sql("(");
      value.walk_ast(out.reborrow())?;
      out.push_sql(")");
    }
    Ok(())
  }
}

impl<T, ST> QueryId for ComparisonRows<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  type QueryId = ();

  const HAS_STATIC_QUERY_ID: bool = false;
}


pub trait AsRowExpression<ST>
where
  ST: SqlType,
{
  type Expression: QueryFragment<DB>;

  fn as_row_expression(self) -> Self::Expression;
}

impl<T0, T1, ST0, ST1> AsRowExpression<(ST0, ST1)> for (T0, T1)
where
  T0: AsExpression<ST0>,
  T1: AsExpression<ST1>,
  T0::Expression: QueryFragment<DB>,
  T1::Expression: QueryFragment<DB>,
  ST0: SqlType + SingleValue,
  ST1: SqlType + SingleValue,
  (ST0, ST1): SqlType,
{
  type Expression = (T0::Expression, T1::Expression);

  fn as_row_expression(self) -> Self::Expression {
    (self.0.as_expression(), self.1.as_expression())
  }
}

#[doc(hidden)]
pub struct ComparisonRowExpression<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  inner: T::Expression,
  _phantom: PhantomData<ST>,
}

impl<T, ST> ComparisonRowExpression<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  pub fn new(row: T) -> Self {
    ComparisonRowExpression {
      inner: row.as_row_expression(),
      _phantom: PhantomData,
    }
  }
}

impl<T, ST, GB> ValidGrouping<GB> for ComparisonRowExpression<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
  T::Expression: ValidGrouping<GB>,
{
  type IsAggregate = <T::Expression as ValidGrouping<GB>>::IsAggregate;
}

impl<T, ST, QS> SelectableExpression<QS> for ComparisonRowExpression<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
  ComparisonRowExpression<T, ST>: AppearsOnTable<QS>,
  T::Expression: Expression<SqlType = ST> + SelectableExpression<QS>,
{
}

impl<T, ST, QS> AppearsOnTable<QS> for ComparisonRowExpression<T, ST>
where
  ST: TypedExpressionType + SqlType,
  T: AsRowExpression<ST>,
  T::Expression: AppearsOnTable<QS>,
{
}

impl<T, ST> Expression for ComparisonRowExpression<T, ST>
where
  ST: TypedExpressionType + SqlType,
  T: AsRowExpression<ST>,
{
  type SqlType = ST;
}

impl<T, ST> QueryFragment<DB> for ComparisonRowExpression<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
  T::Expression: QueryFragment<DB>,
{
  fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
    out.push_sql("(");
    self.inner.walk_ast(out.reborrow())?;
    out.push_sql(")");

    Ok(())
  }
}

impl<T, ST> QueryId for ComparisonRowExpression<T, ST>
where
  ST: SqlType,
  T: AsRowExpression<ST>,
{
  type QueryId = ();

  const HAS_STATIC_QUERY_ID: bool = false;
}