Unable to select data from the device_file table even with partition and device columns provided in where clause

Bug report

What operating system and version are you using?

version = CentOS Linux release 7.7.1908 (Core) build = platform = rhel

What version of osquery are you using?

version = 4.3.0

What steps did you take to reproduce the issue?

My goal is to select a list of rows from the device_file table for known devices. It appears that selecting from that table requires both the partition and device columns be provided in the where clause. In order to identify some test values for the where clause, it appeared that I could utilize the data provided by querying the device_partitions table for a known device

osquery> select device,partition from device_partitions where device='/dev/sda';

+----------+-----------+
| device | partition |
+----------+-----------+
| /dev/sda | 0 |
| /dev/sda | 1 |
| /dev/sda | 2 |
| /dev/sda | 3 |
+----------+-----------+
I then tried to join the device_partitions table to the device_file using the query below, but no results were returned. No errors, but no results. I tried 0-4 for the partition value and nothing was returned for any of them (Partition 4 had the label "primary").

osquery> select * from device_partitions dp join device_file df on dp.device=df.device and dp.partition=df.partition where df.partition=4 and df.device='/dev/sda';

Note I tried it without a join on the device_partitions table and it did not return any results either.

What did you expect to see?

I expected a table of results. It would be helpful also to have the error message that indicates a constraint failed identify the columns required in the where clause within the error message itself.

What did you see instead?

No results.

Start by reproducing the reported queries with osqueryi against device_partitions and device_file on the stated osquery 4.3.0 environment. Trace how device_file handles the device and partition constraints. Done means the query returns the expected rows or reports a clear error identifying the required constraints.