Filter SQL query with null constant does not work

vikter · December 27, 2025, 1:42pm

The following filter that uses data constant Null on the right side:

generates the following SQL query:
SELECT
[BusinessPartner].[Id] AS [Id],
[BusinessPartner].[Name] AS [Name],
[BusinessPartner].[FirstName] AS [FirstName]
FROM
[BusinessPartner] AS [BusinessPartner]
WHERE (
([BusinessPartner].[UserEmail] <> NULL));

But the condition “.[UserEmail] <> NULL” does not work with the SQL Server and should be “IS NOT NULL”.

When leaving the right side of the NotEqual function empty, then it works as it generates the condition with “IS NOT NULL”:

SELECT
[BusinessPartner].[Id] AS [Id],
[BusinessPartner].[Name] AS [Name],
[BusinessPartner].[FirstName] AS [FirstName]
FROM
[BusinessPartner] AS [BusinessPartner]
WHERE (
([BusinessPartner].[UserEmail] IS NOT NULL));

washi · December 29, 2025, 8:54am

We’re not using a constant NULL to render such filters in SQL. You already figured it out by leaving the right side empty - it is also describide here.

I’m not considering this as a bug.

tvavrda · December 29, 2025, 10:43am

According to this line of code I would actually expect it to work. I think I added it exactly because it is intuitive and some people could do it. Which is exactly what just happened.

github.com/origam/origam

backend/Origam.DA.Service/Generators/AbstractFilterRenderer.cs

537deec3a


      
              {
                  return string.Format("{0} IS NOT NULL", leftValue);
              }
          
              return string.Format("{0} <> {1}", leftValue, rightValue);
          }
          
          public string Equal(string leftValue, string rightValue)
          {
              CheckArgumentEmpty("leftValue", leftValue);
              if (rightValue == null || rightValue.ToLower() == "null")
              {
                  return string.Format("{0} IS NULL", leftValue);
              }
          
              return string.Format("{0} = {1}", leftValue, rightValue);
          }
          
          public string Not(string argument)
          {
              CheckArgumentEmpty("argument", argument);

And now I see why it maybe does not work, as the rightValue passed is the constant’s value, so in case of null constant it’s probably not "null”.

Which could be easily tested by creating a new string constant with value “null". @vikter can you please try?