Postgres $any with an array gets a sytax error

[joelanman]

If I construct a WHERE clause with $any and an array, I get an error.

var selectedAnimals = ['cow','pig'];

where.title = {
    $iLike: { $any: selectedAnimals}
};

generates this SQL:

SELECT "name" FROM "Animals" AS "Animal" WHERE "Animal"."type" ILIKE ANY ARRAY['cow','pig']::VARCHAR;

and this error:

syntax error at or near "ARRAY"

From Postgres syntax, it seems like ANY should have brackets, like this:

SELECT "name" FROM "Animals" AS "Animal" WHERE "Animal"."type" ILIKE ANY (ARRAY['cow','pig'])

[janmeier]

Hmm, from the unit tests, it looks like we should be generating with brackets. Have you tried latest master

sequelize/test/unit/sql/where.test.js

Line 394 in bebad6e

suite('$any', function() {

[corbanb]

+1 I am getting this in 3.3.2

SELECT "id", "protocol", "password_encrypted", "provider", "identifier", "friends", "tokens", "accessToken", "createdAt", "updatedAt", "UserId" FROM "Passports" AS "Passport" WHERE "Passport"."identifier" = ANY (ARRAY['333333333333']::VARCHAR) AND "Passport"."provider" = 'facebook';

Removing ::VARCHAR allows this query to work also.

[corbanb]

@mickhansen @janmeier I updated to the bleeding edge. same bug in master.

[vhmth]

Reproducable.

error looking up relevant campaigns: { [SequelizeDatabaseError: syntax error at or near "ARRAY"]
  name: 'SequelizeDatabaseError',
  message: 'syntax error at or near "ARRAY"',
  parent:
   { [error: syntax error at or near "ARRAY"]
     name: 'error',
     length: 88,
     severity: 'ERROR',
     code: '42601',
     detail: undefined,
     hint: undefined,
     position: '206',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'scan.l',
     line: '1053',
     routine: 'scanner_yyerror',
     sql: 'SELECT "id", "client_id", "max_sessions", "name", "url", "complete", "questions", "createdAt", "updatedAt" FROM "campaigns" AS "Campaign" WHERE "Campaign"."complete" = false AND "Campaign"."url" ILIKE ANY ARRAY[\'http://opentest.co%\',\'https://opentest.co%\',\'http://www.opentest.co%\',\'https://www.opentest.co%\']::TEXT ORDER BY "Campaign"."createdAt" ASC;' },
  original:
   { [error: syntax error at or near "ARRAY"]
     name: 'error',
     length: 88,
     severity: 'ERROR',
     code: '42601',
     detail: undefined,
     hint: undefined,
     position: '206',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'scan.l',
     line: '1053',
     routine: 'scanner_yyerror',
     sql: 'SELECT "id", "client_id", "max_sessions", "name", "url", "complete", "questions", "createdAt", "updatedAt" FROM "campaigns" AS "Campaign" WHERE "Campaign"."complete" = false AND "Campaign"."url" ILIKE ANY ARRAY[\'http://opentest.co%\',\'https://opentest.co%\',\'http://www.opentest.co%\',\'https://www.opentest.co%\']::TEXT ORDER BY "Campaign"."createdAt" ASC;' },
  sql: 'SELECT "id", "client_id", "max_sessions", "name", "url", "complete", "questions", "createdAt", "updatedAt" FROM "campaigns" AS "Campaign" WHERE "Campaign"."complete" = false AND "Campaign"."url" ILIKE ANY ARRAY[\'http://opentest.co%\',\'https://opentest.co%\',\'http://www.opentest.co%\',\'https://www.opentest.co%\']::TEXT ORDER BY "Campaign"."createdAt" ASC;' }

[igio]

I'm still running into the same issue as of version 3.20.0, installed from npm.

While @janmeier - is correct for $any tests, the tests targeting the issue here, combination of $ilike, $any and array, (https://github.com/sequelize/sequelize/blob/master/test/unit/sql/where.test.js#L569) expects the array statement without the parentheses, generating an sql statement which throws an error when run against the database.

[serranoarevalo]

I managed to make it work by including a sequelize literar after the iLike.

const tagsArray = ['foo', 'bar']

Images.findAll({
where: {
    tags: {
        $iLike: Sequelize.literal(`ANY(ARRAY[${tagsArray}])`)
    }
}
})