Sqlite math operations with REAL column ignore decimal digits-CodePudding

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

CodePudding user response：

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.