Unexpected empty string as NULL behavior after environment migration.

[irieill]

After migrating my (Windows) environment from

OGR_FDW: 1.0.2 PostgreSQL: 9.3 w64 Built: 20161016 GIT_REPO: https://github.com/pramsey/pgsql-ogr-fdw GIT_BRANCH: v1.0.2 GIT_REVISION: de8b90e34fc64c334674d640db7bd2ec21cb2412 GDAL_VER: 2.1.1

to at least

OGR_FDW: 1.0.8 PostgreSQL: 9.6 w64 Built: 20190520 GIT_REPO: https://github.com/pramsey/pgsql-ogr-fdw GIT_BRANCH: GIT_REVISION: GDAL_VER: 2.2.4

I encounter unexpected behavior on reading SQLServer (2016) in that empty strings are returned as NULL after migration.

On SQLServer side I have the following table:

CREATE TABLE [dbo].[Test](
    [key] [nvarchar](100) NOT NULL,
    [value] [nvarchar](100) NOT NULL
)

On PostgreSQL side I have the following foreign server and table:

CREATE SERVER sqlserver
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS (
        datasource 'MSSQL:Server=localhost\\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server;UID=Foo;PWD=Bar;Tables=dbo.Test'
        , format 'MSSQLSpatial'
    );

CREATE FOREIGN TABLE test(
    key character varying not NULL COLLATE pg_catalog."default",
    value character varying not NULL COLLATE pg_catalog."default"
)
SERVER sqlserver
OPTIONS (layer 'dbo.Test');

When selecting data every empty string is reported as NULL. Even more confusing:

select count(*) from test; -- returns 123
select count(*) from test where value is not null; -- returns 89
select count(*) from test where value is null; --returns 0

I have searched and reviewed code several hours now and learned that with GDAL 2.2 something changed that is maybe related. Since ogw_fdr is my entry point, I opend this issue not knowing if it even is related to ogw_fdr. But I am stuck and appreciate any help.

I also tested having the same problem with

OGR_FDW: 1.1.0 PostgreSQL: 12 w64 Built: 20210608 GIT_REPO: https://github.com/pramsey/pgsql-ogr-fdw GIT_BRANCH: GIT_REVISION: GDAL_VER: 3.2.3

[pramsey]

If you crank your client_min_messages value into the "debug" level and also turn on CPL_DEBUG (see the documentation) you can at least see what queries are being sent back down the wire. It's entirely possible that earlier GDAL was returning NULLs as empty strings, and that behaviour flipped.

I like your count tests, might also want to test where value = '' and where value != '' to see what numbers pop out.

I don't know that I'll be able to fix anything, it's probably a driver quirk. But if you think it's something else, see if you can replicate the issue in a file-based source, like a shape file or csv.

[irieill]

I turned on CPL_DEBUG with

CREATE SERVER sqlserver
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS (
        datasource 'MSSQL:Server=localhost\\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server;UID=Foo;PWD=Bar;Tables=dbo.Table'
        , format 'MSSQLSpatial'
    , config_options 'CPL_DEBUG=ON'
    );

and also set debug level with

SET client_min_messages = debug2;

This is what i get for the extended tests (I constructed numbers in my OP to demonstrate the problem - now these are the current real numbers)

select count(*) from test; --48

DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  OGR SQL: 
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: ExecuteSQL(select [key], [value] from dbo.Test)
DEBUG:  processed 48 rows from OGR
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: 48 features read on layer 'Test'.
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)

select count(*) from test where value is not null; --46

DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  OGR SQL: (value IS NOT NULL)
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: ExecuteSQL(select [key], [value] from dbo.Test where ((value IS NOT NULL)))
DEBUG:  processed 48 rows from OGR
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: 48 features read on layer 'Test'.
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)

select count(*) from test where value is null; --0

DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  OGR SQL: (value IS NULL)
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: ExecuteSQL(select [key], [value] from dbo.Test where ((value IS NULL)))
DEBUG:  processed 0 rows from OGR
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)

select count(*) from test where value != ''; --46

DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  OGR SQL: (value <> '')
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: ExecuteSQL(select [key], [value] from dbo.Test where ((value <> '')))
DEBUG:  processed 46 rows from OGR
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: 46 features read on layer 'Test'.
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)

select count(*) from test where value = ''; --0

DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  OGR SQL: (value = '')
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] ODBC: SQLDriverConnect(Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;)
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: Table Test has no identified FID column.
DEBUG:  GDAL None [0] GDAL: GDALOpen(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0) succeeds as MSSQLSpatial.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 2 FIELDS out of 2 PGSQL COLUMNS
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: ExecuteSQL(select [key], [value] from dbo.Test where ((value = '')))
DEBUG:  processed 2 rows from OGR
DEBUG:  GDAL None [0] OGR_MSSQLSpatial: 2 features read on layer 'Test'.
DEBUG:  GDAL None [0] ODBC: SQLDisconnect()
DEBUG:  GDAL None [0] GDAL: GDALClose(MSSQL:Server=localhost\SQLEXPRESS2016;Database=FooBar;Driver=SQL Server Native Client 11.0;UID=Foo;PWD=Bar;Tables=dbo.Test, this=00000000027110A0)

From my point of view value is not null and value = '' test are smelly. The statements as well as the processed rows and feature counts look good. The problem is with returned row count and consequently row result sets. Do you have any ideas where this is originating from?

[irieill]

I replicated the unexpected behavior with a simple file-based CSV datasource. Especially the explicit empty strings in the last line should not be returned as NULL. Again the counting stuff is weird.

A,B,C,D,E
1,2,3,4,5
1,,3,,5
,2,,4,
"","","","",""

CREATE SERVER test_csv FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
  datasource 'CSV:C:/Daten/test.csv'
  , format 'CSV'
  , config_options 'CPL_DEBUG=ON'
);

CREATE FOREIGN TABLE test_csv (
  a varchar,
  b varchar,
  c varchar,
  d varchar,
  e varchar
) SERVER test_csv
OPTIONS (layer 'test');

select * from test_csv; --4

DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086D5A0) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  OGR SQL: 
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086D5A0)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086DC00) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  processed 4 rows from OGR
DEBUG:  GDAL None [0] CSV: 4 features read on layer 'test'.
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086DC00)

select count(*) from test_csv where a is not null; --2

DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086D6B0) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  OGR SQL: (A IS NOT NULL)
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086D6B0)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086DC00) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  processed 4 rows from OGR
DEBUG:  GDAL None [0] CSV: 4 features read on layer 'test'.
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086DC00)

select count(*) from test_csv where a is null; --0

DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086D270) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  OGR SQL: (A IS NULL)
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086D270)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086DE20) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  processed 0 rows from OGR
DEBUG:  GDAL None [0] CSV: 4 features read on layer 'test'.
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086DE20)

select count(*) from test_csv where a != ''; --2

DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086DC00) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  OGR SQL: (A <> '')
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086DC00)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086D050) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  processed 2 rows from OGR
DEBUG:  GDAL None [0] CSV: 4 features read on layer 'test'.
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086D050)

select count(*) from test_csv where a = ''; --0

DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086DC00) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  OGR SQL: (A = '')
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086DC00)
DEBUG:  GDAL config option 'CPL_DEBUG' set to 'ON'
DEBUG:  GDAL None [0] GDAL: GDALOpen(CSV:C:/Daten/test.csv, this=000000000086D160) succeeds as CSV.
DEBUG:  ogrReadColumnData matched 0 FID, 0 GEOM, 5 FIELDS out of 5 PGSQL COLUMNS
DEBUG:  processed 2 rows from OGR
DEBUG:  GDAL None [0] CSV: 4 features read on layer 'test'.
DEBUG:  GDAL None [0] GDAL: GDALClose(CSV:C:/Daten/test.csv, this=000000000086D160)

[irieill]

As I replicated with a file based data source i do not think that it is a driver based issue. From my point of view in both tests the output DEBUG: processed % rows from OGR is perfectly ok with respect to the expected result. So ogr_fdw seems to be working fine, but what reason for the final count/result is not valid? Do you have any ideas where this is originating from? I am not familiar with the technology stack between PostgreSql and ogr_fdw. So any advise where to ask/report would be very helpful.

[pramsey]

Thanks for the extra info. I'll check into it, now we have a replicator I can run. I bet there's a mapping from '' into NULL somewhere, but I also bet it's there for a reason for some other driver, so it will have to be handled carefully.

[pramsey]

OK, for posterity, I think I see what's going on.

https://github.com/pramsey/pgsql-ogr-fdw/blob/2a01fda95b8189e373584ec5b058ce7dd11cb2e3/ogr_fdw.c#L1900-L1908

So, we have a test that's turning empty string into NULL. I'm pretty sure the reason that is there is because we are also handling non-string types in this block, and for formats that don't have the concept of NULL, a NULL number will come through as an empty string. This is a problem when the input is a string... of course, then we end up being unable to represent NULL for strings for some formats, but for those formats... 🤷

Anyways, pretty sure the fix is to just add in a test that only goes NULL when dealing with non-string inputs.