SqfliteDatabaseException: DatabaseException(near "nulls": syntax error (code 1 SQLITE_ERROR): , while compiling: select p.b...

[sentry-io]

Sentry Issue: SMOOTHIE-3RJ

SqfliteDatabaseException: DatabaseException(near "nulls": syntax error (code 1 SQLITE_ERROR): , while compiling: select p.barcode from gzipped_product p left outer join product_last_access a on p.barcode = a.barcode where p.lc is null or p.lc != ? order by a.last_access desc nulls last limit ?) sql 'select p.barcode from gzipped_product p left outer join product_last_access a on p.barcode = a.barcode where p.lc is null or p.lc != ? order by a.last_access desc nulls last limit ?' args [pt, 20]

[monsieurtanuki]

Very strange bug, that I couldn't reproduce.
It happens only once, in a "dev mode" action: refresh all the products (potentially with a new language).
The bug itself is a syntax error on a const String: that should be easy to reproduce. But it's not.
The trouble seems to be with a nulls last syntax, that seem to be optionally accepted by SQLite implementation, cf. https://pkg.go.dev/github.com/markdessain/sqlitego/lib#Xsqlite3HasExplicitNulls
We don't seem to have control over that option, which is bad news: we cannot 100% trust the version number in pubspec.yaml.
The solution would be to try with the nulls last syntax, and without it - as a fallback (functionally that's not that bad).