Not fetching records matched by JSON column

[bobgubko]

Describe the bug

ORM escapes value in where clause on JSON column. Test below runs this query:

select `u0`.* from `user` as `u0` where `u0`.`value` = '\"test\"' limit 1

which returns 0 rows. It works and returns record if value isn't "stringified":

`select `u0`.* from `user` as `u0` where `u0`.`value` = 'test' limit 1`

To Reproduce

@Entity()
class User {
  @PrimaryKey({ type: 'number' })
  id?: number;

  @Property({ type: 'json' })
  value: string
}

test('It should fetch record matching by json column', async() => {
  const orm = await MikroORM.init({
    type: 'mysql',
    dbName: 'mo-test',
    host: '127.0.0.1',
    user: 'root',
    password: '123',
    port: 3306,
    allowGlobalContext: true,
    entities: [User],
    metadataProvider: TsMorphMetadataProvider,
    debug: true,
  });
  await orm.schema.refreshDatabase();
  const em = orm.em;

  const user = new User();
  user.id = 1
  user.value = 'test'

  await em.persistAndFlush(user)

  em.clear();

  const c = await em.findOne(User, {
    value: 'test'
  })

  expect(c).not.toBeNull();
});

Versions

Dependency	Version
node	18.12.1
typescript	4.9.5
mikro-orm	5.6.15
your-driver	mysql

[B4nan]

Technically it is a correct query, I am sure it would work fine when executed directly against the database (if not, mysql is really super weird, but hey, that's always possible too). The stored value is stringified too... Note that the very same query works with other drivers.

The driver tries to do some JSON processing, but it's a bit weakly implemented, there are inconsistencies, especially with the string value example. I found also this old and unresolved issue sidorares/node-mysql2#1072

I am in the middle of a larger refactor of how the JSON values are processed, trying to disable any JSON handling in the lowest level, but apparently, it's not that easy, hard to call this configurable (the typeCast option is not helping at all). I will try to deal with this for once, worst case by adding some try/catch blocks to places that are quirky. The refactor will be a bit breaking, so will leave that for v6 most probably.

[bobgubko]

Thanks for quick reply and great ORM! mysql is weird indeed. First query returns nothing when executed directly in DB.

I managed to make it work and prevent escaping strings via:

await em.findOne(User, {
  [expr(`JSON_UNQUOTE(value)`)]: 'test',
})

No ideal, but it works.