Fix DISTINCT query with JOIN on multiple segmentby columns by konskov · Pull Request #5680 · timescale/timescaledb

konskov · 2023-05-11T09:49:56Z

Previously when adding equivalence class members for the compressed
chunk's variables, we would only consider Vars. This led us to ignore
cases where the Var was wrapped in a RelabelType,
returning inaccurate results.

Fixed the issue by accepting Vars
with RelabelType for segmentby equivalence class.

Fixes #5585

codecov · 2023-05-11T10:06:26Z

Codecov Report

Merging #5680 (547c191) into main (fb65086) will increase coverage by 3.26%.
The diff coverage is 100.00%.

❗ Current head 547c191 differs from pull request most recent head 07433cc. Consider uploading reports for the commit 07433cc to get more accurate results

@@            Coverage Diff             @@
##             main    #5680      +/-   ##
==========================================
+ Coverage   87.71%   90.98%   +3.26%     
==========================================
  Files         231      230       -1     
  Lines       54803    54624     -179     
  Branches    12055        0   -12055     
==========================================
+ Hits        48073    49697    +1624     
- Misses       4902     4927      +25     
+ Partials     1828        0    -1828

Impacted Files	Coverage Δ
tsl/src/nodes/decompress_chunk/decompress_chunk.c	`94.52% <100.00%> (+4.00%)`	⬆️

... and 190 files with indirect coverage changes

📣 We’re building smart automated test selection to slash your CI/CD build times. Learn more

akuzm · 2023-05-11T13:42:29Z

+-- github issue 5585
+create table kon (
+    time timestamptz not null,
+    a varchar(255) not null,
+    b int,
+    c int
+);
+SELECT create_hypertable('kon', 'time');
+WARNING:  column type "character varying" used for "a" does not follow best practices


What do you think about putting this into a separate file? This one's output is already 9k lines, and it's versioned, it's becoming unwieldy.

sure, I will create a new test file for this, is transparent_decompression_idx_join a good name? and should this test be versioned as well? I think it would be good to make it a versioned test since transparent_decompression is too
Edit: I see we already have transparent_decompression_ordered_index which looks like a better place to add this test

Yep, that one is smaller. About versions, I try to avoid them when possible, because they are inconvenient to work with.

akuzm

Doesn't look like it would harm. Just don't forget to add the proper node type checks, now you have a raw cast.

github-actions · 2023-05-12T08:32:49Z

@gayyappan, @nikkhils: please review this pull request.

Powered by pull-review

konskov · 2023-05-15T06:18:05Z

Thank you for reviewing @akuzm! Actually I’m not sure what node type checks to add and where, could you point them out if it’s not too much trouble? Thank you so much!

akuzm · 2023-05-16T07:57:21Z

Thank you for reviewing @akuzm! Actually I’m not sure what node type checks to add and where, could you point them out if it’s not too much trouble? Thank you so much!

I just meant the IsA, I think you added it already.

fabriziomello · 2023-05-16T17:35:30Z

+-- force an index scan
+set enable_seqscan = 'off';
+-- disable jit to avoid test flakiness
+set jit_above_cost = -1;


Suggested change

set jit_above_cost = -1;

set jit = off;

This is the correct way to complete turn off JIT

fabriziomello · 2023-05-16T17:36:08Z

+-- force an index scan
+set enable_seqscan = 'off';
+-- disable jit to avoid test flakiness
+set jit_above_cost = -1;


Suggested change

set jit_above_cost = -1;

set jit = off;

This is the correct way to complete turn off JIT

fixed, thank you

Previously when adding equivalence class members for the compressed chunk's variables, we would only consider Vars. This led us to ignore cases where the Var was wrapped in a RelabelType, returning inaccurate results. Fixed the issue by accepting Vars with RelabelType for segmentby equivalence class. Fixes timescale#5585

timescale-automation · 2023-05-17T09:59:50Z

Automated backport to 2.10.x not done: cherry-pick failed.

Git status

HEAD detached at origin/2.10.x
You are currently cherry-picking commit 19dd7bbd.
  (fix conflicts and run "git cherry-pick --continue")
  (use "git cherry-pick --skip" to skip this patch)
  (use "git cherry-pick --abort" to cancel the cherry-pick operation)

Changes to be committed:
	modified:   CHANGELOG.md
	modified:   tsl/src/nodes/decompress_chunk/decompress_chunk.c
	new file:   tsl/test/expected/transparent_decompression_join_index.out
	new file:   tsl/test/sql/transparent_decompression_join_index.sql

Unmerged paths:
  (use "git add <file>..." to mark resolution)
	both modified:   tsl/test/sql/CMakeLists.txt

Job log

@mrksngl

This release contains bug fixes since the 2.11.0 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5909 CREATE INDEX ONLY ON hypertable creates index on chunks * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5909 CREATE INDEX ONLY ON hypertable creates index on chunks * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5909 CREATE INDEX ONLY ON hypertable creates index on chunks * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5909 CREATE INDEX ONLY ON hypertable creates index on chunks * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5909 CREATE INDEX ONLY ON hypertable creates index on chunks * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5909 CREATE INDEX ONLY ON hypertable creates index on chunks * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * #5923 Feature flags for TimescaleDB features **Bugfixes** * #5680 Fix DISTINCT query with JOIN on multiple segmentby columns * #5774 Fixed two bugs in decompression sorted merge code * #5786 Ensure pg_config --cppflags are passed * #5906 Fix quoting owners in sql scripts. * #5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * timescale#5923 Feature flags for TimescaleDB features **Bugfixes** * timescale#5680 Fix DISTINCT query with JOIN on multiple segmentby columns * timescale#5774 Fixed two bugs in decompression sorted merge code * timescale#5786 Ensure pg_config --cppflags are passed * timescale#5906 Fix quoting owners in sql scripts. * timescale#5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * #5923 Feature flags for TimescaleDB features **Bugfixes** * #5680 Fix DISTINCT query with JOIN on multiple segmentby columns * #5774 Fixed two bugs in decompression sorted merge code * #5786 Ensure pg_config --cppflags are passed * #5906 Fix quoting owners in sql scripts. * #5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * #5923 Feature flags for TimescaleDB features **Bugfixes** * #5680 Fix DISTINCT query with JOIN on multiple segmentby columns * #5774 Fixed two bugs in decompression sorted merge code * #5786 Ensure pg_config --cppflags are passed * #5906 Fix quoting owners in sql scripts. * #5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

@mrksngl

This release contains bug fixes since the 2.11.1 release. We recommend that you upgrade at the next available opportunity. **Features** * #5923 Feature flags for TimescaleDB features **Bugfixes** * #5680 Fix DISTINCT query with JOIN on multiple segmentby columns * #5774 Fixed two bugs in decompression sorted merge code * #5786 Ensure pg_config --cppflags are passed * #5906 Fix quoting owners in sql scripts. * #5912 Fix crash in 1-step integer policy creation **Thanks** * @mrksngl for submitting a PR to fix extension upgrade scripts * @ericdevries for reporting an issue with DISTINCT queries using segmentby columns of compressed hypertable

github-actions bot assigned konskov May 11, 2023

konskov force-pushed the distinct_compress_fix_5585 branch 3 times, most recently from c902339 to df3e4cd Compare May 11, 2023 12:24

akuzm reviewed May 11, 2023

View reviewed changes

akuzm approved these changes May 11, 2023

View reviewed changes

konskov force-pushed the distinct_compress_fix_5585 branch 4 times, most recently from dee2300 to 832ebea Compare May 12, 2023 07:26

konskov changed the title ~~Accept RelabelType for segby equivclass~~ Fix DISTINCT query with JOIN on multiple segmentby columns May 12, 2023

konskov marked this pull request as ready for review May 12, 2023 08:32

github-actions bot requested review from gayyappan and nikkhils May 12, 2023 08:32

konskov force-pushed the distinct_compress_fix_5585 branch 3 times, most recently from 547c191 to 608a5d3 Compare May 15, 2023 06:14

sb230132 approved these changes May 16, 2023

View reviewed changes

konskov force-pushed the distinct_compress_fix_5585 branch 2 times, most recently from 4348919 to 4a31991 Compare May 16, 2023 11:40

konskov enabled auto-merge (rebase) May 16, 2023 12:42

konskov disabled auto-merge May 16, 2023 12:50

konskov enabled auto-merge (rebase) May 16, 2023 12:50

fabriziomello reviewed May 16, 2023

View reviewed changes

konskov disabled auto-merge May 16, 2023 18:22

konskov force-pushed the distinct_compress_fix_5585 branch from 4a31991 to 07433cc Compare May 17, 2023 05:16

konskov merged commit 19dd7bb into timescale:main May 17, 2023

timescale-automation added the auto-backport-not-done Automated backport of this PR has failed non-retriably (e.g. conflicts) label May 17, 2023

konskov added this to the TimescaleDB 2.11.2 milestone Aug 3, 2023

konskov mentioned this pull request Aug 9, 2023

Release 2.11.2 #5945

Merged

timescale-automation added the backported-2.11.x label Aug 17, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fix DISTINCT query with JOIN on multiple segmentby columns#5680

Fix DISTINCT query with JOIN on multiple segmentby columns#5680
konskov merged 1 commit intotimescale:mainfrom
konskov:distinct_compress_fix_5585

konskov commented May 11, 2023 •

edited

Loading

Uh oh!

codecov bot commented May 11, 2023 •

edited

Loading

Uh oh!

akuzm May 11, 2023

Uh oh!

konskov May 12, 2023 •

edited

Loading

Uh oh!

akuzm May 12, 2023

Uh oh!

akuzm left a comment

Uh oh!

github-actions bot commented May 12, 2023

Uh oh!

konskov commented May 15, 2023

Uh oh!

akuzm commented May 16, 2023

Uh oh!

fabriziomello May 16, 2023

Uh oh!

fabriziomello May 16, 2023

Uh oh!

konskov May 17, 2023

Uh oh!

timescale-automation commented May 17, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

Conversation

konskov commented May 11, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

codecov bot commented May 11, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

akuzm May 11, 2023

Choose a reason for hiding this comment

Uh oh!

konskov May 12, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

akuzm May 12, 2023

Choose a reason for hiding this comment

Uh oh!

akuzm left a comment

Choose a reason for hiding this comment

Uh oh!

github-actions bot commented May 12, 2023

Uh oh!

konskov commented May 15, 2023

Uh oh!

akuzm commented May 16, 2023

Uh oh!

fabriziomello May 16, 2023

Choose a reason for hiding this comment

Uh oh!

fabriziomello May 16, 2023

Choose a reason for hiding this comment

Uh oh!

konskov May 17, 2023

Choose a reason for hiding this comment

Uh oh!

timescale-automation commented May 17, 2023

Git status

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

konskov commented May 11, 2023 •

edited

Loading

codecov bot commented May 11, 2023 •

edited

Loading

konskov May 12, 2023 •

edited

Loading