Timescaledb: Full Outer Join giving wrong result on hyper tables

[zeeshanshabbir93]

Relevant system information:

• OS: [Ubuntu 18]
• PostgreSQL version :[PostgreSQL 11.10 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.3.0) 9.3.0, 64-bit]
• TimescaleDB version : [2.0.0]
• Installation method: [docker]

Describe the bug
The behavior of full outer join in hyper tables is erroneous. Same query run on plain Postgres tables produces correct results. Data from one side of the outer join is removed.
timescale issue-github.zip

To Reproduce
Steps to reproduce the behavior:
Plain Postgres table
• Create a blank database & restore the postgres dump file ts_github.sql (from attachment)
• Run the query provided in query.sql file (from attachment).
Note that the count of records returned by the query is 1536. The count of rows return by the first part of the query (if ran separately) is 1536. The count of rows returned by the second part of the query (if ran separately) is 96. The outer join which combines the results of both queries produces 1536 rows which is correct.
Hyper Table
• Add TimescaleDb extension using : CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
• Create hyper tables using : SELECT create_hypertable('second_fact', 'timestamp', chunk_time_interval => interval '1 day', if_not_exists => TRUE, migrate_data => TRUE);
• Rerun the same query from query.sql file.
Note that the count of records returned by the query is 96. The count of rows return by the first part of the query (if ran separately) is 1536. The count of rows returned by the second part of the query (if ran separately) is 96. The outer join which combines the results of both queries produces 96 rows which is incorrect.

Expected behavior
1536 rows should get returned

Actual behavior
96 rows are getting retunred.

Additional context
Reproducible On
• timescale/timescaledb:latest-pg11
• PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit with TimescaleDB extension 1.4.2

Not Reproducible On
• timescaledb:latest-pg12
• PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit & timescale db 1.7.4

[k-rus]

@zeeshanshabbir93 Thank you for reporting and for the reproduction instructions!
Have you been using older version of TimescaleDB, e.g., 1.7.4? If so, I am wonder if it worked correctly there.

[zeeshanshabbir93]

@k-rus The issue persists for 1.4.2 & 1
.7.4 as well for postgres 11

[davidkohn88]

Interesting, but not on PG12. Thanks that's good info, should help us isolate...