Different results when using gem between macOS and Ubuntu

[Gareth Rees]

I'm getting different query results when I use the gem vs the sqlite3 CLI.

The problem only seems to exhibit itself on macOS (currently Mojave 10.14.6).

I've attached some files that replicate the issue.

I'll step through the expected / actual on each OS, but here's an explainer of the files:

* create-db.rb: creates the sqlite DB and pre-fills it with some data

* daily.sql: the SQL that I'm trying to run. It tallies the number of occurrences of records per day, and fills in missing days with zero values

* query-db.rb: Reads daily.sql and executes it through the ruby bindings and pretty-prints the results

I've also included a Vagrantfile that installs the prerequisites on Ubuntu Cosmic, as that has the same sqlite3 version that I have on macOS.

Okay, let's dig in to this. I'll start with Ubuntu.

vagrant@ubuntu-cosmic:/vagrant$ ruby create-db.rb


vagrant@ubuntu-cosmic:/vagrant$ ruby query-db.rb
[["2018-01-01", 1, 2],
 ["2018-01-02", 0, 0],
 ["2018-01-03", 1, 0],
 ["2018-01-04", 0, 0],
 ["2018-01-05", 1, 1],
 ["2018-01-06", 0, 0],
 ["2018-01-07", 1, 0]]


vagrant@ubuntu-cosmic:/vagrant$ cat daily.sql | sqlite3 db.sqlite
2018-01-01|1|2
2018-01-02|0|0
2018-01-03|1|0
2018-01-04|0|0
2018-01-05|1|1
2018-01-06|0|0
2018-01-07|1|0


vagrant@ubuntu-cosmic:/vagrant$ sqlite3 -version
3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199aalt1


vagrant@ubuntu-cosmic:/vagrant$ ruby -v
ruby 2.5.1p57 (2018-03-29 revision 63029) [x86_64-linux-gnu]

And now for macOS. Note how query-db.rb returns zero values for all dates.

I am using Ruby 2.6.2 on macOS vs 2.5.1 on Ubuntu.

gareth: ~/Desktop/sqlite_issue $ rm db.sqlite

gareth: ~/Desktop/sqlite_issue $ ruby create-db.rb

gareth: ~/Desktop/sqlite_issue $ ruby query-db.rb
[["2018-01-01", 0, 0],
 ["2018-01-02", 0, 0],
 ["2018-01-03", 0, 0],
 ["2018-01-04", 0, 0],
 ["2018-01-05", 0, 0],
 ["2018-01-06", 0, 0],
 ["2018-01-07", 0, 0]]

gareth: ~/Desktop/sqlite_issue $ cat daily.sql | sqlite3 db.sqlite
2018-01-01|1|2
2018-01-02|0|0
2018-01-03|1|0
2018-01-04|0|0
2018-01-05|1|1
2018-01-06|0|0
2018-01-07|1|0

gareth: ~/Desktop/sqlite_issue $ sqlite3 -version
3.24.0 2018-06-04 14:10:15 95fbac39baaab1c3a84fdfc82ccb7f42398b2e92f18a2a57bce1d4a713cbaapl

gareth: ~/Desktop/sqlite_issue $ ruby -v
ruby 2.6.2p47 (2019-03-13 revision 67232) [x86_64-darwin18]

Just out of interest, I installed Ruby 2.6.2 using RVM on Cosmic – I still get correct results:

vagrant@ubuntu-cosmic:/vagrant$ rm db.sqlite

vagrant@ubuntu-cosmic:/vagrant$ ruby create-db.rb

vagrant@ubuntu-cosmic:/vagrant$ ruby query-db.rb
[["2018-01-01", 1, 2],
 ["2018-01-02", 0, 0],
 ["2018-01-03", 1, 0],
 ["2018-01-04", 0, 0],
 ["2018-01-05", 1, 1],
 ["2018-01-06", 0, 0],
 ["2018-01-07", 1, 0]]

vagrant@ubuntu-cosmic:/vagrant$ cat daily.sql | sqlite3 db.sqlite
2018-01-01|1|2
2018-01-02|0|0
2018-01-03|1|0
2018-01-04|0|0
2018-01-05|1|1
2018-01-06|0|0
2018-01-07|1|0

vagrant@ubuntu-cosmic:/vagrant$ ruby -v
ruby 2.6.2p47 (2019-03-13 revision 67232) [x86_64-linux]

So, in summary, I feel like there may be an issue with the macOS version of the sqlite gem.

Thanks to anyone who can take a look at this.

Best,

Gareth