Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Filter with VIEW with hypertables + large array filtering + interval #1864

Closed
airton-neto opened this issue Apr 28, 2020 · 0 comments · Fixed by #1886
Closed

Filter with VIEW with hypertables + large array filtering + interval #1864

airton-neto opened this issue Apr 28, 2020 · 0 comments · Fixed by #1886
Assignees
Labels
Milestone

Comments

@airton-neto
Copy link

Relevant system information:

  • OS: Ubuntu 18.04
  • PostgreSQL 11.7
  • TimescaleDB version 1.6
  • Installation method: Docker

Describe the bug
Case of use: We have a VIEW v AS (SELECT * FROM hypertable1 UNION SELECT * FROM hypertable2), keys device_id integer, sample_time timestamptz, value columns
Bug: We're trying to fetch
SELECT * FROM v
WHERE device_id = ANY(ARRAY[...])
AND sample_time >= '2020-04-26 America/Fortaleza'::timestamptz
AND sample_time < '2020-04-26 America/Fortaleza'::timestamptz + interval '1 day'
We noticed that when the array has 125 or less device_ids, the rows returns correctly (144 per device). When the array size is higher (our common use case is 156 device_ids), the query returns 0 rows.
When we change to
AND sample_time >= '2020-04-26 America/Fortaleza'::timestamptz
AND sample_time < '2020-04-27 America/Fortaleza'::timestamptz
the rows returns correctly with any array size. When we fetch directly from the hypertable2, it returns correctly too.
To Reproduce
Maybe creating separate hypertable using many key device_ids filtering should reproduce the error.

Expected behavior
I expect to return rows for any (or at least for very large) ARRAY sizes on clause ANY

Actual behavior
Summing interval may somehow make rows not to return.

Screenshots
If applicable, add screenshots to help explain your problem.
image
image

@WireBaron WireBaron self-assigned this Apr 29, 2020
@erimatnor erimatnor added this to the 1.7.1 milestone May 11, 2020
svenklemm added a commit to svenklemm/timescaledb that referenced this issue May 15, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* timescale#1834 Define strerror() for Windows
* timescale#1846 Fix segfault on COPY to hypertable
* timescale#1850 Fix scheduler failure due to bad next_start_time for jobs
* timescale#1851 Fix hypertable expansion for UNION ALL
* timescale#1861 Fix qual pushdown for compressed hypertables where quals have casts
* timescale#1864 Fix issue with subplan selection in parallel ChunkAppend
* timescale#1868 Add support for WHERE, HAVING clauses with real time aggregates
* timescale#1871 Don't rely on timescaledb.restoring for upgrade
* timescale#1875 Fix hypertable detection in subqueries

**Thanks**
* @frostwind for reporting issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @pehlert for reporting an issue with pg_upgrade
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
svenklemm added a commit to svenklemm/timescaledb that referenced this issue May 15, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* timescale#1834 Define strerror() for Windows
* timescale#1846 Fix segfault on COPY to hypertable
* timescale#1850 Fix scheduler failure due to bad next_start_time for jobs
* timescale#1851 Fix hypertable expansion for UNION ALL
* timescale#1861 Fix qual pushdown for compressed hypertables where quals have casts
* timescale#1864 Fix issue with subplan selection in parallel ChunkAppend
* timescale#1868 Add support for WHERE, HAVING clauses with real time aggregates
* timescale#1871 Don't rely on timescaledb.restoring for upgrade
* timescale#1875 Fix hypertable detection in subqueries
* timescale#1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @frostwind for reporting issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @pehlert for reporting an issue with pg_upgrade
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
svenklemm added a commit to svenklemm/timescaledb that referenced this issue May 15, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* timescale#1834 Define strerror() for Windows
* timescale#1846 Fix segfault on COPY to hypertable
* timescale#1850 Fix scheduler failure due to bad next_start_time for jobs
* timescale#1851 Fix hypertable expansion for UNION ALL
* timescale#1854 Fix reorder policy job to skip compressed chunks
* timescale#1861 Fix qual pushdown for compressed hypertables where quals have casts
* timescale#1864 Fix issue with subplan selection in parallel ChunkAppend
* timescale#1868 Add support for WHERE, HAVING clauses with real time aggregates
* timescale#1871 Don't rely on timescaledb.restoring for upgrade
* timescale#1875 Fix hypertable detection in subqueries
* timescale#1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @frostwind for reporting an issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @pehlert for reporting an issue with pg_upgrade
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
svenklemm added a commit to svenklemm/timescaledb that referenced this issue May 15, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* timescale#1834 Define strerror() for Windows
* timescale#1846 Fix segfault on COPY to hypertable
* timescale#1850 Fix scheduler failure due to bad next_start_time for jobs
* timescale#1851 Fix hypertable expansion for UNION ALL
* timescale#1854 Fix reorder policy job to skip compressed chunks
* timescale#1861 Fix qual pushdown for compressed hypertables where quals have casts
* timescale#1864 Fix issue with subplan selection in parallel ChunkAppend
* timescale#1868 Add support for WHERE, HAVING clauses with real time aggregates
* timescale#1871 Don't rely on timescaledb.restoring for upgrade
* timescale#1875 Fix hypertable detection in subqueries
* timescale#1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @frostwind for reporting an issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @pehlert for reporting an issue with pg_upgrade
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
svenklemm added a commit to svenklemm/timescaledb that referenced this issue May 15, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading and high priority for users with multiple continuous aggregates.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* timescale#1834 Define strerror() for Windows
* timescale#1846 Fix segfault on COPY to hypertable
* timescale#1850 Fix scheduler failure due to bad next_start_time for jobs
* timescale#1851 Fix hypertable expansion for UNION ALL
* timescale#1854 Fix reorder policy job to skip compressed chunks
* timescale#1861 Fix qual pushdown for compressed hypertables where quals have casts
* timescale#1864 Fix issue with subplan selection in parallel ChunkAppend
* timescale#1868 Add support for WHERE, HAVING clauses with real time aggregates
* timescale#1869 Fix real time aggregate support for multiple continuous aggregates
* timescale#1871 Don't rely on timescaledb.restoring for upgrade
* timescale#1875 Fix hypertable detection in subqueries
* timescale#1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @frostwind for reporting an issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @louisth for reporting an issue with real-time aggregation and multiple continuous aggregates
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @pehlert for reporting an issue with pg_upgrade
svenklemm added a commit to svenklemm/timescaledb that referenced this issue May 17, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading and high priority for users with multiple continuous aggregates.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* timescale#1834 Define strerror() for Windows
* timescale#1846 Fix segfault on COPY to hypertable
* timescale#1850 Fix scheduler failure due to bad next_start_time for jobs
* timescale#1851 Fix hypertable expansion for UNION ALL
* timescale#1854 Fix reorder policy job to skip compressed chunks
* timescale#1861 Fix qual pushdown for compressed hypertables where quals have casts
* timescale#1864 Fix issue with subplan selection in parallel ChunkAppend
* timescale#1868 Add support for WHERE, HAVING clauses with real time aggregates
* timescale#1869 Fix real time aggregate support for multiple continuous aggregates
* timescale#1871 Don't rely on timescaledb.restoring for upgrade
* timescale#1875 Fix hypertable detection in subqueries
* timescale#1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @frostwind for reporting an issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @louisth for reporting an issue with real-time aggregation and multiple continuous aggregates
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @pehlert for reporting an issue with pg_upgrade
svenklemm added a commit to svenklemm/timescaledb that referenced this issue May 18, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading and high priority for users with multiple continuous aggregates.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* timescale#1834 Define strerror() for Windows
* timescale#1846 Fix segfault on COPY to hypertable
* timescale#1850 Fix scheduler failure due to bad next_start_time for jobs
* timescale#1851 Fix hypertable expansion for UNION ALL
* timescale#1854 Fix reorder policy job to skip compressed chunks
* timescale#1861 Fix qual pushdown for compressed hypertables where quals have casts
* timescale#1864 Fix issue with subplan selection in parallel ChunkAppend
* timescale#1868 Add support for WHERE, HAVING clauses with real time aggregates
* timescale#1869 Fix real time aggregate support for multiple continuous aggregates
* timescale#1871 Don't rely on timescaledb.restoring for upgrade
* timescale#1875 Fix hypertable detection in subqueries
* timescale#1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @frostwind for reporting an issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @louisth for reporting an issue with real-time aggregation and multiple continuous aggregates
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @pehlert for reporting an issue with pg_upgrade
svenklemm added a commit that referenced this issue May 18, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading and high priority for users with multiple continuous aggregates.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* #1834 Define strerror() for Windows
* #1846 Fix segfault on COPY to hypertable
* #1850 Fix scheduler failure due to bad next_start_time for jobs
* #1851 Fix hypertable expansion for UNION ALL
* #1854 Fix reorder policy job to skip compressed chunks
* #1861 Fix qual pushdown for compressed hypertables where quals have casts
* #1864 Fix issue with subplan selection in parallel ChunkAppend
* #1868 Add support for WHERE, HAVING clauses with real time aggregates
* #1869 Fix real time aggregate support for multiple continuous aggregates
* #1871 Don't rely on timescaledb.restoring for upgrade
* #1875 Fix hypertable detection in subqueries
* #1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @frostwind for reporting an issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @louisth for reporting an issue with real-time aggregation and multiple continuous aggregates
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @pehlert for reporting an issue with pg_upgrade
svenklemm added a commit that referenced this issue May 18, 2020
This maintenance release contains bugfixes since the 1.7.0 release. We deem it medium
priority for upgrading and high priority for users with multiple continuous aggregates.

In particular the fixes contained in this maintenance release address bugs in continuous
aggregates with real-time aggregation and PostgreSQL 12 support.

**Bugfixes**
* #1834 Define strerror() for Windows
* #1846 Fix segfault on COPY to hypertable
* #1850 Fix scheduler failure due to bad next_start_time for jobs
* #1851 Fix hypertable expansion for UNION ALL
* #1854 Fix reorder policy job to skip compressed chunks
* #1861 Fix qual pushdown for compressed hypertables where quals have casts
* #1864 Fix issue with subplan selection in parallel ChunkAppend
* #1868 Add support for WHERE, HAVING clauses with real time aggregates
* #1869 Fix real time aggregate support for multiple continuous aggregates
* #1871 Don't rely on timescaledb.restoring for upgrade
* #1875 Fix hypertable detection in subqueries
* #1884 Fix crash on SELECT WHERE NOT with empty table

**Thanks**
* @airton-neto for reporting an issue with queries over UNIONs of hypertables
* @dhodyn for reporting an issue with UNION ALL queries
* @frostwind for reporting an issue with casts in where clauses on compressed hypertables
* @fvannee for reporting an issue with hypertable detection in inlined SQL functions and an issue with COPY
* @hgiasac for reporting missing where clause with real time aggregates
* @louisth for reporting an issue with real-time aggregation and multiple continuous aggregates
* @michael-sayapin for reporting an issue with INSERTs and WHERE NOT EXISTS
* @Olernov for reporting and fixing an issue with compressed chunks in the reorder policy
* @pehlert for reporting an issue with pg_upgrade
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants