[sqlite3-ruby] 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

-- 
You received this message because you are subscribed to the Google Groups 
"sqlite3-ruby" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlite3-ruby+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlite3-ruby/6ca00f3a-d8f9-4614-bf7a-60c23bf9ea3b%40googlegroups.com.

create-db.rb
Description: Binary data

WITH
  dtRange(minDT, maxDT) AS (
    SELECT MIN(date(sent_at)), MAX(date(sent_at))
    FROM emails
  ),
  dtAll(dt) AS (
    SELECT minDT
    FROM dtRange
    UNION ALL
    SELECT date(dt, '+1 day')
    FROM dtAll, dtRange
    WHERE dt < maxDT
  ),
  grouped_incoming(sent_on, incoming_count) AS (
    SELECT date(sent_at) AS sent_on, COUNT(*) AS incoming_count
    FROM emails
    WHERE message_type = 'incoming'
    GROUP BY sent_on
    ORDER BY sent_on ASC
  ),
  incoming_full(sent_on, incoming_count) AS (
    SELECT sent_on, incoming_count
    FROM grouped_incoming
    UNION
    SELECT DT, 0 FROM dtAll
  ),
  grouped_outgoing(sent_on, outgoing_count) AS (
    SELECT date(sent_at) AS sent_on, COUNT(*) AS outgoing_count
    FROM emails
    WHERE message_type = 'outgoing'
    GROUP BY sent_on
    ORDER BY sent_on ASC
  ),
  outgoing_full(sent_on, outgoing_count) AS (
    SELECT sent_on, outgoing_count
    FROM grouped_outgoing
    UNION
    SELECT DT, 0 FROM dtAll
  )
SELECT incoming_full.sent_on, incoming_count, outgoing_count
FROM incoming_full
JOIN outgoing_full
ON incoming_full.sent_on = outgoing_full.sent_on
GROUP BY incoming_full.sent_on
ORDER BY incoming_full.sent_on ASC
;

query-db.rb
Description: Binary data

Vagrantfile
Description: Binary data