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.

[noullet]

I had a similar issue with query builders, but I also could not use the proposed workaround as I got this error:

Error: Expected known function, got 'CAST'

Indeed, CAST is not part of DQL (unless an extension is used, apprently).

I worked around it by doing a "dirty" cast by concatenating an empty string to the boolean value:
$qb->andWhere("CONCAT(JSON_EXTRACT(data, '$.boolean_field'), '') = 'true'")

[FlyingDR]

Another possible approach is to convert JSON boolean to values that are passed by the Doctrine:

JSON_TYPE(data->'$.boolean_field') = 'BOOLEAN' AND (data->'$.boolean_field' + 0) = 1

JSON expression has to be evaluated twice in this case, hopefully, MySQL breaks earlier for non-boolean values.

[juslintek]

Seems that Doctrine PDO driver supports boolean type and maps ParameterType::BOLEAN to PDO::PARAM_BOOL. So when using setParameter, just explicitly specify ParameterType::BOOLEAN and it use with PDO boolean type. Regarding JSON operations I always use MEMBER OF, probably easiest and most universal approach, works with arrays and singular values:

    /**
     * Add boolean filter
     *
     */
    private function addBooleanFilter(
        string $property,
        bool $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator
    ): void {
        $alias = $queryBuilder->getRootAliases()[0];
        $jsonColumn = $this->getJsonColumn($property);
        $jsonKey = $this->getJsonKey($property);
        $valueParameter = $queryNameGenerator->generateParameterName($jsonColumn);

        $queryBuilder
            ->andWhere(":{$valueParameter} MEMBER OF({$alias}.{$jsonColumn}->'$.{$jsonKey}')")
            ->setParameter($valueParameter, $value, ParameterType::BOOLEAN);
    }

of-course this is compatible with mysql 8+ versions :-) So would need to drop compatibility to mysql 5.7 with these changes or create separate driver MySQL57:
https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of