Issue with custom functions on 32-bit iOS devices

[JetA2]

Hi, and thanks for the great package!

I'm using a custom function to calculate the distance between two points from the database and using that function to order results by the distance from a reference point.

This works fine in the iOS Simulator but when running on an old 32-bit iPad (iOS 10.3.3) the same SELECT statement returns completely different and invalid results. Running the app on a 64-bit iOS device returns the correct result.

Could there be overflow issues with the returned value from my function?
Is there anything I can do to make my function "32-bit safe"?

sqlite3: ^1.5.0
sqlite3_flutter_libs: ^0.5.3

Code snippets below:

db.createFunction(
    functionName: 'distance',
    argumentCount: const AllowedArgumentCount(4),
    deterministic: true,
    function: _distance,
  );

final result = db.select(
      'SELECT id FROM Column ORDER BY distance(Latitude, Longitude, ?, ?),
      [position.latitude, position.longitude],
    );

Object? _distance(List<Object?> arguments) {
  // Setup constants
  //
  const toRad = 0.0174532925; // M_PI / 180.0

  // Get the four argument values
  //
  final lat1 = (arguments[0] as double) * toRad;
  final lon1 = (arguments[1] as double) * toRad;
  final lat2 = (arguments[2] as double) * toRad;
  final lon2 = (arguments[3] as double) * toRad;

  // Apply the spherical law of cosines to get distance
  //
  return acos(
        sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1),
      ) *
      earthRadius;
}

[simolus3]

Thanks for the report! If you print the values given in _distance, do you see different values there when on an old device too?

This may be a bug in dart:ffi, so we should try to find the exact place its going wrong and maybe file an issue with a much smaller repro on the SDK.

[JetA2]

The distance calculated in _distance is not affected.
It must be that the value returned from the function gets corrupted on the way to native.
An incorrect value is then used to order the results.

I've made a simpler example to illustrate:

import 'dart:math';

import 'package:flutter/material.dart';
import 'package:sqlite3/sqlite3.dart';

const data = [
  ['Park Rapids', 46.90069444, -95.07311111],
  ['Parchment', 42.365400916667, -85.555838833333],
  ['Lake City', 39.766146388889, -88.779515555556],
  ['Bandung', -6.6483333333333, 108.15416666667],
  ['New Castle', 41.012686111111, -80.351711111111],
  ['Baldwin', 44.905522222222, -92.483526111111],
  ['Windhoek', -22.6075, 17.07916667],
  ['Monroe City', 39.634410833333, -91.726994444444],
  ['Manus', -2.06189, 147.424],
  ['Bydgoszcz', 53.09680278, 17.97766944],
];

Object? _distance(List<Object?> arguments) {
  // Setup constants
  //
  const earthRadius = 6371.009;
  const toRad = 0.0174532925; // M_PI / 180.0

  // Get the four argument values
  //
  final lat1 = (arguments[0] as double) * toRad;
  final lon1 = (arguments[1] as double) * toRad;
  final lat2 = (arguments[2] as double) * toRad;
  final lon2 = (arguments[3] as double) * toRad;

  // Apply the spherical law of cosines to get distance
  //
  final distance = acos(
        sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1),
      ) *
      earthRadius;

  print('_distance: $arguments -> $distance');

  return distance;
}

late Database db;

void main() {
  db = sqlite3.openInMemory();

  db.createFunction(
    functionName: 'distance',
    argumentCount: const AllowedArgumentCount(4),
    deterministic: true,
    function: _distance,
  );

  db.execute(
    'CREATE TABLE "City" ("Name" TEXT COLLATE nocase NOT NULL'
    ', "Latitude" REAL NOT NULL'
    ', "Longitude" REAL NOT NULL'
    ');',
  );

  for (var item in data) {
    db.execute('INSERT INTO "City" VALUES (?,?,?)', item);
  }

  runApp(const MyApp());
}

class MyApp extends StatelessWidget {
  const MyApp({Key? key}) : super(key: key);

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: const MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  const MyHomePage({Key? key, required this.title}) : super(key: key);

  final String title;

  @override
  State<MyHomePage> createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  final List<Text> _text = [];

  void _refresh() {
    final reference = data[0];

    final result = db.select(
      'SELECT * FROM "City" ORDER BY distance(Latitude, Longitude, ?, ?) ASC',
      [reference[1], reference[2]],
    ).iterator;

    _text.clear();
    while (result.moveNext()) {
      final city = result.current.columnAt(0);
      final distance = _distance([
        result.current.columnAt(1),
        result.current.columnAt(2),
        reference[1],
        reference[2],
      ]);

      _text.add(Text('${reference[0]} to $city: $distance km'));
    }

    setState(() {});
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: _text,
        ),
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _refresh,
        tooltip: 'Refresh',
        child: const Icon(Icons.add),
      ),
    );
  }
}

64 bit:

32 bit:

[JetA2]

For some further testing, I registered a few simple functions and performed a SELECT on the return value of each with 3 runs. It looks like there is some filling in of random bits happening in the 32-bit environment since the result isn't the same every time.

Object? _returnDouble(List<Object?> arguments) {
  return 1.234;
}

Object? _returnInt(List<Object?> arguments) {
  return 1234;
}

Object? _returnString(List<Object?> arguments) {
  return 'Test';
}

64-bit:

flutter: {returnDouble(): 1.234}
flutter: {returnInt(): 1234}
flutter: {returnString(): Test}

flutter: {returnDouble(): 1.234}
flutter: {returnInt(): 1234}
flutter: {returnString(): Test}

flutter: {returnDouble(): 1.234}
flutter: {returnInt(): 1234}
flutter: {returnString(): Test}

32-bit:

flutter: {returnDouble(): -1.7617588598734772e+42}
flutter: {returnInt(): 5300229592637}
flutter: {returnString(): Test}

flutter: {returnDouble(): -1.761758853976082e+42}
flutter: {returnInt(): 5300210536981}
flutter: {returnString(): Test}

flutter: {returnDouble(): -1.7617588525888637e+42}
flutter: {returnInt(): 5300206054637}
flutter: {returnString(): Test}

[JetA2]

I don't have any experience with FFI but it seems a possible explanation is that the method signature of the sqlite3_result_* methods specify the 64-bit types Double and Int64:

sqlite3.dart/sqlite3/lib/src/ffi/sqlite3.g.dart

Lines 793 to 796 in f6907ac

	late final _sqlite3_result_doublePtr = _lookup<
	ffi.NativeFunction<
	ffi.Void Function(ffi.Pointer<sqlite3_context>,
	ffi.Double)>>('sqlite3_result_double');

This could be due to this ffigen issue: dart-lang/native#530

From the initial comment on that issue: "The lack of 'named' integer types that vary in size on different platforms currently makes ffigen only generate correct bindings for its host platform.".

If this is the reason, then I guess the only solution is to force Float and Int32 for the method signatures, but lose support for 64-bit types on all devices.

I'm not sure if this actually is how FFI deals with different platforms. What do you think?

[simolus3]

You had me worried, but luckily double is 8-byte on 32bit ARM :D

For ints, we explicitly use sqlite3_result_int64, and from a quick look it appears as we do the same thing when reading parameters. Also, an int in C gets translated to Int32 (which is the correct type form 32bit ARM too).

Your smaller repro definitely seems to indicate that there's a problem when binding results though. It sounds like this should be reproducible in a VM too, I'll take a look.

[JetA2]

Thanks for taking a look. I can confirm the arguments get passed to the function unaltered, but the return value gets mangled.

Looking at the integer values, the first 32 bits represent the original number (1234) and the last 32 bits seem to be random bits. However, as the double values are the same size on both platforms it's odd that they too get altered.

If put a breakpoint in the C code in the function below, I can see that the double has been mangled when it gets here.
I'm not sure what magic is happening between Dart's void sqlite3_result_double(ffi.Pointer<sqlite3_context> ctx, double result) and the C function though.

SQLITE_API void sqlite3_result_double(sqlite3_context *pCtx, double rVal){
  assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) );
  sqlite3VdbeMemSetDouble(pCtx->pOut, rVal);
}

[Mike278]

https://twitter.com/timsneath/status/1481365910908596230 is curious timing 👀

[simolus3]

@JetA2 Can you try upgrading to a dev version of Flutter and try the abi-specific-ints branch?

dependency_overrides:
  sqlite3:
    git:
      url: https://github.com/simolus3/sqlite3.dart.git
      ref: abi-specific-ints
      path: sqlite3

On that branch, I'm using native types that will adapt to the actual ABI being used.

[JetA2]

I'm getting a lot of these build errors:

../../../Documents/Development/flutter/.pub-cache/git/sqlite3.dart-cd1e016e57c5e13feb327220f55cf4ee3366b564/sqlite3/lib/src/ffi/sqlite3.g.dart:510:61: Error: Expected type 'NativeFunction<Pointer<Void> Function(Pointer<sqlite3_stmt>, invalid-type)>' to be a valid and instantiated subtype of 'NativeType'.
 - 'NativeFunction' is from 'dart:ffi'.
 - 'Pointer' is from 'dart:ffi'.
 - 'Void' is from 'dart:ffi'.
 - 'sqlite3_stmt' is from 'package:sqlite3/src/ffi/sqlite3.g.dart' ('../../../Documents/Development/flutter/.pub-cache/git/sqlite3.dart-cd1e016e57c5e13feb327220f55cf4ee3366b564/sqlite3/lib/src/ffi/sqlite3.g.dart').
  late final _sqlite3_column_blob = _sqlite3_column_blobPtr.asFunction<

The relevant function looks like this:

  late final _sqlite3_column_blobPtr = _lookup<
      ffi.NativeFunction<
          ffi.Pointer<ffi.Void> Function(
              ffi.Pointer<sqlite3_stmt>, pkg_ffi.Int)>>('sqlite3_column_blob');
  late final _sqlite3_column_blob = _sqlite3_column_blobPtr.asFunction<
      ffi.Pointer<ffi.Void> Function(ffi.Pointer<sqlite3_stmt>, int)>();

Some issue with the FFI auto generation?

[simolus3]

Oh right, you'll also need ffi: ^1.2.0-0, I forgot to update that dependency on the branch. I've pushed a new commit enforcing the right version.

[JetA2]

Thanks, it built fine with the latest commit.
Unfortunately the issue still remains.