I make SQL-queries in Hive using Hue. When I use inner join between two tables, one of the table’s column get new distinct values, that wasn’t in this column before join.

I have table periods with columns period_name and period_first_dt, table traffic with columns period_first_dt, product_id and traffic_sum. Detailed info about tables below:

Table periods

Table traffic

I want to have period_name in traffic table and calculate sum of traffic_sum by period_name. I inner join two tables like this:

with periods as ( 
    select 
      period_name, 
      cast(period_first_dt as date) as period_first_dt
    from 
      periods
    where 1=1 
      and period_name in ('NOV-23', 'DEC-23')
    )
select
  p.period_name,
  sum(t.traffic_sum) as traffic_sum  
from  
  traffic t
join
  periods p on p.period_first_dt = cast(substring(t.period_first_dt, 1, 10) as date)
where 1=1
  and t.product_id = 135
group by
  p.period_name
;

CTE periods have following values:

Query above have following results:

Column period_name somehow have new values that wasn’t before inner join: 2023-11-01 and 2023-12-01. When I change condition in CTE and period_name in ('NOV-23', 'DEC-23') to and period_name = 'DEC-23', results are without new values in period_name:

When I remove condition and t.product_id = 135 and leave condition in CTE and period_name in ('NOV-23', 'DEC-23'), results are somehow without new values in period_name:

Why new distinct values appear in period_name and how to fix that?