New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem with query generated from update operation with where condition to jsonb type #3138

Closed
lucianojfj opened this Issue Feb 15, 2015 · 3 comments

Comments

2 participants
@lucianojfj

lucianojfj commented Feb 15, 2015

Hi!

I have a test suite that used to run properly in the version 2.0.0-rc8 with Postgresql 9.4. However, when I updated to the version 2.0.2, It broke my tests that update my models.

The problem is the where condition of my update method of the Model. The code as follows:

Db.MyEntity.update({
    profile: newValue
  }, 
{
returning: true,
where: Db.Sequelize.json("profile->>'id'", Db.sequelize.cast(id, 'text'))
}

Using sequelize@2.0.0-rc8, it generates this sql:

UPDATE "test"."MyEntity" SET "profile"='{"id":"xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx-62c643f02c9e"}',"updatedAt"='2015-02-15 01:00:10.295 +00:00' WHERE profile->>'id' = CAST('xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx' AS TEXT) 

After updating to sequelize@2.0.2, I am getting the following sql:

UPDATE "test"."MyEntity" SET "profile"=\'{"id":"xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx"}\',"updatedAt"=\'2015-02-15 01:03:30.828 +00:00\' WHERE "id" = profile->>\'id\' = CAST(\'xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx-xxxxxxx\' AS TEXT)

About the sql above, the main difference is that after the WHERE condition, the "id" is added.

I do not know for sure, but It may have something to do with this issue #3113 since this piece of code has been added #L1752

While this problem is not solved, I am using the following workaround:

{
where: "profile->>'id'=CAST('" + id + "' AS TEXT)"
}

I also catch this error:

{ [SequelizeDatabaseError: operator does not exist: uuid = boolean]
  name: 'SequelizeDatabaseError',
  message: 'operator does not exist: uuid = boolean',
  parent: 
   { [error: operator does not exist: uuid = boolean]
     name: 'error',
     length: 195,
     severity: 'ERROR',
     code: '42883',
     detail: undefined,
     hint: 'No operator matches the given name and argument type(s). You might need to add explicit type casts.',
     position: '181',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_oper.c',
     line: '722',
     routine: 'op_error',
     sql: ... },
  original: 
   { [error: operator does not exist: uuid = boolean]
     name: 'error',
     length: 195,
     severity: 'ERROR',
     code: '42883',
     detail: undefined,
     hint: 'No operator matches the given name and argument type(s). You might need to add explicit type casts.',
     position: '181',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_oper.c',
     line: '722',
     routine: 'op_error',
     sql: ... },

Thanks a lot!

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Feb 15, 2015

Looks like i introduced a regression bug in terms of JSON querying.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Feb 17, 2015

Fixed in sequelize@2.0.3

@lucianojfj

This comment has been minimized.

lucianojfj commented Feb 17, 2015

It works now. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment