Select Features By Value not working with boolean and checkboxes in PostgreSQL

[ghtmtt]

What is the bug or the crash?

With a field defined as boolean type in a PG table, the Select by Value tool is not finding the corresponding values in the table. The same table exported in geopackage, with the same field definition and same widget type is working correctly.

Steps to reproduce the issue

At the bottom I'm attaching the PG table definition and the geopackage with the same table.

I'm also attaching some screenshot that should be self explanatory.

Postgres

same behavior for True and False: no matching feature are found. Clicking on Show in table I see this ("bool_val"=)

Geopackage

same behavior for True and False: the selection is made correctly

Here the PG table definition to reproduce the error:

CREATE TABLE public."table" (
	id serial4 NOT NULL,
	geom public.geometry(point, 4326) NULL,
	string_text text NULL,
	num_val int2 NULL,
	bool_val bool NULL,
	CONSTRAINT table_pkey PRIMARY KEY (id)
);

And here the geopackage: test_checkbox.zip

Versions

QGIS 3.34 but also QGIS dev (QGIS version | 3.41.0-Master, QGIS code revision | eba8d20)

<style type="text/css"> p, li { white-space: pre-wrap; }

QGIS version	3.34.14-Prizren	QGIS code revision	0cdaf6d
Qt version	5.15.8
Python version	3.11.2
GDAL/OGR version	3.6.2
PROJ version	9.1.1
EPSG Registry database version	v10.076 (2022-08-31)
GEOS version	3.11.1-CAPI-1.17.1
SQLite version	3.40.1
PostgreSQL client version	15.8 (Debian 15.8-0+deb12u1)
SpatiaLite version	5.0.1
QWT version	6.1.4
QScintilla2 version	2.13.3
OS version	Debian GNU/Linux 12 (bookworm)
Active Python plugins
lizmap	4.4.6
plugin_comune_senigallia	1.0.0
pluginbuilder3	3.2.1
line_plan_survey	0.4.0
plugin_reloader	0.17
DataPlotly	4.2.0
SpreadsheetLayers	2.1.2
groma	1.0.0
processing	2.12.99
MetaSearch	0.3.6
db_manager	0.1.20

QGIS version 3.34.14-Prizren QGIS code revision [0cdaf6d](https://github.com/qgis/QGIS/commit/0cdaf6d94f9) Qt version 5.15.8 Python version 3.11.2 GDAL/OGR version 3.6.2 PROJ version 9.1.1 EPSG Registry database version v10.076 (2022-08-31) GEOS version 3.11.1-CAPI-1.17.1 SQLite version 3.40.1 PostgreSQL client version 15.8 (Debian 15.8-0+deb12u1) SpatiaLite version 5.0.1 QWT version 6.1.4 QScintilla2 version 2.13.3 OS version Debian GNU/Linux 12 (bookworm)

Active Python plugins
lizmap
4.4.6
plugin_comune_senigallia
1.0.0
pluginbuilder3
3.2.1
line_plan_survey
0.4.0
plugin_reloader
0.17
DataPlotly
4.2.0
SpreadsheetLayers
2.1.2
groma
1.0.0
processing
2.12.99
MetaSearch
0.3.6
db_manager
0.1.20

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

No response

[agiudiceandrea]

@ghtmtt, do you know if the issue didn't occur using any previous QGIS versions?

[ghtmtt]

@agiudiceandrea unfortunately no, no idea

[m-kuhn]

@ghtmtt did you check the debugging panel for sql queries that are sent?

[ghtmtt]

@m-kuhn here it is:

BEGIN READ ONLY;DECLARE qgis_138 BINARY CURSOR FOR SELECT "id" FROM "public"."table

with other tests, it seems to me that the problem is related with the drag and drop designer. If you load the table and select the features by values, sometimes (not always) it works, and the sql sent to PG is:

BEGIN READ ONLY;DECLARE qgis_331 BINARY CURSOR FOR SELECT "id",boolout("bool_val")::text FROM "public"."table" WHERE ("bool_val" = TRUE)

if you just change the standard form with the D&D, without adding aliases, tabs, etc, than the query sent is the fist one, and there is no way to get back without removing and reloading the layer

[m-kuhn]

Hmm... I wouldn't be able to explain how D&D could be responsible for this, but possibly the widget configuration (bool representation)?

[ghtmtt]

got it, it's the alias! Even without D&D, if you change the alias of the bool field, it does not work anymore

[nyalldawson]

There's a few red herrings in this ticket -- the actual issue is caused if the widget configuration in the form properties has EVER been opened for the layer. It's unrelated to the storage (postgres or gpkg), and can be reproduced with either. Also not related to the presence of an alias.