Bug 23927 - Error during reading numeric value from SQLite
Summary: Error during reading numeric value from SQLite
Alias: None
Product: Android
Classification: Xamarin
Component: Mono runtime / AOT Compiler ()
Version: 4.18.0
Hardware: PC Windows
: Normal normal
Target Milestone: ---
Assignee: Jonathan Pryor
Depends on:
Reported: 2014-10-20 04:58 UTC by Marco
Modified: 2017-08-08 17:28 UTC (History)
7 users (show)

Is this bug a regression?: ---
Last known good build:

Sample project (16.76 KB, application/zip)
2014-10-20 04:58 UTC, Marco
Sample SQLite DB (929 bytes, application/zip)
2014-10-20 04:59 UTC, Marco
ReadDBXamarin - APK (4.72 MB, application/octet-stream)
2014-10-21 03:28 UTC, Marco
ReadDbXamarin.ReadDbXamarin-Signed.apk (4.70 MB, application/octet-stream)
2014-11-06 17:38 UTC, Jonathan Pryor
Sample project, direct p/invoke (19.03 KB, application/zip)
2014-11-19 22:15 UTC, Brendan Zagaeski (Xamarin Team, assistant)

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.

Please create a new report on Developer Community or GitHub with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:

Description Marco 2014-10-20 04:58:33 UTC
Created attachment 8451 [details]
Sample project

## Environment

Xamarin Studio 	5.5.2
.Net Framework 	4.0.40419.18444
Xamarin.Android	4.18.0
OS windows 		6.1.7601 (64-bit)
Android OS		4.4.2

## Description

When I retrieve a value from NUMERIC column from SQLite database the sqlite command return always 0.
The error occurs only when the application is run in RELEASE mode physically on the tablet. In DEBUG ( with emulator and with the tablet) the error cannot be repeated.
The error occurs using both SqldataReader or ExcuteScalar function and only with Android version 4.4.2; with Android 4.1.2 the function reads correctly the database values.

## Steps to reproduce

Here attached a sample project to reply the error.

1. Excute the App in release mode on any tablet with android 4.4.2
2. Select the key value
3. Press Start 
4. Check the value

Furthermore attached a sqlite Database whin only one table (Table_prova) that contains 2 colums:

## Expected result 

The function ReadData will return a correct value from DB

## Actual result

The function ReadData return always 0
Comment 1 Marco 2014-10-20 04:59:05 UTC
Created attachment 8452 [details]
Sample SQLite DB
Comment 2 Udham Singh 2014-10-20 09:03:37 UTC
I have  tried to reproduce this issue but not able to reproduce this. To reproduce this issue I have followed the steps mentioned in bug description and below are my observations.

1. When selected key value is 1 then returned value is 22.
2. When selected key value is 2 then returned value is 10.

Please let me know if I missed anything or have to do any specific step to reproduce this issue.

Note : I have checked this issue with device 'Samsung Galaxy Tab 4 (Android 4.4.2)' and run app in  release mode.

Environment Info : 

=== Xamarin Studio ===

Version 5.5.2 (build 0)
Installation UUID: 3235da96-1cb2-43d5-a836-234fd96911c2
 Microsoft .NET 4.0.30319.18449
 GTK+ 2.24.22 (MS-Windows theme)
 GTK# 2.12.26

=== Xamarin.Android ===

Version: 4.18.0 (Enterprise Edition)
Android SDK: D:\Backup_OldMachine\D Drive\SDK\android-sdk
 Supported Android versions:
  1.6    (API level 4)
  2.1    (API level 7)
  2.2    (API level 8)
  2.3    (API level 10)
  3.0    (API level 11)
  3.1    (API level 12)
  3.2    (API level 13)
  4.0    (API level 14)
  4.0.3  (API level 15)
  4.1    (API level 16)
  4.2    (API level 17)
  4.3    (API level 18)
  4.4    (API level 19)
  4.4.87 (API level 20)
  4.5    (API level 21)
Java SDK: C:\Program Files\Java\jdk1.7.0_67
java version "1.7.0_67"
Java(TM) SE Runtime Environment (build 1.7.0_67-b01)
Java HotSpot(TM) 64-Bit Server VM (build 24.65-b04, mixed mode)

=== Build Information ===

Release ID: 505020000
Git revision: a5887be38215a6cdd3f349e102ef82a1c4f950a4
Build date: 2014-10-14 12:21:34-04
Xamarin addins: 069ddd29bb70a42238142eee9bac21a5e4b2f9f9

=== Operating System ===

Windows 6.2.9200.0 (64-bit)
Comment 3 Marco 2014-10-20 10:03:59 UTC
The only difference between the environments is that you have used a Samsung Galaxy Tab 4 while we had the problem with Samsung Galaxy Tab 3

Furthermore  we noticed that

Samsung Galaxy Tab 2 (7") Android 4.1.2 without error
Emulator Device (10.1") Android 4.4.2 without error

The error appear only  with the our customer's devices. Can you test with a Samsung Galaxy Tab 3?
Comment 4 Udham Singh 2014-10-20 11:24:37 UTC
Hi Marco,

I have tried to reproduce this issue with Samsung Galaxy Tab 3 (Android 4.4.2) and still not able to reproduce this. To reproduce this issue, I have  followed the same steps mentioned in bug description and getting the same behaviour mentioned in comment 2 with device Samsung Galaxy Tab 3 (Android 4.4.2). 

Comment 5 Marco 2014-10-21 03:28:06 UTC
Created attachment 8459 [details]
ReadDBXamarin - APK
Comment 6 Marco 2014-10-21 03:28:58 UTC
Can you kindly perform a test with our APK here attached. 
If even on your tablet is not possible to read correctly the data from the SQLite DB, can you can check if in the our package there are same incorrect assemblies (framework, sqlite, other) or any kind of errors.

Comment 7 Marco 2014-10-30 04:47:39 UTC
Note that there is no way to carry out the checks with our APK package, can you kindly provide your APK ,generated by your build about the sample project, in order to perform a test with our tablets and inspect if the error already occurred.
Comment 8 Marco 2014-11-06 05:37:44 UTC
Following your suggestion we tried to compile with "don't link" option in the build configuration but the results its the same, the App is not able to read the correct data from DB.

Please, let us know what are the next steps to try to resolve this issue.

Comment 9 Jonathan Pryor 2014-11-06 17:34:34 UTC
I'm not able to reproduce this on a Nexus 5 with Android v4.4 either.

> When I retrieve a value from NUMERIC column from SQLite database the sqlite
> command return always 0.
> ...
> The error occurs using both SqldataReader or ExcuteScalar

...these two facts together don't make sense.

> $ sqlite3 Assets/DbProva.db 
> SQLite version 3.8.5 2014-08-15 22:37:57
> Enter ".help" for usage hints.
> sqlite> .schema

Column A is an INTEGER; B is the NUMERIC column.

Furthermore, DbCommand.ExecuteScalar() returns the *first* column in the query:


The query?

> CM.CommandText = "SELECT A, B FROM Table_prova WHERE A = '" + cod + "'";

Consequently, ExecuteScalar(), when used, SHOULD be returning the INTEGER A column, NOT the NUMERIC B column.

That still doesn't explain why you're getting 0, but as far as I can tell the original logic/report is itself suspect: ExecuteScalar() doesn't work that way.

Furthermore, if I use the ExecuteScaler() version, that's also the behavior I observe -- the value of column A is returned, not B.

Finally, the attachment from Comment #5 works on my Nexus 5 as well.
Comment 10 Jonathan Pryor 2014-11-06 17:38:06 UTC
Created attachment 8649 [details]

As requested in Comment #7, my Release build of Attachment #8451 [details].
Comment 11 Marco 2014-11-07 03:37:31 UTC
Jonathan, obviously the ExcuteScalar command only be used with this SQL command --> "SELECT B FROM Table_prova WHERE A = '" + cod + "'"; 
Regardless this, in the sample we use a SqliteDataReader and we get the value by this code R ["B"].ToString ().

We have just installed your apk but we have different results

Galaxy Note         10.1"     Android 4.1     Result: OK
Galaxy Tab3         10.1"     Android 4.4     Result: ERROR
Galaxy Tab2         7"        Android 4.1.2   Result: OK
Emulator            10.1"     Android 4.4     Result: OK

Looking forward to your reply
Comment 12 Jorge Klemm 2014-11-11 08:11:58 UTC
Hi, I have the same issue.  I'm using tablet Dell Venue 8 3830 with Android 4.4.2.  I have installed the ReadDbXamarin.apk and only display "Value: 0".

The same issue that my app is suffering.  All numeric values that I get from Sqlite database is zero.
Comment 13 Jorge Klemm 2014-11-11 08:28:42 UTC
The same occur using Jonathan's APK, the error persists.

Dell Venue 8 3830      8"     Android 4.4.2   Result: ERROR

Waiting for solutions or work-a-round.

Comment 14 Marco 2014-11-12 04:23:14 UTC
in the meanwhile we discovered a workaround to deliver the solution to our customer. The problem seems to be related to the 2 system apk files embeded in the release. 
If we deliver the Mono Android-19 Support and the Mono Shared Runtime separately then we compile our app with the 'Use Shared Mono runtime' setting (project properties, Generale, Android build), it works fine. I hope it can help you.
Comment 15 Brendan Zagaeski (Xamarin Team, assistant) 2014-11-19 22:15:58 UTC
Created attachment 8815 [details]
Sample project, direct p/invoke

This is a modified version of the original sample project to help the Xamarin team with pinpointing the source of the problem.

This version directly includes the `DllImport` statements [1] for the SQLite functions [2], and uses just a handful of them to perform the SQL query.

> [1] https://github.com/mono/mono/blob/master/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/UnsafeNativeMethods.cs
> [2] http://sqlite.org/c3ref/funclist.html
Comment 16 Ricardo Gehrke Filho 2014-11-20 11:43:16 UTC
I have found a way to deliver our app to playstore making some changes in the SQlite.cs file that we use to connect to Sqlite3

In the SQlite.cs file, int the funcion readCol I removed this line:
return (decimal)SQLite3.ColumnDouble (stmt, index);

and added these:
string stringValue = SQLite3.ColumnString (stmt, index);
stringValue = stringValue.Replace (".", ",");
decimal decimalValue = Decimal.Parse(stringValue);
return decimalValue;

Explaining: Instead of using ColumnDouble I read the decimal column from DB using ColumnString and making the conversion using C# functions.

It worked fine to me. It's not a solution for the bug, but its a solution for the problem.
Comment 17 Jonathan Pryor 2014-11-20 15:04:06 UTC
The workaround in Comment #16 suggests/implies that this isn't a SQLite bug so much as a locale bug.

@Ricardo, @Marco: What locale is your device set to, a "US-like" one ('.' is decimal separator char), or a "European-like" one (',' is decimal separator char)?
Comment 18 Brendan Zagaeski (Xamarin Team, assistant) 2014-11-22 00:58:06 UTC
## Additional findings

I found 2 ways to stop the problem. The second one matches comment 16.

### 1. Add x86 as a Supported ABI

Both the Samsung Galaxy Tab 3 and the Dell Venue 8 are x86 devices. Adding x86 as a "Supported ABI" [1 or 2] for the Release configuration stops the problem. This "feels" like the correct fix, and it also explains why the Debug configuration works correctly: the Debug (shared runtime) configuration includes _all_ ABIs automatically.

[1] Xamarin Studio: "Project Options -> Build -> Android Build -> Advanced [tab] -> Supported ABIs -> x86 [checkbox]"

[2] Visual Studio: "Project Properties -> Android Options -> Advanced [tab] -> Supported architectures -> x86 [checkbox]"

It seems these devices must provide a "compatibility mode" that allows them to use the default Xamarin.Android armeabi-v7a runtime? 

### 2. Use a different `R.Get` method (similar to  comment 16)

Rather than adding the x86 as a "Supported ABI", change the `return R.GetValue (1);` statement in the test case from comment 0 to any of the following:

> return (double)R.GetInt32 (1); // P/Invokes `sqlite3_column_int()` behind the scenes


> return (double)R.GetInt64 (1); // P/Invokes `sqlite3_column_int64()` behind the scenes


> return (double)R.GetDecimal (1); // P/Invokes `sqlite3_column_text()` behind the scenes

For comparison, `R.GetValue()` calls `R.GetDouble()`, which in turn P/Invokes `sqlite3_column_double()`.

## The direct P/Invoke version from comment 15 has the same behavior

1. `UnsafeNativeMethods.sqlite3_column_double()` returns 0 if Supported ABIs is set to just "armeabi-v7a", but it returns the expected value if the supported ABIs include "x86".

2. All of the following return the expected values even when Supported ABIs only includes "armeabi-v7a":

> UnsafeNativeMethods.sqlite3_column_int (stmt, 1)


> UnsafeNativeMethods.sqlite3_column_int64 (stmt, 1)


> UnsafeNativeMethods.sqlite3_column_text (stmt, 1)

## `sqlite3_column_double()` behaves strangely when called "indirectly" via an NDK library (in either a Java or a Xamarin app)

This is the most confusing part of my new findings. I created a small NDK C library that "wraps" a few of the sqlite3 functions in an additional layer of C functions. If I invoke `sqlite3_column_double()` via its wrapper function, it returns 0 on _all_ devices, but if I P/Invoke it directly from a Xamarin app, it behaves as described above. `sqlite3_column_double()` might just be "giving up" and returning the default value as described on [3], *but* `sqlite3_errcode()` still returns a "good" value of "100" after the call to `sqlite3_column_double()`.

Interestingly the problem is again specific to `sqlite3_column_double()`. Other methods like `sqlite3_column_int()` and `sqlite3_column_text()` work _correctly_ in this NDK wrapper setup. Even so, this problem might be a separate issue because it happens on _all_ devices, not just x86 tablets. Calling the NDK wrapper library from a Java Android app via the JNI produces the same results: `double` fails  while `int` and `text` work correctly.

[3] http://sqlite.org/c3ref/column_blob.html
Comment 19 Marco 2014-11-24 04:17:00 UTC
our device is set to "European-like", the comma is decimal separator
Comment 20 Brendan Zagaeski (Xamarin Team, assistant) 2014-11-24 12:24:37 UTC
@Marco, when you get a chance, I'd be curious to know if adding x86 as a supported ABI solves the problem in your full app.

Comment 21 Marco 2014-12-05 06:34:24 UTC
Hi Brendan,
   May I first apologise for my late reply...
We added x86 (Android Build --> Release configuration --> Advanced)  to supported ABIs and with this options and the problem was be solved.

Comment 22 Jon Douglas [MSFT] 2017-08-08 17:28:42 UTC
Marking ANSWERED based on https://bugzilla.xamarin.com/show_bug.cgi?id=23927#c20 and previous comments indicating this issue is resolved. Please re-open if this is still an issue.