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