Sqlite real data invalid(cannot reproduce)

Question

I have text file and I convert it to insert query with transaction (thousands record).

The insert query I used is wrap with transaction like this.

public static void bulkInsert(DatabaseWrapper db, String tableName, String[] columns, String[][] values){
    StringBuilder stringBuilder = new StringBuilder();
    int index = 0;
    String head = "INSERT OR REPLACE INTO " + tableName + " (" + TextUtils.join(",", columns) + ") VALUES ";
    while (index < values.length) {
        stringBuilder.setLength(0);
        stringBuilder.append(head);
        for (int i = 0; i < 500; i++) {
            if (index < values.length) {
                if (i != 0) stringBuilder.append(",");
                stringBuilder.append("(").append(TextUtils.join(",",values[index])).append(")");
            } else {
                break;
            }
            index++;
        }
        db.execSQL(stringBuilder.toString());
    }
}

then I found that single record out of 3964 records that has real value from 10.86 become 1.95223612752995e-314 but the others look fine. I have no clue what happen here.

Edited1

Example of text file are like this

1004500803 KH1 001 P01 344 8850123136016 0000 0000010.86 00000 00000 0054 30082017080

1004500803 KH1 001 P01 345 8850123136023 0000 0000010.86 00000 00000 0055 30082017077

1004500803 KH1 001 P01 199 8850123123337 0001 0000007.24 00000 00000 0061 30082017081

1004500803 KH1 001 P01 032 8850123130410 0000 0000010.86 00000 00000 0065 30082017074

1004500803 KH1 001 P01 383 8850123135040 0001 0000010.86 00000 00000 0071 30082017074

each position of text data are fix in code so decided to convert column name and value into array of string

String[][] fileValues;
fileValues = PRS.getValueArrays();
public String[][] getValueArrays() {
        try {
            File file = new File(Constant.Path.MASTER_FILE_PATH, getFileName());
            List<String[]> list = new ArrayList<>();
            if (file.exists()) {
                BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "TIS-620"));
                String line;
                while ((line = br.readLine()) != null && !line.isEmpty() && !line.contains("EOF")) {
                    String[] valueArray = new String[fileIndex.length];
                    for (int i = 0; i < fileIndex.length - 1; i++) {
                        valueArray[i] = line.substring(fileIndex[i], fileIndex[i + 1]).trim();
                    }
                    valueArray[fileIndex.length - 1] = line.substring(fileIndex[fileIndex.length - 1], lineLength).trim();
                    list.add(valueArray);
                }
                br.close();
                String[][] valueList = new String[list.size()][];
                return list.toArray(valueList);
            }
        } catch (IOException | NullPointerException e) {
            e.printStackTrace();
        }
        return null;
    }

and this is function i parse text file to string arrays (may have some business logic)

public static String[][] parsePrsFileToPresaleValues(String[][] fileValue) {
    String[][] result = new String[fileValue.length][];
    sumRawPrice = 0;
    for (int i = 0; i < fileValue.length; i++) {
        String[] fromFile = fileValue[i];
        double price = Double.parseDouble(fromFile[7]);
        sumRawPrice += price;
        result[i] = getPresaleValue(
                fromFile[0],
                fromFile[5],
                Integer.parseInt(fromFile[6]),
                fromFile[8].equals("00000") ? 9999 : fromFile[8].equals("99999") ? 0 : Integer.parseInt(fromFile[8]),
                fromFile[9].equals("99999") ? 9999 : fromFile[9].equals("00000") ? 0 : Integer.parseInt(fromFile[9]),
                price
        );
    }
    return result;
}

and call bulk insert like this

public static void insertData(DatabaseWrapper db) {
    db.beginTransaction();
    DbUtil.bulkInsert(db, Presale.NAME, Presale.INSERT_COLUMN, Presale.parsePrsFileToPresaleValues(fileValues));
    db.setTransactionSuccessful();
    db.endTransaction();
}

this is the result of insert statement string

INSERT OR REPLACE INTO price_methods (code,item_code,promotion_code,price,created_at,updated_at) VALUES ('KB0','8850123110108','000',24.67,1503503909093,1503503909093),('KB0','8850123110115','000',12.71,1503503909101,1503503909101)

Edited2

By the way it's not happen all the time. This app running like two years and insert data like this about 10k record per day and work fine until this happened

can you add the line from the text file + the resulting sql statement to your question? — zapl, Sep 11 '17 at 13:54
`1.95223612752995e-314` (i.e. something very very close to 0) and `10.86` are quite far apart. Too much to be inexact floating point representation, imo. The smallest (but > 0) value a `double` can represent is `4.9e-324` which is only a few orders of magnitude away. Producing such a value could be possible when you do math and expect that `1/10 + 2/10 - 3/10` result in 0. But it's still hard to get down to e-314 that way. So maybe your parsing or textfile has a flaw and there is an "e" somewhere that causes the price string accidentally to be parsed in scientific notation. — zapl, Sep 11 '17 at 16:47

score 0 · Answer 1 · answered Sep 11 '17 at 14:01

The internal representation of your value has some variance because it can't be represented exactly as a binary floating point number. This is especially a problem if you are casting it between double/float etc.

You haven't posted all of your code so firstly make sure you use the correct column type that suits your usage. Also consider using BigDecimal within your application to take advantage of methods such as its scaling

Sqlite real data invalid(cannot reproduce)

1 Answers1