You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Describe the bug
compressed table return wrong result when using explicit type conversion from timestamp to date. Result is correct when using decompressed table.
To Reproduce
Steps to reproduce the behavior:
`CREATE TABLE t (
fmid smallint,
factorid smallint,
start_dt timestamp without time zone,
end_dt timestamp without time zone,
interval_number smallint,
logret double precision,
knowledge_date date NOT NULL
);
SELECT create_hypertable('t', 'end_dt', chunk_time_interval=>interval '1 month', create_default_indexes=>false);
SELECT add_dimension('t', 'fmid', chunk_time_interval => 1);
Create unique index idx_t on t (end_dt ,fmid, factorid, knowledge_date);
ALTER TABLE t SET (timescaledb.compress, timescaledb.compress_orderby= 'end_dt');
Insert into t values (56,0,'2012-12-10 09:45:00','2012-12-10 09:50:00',1,0.1,'2012-12-10');
SELECT add_compress_chunks_policy('t', INTERVAL '1 month');
##run compression if auto compression is not run
SELECT compress_chunk(i) from show_chunks('t') i;
##compressed table returns 0 rows, which is wrong
select factorid, end_dt, logret
from t
where fmid = 56
and end_dt between '2012-12-10'::date and '2012-12-11'::date
order by factorid, end_dt;
factorid | end_dt | logret
----------+--------+--------
(0 rows)
##decompressed table correctly return 1 row.
SELECT decompress_chunk(i) from show_chunks('t') i;
select factorid, end_dt, logret
from t
where fmid = 56
and end_dt between '2012-12-10'::date and '2012-12-11'::date
order by factorid, end_dt;
factorid | end_dt | logret
----------+---------------------+--------
0 | 2012-12-10 09:50:00 | 0.1
`
Expected behavior
compressed table should return same result as decompressed table Actual behavior
compressed table return no rows given the same query criteria.
Screenshots
If applicable, add screenshots to help explain your problem.
Additional context
Add any other context about the problem here.
The text was updated successfully, but these errors were encountered:
gayyappan
added a commit
to gayyappan/timescaledb
that referenced
this issue
Apr 28, 2020
Relevant system information:
CentOS 7.7.1908
postgres --version
): [e.g. 10.5, 9.6.10]12.2
\dx
inpsql
): [e.g. 1.0.0]1.7.0
yum install
Describe the bug
compressed table return wrong result when using explicit type conversion from timestamp to date. Result is correct when using decompressed table.
To Reproduce
Steps to reproduce the behavior:
`CREATE TABLE t (
fmid smallint,
factorid smallint,
start_dt timestamp without time zone,
end_dt timestamp without time zone,
interval_number smallint,
logret double precision,
knowledge_date date NOT NULL
);
SELECT create_hypertable('t', 'end_dt', chunk_time_interval=>interval '1 month', create_default_indexes=>false);
SELECT add_dimension('t', 'fmid', chunk_time_interval => 1);
Create unique index idx_t on t (end_dt ,fmid, factorid, knowledge_date);
ALTER TABLE t SET (timescaledb.compress, timescaledb.compress_orderby= 'end_dt');
Insert into t values (56,0,'2012-12-10 09:45:00','2012-12-10 09:50:00',1,0.1,'2012-12-10');
SELECT add_compress_chunks_policy('t', INTERVAL '1 month');
##run compression if auto compression is not run
SELECT compress_chunk(i) from show_chunks('t') i;
##compressed table returns 0 rows, which is wrong
select factorid, end_dt, logret
from t
where fmid = 56
and end_dt between '2012-12-10'::date and '2012-12-11'::date
order by factorid, end_dt;
factorid | end_dt | logret
----------+--------+--------
(0 rows)
##decompressed table correctly return 1 row.
SELECT decompress_chunk(i) from show_chunks('t') i;
select factorid, end_dt, logret
from t
where fmid = 56
and end_dt between '2012-12-10'::date and '2012-12-11'::date
order by factorid, end_dt;
factorid | end_dt | logret
----------+---------------------+--------
0 | 2012-12-10 09:50:00 | 0.1
`
Expected behavior
compressed table should return same result as decompressed table
Actual behavior
compressed table return no rows given the same query criteria.
Screenshots
If applicable, add screenshots to help explain your problem.
Additional context
Add any other context about the problem here.
The text was updated successfully, but these errors were encountered: