Bug report #17175

Relation reference widget triggers SQL syntax error with UUID fields in Postgres

Added by Regis Haubourg over 5 years ago. Updated almost 5 years ago.

Assignee:Denis Rouzaud
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


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)

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


relation_widget_issue_with_uuid.qgs (26 KB) Regis Haubourg, 2017-09-21 03:16 PM

relations_qgis.sql (1.49 KB) Regis Haubourg, 2017-09-21 03:16 PM

Related issues

Related to QGIS Application - Bug report #17535: Postgresql: empty SAVEPOINTS Closed 2017-11-23

Associated revisions

Revision 5e70e962
Added by Hugo Mercier almost 5 years ago

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.

Revision e06e95c2
Added by Hugo Mercier almost 5 years ago

Merge pull request #6413 from mhugo/master

Fix call to representValue in identifydialog (refs #17175)


#1 Updated by Regis Haubourg over 5 years ago

Added a project and DDL to reproduce that

#2 Updated by Regis Haubourg about 5 years ago

I found a workaround. Just casting uuid fields to text seems to work.

#3 Updated by Jürgen Fischer about 5 years ago

#4 Updated by Vincent Mora almost 5 years ago

Cannot reproduce on qgis 2.99.

#5 Updated by Hugo Mercier almost 5 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:


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 5 years ago

  • Status changed from Open to Closed
  • Resolution set to fixed/implemented

Fixed in 2.18 and 3.0

Also available in: Atom PDF