NULL is different from empty string in the SQL Standard and it almost every database system, including H2.

@manticore-projects
Why #3786 was implemented in that way?

At least PostgreSQL and H2 distinguish nulls from empty strings in the following way:

id,columnWithNull,columnWithEmptyString
1,,""

After that PR H2 reads both columns as NULL.

@ctabin
Thank you for your contribution, but I think we need to restore old correct behavior at least by default instead of introduction of additional quirks.

If there are real-world use cases where "" needs to be processed as NULL and there are no other ways, these cases should use some new configuration option. If there are no such cases, PR with a breaking change should be simply reverted from my point of view.

Greetings!

Please check this details of the original case. I am aware that NULL != "" however when reading a quoted CSV column with Decimals and there is a NULL value, it would show as "", e. g.:

"1234.45"
""

This caused an exception before which I have fixed. See example #3785
NULL behavior is not specific in CSV.

Do you really need to write numbers with quotes?

Do you really need to write numbers with quotes?

I do not need to, but I have also no control over it since both is allowed and usually "quoting" is the safest option.
All spreadsheet programs I have tried offer quoting and read quoted decimals (incl. NULLs) without any problem.

The problem is not H2, but the CSV spec -- however I think if the spread sheet program can deal with it, H2 must find a way.

Please, add a new configuration option for your change, something like quotedNulls. And this option needs to be disabled by default. If this option is enabled, you need to use normal readNull(String) in Csv.readValue() in a branch with quoted string.

Spreadsheets are not database systems, their columns with numeric or any other formats may still contain strings as well and they don't really have nulls.

CSV is used not only with these spreadsheets, some people use them for other purposes.

Hi @katzyn and @manticore-projects, thanks for jumping in.

Indeed #3786 introduces a breaking change but since it was accepted, I just wanted to minimize the impact of it and allow to at least give a possible way to keep the old behavior (by explicitly set the option nullString=NULL in the csv read/write options), since we cannot update to 2.2.220 because we need to have empty strings instead of NULL values when loading data from CSV (our only solution otherwise would be to completely rewrite the dump/loading of CSVs from/to H2 ourselves).

Let me know if you want to rollback #3786 and then implement the quotedNulls flags or if directly a new PR would be good enough.

Replaced by #3842