Orm: Cannot differ between TRUE/FALSE and 1/0 in DQL (needed for JSON data)

[Hikariii]

Bug Report

| Q | A
|------------ | ------
| BC Break | no
| Version | 2.6.3

Summary

For all db implementations the SqlWalker uses the Platform to convert a boolean literal to an SQL representation. The default behaviour is to convert a boolean to 0 or 1 respectively. As the mysql documentation also states as its behaviour: https://dev.mysql.com/doc/refman/5.7/en/boolean-literals.html

Since mysql 5.7 with JSON functions there is a different behaviour though.
Consider this query:
SELECT JSON_SET('{ "a": 0, "b": false}', '$.a', 1, '$.b', TRUE);
results in the json object:
{"a": 1, "b": true}

Here TRUE and 1 yield a different result.

Current behavior

Using DQL extension https://github.com/ScientaNL/DoctrineJsonFunctions to be able to have JSON functions enabled the DQL generates incorrect sql:

| lang | query |
|----- |----|
| DQL | SELECT JSON_SET('{ "a": 0, "b": false}', '$.a', 1, '$.b', TRUE); |
| SQL generated | SELECT JSON_SET('{ "a": 0, "b": false}', '$.a', 1, '$.b', 1); |
| SQL expected | SELECT JSON_SET('{ "a": 0, "b": false}', '$.a', 1, '$.b', TRUE); |

How to reproduce

Generate sql from DQL SELECT TRUE.
This will yield the SQL: SELECT 1.

Expected behavior

The expected behaviour is to be able to differ between 1 and TRUE in DQL and generate a query like SELECT TRUE

[Ocramius]

to be able to differ between 1 and TRUE in DQL

I'd say you need a bound parameter and a custom DBAL type for this, not really a DQL extension.

Making parsing of 1 and TRUE context-aware is much more added complexity for a single engine (MySQL) which is misbehaving.

[Hikariii]

I tend to agree with you, but then wonder why the Lexer parses Boolean literals and converts them to an integer representation in the first place if (for MySQL) the Boolean literals have the same meaning in all cases except for Json functions.
Wouldn't it be better and more consistent to follow the already implemented postgresql implementation and implement the TRUE and FALSE literals via the Platform.

[Hikariii]

I believe the way forward should be to change the MysqlPlatform like the PostgresqlPlatform to a behaviour where boolean literals are (optionally) preserved.
See https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php#L864-L885
Because the boolean literals are interchangeable for 1 and 0 in mysql it will not yield any errors in SQL to preserve these literals

[franzwilding]

I found a workaround for the moment:

WHERE
    CAST(JSON_EXTRACT(data, '$.boolean_field') AS char) = 'true' OR
    CAST(JSON_EXTRACT(data, '$.boolean_field') AS char) = 'false'

Not the best solution, but I guess the only way for the moment.