Hi all,
we did a bit of debugging. The nullability from the SQL view is
determined in org.geotools.jdbc.JDBCFeatureSource.getColumnMetadata
using the ResultSet.getMetaData().isNullable(i) call. We did some
experiments and googling and it looks like this is not reliable for the
Postgres JDBC driver. For the scenario below isNullable thinks, that the
columns from the left join are not nullable.
In this case we hoped the feature type customization feature could help.
Even after setting all columns as nillable Geoserver still converts null
values to zeros. Am I correct, that this setting should solve the
problem? We might look into this more, could you please point us to the
classes that could cause this?
best regards,
Gyorgy Tomcsanyi
On 13. 3. 2023 16:28, Gyorgy Tomcsanyi wrote:
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
_______________________________________________
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
