REGRESSION: Incorrect results when filtering on PostGIS/PostgreSQL or GeoPackage boolean columns #61072

Alex-Kent · 2025-03-19T01:13:43Z

What is the bug or the crash?

The handling of boolean fields for PostGIS/PostgreSQL and GeoPackage layers (and possibly others) broke some time between 3.35.0-Master 23f3af4 (2024-01-30) [working] and 3.38.3-Grenoble Release 3.38 37f9e6e / 3.39.0-Master c50c923 (2024-09-13) [both broken]. Filtering layer features based on the value of the boolean column using a Rule-based Symbology results in incorrect feature filtering. Additional unexpected results and, at times, odd GUI behaviour are also seen when using such columns in a Provider Feature Filter (this additional behavior seems, at least in part, to be due to 3rd-party plugins).

Erroneous behavior for Rule-based symbology is present for both my normal user profile and a new user profile. The erroneous/odd behavior for Provider Feature Filter is only present for my normal user profile (and not for a new profile). In all cases the behaviour on the older QGIS version is correct.

Most of what's below are test results. Click here to skip to the resolution and related commentary.

Update: Testing shows that the SQL queries are identical in both the correct and erroneous cases. This implies that the issue lies in how QGIS is interpreting or presenting the returned features.

[See also my comment below where I narrow down when the breaking change occurred.]

Here are the results of filtering a layer backed by a PostGIS table with 166914 rows. The data type of the is_hidden column is native PostgreSQL boolean and the column contains a mix of NULL, true, and false values. The layer is added to the project and then either a Provider Feature Filter is applied to the layer or a Rule-based Symbology is used.

The numbers shown below are, as appropriate:

The number shown when running the SQL expression directly against the database.
The number shown when clicking on the Test button when editing the expression.
The number shown in the Layers tree when Show Feature Count is active for the layer.

Unexpected/Erroneous values/behavior shown in bold. Where behavior is different between my normal user profile and a new profile the former is shown in normal text and the latter is shown in italics.

The expressions shown below are specified in the direct SQL query, as the expression for the layer's Provider Feature Filter, or as the classification rule applied in the Rule-based symbology. is_hidden is the name of the boolean column.

Expression	SQL	Old PT	Old P	Old RT	Old R	Cur. PT	Cur. P	Cur. RT	Cur. R
(none)	166914	166914	166914¹	Error²	166914	166914	166914¹	Error³	166914
`"is_hidden" IS NULL`	47	47	47	47	47	47/35608^4,5 47	47/35608^4,5 47	47	47
`"is_hidden" IS NOT NULL`	166867	166867	166867	166867	166867	166867	166867	166867	166867
`"is_hidden" = 'true'`	251	251	251	251	251	251	35608⁵ 251	251	0
`"is_hidden" != 'true'`	166616	166616	166616	166616	166616	166616	166616⁶	166616	0
`"is_hidden" = 'false'`	166616	166616	166616	166616	166616	166616	166616	166616	0
`"is_hidden" != 'false'`	251	251	251	251	251	251	35608⁵ 251	251	0
`"is_hidden" = true`	251	251	251	251	251	251	35608⁵ 251	251	251
`"is_hidden" != true`	166616	166616	166616	166616	166616	166616	166616⁶	166616	251
`"is_hidden" = false`	166616	166616	166616	166616	166616	166616	166616⁶	166616	166616
`"is_hidden" != false`	251	251	251	251	251	251	35608⁵ 251	251	166616

¹ No filter is shown as being applied (a blank filter was specified).
  [This is reasonable behaviour.]
² Error for old is "Filter expression parsing error: syntax error, unexpected end of file".
  [This is reasonable behaviour.]
³ Error for current is "Filter expression parsing error: Incomplete expression. You might not have finished the full expression."
  [This is reasonable behaviour.]
⁴ Value is initially 47 but changes to 35608.
  Clicking OK results in the Filter window immediately re-opening; clicking OK again closes the window.
⁵ I have no idea where the 35608 value is coming from.
⁶ For my normal user profile (but not for a new profile) clicking OK results in the Filter window immediately re-opening; clicking OK again closes the window.

SQL = Result of running select count(*) from "my_feature_table" where … directly against the database.
PT = Feature count shown when clicking Test when editing the Provider Feature Filter.
P = Feature count shown for layer in Layers tree when expression is set as the Provider Feature Filter.
RT = Feature count shown when clicking Test when editing the Rule-based Symbology rule.
R = Feature count shown for classification within layer in Layers tree when Rule-based Symbology rule is applied.
Old = 3.22.4-Białowieża
Cur. = 3.43.0-Master (8ea86e8)

Further investigation with a different table shows the following:

Expression	SQL	Old test	Old tree	Old map	Old table	Cur. test	Cur. tree	Cur. map	Cur. table
`"is_edited"=true`	3056	3056	3056	Yes	3056	3056	3056	Yes	3056
`"is_edited"!=false`	3056	3056	3056	Yes	3056	3056	332	No	0
`"is_edited"=false`	332	332	332	Yes	332	332	332	Yes	332
`"is_edited"!=true`	332	332	332	Yes	332	332	3056	No	0

`"is_edited"='true'`	3056	3056	3056	Yes	3056	3056	0	No	0
`"is_edited"!='false'`	3056	3056	3056	Yes	3056	3056	0	No	0
`"is_edited"='false'`	332	332	332	Yes	332	332	0	No	0
`"is_edited"!='true'`	332	332	332	Yes	332	332	0	No	0

is_edited is the boolean column and the erroneous behavior is marked in bold.

Old → 3.22.4-Białowieża
Cur. → 3.43.0-Master (8ea86e8)

SQL → Result of select count(*) from "table_name" whereexpression query run directly against the database.
Test → Value shown when clicking Test when editing the expression as a Rule-based symbology rule.
Tree →Number of features listed for category in Layers tree when expression is applied as a Rule-based symbology rule.
Map → Whether features are displayed on the map when expression is applied as a Rule-based symbology rule.
Table → Returned row count when expression is applied as an Advanced Filter (Expression) in the layer's Attribute Table window

Steps to reproduce the issue

Create a PostGIS table with the the geometry specified as geom geometry(MultiPolygonZ,4326) and also containing a boolean column.
Add the table to a QGIS project.
Add multiple features to the table via QGIS.
Manually set some of the boolean column values to NULL, some to true (t in PostgreSQL parlance), and some to false (f).
Select Rule-based symbology for the layer and add a new rule to test the expressions on.
Enable Show Feature Count for the layer.
Examine the number of features returned via various methods as detailed in the table above.

Versions

PostgreSQL

9.5.2 (64-bit), Ubuntu-supplied package.

QGIS (old version, from Ubuntu-supplied Jammy package)

Differences are bolded.


QGIS version	3.22.4-Białowieża	QGIS code branch	Release 3.22
Qt version	5.15.3
Python version	3.10.3
GDAL/OGR version	3.4.1
PROJ version	8.2.1
EPSG Registry database version	v10.041 (2021-12-03)
GEOS version	3.10.2-CAPI-1.16.0
SQLite version	3.37.2
PostgreSQL client version	14.2 (Ubuntu 14.2-1)
SpatiaLite version	5.0.1
QWT version	6.1.4
QScintilla2 version	2.11.6
OS version	Ubuntu 22.04.5 LTS

QGIS (current Git `master` version, built from source)

Differences are bolded.


QGIS version	3.43.0-Master	QGIS code revision	`8ea86e8`
Qt version	5.15.3
Python version	3.10.12
GDAL version	3.4.1
PROJ version	8.2.1
EPSG Registry database version	v10.041 (2021-12-03)
GEOS version	3.10.2-CAPI-1.16.0
SQLite version	3.37.2
PDAL version	2.9.0
PostgreSQL client version	17.4
SpatiaLite version	5.0.1
QWT version	6.1.4
QScintilla2 version	2.11.6
OS version	Ubuntu 22.04.5 LTS

Active Python plugins

The same plugins (from the same user profile directory) are used for both versions of QGIS.

Normal user profile

Plugin	Old QGIS version	New QGIS version (normal user profile)	New QGIS version (new profile)
layer_tree_tools	1.4.0	1.4.0
mmqgis	2024.11.8	2024.11.8
rvt-qgis	0.9.6	0.9.6
quick-geojson	1.0	1.0
Pic2Map	version 3.3	version 3.3
Qgis2threejs	2.7.3	2.7.3
reloader	0.1	0.3
LAStools	2.2.0	2.2.0
Serval	3.32.0	3.32.0
inkscape2symbol-master	0.2	0.2
searchlayers	3.0.17	3.0.17
quick_map_services	0.19.34	0.19.34
web2qgis	0.2.0	0.2.0
easyPlugin	1.0.0
GarminCustomMap	3.4.2	3.4.2
pluginbuilder3	3.2.1	3.2.1
map-units-per-pixel	0.2.1	0.2.1
zoom_level	0.3.1	0.3.1
layer_color_plugin	0.3	0.3
group_transparency	0.2
ThRasE	23.8
wbt_for_qgis	1.0.9
plugin_reloader	0.17	0.17
open_lidar_tools	2.2.2
valuetool	3.0.19
lockzoom	3.0.4
nominatim	1.4.5
openlayers_plugin	2.0.0
batch_gps_importer	version 1.0.1
db_manager	0.1.20		0.1.20
sagaprovider	2.12.99
grassprovider	2.12.99	2.12.99	2.12.99
otbprovider	2.12.99
processing	2.12.99		2.12.99
MetaSearch	0.3.5		0.3.6

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

The text was updated successfully, but these errors were encountered:

Alex-Kent · 2025-03-20T00:02:01Z

This issue also effects Geopackages. There's also some probably-related weirdness going on as well. I've put together a simple test project; I'll shortly update the bug report with it and a clearer description of the issue and how to reproduce it.

I looked at what I think is the file that would be handling this (src/core/symbology/qgsrulebaserender.cpp) and didn't see anything obvious. Likewise looking through the changes to that file since 2022-02-18 (the release date of the version I'm running that doesn't show the issue) shows nothing that would obviously cause problems. I'm currently building older versions of QGIS to try to narrow down which version the issue first appears in.

Alex-Kent · 2025-03-20T01:05:08Z

Test project / Narrowing down when the issue first appeared

TLDR: The issues are observed to have appeared sometime between 3.35.0-Master 23f3af4 (2024-01-30) [exclusive] and 3.38.3-Grenoble Release 3.38 37f9e6e / 3.39.0-Master c50c923 (2024-09-13) [inclusive].

Test project and test data

The following can be used to reproduce the test results:

Download the test project: boolean-test.zip
Extract the files.
Edit boolean-test.sql as appropriate.
Run the queries in boolean-test.sql on your PostGIS server.
Open boolean-test.qgs in a text editor.
Change all occurances of dbname='DB' service='SERVICE' sslmode=disable to match the settings for your PostGIS database. DB is the database name and SERVICE is the name of the PostgreSQL service that the database resides on (as listed in ~/.pg_service.conf); these match the settings found in Layers→Add Layer→Add PostGIS Layers→Edit. Edit additional options (e.g. sslmode) as needed. Alternatively these can be set through the QGIS GUI at project load time.
Save boolean-test.qgs.
Load boolean-test.qgs into QGIS.

Sample data for all tests (Postgres and Geopackage) has:

2 false rows
3 true rows
1 null row

Both use a rule-based symbology with the rules set to the listed expressions. Both layers have Show Feature Count enabled . The items displayed on the map for each rule is shown; the following column (# in tree) contains the number shown in the layers tree for each rule category.

[3.22.4 is a "known good" version. Later versions chosen to test all have changes to src/core/symbology/qgsrulebasedrenderer.cpp. Versions numbers are those given in the Help→About window.]

3.22.4 (Ubuntu-maintained package)

Expression	Postgres	# in tree	Geopackage	# in tree
`"flag" IS NULL`	null	1	null	1
`"flag" IS NOT NULL`	true & false	5	true & false	5
`"flag" = false`	false	2	false	2
`"flag" != false`	true	3	true	3
`"flag" = true`	true	3	true	3
`"flag" != true`	false	2	false	2
`"flag" = 'false'`	false	2	— ¹	2
`"flag" != 'false'`	true	3	true	3
`"flag" = 'true'`	true	3	— ²	3
`"flag" != 'true'`	false	2	false	2
`"flag" = 'f'`	—	0	—	0
`"flag" != 'f'`	true	5	true & false	5
`"flag" = 't'`	—	0	—	0
`"flag" != 't'`	false	5	true & false	5

Inconsistencies:

Postgres for != 'f' and != 't'
Geopackage for = 'false' and = 'true'

Oddities:

¹ Selecting only "flag" = 'false' displays nothing on the map
Also selecting "flag" != 'false' displays true & false on the map.
² Selecting only "flag" = 'true' displays nothing on the map
Also selecting "flag" != 'true' displays true & false on the map.

3.23.0-Master `fcb8cf0` (2022-01-31)

Same as 3.22.4

3.27.0-Master `4bfef0c` (2022-08-29)

Same as 3.22.4

3.35.0-Master `26798b8` (2024-01-30)

Same as 3.22.4

3.35.0-Master `23f3af4` (2024-01-30)

[One unrelated test failed during build but qgis built successfully.]

This version arguably has correct behavior. (It's my understanding that official support for 't' and 'f' as boolean values was dropped with QGIS 3.x so the behavior seen for them is not necessarily a problem. Please correct me if this is not the case.)

Expression	Postgres	# in tree	Geopackage	# in tree
`"flag" IS NULL`	null	1	null	1
`"flag" IS NOT NULL`	true & false	5	true & false	5
`"flag" = false`	false	2	false	2
`"flag" != false`	true	3	true	3
`"flag" = true`	true	3	true	3
`"flag" != true`	false	2	false	2
`"flag" = 'false'`	false	2	false	2
`"flag" != 'false'`	true	3	true	3
`"flag" = 'true'`	true	3	true	3
`"flag" != 'true'`	false	2	false	2
`"flag" = 'f'`	—	0	—	0
`"flag" != 'f'`	true	5	true & false	5
`"flag" = 't'`	—	0	—	0
`"flag" != 't'`	false	5	true & false	5

Selecting only "flag" = 'false' or "flag" = 'true' now displays false and true, respectively, on the map.

3.38.0-Master (?) `49d22d1` (2024-05-30)

Not tested due to build errors for src/core/providers/sensorthings/qgssensorthingsutils.cpp

3.38.3-Grenoble `37f9e6e` (2024-09-13)

a.k.a. Release 3.38
a.k.a. final-3_38_3

Expression	Postgres	# in tree	Geopackage	# in tree
`"flag" IS NULL`	null	1	null	1
`"flag" IS NOT NULL`	true & false	5	true & false	5
`"flag" = false`	false	2	false	2
`"flag" != false`	— ¹	2	— ¹	2
`"flag" = true`	true	3	true	3
`"flag" != true`	— ¹	3	— ¹	3
`"flag" = 'false'`	— ²	0	—	0
`"flag" != 'false'`	— ³	0	— ³	0
`"flag" = 'true'`	— ⁴	0	—	0
`"flag" != 'true'`	— ⁵	0	— ⁵	0
`"flag" = 'f'`	—	0	—	0
`"flag" != 'f'`	—	0	—	0
`"flag" = 't'`	—	0	—	0
`"flag" != 't'`	—	0	—	0

Inconsistencies:

Incorrect counts shown for "flag" != false and "flag" != true
All counts for quoted flag values are 0.

Oddities for Postgres:

¹ Nothing shown for "flag" != false or "flag" != true unless both are checked (which results in true & false being shown)
² "flag" = 'false' → Shows false only if "flag" != false is also checked
³ "flag" != 'false' → Shows true only if "flag" != true is also checked
⁴ "flag" = 'true' → Shows true only if "flag" != true is also checked
⁵ "flag" != 'true' → Shows false only if "flag" != false is also checked

Oddities for Geopackage:

¹ Nothing shown for "flag" != false or "flag" != true unless both are checked (which results in true & false being shown)
³ "flag" != 'false' → Shows true only if "flag" != true is also checked
⁵ "flag" != 'true' → Shows false only if "flag" != false is also checked

As mentioned above, it's my understanding that support for 't' and 'f' as boolean values was deprecated with QGIS 3.x so having them return no results in any case seems reasonable (though an error should really be raised if they are used to test boolean fields).

3.39.0-Master `c50c923` (2024-09-13)

Same as 3.38.3-Grenoble

3.39.0-Master `dd907d6` (2024-10-07)

Same as 3.38.3-Grenoble

3.43.0-Master `e634431` (2025-03-14)

Same as 3.38.3-Grenoble

Alex-Kent · 2025-03-20T21:54:17Z

Test results: SQL queries

I turned on Postgres logging and noted the queries used for both the correct and erroneous results. These are detailed below but the TLDR is that the queries used by both versions are identical in all cases.

old: 3.35.0-Master 23f3af4 (2024-01-30) ["working"]
new: 3.43.0-Master e634431 (2025-03-14) ["broken"] – This is [near] the head of the current QGIS master branch.

Tests were run using the project and data mentioned in the previous comment. The headings indicate which rule or rules are active for each test. Old seen and new seen are what is displayed on the map with the numbers in parentheses being the feature counts shown for each rule (in order listed in heading). Note that the feature counts are determined at layer load time with values being computed only for active rules. Only the filter-specific portion of each query is shown as the preceding part of the queries (SELECT …) is identical in all cases).

`"flag" IS NULL`


old:	`AND (("flag" IS NULL))`
new:	`AND (("flag" IS NULL))`
expected:	null (1)
old seen:	null (1)
new seen:	null (1)

`"flag" IS NOT NULL`


old:	`AND (("flag" IS NOT NULL))`
new:	`AND (("flag" IS NOT NULL))`
expected:	true & false (5)
old seen:	true & false (5)
new seen:	true & false (5)

`"flag" = false`


old:	`AND ("flag" IN (FALSE))`
new:	`AND ("flag" IN (FALSE))`
expected:	false (2)
old seen:	false (2)
new seen:	false (2)

`"flag" != false`


old:	`AND (("flag" <> FALSE))`
new:	`AND (("flag" <> FALSE))`
expected:	true (3)
old seen:	true (3)
new seen:	— (2)

`"flag" != false` and `"flag" != true`


old:	`AND ((("flag" <> FALSE) OR ("flag" <> TRUE)))`
new:	`AND ((("flag" <> FALSE) OR ("flag" <> TRUE)))`
expected:	true & false (3, 2)
old seen:	true & false (3, 2)
new seen:	true & false (2, 3)

`"flag" = true`


old:	`AND ("flag" IN (TRUE))`
new:	`AND ("flag" IN (TRUE))`
expected:	true (3)
old seen:	true (3)
new seen:	true (3)

`"flag" != true`


old:	`AND (("flag" <> TRUE))`
new:	`AND (("flag" <> TRUE))`
expected:	false (2)
old seen:	false (2)
new seen:	— (3)

`"flag" = 'false'`


old:	`AND ("flag" IN ('false'))`
new:	`AND ("flag" IN ('false'))`
expected:	false (2)
old seen:	false (2)
new seen:	— (0)

`"flag" != false` and `"flag" = 'false'`


old:	`AND ((("flag" <> FALSE) OR ("flag" = 'false')))`
new:	`AND ((("flag" <> FALSE) OR ("flag" = 'false')))`
expected:	true & false (3, 2)
old seen:	true & false (3, 2)
new seen:	false (2, 0)

`"flag" != 'false'`


old:	`AND (("flag" <> 'false'))`
new:	`AND (("flag" <> 'false'))`
expected:	true (3)
old seen:	true (3)
new seen:	— (0)

`"flag" != true` and `"flag" != 'false'`


old:	`AND ((("flag" <> TRUE) OR ("flag" <> 'false')))`
new:	`AND ((("flag" <> TRUE) OR ("flag" <> 'false')))`
expected:	true & false (2, 3)
old seen:	true & false (2, 3)
new seen:	true (3, 0)

"flag" = 'true'


old:	`AND ("flag" IN ('true'))`
new:	`AND ("flag" IN ('true'))`
expected:	true (3)
old seen:	true (3)
new seen:	— (0)

`"flag" != true` and `"flag" = 'true'`


old:	`AND ((("flag" <> TRUE) OR ("flag" = 'true')))`
new:	`AND ((("flag" <> TRUE) OR ("flag" = 'true')))`
expected:	true & false (2, 3)
old seen:	true & false (2, 3)
new seen:	true (3, 0)

`"flag" != 'true'`


old:	`AND (("flag" <> 'true'))`
new:	`AND (("flag" <> 'true'))`
expected:	false (2)
old seen:	false (2)
new seen:	— (0)

`"flag" != false` and `"flag" != 'true'`


old:	`AND ((("flag" <> FALSE) OR ("flag" <> 'true')))`
new:	`AND ((("flag" <> FALSE) OR ("flag" <> 'true')))`
expected:	true & false (3, 2)
old seen:	true & false (3, 2)
new seen:	false (2, 0)

`"flag" = 'f'`


old:	`AND ("flag" IN ('f'))`
new:	`AND ("flag" IN ('f'))`
expected:	false (2) or — (0)
old seen:	— (0)
new seen:	— (0)

`"flag" != 'f'`


old:	`AND (("flag" <> 'f'))`
new:	`AND (("flag" <> 'f'))`
expected:	true (3) or true & false (5)
old seen:	true (5)
new seen:	— (0)

`"flag" = 't'`


old:	`AND ("flag" IN ('t'))`
new:	`AND ("flag" IN ('t'))`
expected:	true (3) or — (0)
old seen:	— (0)
new seen:	— (0)

`"flag" != 't'`


old:	`AND (("flag" <> 't'))`
new:	`AND (("flag" <> 't'))`
expected:	false (2) or true & false (5)
old seen:	false (5)
new seen:	— (0)

Alex-Kent · 2025-03-21T06:29:43Z

I've isolated the issue to a specific commit. Am evaluating things before commenting further.

Alex-Kent · 2025-03-31T02:06:17Z

The issue was introduced in commit f1f6661e09b, merged in #56369. This commit was intended to require that both arguments to a binary boolean expression have boolean types. This has several issues:

The use of strings and numeric values in boolean expressions is very common in the SQL world (see comment below) and to have them thus interpreted is expected behavior.
The interpretation of strings and numeric values as boolean values has long been supported in QGIS expressions. The implication is that there are likely a lot of filters out there in the wild that depend on such behavior [indeed it was my investigation of an incorrectly-behaving filter that led me to discover this bug].
The code in the aforementioned commit is incorrect in that it assumes that the operation is always == when in fact the code in question is also called for != (a.ka. <>), <, <=, >, and >=.

~~I've coded a fix for this that I will push shortly.~~ [edit: fix is in #61269]

Alex-Kent · 2025-03-31T02:35:16Z

I investigated the behavior of the interpretation of string and numeric values both by Qt (which QGIS relies on for such matters) and by a number of SQL databases typically used with QGIS [based on the list in documentation].

Qt

QGIS uses Qt's QVariant::toBool() method to convert non-boolean types to boolean values. The documented behavior of QVariant::toBool() for each QVariant::userType():

QMetaType::Bool
- true if value is true
- false otherwise
QMetaType::QChar, QMetaType::Double, QMetaType::Int, QMetaType::LongLong, QMetaType::UInt, and QMetaType::ULongLong
- true if the value is non-zero
- false otherwise
QMetaType::QString and QMetaType::QByteArray
- false if its lower-case content is empty, "0" or "false"
- true otherwise
All other variants always return false.

SQL

String conversions for all formats/databases is case-insensitive.

PostgreSQL

true, '1', 't', 'true' → true
false, '0', 'f', 'false' → false

GeoPackage

true, 1, '1' → true
false, 0, '0' → false

SQLite

true, 1 → true
false, 0 → false

Based on documentation, not tested:

SQL Server

1, 'TRUE', non-zero number → true
0, 'FALSE', zero number (0.0, etc.) → false

Implicit conversion to boolean is supported for column types binary, varbinary, char, varchar, nchar, nvarchar, decimal, numeric, float, real, bigint, int, smallint, tinyint, money, smallmoney, and timestamp.

Oracle (official support since release 23)

TRUE, 'true', 'yes', 'on', '1', 't', 'y', non-zero number → true
FALSE, 'false', 'no', 'off', '0', 'f', 'n', zero number → false

The official values above reflect the ad hoc methods used prior to official support being added.

SAP HANA

TRUE → true
FALSE → false

There is no implicit conversion to boolean but values can be explicitly converted:

1, 'true', true → true
0, 'false', false → false

MySQL

1, TRUE → true
0, FALSE → false

MySQL does not have native support for boolean values; the constants TRUE and FALSE are available (evaluating to 1 and 0, respectively) but the 'equivalent' storage type to boolean is tinyint (1 octet). There are no conversion functions to or from "boolean" (neither implicit nor explicit).

…ng implicit value conversion) Fixes qgis#61072

Alex-Kent added the Bug Either a bug report, or a bug fix. Let's hope for the latter! label Mar 19, 2025

agiudiceandrea added the PostGIS data provider label Mar 19, 2025

Alex-Kent changed the title ~~Incorrect results when filtering on PostGIS/PostgreSQL boolean columns~~ REGRESSION: Incorrect results when filtering on PostGIS/PostgreSQL or GeoPackage boolean columns Mar 20, 2025

Alex-Kent added a commit to Alex-Kent/QGIS that referenced this issue Mar 31, 2025

Correct handling of boolean comparison in filter expressions (includi…

229d8a9

…ng implicit value conversion) Fixes qgis#61072

Alex-Kent linked a pull request Mar 31, 2025 that will close this issue

Fixes regression of boolean handling in filter expressions - fixes #61072 #61269

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

REGRESSION: Incorrect results when filtering on PostGIS/PostgreSQL or GeoPackage boolean columns #61072

REGRESSION: Incorrect results when filtering on PostGIS/PostgreSQL or GeoPackage boolean columns #61072

Alex-Kent commented Mar 19, 2025 •

edited

Loading

Alex-Kent commented Mar 20, 2025 •

edited

Loading

Alex-Kent commented Mar 20, 2025 •

edited

Loading

Alex-Kent commented Mar 20, 2025 •

edited

Loading

Alex-Kent commented Mar 21, 2025

Alex-Kent commented Mar 31, 2025 •

edited

Loading

Alex-Kent commented Mar 31, 2025

REGRESSION: Incorrect results when filtering on PostGIS/PostgreSQL or GeoPackage boolean columns #61072

REGRESSION: Incorrect results when filtering on PostGIS/PostgreSQL or GeoPackage boolean columns #61072

Comments

Alex-Kent commented Mar 19, 2025 • edited Loading

What is the bug or the crash?

Steps to reproduce the issue

Versions

PostgreSQL

QGIS (old version, from Ubuntu-supplied Jammy package)

QGIS (current Git master version, built from source)

Active Python plugins

Normal user profile

Supported QGIS version

New profile

Additional context

Alex-Kent commented Mar 20, 2025 • edited Loading

Alex-Kent commented Mar 20, 2025 • edited Loading

Test project / Narrowing down when the issue first appeared

Test project and test data

3.22.4 (Ubuntu-maintained package)

3.23.0-Master fcb8cf0 (2022-01-31)

3.27.0-Master 4bfef0c (2022-08-29)

3.35.0-Master 26798b8 (2024-01-30)

3.35.0-Master 23f3af4 (2024-01-30)

3.38.0-Master (?) 49d22d1 (2024-05-30)

3.38.3-Grenoble 37f9e6e (2024-09-13)

3.39.0-Master c50c923 (2024-09-13)

3.39.0-Master dd907d6 (2024-10-07)

3.43.0-Master e634431 (2025-03-14)

Alex-Kent commented Mar 20, 2025 • edited Loading

Test results: SQL queries

"flag" IS NULL

"flag" IS NOT NULL

"flag" = false

"flag" != false

"flag" != false and "flag" != true

"flag" = true

"flag" != true

"flag" = 'false'

"flag" != false and "flag" = 'false'

"flag" != 'false'

"flag" != true and "flag" != 'false'

"flag" = 'true'

"flag" != true and "flag" = 'true'

"flag" != 'true'

"flag" != false and "flag" != 'true'

"flag" = 'f'

"flag" != 'f'

"flag" = 't'

"flag" != 't'

Alex-Kent commented Mar 21, 2025

Alex-Kent commented Mar 31, 2025 • edited Loading

Alex-Kent commented Mar 31, 2025

Qt

SQL

PostgreSQL

GeoPackage

SQLite

SQL Server

Oracle (official support since release 23)

SAP HANA

MySQL

Alex-Kent commented Mar 19, 2025 •

edited

Loading

QGIS (current Git `master` version, built from source)

Alex-Kent commented Mar 20, 2025 •

edited

Loading

Alex-Kent commented Mar 20, 2025 •

edited

Loading

3.23.0-Master `fcb8cf0` (2022-01-31)

3.27.0-Master `4bfef0c` (2022-08-29)

3.35.0-Master `26798b8` (2024-01-30)

3.35.0-Master `23f3af4` (2024-01-30)

3.38.0-Master (?) `49d22d1` (2024-05-30)

3.38.3-Grenoble `37f9e6e` (2024-09-13)

3.39.0-Master `c50c923` (2024-09-13)

3.39.0-Master `dd907d6` (2024-10-07)

3.43.0-Master `e634431` (2025-03-14)

Alex-Kent commented Mar 20, 2025 •

edited

Loading

`"flag" IS NULL`

`"flag" IS NOT NULL`

`"flag" = false`

`"flag" != false`

`"flag" != false` and `"flag" != true`

`"flag" = true`

`"flag" != true`

`"flag" = 'false'`

`"flag" != false` and `"flag" = 'false'`

`"flag" != 'false'`

`"flag" != true` and `"flag" != 'false'`

`"flag" != true` and `"flag" = 'true'`

`"flag" != 'true'`

`"flag" != false` and `"flag" != 'true'`

`"flag" = 'f'`

`"flag" != 'f'`

`"flag" = 't'`

`"flag" != 't'`

Alex-Kent commented Mar 31, 2025 •

edited

Loading