Osquery – JOIN with Users table not returning results

This is a question that I have seen asked many times (and yes, a few of those were me!):

“I can’t figure out why my query isn’t working. `SELECT * FROM users JOIN chrome_extensions USING (uid);` works and returns the list of extensions, but `SELECT * FROM users JOIN chrome_extensions USING (uid) WHERE identifier=”;` returns empty even though its there.”

On the osquery Slack I recently saw a great explanation for this issue from Zach (https://github.com/zwass):

“Use `CROSS JOIN` for those queries where you need to join against the users table. The sqlite optimizer will sometimes change the order of the join otherwise and cause false negatives… When `CROSS JOIN` is used, ensure that the users table appears before the `CROSS JOIN` “

From the sqlite docs (https://www.sqlite.org/lang_select.html):

There is no difference between the “INNER JOIN”, “JOIN” and “,” join operators. They are completely interchangeable in SQLite. The “CROSS JOIN” join operator produces the same result as the “INNER JOIN”, “JOIN” and “,” operators, but is handled differently by the query optimizer in that it prevents the query optimizer from reordering the tables in the join.

But according to Zach:

“Note the docs indicate that the results will be the same, but this is not the case because changing the loop nesting ordering actually changes the results of the tables in osquery (remember that some tables only return data when they have the appropriate constraints in the WHERE clause). This would not be a property that the SQLite optimizer expects. There is also a good explanation for why it works for some folks and not others: the optimizer is making different decisions about reordering tables.”