ILIKE not working on GeoPackage

[gubuntu]

Describe the bug
ILIKE is not supported in queries against GeoPackages

LIKE works fine. ILIKE gives this error.

ILIKE works fine against the same data in a shapefile.

How to Reproduce

Try filtering with ILIKE against a GeoPackage

QGIS and OS versions

QGIS version	3.12.0-București	QGIS code revision	409bcf8
Compiled against Qt	5.12.3	Running against Qt	5.12.3
Compiled against GDAL/OGR	2.4.1	Running against GDAL/OGR	2.4.1
Compiled against GEOS	3.7.2-CAPI-1.11.2	Running against GEOS	3.7.2-CAPI-1.11.2 b55d2125
Compiled against SQLite	3.28.0	Running against SQLite	3.28.0
PostgreSQL Client Version	11.3	SpatiaLite Version	4.3.0a
QWT Version	6.1.4	QScintilla2 Version	2.11.1
Compiled against PROJ	5.2.0	Running against PROJ	Rel. 5.2.0, September 15th, 2018
OS Version	macOS 10.15
Active python plugins	QgisModelBaker; SpreadsheetLayers; lrs; QuickOSM; VectorBender; quick_map_services; stac_browser; Discovery; qfieldsync; qchainage; OSMDownloader; parcel_plugin; lizmap; openlayers_plugin; Projestions; mmqgis; processing_circuitscape; mapswipetool_plugin; Mergin; travel_time_platform_plugin; nominatim_locator_filter; timemanager; processing; db_manager; MetaSearch

Additional context

[gioman]

@gubuntu does not seems a QGIS issue:

ogr2ogr -f "GPKG" -sql "SELECT * FROM cos_2010_v1 WHERE descricao LIKE '%stria'" exp2.gpkg cos_2010_v1.gpkg

works

ogr2ogr -f "GPKG" -sql "SELECT * FROM cos_2010_v1 WHERE descricao ILIKE '%stria'" exp3.gpkg cos_2010_v1.gpkg
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT * FROM cos_2010_v1 WHERE descricao ILIKE '%stria'):
  near "ILIKE": syntax error

does not.

If you replace the input datasource with a shapefile it works in both cases.

[rouault]

ILIKE is not a SQLite3 supported operator: see https://www.sqlite.org/lang_expr.html

It look like the QgsSQLiteExpressionCompiler class that translates QGIS expression to backend SQL should be modified to fail on ILIKE (as the generic QgsOgrExpressionCompiler does), so that QGIS-side evaluation is done

[jef-n]

@gubuntu lower("SP_NAME") like lower('Alma%') will work.

@rouault The subset string used by Filter... is supposed to be a native provider expression and not a QGIS expression.

The sqlite expression compiler issue is unrelated (but becomes visible, if you use an expression with ILIKE to select features or filter the attribute table).

[agiudiceandrea]

It seems this issue still occurs although it was allegedly fixed with #35706 by @jef-n: see #51375.

[jef-n]

See my comment before closing the issue: There is no issue as the filter string is provider specific and SQLite/GPKG doesn't support ILIKE - others do. That's why the issue was closed.
The commit doesn't change that, but adds support for ILIKE to the sqlite expression compiler- that is not used for the filter string. That's the other issue Even mentioned here and that's why the commit only refs this issue, but doesn't fix/close it.