Improper treatment of booleans in QueryBuilder::toRawSql for Postgres

[bastien-phi]

Laravel Version

10.17

PHP Version

8.2.8

Database Driver & Version

Postgresql 14

Description

When using Postgresql, booleans are transformed into integers in Builder::toRawSql().

I tried to fix it but I'm a bit worried to make a mess to fix that.

PostgresConnectiondoes have a valid escapeBoolmethod but the proble is higher in the call stack.

When running User::query()->where('is_admin', true)->toRawSql();, toRawSql() looks like

    public function toRawSql()
    {
        return $this->grammar->substituteBindingsIntoRawSql(
            $this->toSql(), $this->connection->prepareBindings([true])
        );
    }

which resolves in

    public function toRawSql()
    {
        return $this->grammar->substituteBindingsIntoRawSql(
            $this->toSql(), [1]
        );
    }

because of

framework/src/Illuminate/Database/Connection.php

Lines 713 to 729 in 19b4209

	public function prepareBindings(array $bindings)
	{
	$grammar = $this->getQueryGrammar();
	foreach ($bindings as $key => $value) {
	// We need to transform all instances of DateTimeInterface into the actual
	// date string. Each query grammar maintains its own date string format
	// so we'll just ask the grammar for the format to get from the date.
	if ($value instanceof DateTimeInterface) {
	$bindings[$key] = $value->format($grammar->getDateFormat());
	} elseif (is_bool($value)) {
	$bindings[$key] = (int) $value;
	}
	}
	return $bindings;
	}

(note that the boolean cast into integer)

@tpetry Any smart idea to fix that ?

I guess overriding prepareBindingsand bindValues in PostgresConnection should do the trick but is not a valid move for Laravel :
Related explanations on why booleans are casted to integers in Laravel by Taylor : php/php-src#6801 (comment)

Steps To Reproduce

User::query()->where('is_admin', true)->toRawSql();

returns 'select * from "users" where "is_admin" = 1'
while it should return 'select * from "users" where "is_admin" = true'

[tpetry]

Changing this in Laravel may break some applications. So it is not a good approach to change it.

Casting those values to int is not perfect, but it is also not a problem. PostgreSQL is intelligent enough to recognize that you are comparing a boolean column to a 0 or 1 value and will automatically cast them. So for almost any application, this is not a problem. I had some code parts in the past where this automatic casting did not work correctly and PG will then fail with an error about incompatible types. If you haven't seen this yet, the bool -> int cast is not a problem.

If you've had this problem or insist on having is_admin = TRUE in your query, you can use the whereBool method from tpetry/laravel-postgresql-enhanced

[bastien-phi]

@tpetry unfortunately, postgres does not seed to be able to cast int -> bool.

When I try to replay manually an SQL query fetched from ddRawSql(), Postgresql complains about the type :

select * from "users" where "is_admin" = 1;
ERROR:  operator does not exist: boolean = integer
LINE 1: select * from "users" where "is_admin" = 1;
                                               ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Is it a configuration problem in postgres ? I quite don't understant why the int instead of bool will pass in PDO but not in the pgsql console.

[tpetry]

I think maybe that was the edge case for me too, not quite sure. As said, you can use my whereBool method.

[bastien-phi]

Ok. There is some black magic in PDO that I can't understand but boolean columns are sent as boolean to postgres, not integers.

User::query()->where('is_admin', true)->where('id', 1)->get();
// postgres log : 
// execute pdo_stmt_00000003: select * from ""users"" where ""is_admin"" = $1 and ""id"" = $2","parameters: $1 = 't', $2 = '1'
// toRawSql : 
// select * from "users" where "is_admin" = 1 and "id" = 1

User::query()->where('is_admin', 1)->where('id', 1)->get();
// execute pdo_stmt_00000003: select * from ""users"" where ""is_admin"" = $1 and ""id"" = $2","parameters: $1 = 't', $2 = '1'
// select * from "users" where "is_admin" = 1 and "id" = 1

User::query()->where('is_admin', '1')->where('id', '1')->get();
// execute pdo_stmt_00000003: select * from ""users"" where ""is_admin"" = $1 and ""id"" = $2","parameters: $1 = 't', $2 = '1'
// select * from "users" where "is_admin" = '1' and "id" = '1'

User::query()->where('is_admin', 'true')->where('id', '1')->get();
// execute pdo_stmt_00000003: select * from ""users"" where ""is_admin"" = $1 and ""id"" = $2","parameters: $1 = 't', $2 = '1'
// select * from "users" where "is_admin" = 'true' and "id" = '1'

It looks like the bindings are treated slightly differently between Eloquent -> PDO -> Database and Eloquent -> rawSql.

In any case, great job on your recent contributions, it will help a lot !

[CamKem]

@tpetry - I have just run into this issue today, raw SQL dumps have the bool value as 1/0 - when I take the sql query dump and try to run it in TablePlus because of the native bool types, the query errors out. Will the package you mentioned fix the output for the toRawSql() Method for Postgres?

[tpetry]

Yes. the whereBool() method of my package fixes it.

But the behaviour in Laravel can't be changed as it would break a lot of apps.