The hash join does not support inequality yet. Try nested loop join by setting:
alter system set `planner.enable_nljoin_for_scalar_only` = false;
And change the LEFT to a regular join followed by an inequality predicate:
```
SELECT m.startdate as monthdate, COUNT(distinct p.period_id) subs
FROM dfs.test.months m
, dfs.test.periods p
WHERE p.startdate <= m.enddate AND p.enddate >= m.startdate
AND m.startdate BETWEEN '2016-01-01' AND '2017-02-01'
GROUP BY p.period_id, m.startdate
ORDER BY p.period_id, m.startdate;
```
Boaz
On 3/20/17, 2:54 PM, "Matt" <[email protected]> wrote:
Using a calendar table with monthly start and end dates, I am attempting
to count records in another table that has cycle start and end dates.
In PostgreSQL I would either use a date range type, or in standard SQL
do something like:
```
SELECT m.startdate as monthdate, COUNT(distinct p.period_id) subs
FROM dfs.test.months m
LEFT JOIN dfs.test.periods p ON p.startdate <= m.enddate AND p.enddate
>= m.startdate
WHERE m.startdate BETWEEN '2016-01-01' AND '2017-02-01'
GROUP BY p.period_id, m.startdate
ORDER BY p.period_id, m.startdate;
```
This should result in a list of period counts that occur in each month,
where the date range overlaps the month start and end date.
So far all my attempts with Drill result in **UNSUPPORTED_OPERATION
ERROR: This query cannot be planned possibly due to either a cartesian
join or an inequality join**.
Is there an approach that Drill can execute?
