Title
#general
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
6:30 PM
as a result you need to ensure the tables are being joined in the appropriate order
6:30 PM
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
6:31 PM
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;
6:32 PM
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
8:58 PM
basically what is happening is the following
8:58 PM
sqlite sees your FROM and JOIN clauses
8:58 PM
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
8:59 PM
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
8:59 PM
without using CROSS JOIN, it just does what it thinks is best
8:59 PM
which may or may not be what you expect/want
:til: 1