https://github.com/osquery/osquery logo
Title
e

ec4

11/24/2021, 5:12 PM
👋 Hi, any idea why
join
returns results but
left join
returns 0 results?
SELECT *
FROM docker_container_processes process
JOIN docker_containers container ON container.id=process.id
vs.
SELECT *
FROM docker_container_processes process
LEFT JOIN docker_containers container ON container.id=process.id
f

fritz

11/24/2021, 6:29 PM
iirc the docker_container_process table has a required column for the where clause
as a result you need to ensure the tables are being joined in the appropriate order
You can force the SQLite engine to query in the expected order by utilizing a
CROSS JOIN
, I have described this nuance in the following blog article: https://blog.kolide.com/running-osquery-as-sudo-root-vs-user-4fcfc698c45e
So in your first case you want to be running:
SELECT *
FROM docker_containers container
CROSS JOIN docker_container_processes process ON container.id=process.id;
The
LEFT JOIN
cannot work in the order you are trying query, because you are trying to
SELECT * FROM docker_container_processes
without a required where condition.
e

ec4

11/24/2021, 8:57 PM
@fritz Ohh interesting. In the first case,
join
alone appears to be working as I can see results. In the 2nd case
left join
does not appear to be working. Thanks I’ll check out
cross
and
where
🙇
f

fritz

11/24/2021, 8:58 PM
@ec4 it is working by luck
basically what is happening is the following
sqlite sees your FROM and JOIN clauses
and it decides based on some internal rule logic which table to call from first
e

ec4

11/24/2021, 8:58 PM
@ec4 it is working by luck
lol story of my life. Thanks for explaining that
It’s strange. I would expect it to compare it against the first table
f

fritz

11/24/2021, 8:59 PM
if you happen to call a column from the table without the required where clause first, it will order it that way
without using CROSS JOIN, it just does what it thinks is best
which may or may not be what you expect/want
:til: 1