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

New Issue

admin · 2026-01-22T15:27:46+01:00

admin commented

2026-01-22 15:27:46 +01:00

Originally created by @Hikariii on GitHub (Dec 31, 2018).

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

Originally created by @Hikariii on GitHub (Dec 31, 2018). ### Bug Report  | Q | A |------------ | ------ | BC Break | no | Version | 2.6.3 #### Summary For all db implementations the [SqlWalker](https://github.com/doctrine/doctrine2/blob/2.6/lib/Doctrine/ORM/Query/SqlWalker.php#L2082) 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`

admin added the Improvement Question labels 2026-01-22 15:27:46 +01:00

admin commented

2026-01-22 15:27:46 +01:00

@Ocramius commented on GitHub (Dec 31, 2018):

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.

@Ocramius commented on GitHub (Dec 31, 2018): > 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.

admin commented

2026-01-22 15:27:47 +01:00

@Hikariii commented on GitHub (Dec 31, 2018):

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 commented on GitHub (Dec 31, 2018): 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.

admin commented

2026-01-22 15:27:47 +01:00

@Hikariii commented on GitHub (Dec 31, 2018):

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

@Hikariii commented on GitHub (Dec 31, 2018): 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

admin commented

2026-01-22 15:27:48 +01:00

@franzwilding commented on GitHub (Mar 26, 2019):

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.

@franzwilding commented on GitHub (Mar 26, 2019): 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.

admin commented

2026-01-22 15:27:48 +01:00

@noullet commented on GitHub (Mar 30, 2022):

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'")

@noullet commented on GitHub (Mar 30, 2022): 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'")`

admin commented

2026-01-22 15:27:48 +01:00

@FlyingDR commented on GitHub (Nov 23, 2023):

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.

@FlyingDR commented on GitHub (Nov 23, 2023): 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.

admin commented

2026-01-22 15:27:48 +01:00

@juslintek commented on GitHub (Nov 28, 2024):

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

@juslintek commented on GitHub (Nov 28, 2024): 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: ```php /** * 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

admin referenced this issue

2026-01-22 16:05:40 +01:00

[PR #6151] Embeddable attribute override support #9867

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6151