Bug report #17175
Relation reference widget triggers SQL syntax error with UUID fields in Postgres
Status: | Closed | ||
---|---|---|---|
Priority: | Normal | ||
Assignee: | Denis Rouzaud | ||
Category: | Forms | ||
Affected QGIS version: | 2.18.13 | Regression?: | No |
Operating System: | Easy fix?: | No | |
Pull Request or Patch supplied: | No | Resolution: | fixed/implemented |
Crashes QGIS or corrupts data: | No | Copied to github as #: | 25074 |
Description
The case is pretty subtle:
- create two tables named "Child" and "Parent" with UUID Primary keys
- add a Foreign key in "Child" table pointing to the parent ID (allowing null values)
- In QGIS, add the same relation and use a reference relation widget for the FK field.
- Check the "Allow Null" box
- watch pg logs
- activate transaction groups
- create a child feature, and DO NOT create or affect any parent related feature. Validate.
- use identify tool to open the child feature form again
This triggers a syntax error in QGIS when QGIS tries to populate the new form with parent features values.
QGIS opens a cursor to fetch values with a (translated) error:
DECLARE qgis_235 BINARY CURSOR WITH HOLD FOR SELECT st_asbinary("geometrie",'NDR'),"id"::text, ...field list here ... FROM "public"."parent" WHERE ("id" = 'NULL') Result: 7 (ERROR: invalid syntax for uuid : « NULL »
In fact this should be converted to ` "id" is NULL ` to avoid that.
unchecking the box solves the issue. (Which makes me wonder what is the use of that checkbox)
This DDL script can help reproducing the issue.
create table parent ( id uuid NOT NULL, -- Identifiant unique. Lien vers la table ouvrepere comment character varying, -- Commentaire sur la sécurité du personnel geom geometry(LineString,2154), CONSTRAINT parent_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); create table child_geo ( id uuid NOT NULL, comment character varying, fk_parent uuid, -- parent fk id geom geometry(Point, 2154), CONSTRAINT child_geo_pkey PRIMARY KEY (id), CONSTRAINT child_geo_parent_fk FOREIGN KEY (fk_parent) REFERENCES public.parent(id) MATCH SIMPLE ON UPDATE SET NULL ON DELETE SET NULL ) WITH ( OIDS=FALSE );
Related issues
Associated revisions
Fix call to representValue in identifydialog (refs #17175)
This should fix the problem of queries with WHERE (id = 'NULL') when the
string 'NULL' cannot be converted to integer / uuid, as seen with
relation reference widgets.
History
#1 Updated by Regis Haubourg over 7 years ago
- File relation_widget_issue_with_uuid.qgs added
- File relations_qgis.sql added
Added a project and DDL to reproduce that
#2 Updated by Regis Haubourg about 7 years ago
I found a workaround. Just casting uuid fields to text seems to work.
#3 Updated by Jürgen Fischer almost 7 years ago
- Related to Bug report #17535: Postgresql: empty SAVEPOINTS added
#4 Updated by Vincent Mora almost 7 years ago
Cannot reproduce on qgis 2.99.
#5 Updated by Hugo Mercier almost 7 years ago
No need for uuid type.
With two postgis tables like this:
create table a(id int primary key, name text, id_b int, geom geometry(point, 4326)); insert into a values (1, 'Oki', NULL, 'srid=4326; point(1 1)'::geometry); -- no link to B here create table b(id int primary key, name txt); insert into b values (1, 'BB');
And a project with a relation declared between a and b on a.id_b -> b.id
And a relation reference widget on a.id_b.
When using the identify tool on a, it will request b with a query like "FROM b WHERE (id = 'NULL')". Since 'id' is integer, it will try to convert 'NULL' to an integer, which raises an exception.
I think it comes from:
https://github.com/qgis/QGIS/blob/master/src/app/qgsidentifyresultsdialog.cpp#L613
where
QString value2 = representValue( vlayer, setup, fields.at( i ).name(), value );
should be replaced by
QString value2 = representValue( vlayer, setup, fields.at( i ).name(), attrs.at( i ) );
PR coming (compiling ...)
#6 Updated by Hugo Mercier almost 7 years ago
- Status changed from Open to Closed
- Resolution set to fixed/implemented
Fixed in 2.18 and 3.0
#7 Updated by Regis Haubourg almost 7 years ago
Nice !
PR was : [[https://github.com/qgis/QGIS/pull/6419]]