Coalesce not working on SQLite

[agustinzamar]

Laravel Version

v11.14.0

PHP Version

8.3.12

Database Driver & Version

SQLite on Herd for macOS 1.11.1

Description

The functions COALESCE and IFNULL do not work properly over SQLite databases.

The raw SQL query provided by $builder->ddRawSQL() is corectly parsed like the example below
select * from "products" where IFNULL(promotional_price, price) >= 100;
and it works just fine when ran directly against the DB.

For some reason it will return no results when the builder excecutes

Steps To Reproduce

Running a query like this one
$query->where(DB::raw('COALESCE(promotional_price, price)'), '>=', $priceFrom)
will return no results when using the sqlite driver but running the resulting raw query against the DB does work correctly

Using $query->whereRaw('COALESCE(promotional_price, price) >= ?', [$priceFrom]) does not work either.
Also querying it as a column and then running the where clause like $query->selectRaw('COALESCE(promotional_price, price) as current_price') and then $query->where('current_price', '>=', $priceFrom) doesnt seem to work

[rodrigopedra]

works just fine when ran directly against the DB.

Which DB? (MySQL, MariaDB, Postgres, SQL Server?)

Also,

• Is your SQLite database seeded?
• Is there any output if you run the generated query directly to the SQLite database?
  • (e.g. from sqlite3 CLI, or using a GUI like DBeaver/Data Grip/PHPStorm/etc.?)

[agustinzamar]

I just tried using cast() like cast(COALESCE(promotional_price, price) as float) and worked.
It does seem odd the need to cast since the values are already SQLites's numeric type