[requesting assistance] Can the same parameter be used in multiple places in the same prepared statement?

[margaretselzer]

In an SQLite database I am using a prepared statement to look up a record in a table storing component data. Each component has two names, a schematicName and a name (which can be null), When I search the table I need to find records that match the name or schematicName. So far this could be easily done with something like this:

    return sqlpp::select(
         all_of(components)
         .from(components)
         .where(
             (components.name.is_not_null() and (components.name == sqlpp::parameter(components.name))
                 or (components.schematicName == sqlpp::parameter(components.schematicName)))
         );

But I also need to know what the match was made on, the name or the schematicName. To this I would ideally use a calculated column with an alias, e.g.:

    auto matchCalc_exp =
        case_when(components.name == sqlpp::parameter(components.name))
            .then(0)
            .else_(case_when(components.schematicName
                                    == sqlpp::parameter(components.schematicName))
                       .then(1)
                       .else_(3))
            .as(matchQualifier);

In an ideal world I could use this alias matchQualifier in the where clause instead of doing the equality checks again. If it worked, it could translate into the following SQL:

SELECT tblComponents.*, 
      (CASE WHEN (tblComponents.name=?1) THEN 1 
            ELSE (CASE WHEN (tblComponents.schematicName=?2) THEN 0 
                    ELSE 3 
                  END) 
       END) AS matchQualifier 
FROM tblComponents 
WHERE (matchQualifier != 3)

But I just could not find any way to make this work.

1. I could not find any way to use an aliased column in the where clause.
2. Directly using the expression in the where clause makes duplicating the parameters which results in a compile error. (Even if it worked, the resulting query would not be too efficient either.)
3. Ideally, I would like to use just one parameter, a searchName instead of needing to assign the same string value to both myQuery.params.name and myQuery.params.schematicName, but this results in duplication as well, so does not compile.
4. Tried to use sqlpp::verbatim() in the where clause to add matchQualifier != 3 but could not make it work. All it gave me was a static_assert: Invalid operand(s)
5. I tried to make a temporary table alias to the query with the extra matchQualifier column which actually compiled, but then the parameters of the expression are not visible in the prepared statement and I am back to square one. Besides, with this approach I would run the select twice on the table (SELECT * FROM (SELECT *, CASE WHEN ... AS matchQualifier) WHERE ...) WHERE matchQualifier != 3) which is not too healthy.

Update
I found how I can use the expression in the WHERE clause. Removing the .as(matchQualifier) part does the trick. Then the expression can be directly compared with another sqlpp value, e.g.: .where(matchCalc_exp != sqlpp::value(3)).

However, I now realize that my initial query design is less than optimal, To be able to utilize indexes on the name fields I have to have the names in the where clause. In this case if I wanted to have my calculated matchQualifier field I would need to use the same expression there, but it would then require me to use different parameter aliases.

So all my above questions really boil down to:
Can the same parameter be used in multiple places in the same prepared statement?

[rbock]

Admittedly, I was not aware that your query is legal SQL and I am a bit confused by the update.

That said, there is support for sub-queries. Here is an example from test code:

  const auto subQuery = select(tab.alpha).from(tab).unconditionally().as(sub);
  for (const auto& row : db(select(subQuery.alpha).from(subQuery).unconditionally()))
  { 
    std::cerr << row.alpha;
  }

In subQuery, replace tab.alpha with matchCalc_exp and you're almost there (again, not sure about the update).

Hope this helps?

Cheers,
Roland

[margaretselzer]

Thanks for the quick reply, I really appreciate it. Unfortunately it is not a sub-query I am after - and even a sub-query would not solve the problem. So let me illustrate the issue with concrete code.

SQLPP_ALIAS_PROVIDER(nameToMatch)
SQLPP_ALIAS_PROVIDER(matchQualifier)

auto components = TblComponents{};

auto matchCalc_exp =
    case_when(components.name == sqlpp::parameter(sqlpp::text(), nameToMatch))
        .then(0)
        .else_(case_when(components.schematicName == sqlpp::parameter(sqlpp::text(), nameToMatch))
                   .then(1)
                   .else_(sqlpp::value(3)));

 auto q = sqlpp::select(
    all_of(components), matchCalc_exp.as(matchQualifier))
    .from(components)
    .where(matchCalc_exp != sqlpp:::value(3));

sqlpp::sqlite3::connection db( /*config here*/ );

auto stmnt = db.prepare(q);

stmnt.parameters.nameToMatch = "mycomponent";

const auto& selectedRow = db(stmnt).front();

Now the above code will not compile. It will not compile because the nameToMatch alias is used 4 times so it will result in a error: duplicate base type ‘nameToMatch_t::_alias_t::_member_t<sqlpp::parameter_value_t<sqlpp::text> >’ invalid error. If I however duplicate the expression (i.e. matchCalc_exp1 and matchCalc_exp2 ) and use 4 different aliases in those two expressions, then it will compile.

Just to reflect on the sub-query suggestion as well - I tried it. I made first a select query including my matchQualifier field and aliased it to be a table. Then I queried that aliased table. (Let us disregard that it is a very inefficient query.) In that structure the first problem is that the parameters used in matchCalc_exp are not visible in the resulting statement (or I could not find a way to access the sub-query parameters.) But the other problem of duplicate base type is also there, that I have to use redundant statement parameters (for the two name fields) even if they all have the same value.

Right now my working solution is using 4 parameters for the same actual argument:

SQLPP_ALIAS_PROVIDER(nameToMatch)
SQLPP_ALIAS_PROVIDER(schematicNameToMatch)
SQLPP_ALIAS_PROVIDER(matchQualifier)

auto matchCalc_exp =
    case_when(components.name == sqlpp::parameter(sqlpp::text(), nameToMatch))
        .then(0)
        .else_(case_when(components.schematicName
                                == sqlpp::parameter(sqlpp::text(), schematicNameToMatch))
                   .then(1)
                   .else_(sqlpp::value(3))
        .as(matchQualifier);

auto q = sqlpp::select(
    all_of(components), matchCalc_exp)
    .from(components)
    .where(
        ((components.name.is_not_null() and (components.name == sqlpp::parameter(components.name)))
        or
        (components.schematicName == sqlpp::parameter(components.schematicName)))
    );

sqlpp::sqlite3::connection db( /*config here*/ );   

auto stmnt = db.prepare(q);

stmnt.params.nameToMatch = "XYZ component";
stmnt.params.schematicNameToMatch = "XYZ component";
stmnt.params.name = "XYZ component";
stmnt.params.schematicName = "XYZ component";

const auto& selectedRow = db(stmnt).front();

Note: I am not using the same expression in the where clause because I do not think that SQLite is clever enough to figure out that it should use the indexes on the name fields if they are used within this cascaded case-when structure.

So is there a way to make this work somehow with only one parameter instead of needing 4? Related question;: is there a way to access parameters in a sub-query?

Cheers,
Marcell

[rbock]

Thanks for the detailed explanation. If that query is working for you, I think you could reduce the number of parameters:

1. Given the condition in the WHERE expression, matchCalc_exp would always have the same value, wouldn't it? You don't need the expression there.
2. You could get more creative with the WHERE expression:

.where(sqlpp::parameter(sqlpp::text(), nameToMatch)
                   .in(components.name, components.schematicName)

If both ideas work for you, then you're down to using the parameter once.

WDYT?

Roland

[margaretselzer]

What a great idea using this "reversed" in in the where clause! Typical "why didn't I think of it" moment! :-) Thank you!

This reduces the number of parameters required by one. However, 'matchCalc_exp' does have different values based on whether the match is on the 'name' or the 'schematicName' field. (Yes, together with the where clause it will never get the value of the embedded else_ branch but it still can have two distinct values.) So if I want to keep all the possible results of the matchCalc_exp expression I still need three different parameters having the same value. If this expression is only ever used together with this where clause I could get rid of the embedded case_when and that would take one more parameter out, but it is still two left having the same value.

From your response (or rather, from what you did not respond to) I suspect that
A) it is not possible to use only one parameter in multiple locations in the same query.
B) It is not possible to access the parameters of a sub-query either.

Am I correct?

Marcell

[rbock]

Ah, right, the case-statement can still have two different values...

As for your "suspicions" from my missing answers (sorry for that), yeah, that is correct. You question "B" triggered another idea, though. How about something like this:

SQLPP_ALIAS_PROVIDER(subTable);
// ...
const auto sub = select(sqlpp::parameter(sqlpp::text(), nameToMatch).as(nameToMatch)).as(subTable);
const auto matchCalc_exp =
    case_when(components.name == sub.nameToMatch)
        .then(0)
        .else_(case_when(components.schematicName
                                == sub.nameToMatch)
                   .then(1)
                   .else_(sqlpp::value(3))
        .as(matchQualifier);
const auto q = sqlpp::select(
    all_of(components), matchCalc_exp)
    .from(components.cross_join(sub))
    .where(
        ((components.name.is_not_null() and (components.name == sub.nameToMatch))
        or
        (components.schematicName == sub.nameToMatch))
    );

This would require a small change in the library:

diff --git a/include/sqlpp11/parameter.h b/include/sqlpp11/parameter.h
index 20f27a9..89db1bb 100644
--- a/include/sqlpp11/parameter.h
+++ b/include/sqlpp11/parameter.h
@@ -54,6 +54,12 @@ namespace sqlpp
     parameter_t& operator=(const parameter_t&) = default;
     parameter_t& operator=(parameter_t&&) = default;
     ~parameter_t() = default;
+
+    template <typename alias_provider>
+    expression_alias_t<parameter_t, alias_provider> as(const alias_provider& /*unused*/) const
+    {
+      return {*this};
+    }
   };

If this works for you, I would make a change to the library allowing

const auto sub = select(sqlpp::parameter(sqlpp::text(), nameToMatch)).as(subTable);

I guess that change will be as short, but I need to think about how to do this :-)

WDYT?

Cheers,
Roland

[margaretselzer]

Hi Roland,

Do I understand correctly that what your suggestion does is that it basically converts a parameter to a table having one single field with one single row which then is practically concatenated with all rows of the original table? If yes, then absolutely, this could really work as long as the resulting query does not add lots of overhead to the query (it probably does not).

Again, if I understand this correctly, at the heart of this solution is the newly added possibility to alias a parameter. But if that is becoming possible, why cannot we use an alias in the query directly? In general it is a bit limiting that while aliases can be defined and translate into a nice SQL statement, they cannot be referenced afterwards anywhere else in the query, only as a result field. If this could be allowed that would really make a good solution because that way all parameters then could be treated as fields of the queried table. (I just hope that this is not insanely complex or impossible to implement. :))

Cheers,
Marcell

[rbock]

Hi Marcell,

As for the first part: Yes, that is my proposal. I will make no predictions regarding performance. You need to test this yourself :-)
If that works for you, I would look into the change for paramter_t.

As for the second part, the answer is simple: Sub-queries are implemented.

Repeated use of a parameter or referencing an alias in the way you want is not. I have not thought about this in detail, but I suspect this will be non-trivial (for instance, the serialization of the aliased expression needs to be different depending on the context, SELECT vs. WHERE). I am also not sure if all database backends support those features.

That said, both features are certainly interesting to consider.

Best,
Roland

[margaretselzer]

Hi Roland,

Yes, even if it is a bit of a workaround (for my particular problem), it is probably worth implementing this change to parameter_t. It might make life easier for others as well. Also, it feels like a step closer to those other features I "dream" about. :)

Please excuse my ignorant question, but when serializing the parameter, isn't it only a parameter name that is required to be serialized? When I think about a query like SELECT t.name || '_suffix' AS paramname FROM t WHERE paramname = 'text' I do not see the difference in how paramname appears. Or does the serialization of an alias always include the AS keyword as well? I thought that AS somehow comes from calling the as() method of the expression in question. (Admittedly, I do not quite grasp - yet? - how the template magic in the library works.)

And another question that is potentially related to the above discussion. I am trying to use a parameter (which might also contain an sqlpp::null) in the is_equal_to_or_null() function in a WHERE clause but I get a compilation error. Am I trying to do something that is not supported or I just do not know how to do it?

In SQLite at least it is possible to use the IS keyword for equality comparison of both null and non-null values so a query like SELECT * FROM t WHERE t.name IS ?1 works. Then in sqlpp I would expect select(all_of(t)).from(t).where(is_equal_to_or_null(t.name, parameter(nullableParam)) also work. Am I supposed to use instead the long version of

select(all_of(t))
    .from(t)
    .where((sqlpp::parameter(t.name).is_not_null() and (t.name == sqlpp::parameter(t.nullableParam)))
                    or (sqlpp::parameter(t.nullableParam).is_null() and t.name.is_null())):

Of course I realize now that in the long version I am proposing above there is again the problem of parameter duplication so the change to parameter_t comes handy again.

Thanks,
Marcell

Update
I tried to apply your suggested solution to my above problem. i.e.

+
+    template <typename alias_provider>
+    expression_alias_t<parameter_t, alias_provider> as(const alias_provider& /*unused*/) const
+    {
+      return {*this};
+    }

The problem seems to be what I mentioned in my first post, that the parameter in subTable is not accessible, only the parameters that appear in the primary query.

[margaretselzer]

I made an attempt to hack my way through with is_equal_to_or_null by splitting it into two. One for a value as rhs and one for a parameter as rhs. (I am almost sure that this two could be fused into one by using SFINAE, but that is probably a next step if it ever works.)

#pragma once

/*
 * Copyright (c) 2021, Roland Bock
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without modification,
 * are permitted provided that the following conditions are met:
 *
 *   Redistributions of source code must retain the above copyright notice, this
 *   list of conditions and the following disclaimer.
 *
 *   Redistributions in binary form must reproduce the above copyright notice, this
 *   list of conditions and the following disclaimer in the documentation and/or
 *   other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR
 * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

#include <sqlpp11/value_or_null.h>

namespace sqlpp
{
  template <typename Expr, typename ValueType>
  struct is_equal_to_or_null_value_t: public expression_operators<is_equal_to_or_null_value_t<Expr, ValueType>, boolean>,
                     public alias_operators<is_equal_to_or_null_value_t<Expr, ValueType>>
  {
    using _traits = make_traits<boolean, tag::is_expression>;
    using _nodes = detail::type_vector<Expr, value_or_null_t<ValueType>>;

    is_equal_to_or_null_value_t(Expr expr, value_or_null_t<ValueType> value) : _expr(expr), _value(value)
    {
    }

    is_equal_to_or_null_value_t(const is_equal_to_or_null_value_t&) = default;
    is_equal_to_or_null_value_t(is_equal_to_or_null_value_t&&) = default;
    is_equal_to_or_null_value_t& operator=(const is_equal_to_or_null_value_t&) = default;
    is_equal_to_or_null_value_t& operator=(is_equal_to_or_null_value_t&&) = default;
    ~is_equal_to_or_null_value_t() = default;


    Expr _expr;
    value_or_null_t<ValueType> _value;
  };

  template <typename Context, typename Expr, typename ValueType>
  Context& serialize(const is_equal_to_or_null_value_t<Expr, ValueType>& t, Context& context)
  {
    if (t._value._is_null)
      serialize(t._expr.is_null(), context);
    else
      serialize(t._expr == t._value, context);

    return context;
  }

  template <typename Expr, typename ValueType>
  auto is_equal_to_or_null_value(Expr expr, value_or_null_t<ValueType> value) -> is_equal_to_or_null_value_t<Expr, ValueType>
  {
    static_assert(is_expression_t<Expr>::value,
                  "is_equal_to_or_null_value() is to be called with an expression (e.g. a column) and a value_or_null expression");
    static_assert(std::is_same<value_type_of<Expr>, ValueType>::value,
                  "is_equal_to_or_null_value() arguments need to have the same value type");
    return {expr, value};
  }


  template <typename Expr, typename Param>
  struct is_equal_to_or_null_param_t: public expression_operators<is_equal_to_or_null_param_t<Expr, Param>, boolean>,
                     public alias_operators<is_equal_to_or_null_param_t<Expr, Param>>
  {
    using _traits = make_traits<boolean, tag::is_expression>;
    using _nodes = detail::type_vector<Expr, Param>;

    is_equal_to_or_null_param_t(Expr expr, Param param) : _expr(expr), _param(param)
    {
    }

    is_equal_to_or_null_param_t(const is_equal_to_or_null_param_t&) = default;
    is_equal_to_or_null_param_t(is_equal_to_or_null_param_t&&) = default;
    is_equal_to_or_null_param_t& operator=(const is_equal_to_or_null_param_t&) = default;
    is_equal_to_or_null_param_t& operator=(is_equal_to_or_null_param_t&&) = default;
    ~is_equal_to_or_null_param_t() = default;


    Expr _expr;
    Param _param;
  };

  template <typename Context, typename Expr, typename Param>
  Context& serialize(const is_equal_to_or_null_param_t<Expr, Param>& t, Context& context)
  {
    context << "((";
    serialize(t._expr.is_null(), context);
    context << " AND ";
    serialize(t._param.is_null(), context);
    context << ") OR (";
    serialize(t._expr.is_not_null(), context);
    context << " AND ";
    serialize(t._expr == t._param, context);
    context << "))";

    /* -- An SQLite solution ---
    context << "(";
    serialize_operand(t._expr, context);
    context << " IS ";
    serialize_operand(t._param, context);
    context << ")";
    */ 

    return context;
  }

  template <typename Expr, typename Param>
  auto is_equal_to_or_null_param(Expr expr, Param param) -> is_equal_to_or_null_param_t<Expr, Param>
  {
    static_assert(is_expression_t<Expr>::value && is_parameter_t<Param>::value,
                  "is_equal_to_or_null_param() is to be called with an expression (e.g. a column) and a parameter");
    static_assert(std::is_same<value_type_of<Expr>, value_type_of<Param>>::value,
                  "is_equal_to_or_null_param() arguments need to have the same value type");
    return {expr, param};
  }

}  // namespace sqlpp

This compiles beautifully and produces a nice prepared statement, the only problem being is that it introduces two placeholders - ?1, ?2 - (i.e. for two distinct parameters) in the prepared query instead of only one. As a result, the second parameter is never bound to any value when running the query so it fails with non-null parameter values.

I added a commented out part that does work with SQLite, but I guess sqlpp11 being a generic tool this will not fly with every database, In any case I am sharing this as it might give you some idea that could lead to a functional generic solution. Actually I really hope so. :)

Cheers,
Marcell

[rbock]

Please excuse my ignorant question, but when serializing the parameter, isn't it only a parameter name that is required to be serialized? When I think about a query like SELECT t.name || '_suffix' AS paramname FROM t WHERE paramname = 'text' I do not see the difference in how paramname appears. Or does the serialization of an alias always include the AS keyword as well? I thought that AS somehow comes from calling the as() method of the expression in question. (Admittedly, I do not quite grasp - yet? - how the template magic in the library works.)

To express this query, you would say

const auto param = (t.name || '_suffix').as(paramname);
auto q = select(param).from(t).where(param == "text");

In order to reproduce your query you would have to serialize param in two different ways.
Or did you want to write something like this?

auto q = select((t.name || '_suffix').as(paramname)).from(t).where(paramname == "text");

That does not work since paramname is a class representing a name, The expression paramname == "text" cannot be checked for validity.

Regarding is_equal_to_or_null: Thanks for sharing your experiement. That seems non-trivial. We should tackle it once support for std::optional is implemented.

All that said: It seems to me that this thread is covering a lot of questions and requests. I am certainly losing track. I suggest to close this one and I would ask to open a new thread for a new topic, if needed beyond the answers above.

Just to summarize the answer for the orginal question: No, you cannot use the same parameter in multiple places in the same prepared statement.

[margaretselzer]

Hi Roland,

Thank you for your help and insights.

As a last post in this thread let me share an improved version of is_equal_to_or_null that accepts a parameter if the database used is SQLite. I believe that std::optional (by itself) is not going to solve the problem of multiple occurrences of the same parameter in a query so I see this update being useful regardless. If you deem it worthy I can submit a PR with it.

#include <sqlpp11/value_or_null.h>
#include <sqlpp11/sqlite3/connection.h>

namespace sqlpp
{
  namespace detail
  {
    template <class... Ts>
    struct always_false : std::false_type
    {
    };
  }  // namespace detail

  template <typename Expr, typename ValueType = value_type_of<Expr>>
  struct is_value_or_null_expression_t : std::false_type
  {
  };

  template <typename ValueType>
  struct is_value_or_null_expression_t<value_or_null_t<ValueType>, value_type_of<value_or_null_t<ValueType>>>
      : std::true_type
  {
  };

  template <typename Expr, typename ValueOrParam, typename Enable = void>
  struct is_equal_to_or_null_t
  {
    static_assert(detail::always_false<Expr>::value,
                  "is_equal_to_or_null_t cannot be instantiated with the provided arguments.");
  };

  template <typename Expr, typename ValueOrParam>
  struct is_equal_to_or_null_t<
      Expr,
      ValueOrParam,
      std::enable_if_t<is_expression_t<Expr>::value &&
                       (is_value_or_null_expression_t<ValueOrParam>::value || is_parameter_t<ValueOrParam>::value)>>
      : public expression_operators<is_equal_to_or_null_t<Expr, ValueOrParam>, boolean>,
        public alias_operators<is_equal_to_or_null_t<Expr, ValueOrParam>>
  {
    using _traits = make_traits<boolean, tag::is_expression>;
    using _nodes = detail::type_vector<Expr, ValueOrParam>;

    is_equal_to_or_null_t(Expr expr, ValueOrParam _valueOrParam) : _expr(expr), _valueOrParam(_valueOrParam)
    {
    }

    is_equal_to_or_null_t(const is_equal_to_or_null_t&) = default;
    is_equal_to_or_null_t(is_equal_to_or_null_t&&) = default;
    is_equal_to_or_null_t& operator=(const is_equal_to_or_null_t&) = default;
    is_equal_to_or_null_t& operator=(is_equal_to_or_null_t&&) = default;
    ~is_equal_to_or_null_t() = default;

    Expr _expr;
    ValueOrParam _valueOrParam;
  };

  template <typename Context, typename Expr, typename ValueOrParam>
  Context& serialize(const is_equal_to_or_null_t<Expr, ValueOrParam>& t, Context& context)
  {
    constexpr bool compare_value = is_value_or_null_expression_t<ValueOrParam>::value;
    constexpr bool use_is_for_equality =
        is_parameter_t<ValueOrParam>::value && std::is_same_v<Context, sqlpp::sqlite3::context_t>;

    static_assert(
        compare_value || use_is_for_equality,
        "Serializing is_equal_to_or_null() is not supported with the given arguments and the used database type.");

    if constexpr (compare_value)
    {
      if (t._valueOrParam._is_null)
        serialize(t._expr.is_null(), context);
      else
        serialize(t._expr == t._valueOrParam, context);
    }
    else if constexpr (use_is_for_equality)
    {
      context << "(";
      serialize_operand(t._expr, context);
      context << " IS ";
      serialize_operand(t._valueOrParam, context);
      context << ")";
    }

    return context;
  }

  template <typename Expr, typename ValueOrParam>
  auto is_equal_to_or_null(Expr expr, ValueOrParam valueOrParam) -> is_equal_to_or_null_t<Expr, ValueOrParam>
  {
    static_assert(
        is_expression_t<Expr>::value &&
            (is_value_or_null_expression_t<ValueOrParam, value_type_of<ValueOrParam>>::value ||
             is_parameter_t<ValueOrParam>::value),
        "is_equal_to_or_null() is to be called with an expression (e.g. a column) as the lhs and a value_or_null or a "
        "parameter as the rhs argument.");
    static_assert(std::is_same_v<value_type_of<Expr>, value_type_of<ValueOrParam>>,
                  "is_equal_to_or_null() arguments need to have the same value type");

    return {expr, valueOrParam};
  }
}  // namespace sqlpp

I realize that adding is_value_or_null_expression_t as a new trait to value_or_null_t is probably more in line with the sqlpp11 library architecture, but as I do not know what side-effects that might have I opted to restrict the footprint of this change as much as possible.

Cheers,
Marcell

[rbock]

Thanks for sharing! I might get back to you on that.

Agreed that support for optional will not solve the issue with two parameters. But I expect the code to be more concise, making it easier to think about options.

I'll leave this open for now while I am moving forward with the optional branch.

[rbock]

Just for completeness:

• postgresql has IS DISTINCT FROM and IS NOT DISTINCT FROM (this is SQL standard)
• sqlite3 has IS and IS NOT which are equivalent to IS NOT DISTINCT FROM and IS DISTINCT FROM
• mysql has <=> which is equivalent to IS NOT DISTINCT FROM

I'll add this to the optional branch...

[rbock]

This (IS_DISTINCT_FROM and friends) is now implemented in https://github.com/rbock/sqlpp23.