Bug: NULL is displayed in INTEGER fields when the value is REAL

[rentalhost]

SQLite is flexible with the data type stored in a field. Even if a column is specified as INTEGER, you can still store different types of values, such as REAL. In a real scenario, I mistakenly created a table that was supposed to store REAL values but configured it as INTEGER. This works in practice because SQLite is flexible, and the data can be easily interpreted in a JS environment. However, LibSQL Studio displays NULL, even though the data is loaded into the pipeline as float.

CREATE TABLE "coordinates" (
    "latitude"      INTEGER NOT NULL,
    "longitude"     INTEGER NOT NULL,
    "address"       TEXT,
    "updatedAt"     INTEGER NOT NULL,
    PRIMARY KEY("latitude","longitude")
) WITHOUT ROWID;

INSERT INTO "main"."coordinates" ("latitude", "longitude", "address", "updatedAt") VALUES ('1.23', '-1.23', '3', '4');

                "type": "execute",
                "result": {
                    "cols": [
                        {
                            "name": "latitude",
                            "decltype": "INTEGER"
                        },
                        {
                            "name": "longitude",
                            "decltype": "INTEGER"
                        },
                        {
                            "name": "address",
                            "decltype": "TEXT"
                        },
                        {
                            "name": "updatedAt",
                            "decltype": "INTEGER"
                        }
                    ],
                    "rows": [
                        [
                            {
                                "type": "float",
                                "value": -23.7128
                            },
                            {
                                "type": "float",
                                "value": -46.5429
                            },
                            {
                                "type": "text",
                                "value": "Av Francisco Prestes Maia 1114 - Santa Terezinha, São Bernardo do Campo - SP, 09770-000, Brazil"
                            },
                            {
                                "type": "integer",
                                "value": "1722964253480"
                            }
                        ],

[invisal]

Thanks. I will look into it.

[invisal]

I found the bug and deploying the fix now.

[invisal]

Please try again :)

[rentalhost]

@invisal Impressively fast! It's working perfectly now. Thanks!!!

[rentalhost]

I noticed that the same problem happens for INTEGER fields that receive TEXT or BLOB values.

CREATE TABLE IF NOT EXISTS "types" (
  "integer" INTEGER NOT NULL,
  "real" REAL NOT NULL,
  "text" TEXT NOT NULL,
  "blob" BLOB NOT NULL,
  "description" TEXT NOT NULL
);

INSERT INTO types VALUES(123,1.23,'a.bc',X'616263','Respecting all column types');
INSERT INTO types VALUES(123,123,'123',123,'Forcing INTEGER to all'); # OK
INSERT INTO types VALUES(1.23,1.23,'1.23',1.23,'Forcing REAL to all'); # OK
INSERT INTO types VALUES('abc','abc','abc','abc','Forcing TEXT to all'); # Fail 1, 2
INSERT INTO types VALUES(X'616263',X'616263',X'616263',X'616263','Forcing BLOB to all'); # Fail 1, 2

[invisal]

I noticed that the same problem happens for INTEGER fields that receive TEXT or BLOB values.

CREATE TABLE IF NOT EXISTS "types" (
  "integer" INTEGER NOT NULL,
  "real" REAL NOT NULL,
  "text" TEXT NOT NULL,
  "blob" BLOB NOT NULL,
  "description" TEXT NOT NULL
);

INSERT INTO types VALUES(123,1.23,'a.bc',X'616263','Respecting all column types');
INSERT INTO types VALUES(123,123,'123',123,'Forcing INTEGER to all'); # OK
INSERT INTO types VALUES(1.23,1.23,'1.23',1.23,'Forcing REAL to all'); # OK
INSERT INTO types VALUES('abc','abc','abc','abc','Forcing TEXT to all'); # Fail 1, 2
INSERT INTO types VALUES(X'616263',X'616263',X'616263',X'616263','Forcing BLOB to all'); # Fail 1, 2

Interesting. I guess I need to check value type instead of the column type and decide which cell type it should it use. Thanks for reporting.

[invisal]

Merged and deployed to production. Thanks for helping out. You are amazing :)