libsql Integer Causes Query Error “Unable to read data because it is in the wrong format”

[zinc1234596]

Hi team,

I have encountered an issue when using libsql: if an integer type is involved, queries fail with the following error message: “Unable to read data because it is in the wrong format.” This problem is affecting my workflow, and I hope it can be fixed as soon as possible.

Thank you for your attention!

[Tehnix]

I seem to be getting this on certain numeric datatypes as well, such as a REAL. It seems TablePlus is missing a mapping for this (it's not listed in the available datatypes when creating a new column), which probably is what's going wrong when it needs to figure out how to display it.

I get the following error when trying to display or query it:

The data couldn’t be read because it isn’t in the correct format.

I can however query it fine using the turso CLI:

$ turso db shell <db-name> "SELECT latitude, longitude FROM table_name_here LIMIT 2"
LATITUDE               LONGITUDE
36.8620148             -5.1792859
36.863912899999995     -5.181303799999999

If I directly query other fields in TablePlus they'll show up, just not these ones.

[jeremybarbet]

Furthermore, it used to work on a previous version of TablePlus. I went back to Version 6.3.1 (584) and it works for all my tables.

I didn't test older versions to find which version started to break exactly.

[jeremybarbet]

Did a little bit more digging and last working version was Version 6.4.4 (604).

Whatever happened on Version 6.4.8 (608) broke some things for libSQL (Source: https://tableplus.com/blog/2017/02/changelogs.html)

[llbbl]

Broken for me also in Version 6.7.1 (637). I got Claude to figure out a work around and provide a better description of the problem.

---

TablePlus (version 6.4.8+) cannot display nullable REAL (float/double) columns from LibSQL/Turso databases. Queries fail with:

"The data couldn't be read because it isn't in the correct format"

The Workaround

Cast nullable REAL columns to TEXT in your SELECT queries:

-- ❌ FAILS in TablePlus
SELECT id, price, rating, average_score FROM products;

-- ✅ WORKS in TablePlus
SELECT
  id,
  CAST(price AS TEXT) as price,
  CAST(rating AS TEXT) as rating,
  CAST(average_score AS TEXT) as average_score
FROM products;

Example Schema & Queries

-- Sample table with nullable REAL columns
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL,           -- Nullable float
  rating REAL,          -- Nullable float
  stock_count INTEGER
);

-- Insert sample data
INSERT INTO products (id, name, price, rating, stock_count) VALUES
  (1, 'Widget A', 19.99, 4.5, 100),
  (2, 'Widget B', 29.99, NULL, 50),  -- NULL rating
  (3, 'Widget C', NULL, 3.8, 0);     -- NULL price

-- ❌ This query FAILS in TablePlus
SELECT * FROM products;

-- ❌ This query also FAILS (selecting nullable REAL columns directly)
SELECT id, name, price, rating FROM products;

-- ✅ This query WORKS (non-nullable columns only)
SELECT id, name, stock_count FROM products;

-- ✅ This query WORKS (casting REAL to TEXT)
SELECT
  id,
  name,
  CAST(price AS TEXT) as price,
  CAST(rating AS TEXT) as rating,
  stock_count
FROM products;

-- ✅ Alternative: Use COALESCE with cast
SELECT
  id,
  name,
  CAST(COALESCE(price, 0) AS TEXT) as price,
  CAST(COALESCE(rating, 0) AS TEXT) as rating,
  stock_count
FROM products;

More Complex Examples

-- Aggregates with REAL columns
-- ❌ FAILS
SELECT AVG(price), MAX(rating) FROM products;

-- ✅ WORKS
SELECT
  CAST(AVG(price) AS TEXT) as avg_price,
  CAST(MAX(rating) AS TEXT) as max_rating
FROM products;

-- JOINs with REAL columns
-- ❌ FAILS
SELECT o.id, o.total_amount, p.price
FROM orders o
JOIN products p ON o.product_id = p.id;

-- ✅ WORKS
SELECT
  o.id,
  CAST(o.total_amount AS TEXT) as total,
  CAST(p.price AS TEXT) as price
FROM orders o
JOIN products p ON o.product_id = p.id;

-- WHERE clauses work fine (issue is only with SELECT display)
SELECT
  id,
  name,
  CAST(price AS TEXT) as price
FROM products
WHERE price > 20;  -- ✅ WHERE clause works without cast

Notes

• Non-nullable REAL columns may also be affected
• INTEGER columns typically work fine
• TEXT, BLOB, DATE columns are unaffected
• The issue is display-only - Turso CLI, official SDKs, and LibSQL drivers work correctly
• Workaround required for SELECT only; INSERT/UPDATE/DELETE work normally

Verification

Test if you're affected:

-- Create test table
CREATE TABLE test_reals (
  id INTEGER PRIMARY KEY,
  nullable_float REAL,
  not_null_float REAL NOT NULL DEFAULT 0,
  regular_int INTEGER
);

INSERT INTO test_reals VALUES (1, 3.14, 2.71, 42);
INSERT INTO test_reals (id, not_null_float, regular_int) VALUES (2, 1.41, 99);

-- If this fails, you're affected:
SELECT * FROM test_reals;

-- If this works, casting is the solution:
SELECT
  id,
  CAST(nullable_float AS TEXT) as nullable_float,
  CAST(not_null_float AS TEXT) as not_null_float,
  regular_int
FROM test_reals;