Inner join problems with versions 5.0.3+

[rlindner81]

Our project's unit tests started failing for sqlite3 versions after 5.0.2.

After investigations, I made a script with the smallest reproducible query that delivers a wrong result:

const sqlite3 = require("sqlite3").verbose();
const db = new sqlite3.Database("test.db");

const QUERY = `
    SELECT c.id AS "c_ID",
           b.id AS "b_ID"
    FROM localized_defineservice_c_closingfolderorgunittp c
             INNER JOIN localized_defineservice_c_closinghierarchynodetp b ON (b.id = c.to_closinghierarchynode_id)
`;

db.all(QUERY, function(err, rows) {
  if (err) {
    console.error(err);
    db.close();
    return;
  }
  console.log("result:", rows);
  db.close();
});

Db and script file:
sqlite-test.zip

If you run this query with any other driver or sqlite3 up to 5.0.2., it will return 200 results, but for recent versions it will return 0 results.

[daniellockyer]

I think this is a bug in SQLite. If I compile SQLite 3.34.0 (what 5.0.2 shipped with), I get all the rows. If I compile 3.38.2 (or even the latest prerelease), I get nothing. If I re-run the query, I get results.

[daniellockyer]

Reported here: https://www.sqlite.org/forum/forumpost/0d3200f4f3

[rlindner81]

Hi @daniellockyer, thanks for taking a look so quickly! It can well be a problem in sqlite. I did test other drivers and it worked, but I suspect they emebed sqlite in the driver as well, so they might be on a version that still works.

[daniellockyer]

Hey @rlindner81 - good news! It's been fixed and will be out in SQLite v3.38.3. I'll update node-sqlite3 and publish when it's available 🙂

[rlindner81]

Very nice, thanks for pushing this upstream.

[daniellockyer]

Fix is in master, CI is working on the prebuilt binaries so v5.0.6 will be out shortly 🙂