Invalid SQL generation using query interface with replaceSearch

Feature(s) impacted

Search when using replaceSearch.

Observed behavior

Take users with a one-to-many relationship to companies.
This code fails:

    users.replaceSearch((searchString, extendedMode, context) => {
      return context.generateSearchFilter(searchString, {
        extended: extendedMode,
        onlyFields: [
          'Id',
          'FirstName',
          'LastName',
          'Email',
        ],
      })
    })

The error is Ambiguous column name 'Id'.
Indeed, here is the generated SQL:

SELECT [User].[Id],
       [User].[FirstName],
       [User].[LastName],
       [User].[Email],
       -- snip other User fields
       [Company].[Name] AS [Company.Name],
       [Company].[Id]   AS [Company.Id]
FROM [Users] AS [User]
         LEFT OUTER JOIN [Companies] AS [Company] ON [User].[CompanyId] = [Company].[Id]
WHERE ((LOWER([FirstName]) LIKE N'%search_string%' OR
        LOWER([LastName]) LIKE N'%search_string%' OR
        LOWER([Id]) LIKE N'%search_string%' OR -- Uh-oh.
        LOWER([Email]) LIKE N'%search_string%'))
ORDER BY [User].[Id] ASC
OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY;

The offending line is in the WHERE clause, in which you can see we’re hitting an ambiguous Id. MSSQL has no way to know whether we’re looking up a company Id or a user Id.

Expected behavior

The generated SQL should properly qualify fields looked up in the WHERE clause.

Failure Logs

===== An exception was raised =====
GET /forest/Users/count?{
 -- snip
}

 Ambiguous column name 'Id'.

Error
    at Query.run -- snip...
===================================

Context

  • Agent technology: nodejs
  • Agent (forest package) name & version: 1.40.1 with sequelize 6.37.3 and sequelize-typescript 2.1.6
  • Database type: MSSQL
  • Recent changes made on your end if any: Migrated from old agent.

Hi @kll ,

Thank you for the feedback, I’ve reproduced and created a bug ticket. The fix will be live in the following days.

Kind regards

2 Likes