Getting wrong SQL generated with "!= 0" in statement since v8.81.0

[martin-pfeiffer-dibooq]

• Laravel Version: > 8.81
• PHP Version: used > 7.4.16
• Database Driver & Version: pgsql & postgresql v12-14

Description:

Since this commit a9c5009
we get unnecessary "!= 0" in SQL statements.

Steps To Reproduce:

In Tinker:
App\Models\Occupation::query()->where('real_estate_id',59)->where('date_range', '>>','[2022-01-08 00:00:00,2022-01-09 00:00:00)')->toSql();

SQL output with >v8.81:

=> "select * from "occupations" where "real_estate_id" = ? and ("date_range" >> ?) != 0 and "occupations"."deleted_at" is null"

SQL output with <v8.81:

=> "select * from "occupations" where "real_estate_id" = ? and "date_range" >> ? and "occupations"."deleted_at" is null"

Error:

Illuminate\Database\QueryException with message 'SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: boolean <> integer
LINE 1: ...re "real_estate_id" = $1 and ("date_range" >> $2) != 0 and "...

HINT: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL: select * from "occupations" where "real_estate_id" = 226 and ("date_range" >> [2022-01-08 00:00:00,2022-01-09 00:00:00)) != 0 and "occupations"."deleted_at" is null limit 1)'

Why changes are made on a9c5009#r65853324 e.G. ?

[BrandonSurowiec]

@martin-pfeiffer-dibooq I know nothing about the differences between PgSQL and PostgresSQL when compared to MySql. Is it normal to open the date_range argument with a square bracket "[" and then close it with parenthesis ")" '[2022-01-08 00:00:00,2022-01-09 00:00:00)' ? Should it close with a square bracket? '[2022-01-08 00:00:00,2022-01-09 00:00:00]'

[martin-pfeiffer-dibooq]

@BrandonSurowiec its not about the date range. Its just about the "!= 0" what is added afterwords.

This causes an SQL error like shown.

[BrandonSurowiec]

Seems like it would be a good idea to verify that the error isn't thrown due to your argument though. If that is the cause of the SQL error, then the != 0 works, whether you like the syntax or not. Just trying to help verify the bug is actually a bug.

You should be able to throw this into your SQL tool and see the output doesn't fail (I deleted the ! = 0 part):

select * from "occupations" where "real_estate_id" = 226 and ("date_range" >> [2022-01-08 00:00:00,2022-01-09 00:00:00)) and "occupations"."deleted_at" is null limit 1

[martin-pfeiffer-dibooq]

@BrandonSurowiec Just to add the information, this was working before laravel/framework v8.81.

The origin error in sentry:

Illuminate\Database\QueryException

SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: boolean <> integer
LINE 1: ...= $4) or "status" != $5) and ("date_range" >> $6) != 0 and "...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. (SQL: select * from "occupations" where "real_estate_id" = 2 and "id" is not null and (("status" = INQUIRY and "origin_id" = 1 and "origin_type" = AGENCY) or "status" != CANCELLED) and ("date_range" >> [2022-02-15 00:00:00,2022-02-16 00:00:00)) != 0 and "occupations"."deleted_at" is null order by LOWER (date_range) ASC limit 1)

Two things i discovered:

First, the date range value should be in quotes. It will not work like this [2022-02-15 00:00:00,2022-02-16 00:00:00) but with quotes like this '[2022-01-08 00:00:00,2022-01-09 00:00:00)'.

Second, the != 0 should be actually like != false. Its a boolean comparison. It will not work like it is right now.

So, in the end this works on Postgresql:

select * from "occupations" where "real_estate_id" = 59
and "id" is not null and 
(("status" = 'INQUIRY' and "origin_id" = 66 and "origin_type" = 'AGENCY') or "status" != 'CANCELLED') 
and ("date_range" >> '[2022-01-08 00:00:00,2022-01-09 00:00:00)') != false
and "occupations"."deleted_at" is null order by LOWER (date_range) 
ASC limit 1