Bug report #17175

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

Added by Regis Haubourg almost 3 years ago. Updated over 2 years ago.

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
);

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 over 2 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 over 2 years ago

Merge pull request #6413 from mhugo/master

Fix call to representValue in identifydialog (refs #17175)

History

#1 Updated by Regis Haubourg almost 3 years ago

Added a project and DDL to reproduce that

#2 Updated by Regis Haubourg over 2 years ago

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

#3 Updated by Jürgen Fischer over 2 years ago

#4 Updated by Vincent Mora over 2 years ago

Cannot reproduce on qgis 2.99.

#5 Updated by Hugo Mercier over 2 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 over 2 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