`CASE WHEN EXISTS(...) THEN TRUE ELSE FALSE END AS my_column` is not inferred as boolean

[yguedidi]

• Create an ORM QueryBuilder
• Call ->select('CASE WHEN EXISTS(SELECT 1) THEN TRUE ELSE FALSE END AS my_column')
• Call ->getQuery()->getResult()

See the type of my_column inferred as 0|'0'

[stof]

It is still not inferred as a boolean, but as 0|1|'0'|'1'

[arnaud-lb]

Unfortunately this is the actual type returned by Doctrine.

DQL true and false literals are transpiled to 1 and 0 in SQL, so SELECT (true) in DQL becomes SELECT 1.

After that, whether integers are returned as ints or strings depend on the driver, the driver settings, the Doctrine version, and the integer value. Currently the type inference simply assumes that integers can be returned as int|string.

That's why true|false in DQL is 0|1|'0'|'1'.

[arnaud-lb]

Note: This fact about integers is only true for expressions (SELECT (true), SELECT MAX(...), etc) because Doctrine doesn't reinterprets the type of expressions results.

Doctrine does reinterpret the type of fields (SELECT x.foo), so selections of integer fields return int and selections or bigint fields return string.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.