Hey guys,
I just had this issue where a division by 2 decimals is rounding some numbers if they don't have a floating point in the actual value. Let me demonstrate the issue:
Here is the schema for my table:
```
sqlite> .schema product_product
CREATE TABLE IF NOT EXISTS "product_product" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, "name" varchar(255) NOT NULL, "price" decimal NOT NULL, "quantity" decimal NOT NULL, "depth" decimal NULL, "height" decimal NULL, "weight" decimal NULL, "width" decimal NULL, "freight_help" decimal NOT NULL, "ref" char(32) NOT NULL UNIQUE, "erp_id" varchar(255) NULL UNIQUE, "erp_synced_at" datetime NULL, "unit" varchar(2) NOT NULL, "is_active" bool NOT NULL, "kind" varchar(9) NOT NULL, "original_price" decimal NULL, "coffee_kind" varchar(7) NULL, "producer_id" integer NULL REFERENCES "product_producer" ("id") DEFERRABLE INITIALLY DEFERRED, "region_id" integer NULL REFERENCES "product_region" ("id") DEFERRABLE INITIALLY DEFERRED, "variety_id" integer NULL REFERENCES "product_variety" ("id") DEFERRABLE INITIALLY DEFERRED, "altitude" integer NULL, "characteristics" varchar(255) NULL, "score" integer NULL, "olfactory" varchar(255) NULL, "taste" varchar(255) NULL, "visual" varchar(255) NULL, "is_fresh" bool NOT NULL, "is_featured" bool NOT NULL, "description" text NOT NULL, "sca_report" varchar(2000) NULL, "stock" integer NULL, "slug" varchar(50) NOT NULL);
CREATE INDEX "product_product_is_active_79c22e04" ON "product_product" ("is_active");
CREATE INDEX "product_product_producer_id_48defcb2" ON "product_product" ("producer_id");
CREATE INDEX "product_product_region_id_8af442d6" ON "product_product" ("region_id");
CREATE INDEX "product_product_variety_id_899ca5d0" ON "product_product" ("variety_id");
CREATE INDEX "product_product_is_fresh_8e90cb7e" ON "product_product" ("is_fresh");
CREATE INDEX "product_product_is_featured_27b90897" ON "product_product" ("is_featured");
CREATE INDEX "product_product_slug_76cde0ae" ON "product_product" ("slug");
```
I want to select the price, original price and price / original_price (both columns of `decimal` type). Here is what I get:
```
sqlite> SELECT price, original_price, price / coalesce(original_price, price) FROM product_product;
18.9||1.0
24.9||1.0
24.9||1.0
39.9|50|0.798
20||1
29.9||1.0
34.7||1.0
20||1
20|30|0
15|34|0
```
Notice that even though 39.9 / 50 gave me 0.798, 20 / 30 and 15 / 34 gave me 0. And as you can see on my schema, they are indeed decimals and not integers.
I did a test by forcing price to be casted to numeric (I tried decimal too):
```
sqlite> SELECT price, original_price, cast(price as numeric) / coalesce(original_price, price) FROM product_product;
18.9||1.0
24.9||1.0
24.9||1.0
39.9|50|0.798
20||1
29.9||1.0
34.7||1.0
20||1
20|30|0
15|34|0
```
Same result. The only way I got the real result from this was when I casted price to real:
```
sqlite> SELECT price, original_price, cast(price as real) / coalesce(original_price, price) FROM product_product;
18.9||1.0
24.9||1.0
24.9||1.0
39.9|50|0.798
20||1.0
29.9||1.0
34.7||1.0
20||1.0
20|30|0.666666666666667
15|34|0.441176470588235
```
AFAIK, even the result without cast should be working and giving the right result, right?
Btw, I'm using sqlite3 from the current debian unstable build:
```
❯ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
```