BUG #15551: Date/Time comparison not correct when the comparison isinside join clause and involves "+" or "-" - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15551
Logged by:          Lei He
Email address:      whereverlei@gmail.com
PostgreSQL version: 11.1
Operating system:   macOS Mojave 10.14
Description:

dil=# select version();
                                                     version
                                     
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM
version 10.0.0 (clang-1000.11.45.5), 64-bit
(1 row)



I want to compute growth of something quarterly. Here is the simplified
data:

with code_time as (
  select code, "time" from (values
    ('000001', date '2016-12-31'),
    ('000001', date '2017-03-31'),
    ('000001', date '2017-06-30'),
    ('000001', date '2017-09-30'),
    ('000001', date '2017-12-31'),
    ('000001', date '2018-03-31'),
    ('000001', date '2018-06-30'),
    ('000001', date '2018-09-30'),
    ('000001', date '2018-12-31'),
    ('000002', date '2016-12-31'),
    ('000002', date '2017-03-31'),
    ('000002', date '2017-06-30'),
    ('000002', date '2017-09-30'),
    ('000002', date '2017-12-31'),
    ('000002', date '2018-03-31'),
    ('000002', date '2018-06-30'),
    ('000002', date '2018-09-30'),
    ('000002', date '2018-12-31')
  ) t("code", "time")
)

The result I need is like this:
  code  | cur_quarter | pre_quarter 
--------+-------------+-------------
 000001 | 2017-03-31  | 2016-12-31
 000001 | 2017-06-30  | 2017-03-31
 000001 | 2017-09-30  | 2017-06-30
 000001 | 2017-12-31  | 2017-09-30
 000001 | 2018-03-31  | 2017-12-31
 000001 | 2018-06-30  | 2018-03-31
 000001 | 2018-09-30  | 2018-06-30
 000001 | 2018-12-31  | 2018-09-30
 000002 | 2017-03-31  | 2016-12-31
 000002 | 2017-06-30  | 2017-03-31
 000002 | 2017-09-30  | 2017-06-30
 000002 | 2017-12-31  | 2017-09-30
 000002 | 2018-03-31  | 2017-12-31
 000002 | 2018-06-30  | 2018-03-31
 000002 | 2018-09-30  | 2018-06-30
 000002 | 2018-12-31  | 2018-09-30

If I use query
select ct1.code, ct1."time" cur_quarter, ct2."time" pre_quarter from
code_time ct1
join (select code, "time" from code_time) ct2 on ct1.code = ct2.code and
ct1."time" = ct2."time" + interval '3 months'
order by ct1.code, ct1."time";

I got:
  code  | cur_quarter | pre_quarter 
--------+-------------+-------------
 000001 | 2017-03-31  | 2016-12-31
 000001 | 2017-06-30  | 2017-03-31
 000001 | 2017-09-30  | 2017-06-30
 000001 | 2018-03-31  | 2017-12-31
 000001 | 2018-06-30  | 2018-03-31
 000001 | 2018-09-30  | 2018-06-30
 000002 | 2017-03-31  | 2016-12-31
 000002 | 2017-06-30  | 2017-03-31
 000002 | 2017-09-30  | 2017-06-30
 000002 | 2018-03-31  | 2017-12-31
 000002 | 2018-06-30  | 2018-03-31
 000002 | 2018-09-30  | 2018-06-30

Note: the fourth quarter (*-12-31) of all years are missing.

If I use query
select ct1.code, ct1."time" cur_quarter, ct2."time" pre_quarter from
code_time ct1
join (select code, "time" from code_time) ct2 on ct1.code = ct2.code and
ct2."time" = ct1."time" - interval '3 months'
order by ct1.code, ct1."time";

I got:
  code  | cur_quarter | pre_quarter 
--------+-------------+-------------
 000001 | 2017-03-31  | 2016-12-31
 000001 | 2017-09-30  | 2017-06-30
 000001 | 2017-12-31  | 2017-09-30
 000001 | 2018-03-31  | 2017-12-31
 000001 | 2018-09-30  | 2018-06-30
 000001 | 2018-12-31  | 2018-09-30
 000002 | 2017-03-31  | 2016-12-31
 000002 | 2017-09-30  | 2017-06-30
 000002 | 2017-12-31  | 2017-09-30
 000002 | 2018-03-31  | 2017-12-31
 000002 | 2018-09-30  | 2018-06-30
 000002 | 2018-12-31  | 2018-09-30

Note: the second quarter (*-06-30) of all years are missing.

Only if I use query
select ct1.code, ct1."time" cur_quarter, ct2."time" pre_quarter from
code_time ct1
join (select code, "time" from code_time) ct2 on ct1.code = ct2.code and (
ct1."time" = ct2."time" + interval '3 months' or ct2."time" = ct1."time" -
interval '3 months' )
order by ct1.code, ct1."time";

can I get the expected result:
  code  | cur_quarter | pre_quarter 
--------+-------------+-------------
 000001 | 2017-03-31  | 2016-12-31
 000001 | 2017-06-30  | 2017-03-31
 000001 | 2017-09-30  | 2017-06-30
 000001 | 2017-12-31  | 2017-09-30
 000001 | 2018-03-31  | 2017-12-31
 000001 | 2018-06-30  | 2018-03-31
 000001 | 2018-09-30  | 2018-06-30
 000001 | 2018-12-31  | 2018-09-30
 000002 | 2017-03-31  | 2016-12-31
 000002 | 2017-06-30  | 2017-03-31
 000002 | 2017-09-30  | 2017-06-30
 000002 | 2017-12-31  | 2017-09-30
 000002 | 2018-03-31  | 2017-12-31
 000002 | 2018-06-30  | 2018-03-31
 000002 | 2018-09-30  | 2018-06-30
 000002 | 2018-12-31  | 2018-09-30


The only difference among these query lies in the join clause involving date
/ time comparison having date / time operators being used:
1st: ct1."time" = ct2."time" + interval '3 months'
2nd: ct2."time" = ct1."time" - interval '3 months'
3rd: ct1."time" = ct2."time" + interval '3 months' or ct2."time" =
ct1."time" - interval '3 months'

It seems to me all the 3 conditions are logically same and should have the
same result.