ogr2ogr: Conversion from PostGIS to GeoPackage fails when joined values are NULL

[ilosuna]

Expected behavior and actual behavior

I want to create a GeoPackage from a PostGIS database by using ogr2ogr with an SQL query. The query contains JOINs that might return NULL values so I would expect a GeoPackage table with NULL values. However, an error occurs in case NULL is returned:

ERROR 1: failed to execute insert : NOT NULL constraint failed: test.type
ERROR 1: Unable to write feature 1 from layer sql_statement.
ERROR 1: Terminating translation prematurely after failed
translation from sql statement.
ERROR 1: sqlite3_exec(CREATE TRIGGER "trigger_insert_feature_count_test" AFTER INSERT ON "test" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('test'); END;) failed: no such table: main.test
ERROR 1: sqlite3_exec(CREATE TRIGGER "trigger_delete_feature_count_test" AFTER DELETE ON "test" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE lower(table_name) = lower('test'); END;) failed: no such table: main.test

Steps to reproduce the problem

My database structure and data:

CREATE TABLE point_type (
    id serial PRIMARY KEY,
    name CHARACTER VARYING NOT NULL
);

CREATE TABLE point (
    id serial primary key,
    name character varying NOT NULL,
    fk_point_type_id INTEGER REFERENCES point_type (id) ON DELETE SET NULL,
    geom geometry(Point,4326)
);

INSERT INTO point_type (name) VALUES ('A');
INSERT INTO point_type (name) VALUES ('B');
INSERT INTO point_type (name) VALUES ('C');

INSERT INTO point (name, fk_point_type_id, geom) VALUES ('Zero/Zero', 2, ST_GeomFromText('POINT(0 0)', 4326));
INSERT INTO point (name, fk_point_type_id, geom) VALUES ('One/One', NULL, ST_GeomFromText('POINT(1 1)', 4326));

Now I can query my points with their types...
SELECT point.name, point_type.name AS type, geom FROM point LEFT JOIN point_type ON point_type.id=point.fk_point_type_id;

...and get this result:

name	type	geom
Zero/Zero	B	0101000020E610...
One/One	[null]	0101000020E610...

If I pass this query to ogr2ogr I get the above mentioned error:

ogr2ogr -f "GPKG" test.gpkg PG:"dbname='gdaltest' host='localhost' port=5432 user='gdaltest' password='gdaltest'" -nln 'test' -sql "SELECT point.name, point_type.name AS type, geom FROM point LEFT JOIN point_type ON point_type.id=point.fk_point_type_id;"

Operating system

Ubuntu 18.04.1 LTS 64 bit

GDAL version and provenance

GDAL 2.2.3 from the Ubuntu bionic sources