Sqlite math operations with REAL column ignore decimal digits

paolof89 0
Viewed 23

I built a table with a REAL column called QTY but when I try to apply simple math operations it ignores the decimal digits. Here an example:

SELECT QTY, QTY*2.0, QTY+1.0 FROM BOM
QTY QTY*2.0 QTY+1.0
0,67 0.0 1.0
1,00 2.0 2.0
0,50 0.0 1.0

The QTY type is REAL, here the create table command:

CREATE TABLE IF NOT EXISTS bom(
                    BOM_ID text NOT NULL,
                    RM_FK text NOT NULL,
                    QTY REAL NOT NULL,
                    PRIMARY KEY (BOM_ID, RM_FK))
sqlite
1 Answers

Sqlite uses Flexible Typing.

Even if you declared QTY as REAL, sqlite will accept and store any value, even text, converting it to REAL if it can, or storing it as TEXT if it can't.

As pointed out by @forpas, you inserted '0,67' in QTY, which sqlite couldn't convert in REAL because the decimal separation symbol is '.' and not ','.

So, when doing your computation, it just uses the numbers before the ',' discarding what comes after.

Andrea B.0
Related