DBeaver: `NumberFormatException` when importing data into table with boolean column

[hlcianfagna]

create table repro (flag BOOLEAN);

Then create a .csv file and try to import it from DBeaver with the default "Data load settings" with "Skip bind values during insert" not selected

flag
0

In CrateDB this will fail with

NumberFormatException in java.base/java.math.BigDecimal.<init>(BigDecimal.java:703).CXX000.FSQLExceptions.java.ResToCrateException.L152.Wio.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:152)

in PostgreSQL it works
(DBeaver also succeeds if the column is INT instead of BOOLEAN or if we select the option to skip binding)

I can get the same stack trace in Java if I use a setBigDecimal against a PreparedStatement instead of a setBoolean , however this fails also against pgqsl while DBeaver works against pgsql.

So perhaps this something about pg_type not having composite type entries for the tables or about pg_description not having an entry for the boolean data type?

[amotl]

Hi @hlcianfagna. Thanks for your report.

Can I humbly ask you to evaluate the problem closer, maybe together with @surister, @seut, or @proddata, and maybe derive or transfer this issue into crate/crate, also tagging it with tool: DBeaver, when applicable?

• See also [Meta] DBeaver / Datagrip - PG connector compatibility tracker  crate#12101.

[hlcianfagna]

I did a quick test, this still occurs with the latest version of DBeaver and the latest nightly of CrateDB.

[amotl]

Hi @hlcianfagna. I guess you have been using the PostgreSQL JDBC Driver in your experiments? Does the problem also happen when using the CrateDB JDBC Driver?

Because PostgreSQL compatibility on so many details is currently out of reach, we have been refurbishing the messaging about the CrateDB JDBC Driver, planning to focus the DBeaver integration on that one, what Blog: Use CrateDB With DBeaver is also doing, and what we also have been doing on the documentation for DataGrip.

• https://cratedb.com/docs/jdbc/
• Integrate: Add dedicated page about DBeaver cratedb-guide#157

[amotl]

In the spirit of the above proposal, to focus on the CrateDB JDBC Driver again, which hopefully works in more occasions than the one for PostgreSQL, let me reference DBeaver plugin descriptions of CrateDB (Legacy) vs. CrateDB.

Because being thoroughly compatible with vanilla PostgreSQL is difficult and will always be a moving target for CrateDB, maybe we should remove the PostgreSQL-based item again, and also promote "CrateDB (Legacy)" to canonical "CrateDB" here, to reduce confusion and friction for all users of DBeaver and CrateDB.

This might partly revert the updates submitted by @proddata the other day,

Also this moves the generic CrateDB connector using the CrateDB specific JDBC Driver to legacy, as with recent CrateDB versions the default recommendations is to use the standard PostgreSQL JDBC Driver.

• Add CrateDB variant of PostgreServer to improve compatibility dbeaver/dbeaver#20549

so I'd also like to involve him into this discussion, in order to possibly collectively agree to the conclusion that the CrateDB JDBC Driver should NOT be declared legacy, but should be promoted and avertised to be the primary JDBC connector library to use with CrateDB instead.

/cc @kneth, @surister

[proddata]

agree to the conclusion that the CrateDB JDBC Driver should NOT be declared legacy, but should be promoted and avertised to be the primary JDBC connector library to use with CrateDB instead.

Caution

The CrateDB JDBC driver is outdated and shall only be used with caution. Until there is a decision to bring this back to full maintenance, no tool or integration shall use this at all.

If there is something not working in DBeaver, this shall be fixed by customizing the CrateDB connection there (which is possible) or adding functionality to CrateDB.

[proddata]

This issue has been fixed with CrateDB 5.9.1 and later versions

related issue: crate/crate#16797
related PR: crate/crate#16806

Fixed an issue that would cause an error to be thrown when using PostgreSQL wire protocol and attempting to insert 0 value for a numeric NUMERIC, or trying to get 0 numeric value from a query, e.g.:

cr> create table repro (flag BOOLEAN);
CREATE OK, 1 row affected (0.164 sec)
cr> SELECT * FROM repro;
+------+
| flag |
+------+
+------+
SELECT 0 rows in set (0.064 sec)

cr> SELECT * FROM repro;
+-------+
| flag  |
+-------+
| FALSE |
+-------+
SELECT 1 row in set (0.013 sec)