Hello all,
I have encountered a problem when migrating SQL view layers from an old
project using Geoserver 2.14.0 with Oracle to Geoserver 2.22.2 with
Postgres. The issue is that Geoserver incorrectly assumes, that some of
the attributes in the query are not nillable. This results in a
conversion of null values to 0 (for integers), which is not what we
want. It affects the styles, that expect null values and also the WMS
GetFeatureInfo calls get incorrect data. I tried the new feature type
customization option, to force the attributes to be nillable, but it did
not help. I have managed to replicate this using a minimal example and
it seems to be caused by a query containing a left join, where the table
on the right side of the join has "not null" columns. If I remove the
"not null" constraint the SQL view works correctly. Did anyone else
experience this? Is this a bug?
Following is an example to reproduce the issue:
First we create two tables with some data:
create table table1 (
id integer not null,
value integer not null
);
create table table2 (
id integer not null,
geom geometry(Point, 4326)
);
insert into table1 values (1, 11);
insert into table1 values (2, 22);
insert into table1 values (3, 33);
insert into table2 values (1, ST_MakePoint(1,1));
insert into table2 values (2, ST_MakePoint(2,2));
insert into table2 values (3, ST_MakePoint(3,3));
insert into table2 values (4, ST_MakePoint(4,4));
Here is the SQL query used to create the layer in Geoserver:
select table1.id, table1.value, table2.geom from table2 left join table1
on (table1.id=table2.id)
When the layer is created in Feature type details the properties id and
value are incorrectly marked as not nillable. The result can also be
tested in layer preview, by clicking on the rightmost point. Expected
result is null id and value, but Geoserver returns 0 for both.
best regards,
Gyorgy Tomcsanyi
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
If you want to request a feature or an improvement, also see this:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
