Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Infinity values in SQLite #405

Open
reconka opened this issue Jan 12, 2016 · 5 comments
Open

Infinity values in SQLite #405

reconka opened this issue Jan 12, 2016 · 5 comments

Comments

@reconka
Copy link

reconka commented Jan 12, 2016

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'

@brodycj
Copy link
Contributor

brodycj commented Jan 12, 2016

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.

@brodycj
Copy link
Contributor

brodycj commented Aug 22, 2016

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).

@brodycj
Copy link
Contributor

brodycj commented Aug 22, 2016

Now reported in Apache CB-11737

@brodycj
Copy link
Contributor

brodycj commented Sep 27, 2016

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://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.

brodycj pushed a commit to brodycj/Cordova-sqlite-storage-common-dev that referenced this issue Sep 28, 2016
Reproduce more +/- Infinity issues on Android/iOS plugin
(ref: storesafe#405)
Additional error code/message check
@brodycj
Copy link
Contributor

brodycj commented Nov 8, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants