Cleared feature attributes not acting like they have been cleared, and preventing saving of layer

[CoryAlbrecht]

On clearing a value from a PostGIS field with the clear button in the Feature Attributes form, QGIS later tries to save the text of the SQL default clause for that field, with the following errors on the console:

../../src/core/qgsmessagelog.cpp:29 : (logMessage) [14405ms] 2020-10-02T15:17:52 PostGIS[1] Erroneous query: UPDATE "lawta"."timeline_level1" SET "stripe_rgb"='NULL::character varying',"info_url"=E'{""}',"flag_url"=E'{""}' WHERE "id"=18 AND "feature_uuid"::text='ef13fbaa-4049-48de-819c-f95b7cb89b94' returned 7 [ERROR:  value too long for type character varying(8)
]
../../src/core/qgsvectordataprovider.cpp:740 : (pushError) [1ms] PostGIS error while changing attributes: ERROR:  value too long for type character varying(8)

../../src/core/qgsmessagelog.cpp:29 : (logMessage) [0ms] 2020-10-02T15:17:52 [1] Commit errors:
  ERROR: 1 attribute value change(s) not applied.
  
  Provider errors:
      PostGIS error while changing attributes: ERROR:  value too long for type character varying(8)

If you open up the attribute table, you can see that the field you cleared now has the SQL default clause in non-italic dark text, just like other entered variables, unlike features where the default value appears in italic and light grey.

Newly added features where the problematic field was not given a value save as expected.

How to Reproduce

1. Create PostGIS layer table with a field similar to stripe_rgb character varying(8) COLLATE pg_catalog."default" DEFAULT NULL::character varying.
2. Using the Feature Attributes form, add a avlue to a feature for that form
3. Save the layer.
4. Open the Feature Attributes form and clear that field.
5. Try to save the layer and see the error appears

QGIS and OS versions

QGIS version	3.15.0-Master	QGIS code revision	ef7ecbf
Compiled against Qt	5.12.8	Running against Qt	5.12.8
Compiled against GDAL/OGR	3.0.4	Running against GDAL/OGR	3.0.4
Compiled against GEOS	3.8.0-CAPI-1.13.1	Running against GEOS	3.8.0-CAPI-1.13.1
Compiled against SQLite	3.31.1	Running against SQLite	3.31.1
PostgreSQL Client Version	12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)	SpatiaLite Version	4.3.0a
QWT Version	6.1.4	QScintilla2 Version	2.11.2
Compiled against PROJ	6.3.1	Running against PROJ	Rel. 6.3.1, February 10th, 2020
OS Version	Ubuntu 20.04.1 LTS	This copy of QGIS writes debugging output.
Active python plugins	CanadianWebServices; qgis2web; quick_map_services; Qgis2threejs; qgiscloud; postgisQueryBuilder; QuickOSM; OSMDownloader; OnlineRoutingMapper; processing; db_manager; MetaSearch

Ubuntu 20.04

Additional context
As mentioned, newly added features where the problematic field was not given a value save as expected, but when Symbology is set to Rule-based, with the rules being based on this problematic field such as being NULL or not, as in the window shown below, the features get drawn as if the field has data, is not NULL, even though nothing was ever entered for it.

[SebastienPeillet]

Hello,

you need to activate the default values evaluation on provider side in your project configuration. From Qgis menu:

Project > Properties... > DataSources > Evaluate default values on provider side

Instead of NULL::character varying, postgres will evaluate it as Null value and it will work.

[elpaso]

@SebastienPeillet setting that option looks more like a workaround to me. Do we all (@gioman) agree that this is still a bug?

[SebastienPeillet]

@elpaso If it's still a bug, this change can fix the error without activate default values evaluation. But for some reason the cell is still written in black instead of grey. Attribute table needs to be reloaded to appear in grey.

[gioman]

Do we all (@gioman) agree that this is still a bug?

pretty much.

[CoryAlbrecht]

@SebastienPeillet Are you sure? As the console log shows, th SQL is

SET "stripe_rgb"='NULL::character varying'

rather than

SET "stripe_rgb"=NULL::character varying

which are two vet different things. The second is an actual NULL value that is typecast, the first is a string value that starts with the word "NULL".

When QGIS doesn't have the provider side evaluate default values, it still shouldn't be sticking the text of the default value into the SQL to run as the value to be set.

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[CoryAlbrecht]

@SebastienPeillet Are you sure? As the console log shows, th SQL is

SET "stripe_rgb"='NULL::character varying'

rather than

SET "stripe_rgb"=NULL::character varying

which are two vet different things. The second is an actual NULL value that is typecast, the first is a string value that starts with the word "NULL".

When QGIS doesn't have the provider side evaluate default values, it still shouldn't be sticking the text of the default value into the SQL to run as the value to be set.

@SebastienPeillet Can you comment on this difference?

[troopa81]

I have a fix for this one. I stll have to write tests and also try to fix the grey/black difference and I'll propose a PR

[troopa81]

Just to be complete, #45176 fixes the main issue, meaning the error when commiting on Postgres. There is still the grey/dark issue and other tiny issues on how the attribute table manage default and null values. I will try to tackle them in separate issues/PRs