sqlite: Expected integer, got something else

[richb-hanover]

Ahah. I see the problem you cited. A database query for a field of type INTEGER gives an error, while a VARCHAR column works fine.

The following are pq commands against a copy of the chinook database in the local directory. pq was built with cargo build (not using the Dockerfile).

What other troubleshooting information could I provide? Thanks.

Working:

√ pqtest % pq --database sqlite://chinook.sqlite 'from albums | select title | take 2'
+---------------------------------------+
| title                                 |
+---------------------------------------+
| title                                 |
| For Those About To Rock We Salute You |
+---------------------------------------+

Not working:

√ pqtest % pq --database sqlite://chinook.sqlite 'from albums | select album_id | take 2'
Error: Invalid Error: Expected integer, got something else

Caused by:
    Error code 1: Unknown error code

[richb-hanover]

Is there a way to force debug output for pq?

[snth]

Yes, sorry about that.

RUST_LOG=debug pq ... gives you debug output but I think it stops at the DuckDB boundary because that's written in C++.

[richb-hanover]

Yes - the RUST_LOG=debug gives a tiny bit more info, but not much... See the output below

Do we have the ability to print more troubleshooting info at the point the "Invalid Error" is detected?

√ pqtest % RUST_LOG=debug pq --database sqlite://chinook.sqlite 'from albums | select album_id | take 2'
[2022-10-28T12:38:12Z DEBUG pq] args = Cli { from: [], to: "-", database: Some("sqlite://chinook.sqlite"), backend: None, no_exec: false, format: None, writer: arrow, sql: false, query: "from albums | select album_id | take 2" }
[2022-10-28T12:38:12Z DEBUG pq] query = "from albums | select album_id | take 2"
[2022-10-28T12:38:12Z DEBUG pq] from=[]
[2022-10-28T12:38:12Z DEBUG pq] sources=[]
[2022-10-28T12:38:12Z DEBUG pq] query = "from albums | select album_id | take 2"
[2022-10-28T12:38:12Z DEBUG pq] query = "prql version:1 dialect:ansi\nfrom albums | select album_id | take 2"
[2022-10-28T12:38:12Z DEBUG pq] to = "-"
[2022-10-28T12:38:12Z DEBUG pq] args.format = None
[2022-10-28T12:38:12Z INFO  pq] inferred format = table
[2022-10-28T12:38:12Z DEBUG pq] format = None
[2022-10-28T12:38:12Z DEBUG pq] args.database = Some("sqlite://chinook.sqlite")
[2022-10-28T12:38:12Z DEBUG pq] database = "sqlite://chinook.sqlite"
[2022-10-28T12:38:12Z DEBUG pq] args.backend = None
[2022-10-28T12:38:12Z DEBUG pq] backend = "duckdb"
[2022-10-28T12:38:12Z DEBUG pq] args.writer = arrow
[2022-10-28T12:38:12Z DEBUG pq::backends::duckdb] sources = []
[2022-10-28T12:38:12Z DEBUG pq::backends::duckdb] query = "prql version:1 dialect:ansi\nfrom albums | select album_id | take 2"
[2022-10-28T12:38:12Z DEBUG pq::backends::duckdb] sql = "SELECT album_id FROM albums LIMIT 2"
[2022-10-28T12:38:12Z DEBUG pq::backends::duckdb] sql = "SELECT\n  album_id\nFROM\n  albums\nLIMIT\n  2"
Error: Invalid Error: Expected integer, got something else

Caused by:
    Error code 1: Unknown error code
?1 pqtest % RUST_LOG=debug pq --database sqlite://chinook.sqlite 'from albums | select title | take 2'
[2022-10-28T12:38:28Z DEBUG pq] args = Cli { from: [], to: "-", database: Some("sqlite://chinook.sqlite"), backend: None, no_exec: false, format: None, writer: arrow, sql: false, query: "from albums | select title | take 2" }
[2022-10-28T12:38:28Z DEBUG pq] query = "from albums | select title | take 2"
[2022-10-28T12:38:28Z DEBUG pq] from=[]
[2022-10-28T12:38:28Z DEBUG pq] sources=[]
[2022-10-28T12:38:28Z DEBUG pq] query = "from albums | select title | take 2"
[2022-10-28T12:38:28Z DEBUG pq] query = "prql version:1 dialect:ansi\nfrom albums | select title | take 2"
[2022-10-28T12:38:28Z DEBUG pq] to = "-"
[2022-10-28T12:38:28Z DEBUG pq] args.format = None
[2022-10-28T12:38:28Z INFO  pq] inferred format = table
[2022-10-28T12:38:28Z DEBUG pq] format = None
[2022-10-28T12:38:28Z DEBUG pq] args.database = Some("sqlite://chinook.sqlite")
[2022-10-28T12:38:28Z DEBUG pq] database = "sqlite://chinook.sqlite"
[2022-10-28T12:38:28Z DEBUG pq] args.backend = None
[2022-10-28T12:38:28Z DEBUG pq] backend = "duckdb"
[2022-10-28T12:38:28Z DEBUG pq] args.writer = arrow
[2022-10-28T12:38:28Z DEBUG pq::backends::duckdb] sources = []
[2022-10-28T12:38:28Z DEBUG pq::backends::duckdb] query = "prql version:1 dialect:ansi\nfrom albums | select title | take 2"
[2022-10-28T12:38:28Z DEBUG pq::backends::duckdb] sql = "SELECT title FROM albums LIMIT 2"
[2022-10-28T12:38:28Z DEBUG pq::backends::duckdb] sql = "SELECT\n  title\nFROM\n  albums\nLIMIT\n  2"
+---------------------------------------+
| title                                 |
+---------------------------------------+
| title                                 |
| For Those About To Rock We Salute You |
+---------------------------------------+
√ pqtest %

[richb-hanover]

PS I also wondered whether DuckDB had a regression - there were some hints in the duckdb repo's Issues. I changed Cargo.toml of the prql-query repo to retrieve 0.4.0, then rebuilt the Dockerfile.

1. It worked without error - no libduckdb error during the docker build
2. It gave exactly the same error message

[richb-hanover]

@snth - any idea about how to start debugging this? What's special about columns declared to be INTEGER? Thanks.

[snth]

Hi,

Sorry to have been leaving you hanging on this, I've had work and family things to attend to.

I suspect that the duckdb-rs Rust wrapper for DuckDB might not have implemented the type mapping for those types. Therefore my plan is to first check whether these work fine in DuckDB and then check what's happening on the duckdb-rs side.

---

I tried just opening those sqlite databases with the DuckDB CLI:

That doesn't seem to work at all. pq and duckdb-rs seems to get further than that and can at least query the text fields.

sqlite itself seems to have no problem with the file:

---

Maybe we need to file a bug report in the sqlite_scanner repo? Do you want to do that?

[snth]

@richb-hanover Do you have any other sqlite files that you can try the DuckDB CLI or pq on?

[snth]

Looks like there might be an issue for this already: duckdb/sqlite_scanner#7

[richb-hanover]

Looks like there might be an issue for this already:

Maybe - I'm not sure. I'm way beyond my depth, at least with my current understanding of the interrelationship between all the tools.

I too have pressing other work, so my preference would be to document the error I'm seeing (can't read an integer from SQLite database) in the appropriate place and let those more familiar with the code find and fix the problem.

What's the best way / best place to submit a report? (It looks as if your examples with the duckdb CLI are pretty compelling...) Thanks

[richb-hanover]

I tried just opening those sqlite databases with the DuckDB CLI:

I don't understand how to do this. The screen shots above don't explain the commands to give (and what software needs to be installed prior to running them.) Thanks.

[snth]

I think don't worry about it for now. I initially thought I wouldn't be able to test it myself, but I was able to test it in the DuckDB CLI and it seems to point to the problem lying there.

I'll track what's happening on that bug issue thread on their side and hopefully we can get a fix from them soon.

[richb-hanover]

Update: I know more than I used to...

1. I posted a note on the DuckDB Discord server asking for help with this
2. I figured out how to install duckdb cli and can reproduce the error there. (TL;DR: download the macOS installer from https://duckdb.org/docs/installation/, copy the resulting binary to your favorite directory, then start it with ./duckdb) I get the following results:

?137 pqtest % ./duckdb
v0.5.1 7c111322d
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D INSTALL sqlite_scanner;
D LOAD sqlite_scanner;
D CALL sqlite_attach('chinook.sqlite');
D select * from albums limit 5;
Error: Invalid Error: Expected integer, got something else
D select title from albums limit 5;
┌───────────────────────────────────────┐
│                 title                 │
├───────────────────────────────────────┤
│ title                                 │
│ For Those About To Rock We Salute You │
│ Balls to the Wall                     │
│ Restless and Wild                     │
│ Let There Be Rock                     │
└───────────────────────────────────────┘
D select album_id from albums limit 5;
Error: Invalid Error: Expected integer, got something else

[richb-hanover]

New bug report filed in sqlite_scanner repo: duckdb/sqlite_scanner#15

[snth]

Thanks for all your help with this @richb-hanover . I have created a new version of the chinook.sqlite file which shouldn't have those string headers in the numeric fields anymore.

The sqlite3 version on my system (Ubuntu 20.04) is pretty old and didn't have the --skip 1 option so I created the db without the table definitions (which therefore would have been inferred from the csv files). I hope they are all alright. Once I get a bit more time and can install a more up to date sqlite3 version I can create another version with the column types specified.

I hope this is enough your problems in the meantime. The example queries I tried worked now so I removed the WIP disclaimer from the SQLite section in the README.

[richb-hanover]

Thanks for the update. I am not using the Chinook database for any real work - only to test out some queries against a real database. But it's real useful to have a consistent database

And as an "old software tester" when I run into an anomaly ("... got something else") I just had to run the test case to the ground. There's a perfectly reasonable explanation, so I'm content. I'm going to close this, since we now know what's happening. Thanks again.