#105 SQL dump for SQLite uses wrong representation of Infinity and NaN

[Michal Kaut]

Summary: Dumping a database to SQL for SQLite uses INF and NAN for infinity and NaN. However, these values are not recognized by SQLite, so import fails. Replacing these with 'Infinity' and 'NaN' (incl. quotes) fixes the issue.

Application version: 2.5.2.771

Steps to reproduce: Insert some Inf, -Inf, and/or NaN values into the database. Then open it in MDB Admin and select Database->Tools->Dump and then SQLite with values. Then import the resulting SQL file into a fresh SQLite database.

Expected results: The final step should result in an SQLite database.

Actual results: The final step results in "Error: near line xxxx: no such column: INF/NAN" error for each INF/NAN value in the database.

[Marciel Degasperi]

I haven't found a way to insert NaN or infinity into an Access database. Can you send me your database with these conditions?

[Michal Kaut]

I attach a database that includes some positive infinities (1.#INF) and negative
NaNs (-1.#IND).
From a short search on the internet, there should also be a quiet NaN (1.#QNAN)
and possibly even a signalling NAN (1.#SNAN) - all these coming with both
positive and negative variant.

Unfortunately, I do not know what is a correct way of representing these values
in an SQL script, or even if there is one (I suspect there might not be).
One option could be to detect these and ask a user how to represent them, or at
least issue a warning saying that these values appear in the SQL script, so user
know it should be handled...

After more searching, I found a quote from MySQL documentation
(https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html) that says that
"|-inf|, |+inf|, or |NaN [..] |are not supported because the SQL standard
defines them as invalid values for |FLOAT|
https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html or |DOUBLE|
https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html".
But, this does not solve the issue that they can actually appear in an Access
database...

Best regards,
Michal Kaut

On 07/09/2021 21:12, Marciel Degasperi wrote:

I haven't found a way to insert NaN or infinity into an Access database. Can
you send me your database with these conditions?

---

[tickets:#105] https://sourceforge.net/p/mdbadmin/tickets/105/ SQL dump for
SQLite uses wrong representation of Infinity and NaN

Status: open
Created: Tue Aug 03, 2021 07:38 AM UTC by Michal Kaut
Last Updated: Tue Aug 03, 2021 07:38 AM UTC
Owner: nobody

Summary: Dumping a database to SQL for SQLite uses INF and NAN for infinity
and NaN. However, these values are not recognized by SQLite, so import fails.
Replacing these with 'Infinity' and 'NaN' (incl. quotes) fixes the issue.

Application version: 2.5.2.771

Steps to reproduce: Insert some Inf, -Inf, and/or NaN values into the
database. Then open it in MDB Admin and select Database->Tools->Dump and then
SQLite with values. Then import the resulting SQL file into a fresh SQLite
database.

Expected results: The final step should result in an SQLite database.

Actual results: The final step results in "Error: near line xxxx: no such
column: INF/NAN" error for each INF/NAN value in the database.

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/mdbadmin/tickets/105/
https://sourceforge.net/p/mdbadmin/tickets/105/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/
https://sourceforge.net/auth/subscriptions/

[Marciel Degasperi]

Fix in version 2.5.4.

Since there is no representation for NaN and Infinity in SQL, I had these values converted to NULL, with a little comment showing the original value.

Thanks for reporting.