Infinity values in SQLite

[reconka]

Hi,

I noticed the IOS application crash when I try to select rows which contains infinite number.

here is my simple test:

document.addEventListener("deviceready", onDeviceReady, false);
      function onDeviceReady() {
        var db = window.sqlitePlugin.openDatabase("Database", "1.0", "Demo", -1);

        db.transaction(function(tx) {
          tx.executeSql('DROP TABLE IF EXISTS test_table');
          tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id REAL)');

          tx.executeSql("INSERT INTO test_table (id) VALUES (?)", ['9e999'], function(tx, res) {
          console.log("insertId: " + res.insertId +  " -- probably 1"); // check #18/#38 is fixed
          alert("insertId: " + res.insertId  + " -- should be valid");

            db.transaction(function(tx) {
              tx.executeSql("SELECT id from test_table;", [], function(tx, res) {
                 alert("res.rows.length: " + res.rows.length  + " -- should be 1");
              });
            });

          }, function(e) {
            console.log("ERROR: " + e.message);
          });
        });
       }

And ERROR msg:
** Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: 'Invalid number value (infinite) in JSON write'

[brodybits]

See litehelpers/cordova-sqlite-evplus-ext-legacy-build-free/issues/4: this issue will be fixed in litehelpers / Cordova-sqlite-enterprise-free (available under GPL and commercial licensing terms). Will be documented here when I get a chance.

[brodybits]

Reproduced in a simpler test:

        it(suiteName + "SELECT abs('9e999') (Infinity) result test", function(done) {
          var db = openDatabase('Infinite-results-test.db', '1.0', 'Test', DEFAULT_SIZE);

          db.transaction(function(tx) {
            expect(tx).toBeDefined();

            tx.executeSql('SELECT abs(?) AS absResult', ['9e999'], function(tx, res) {
              expect(res).toBeDefined();
              expect(res.rows).toBeDefined();
              expect(res.rows.length).toBe(1);
              expect(res.rows.item(0).absResult).toBeDefined();
              expect(res.rows.item(0).absResult).toBe(Infinity);

              // Close (plugin only) & finish:
              (isWebSql) ? done() : db.close(done, done);
            });

          });
        }, MYTIMEOUT);

On iOS it crashes with the error as already reported:

2016-08-23 00:10:36.295 HelloCordova[221:18705] *** Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: 'Invalid number value (infinite) in JSON write'
*** First throw call stack:
(0x245a95f7 0x31e1bc77 0x245a953d 0x253202a9 0x2531edb1 0x253209ef 0x244c6f71 0x244c6e93 0x25320671 0x2531eda7 0x25320919 0x244d0071 0x244cff99 0x25320861 0x2531ede5 0x253209ef 0x244c6f71 0x244c6e93 0x25320671 0x2531eda7 0x253209ef 0x244c6f71 0x244c6e93 0x25320671 0x2531eda7 0x25320919 0x244d0071 0x244cff99 0x25320861 0x2531ede5 0x25320919 0x244d4f9b 0x244ce0d7 0x25320861 0x2531ede5 0x2531ebbd 0x2531fa5f 0xb65eb 0xc01a5 0xb6189 0x24691 0x2402f 0x25d9cb 0x2677bb 0x268881 0x324f9e25 0x324f9b78)
libc++abi.dylib: terminating with uncaught exception of type NSException

I think this is due to the way Cordova attempts to encode the JSON result.

This test also fails on the Android version of the plugin. This may be handled in a different issue.

This test passes on Web SQL (Android and iOS) and the Windows platform. This test fails on WP8 (legacy version).

[brodybits]

Now reported in Apache CB-11737

[brodybits]

Additional findings:

• The Android version returns a result with the row missing if the row includes a +/- Infinity values.
• Android/iOS versions generally treat (+/-) Infinity/NaN values like null values.
• In general both (WebKit) Web SQL and the plugin treat NaN values like null values which is consistent with SQLite (3) behavior ref: http:https://sqlite.1065341.n5.nabble.com/NaN-in-0-0-out-td19086.html
• The Windows version seems to handle (+/-) Infinity/NaN values properly, just like (WebKit) Web SQL. This is because the Windows version does not use JSON between the JavaScript and native platform layers.

I expect to DID commit the reproduction spec scripts within the next few hours or so.

I think the easiest way to fix the plugin to handle +/- Infinity values properly is to use the JSON Boolean false and true values to indicate +/- Infinity values. Note that SQLite (3) does not deal with specific Boolean values ref: https://www.sqlite.org/datatype3.html#boolean_datatype. Unfortunately I cannot promise when I will get a chance to fix this due to my current backlog.

[brodybits]

Issues on macOS ("osx") and Android have been reproduced as well.

Quick summary (general):

Statement	result on iOS	result on macOS ("osx")	result on Android	result on Windows & (WebKit) Web SQL
tx.executeSql('SELECT ABS(?) AS myresult', ['9e999'], ...)	crashes	crashes	missing result row	Infinity
tx.executeSql('SELECT (?) AS myresult', [Infinity], ...)	null value	crashes	null value (empty string in case of androidDatabaseProvider: 'system' setting)	Infinity