-- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION ts_compress_table(in_table REGCLASS, out_table REGCLASS, compression_info _timescaledb_catalog.hypertable_compression[])
    RETURNS VOID
    AS :TSL_MODULE_PATHNAME LANGUAGE C STRICT VOLATILE;

CREATE OR REPLACE FUNCTION ts_decompress_table(in_table REGCLASS, out_table REGCLASS)

            

Reported by SQLint.

              CREATE OR REPLACE FUNCTION ts_decompress_table(in_table REGCLASS, out_table REGCLASS)
    RETURNS VOID
    AS :TSL_MODULE_PATHNAME LANGUAGE C STRICT VOLATILE;
\ir include/compression_utils.sql
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-- column name, algorithm, idx, asc, nulls_first
--no sgement_byindex (use 0 to indicate that)
CREATE FUNCTION ord(TEXT, INT, INT, BOOL = true, BOOL = false)

            

Reported by SQLint.

                  nulls  _timescaledb_internal.compressed_data,
    texts  _timescaledb_internal.compressed_data);

\set DATA_IN uncompressed
\set DATA_OUT uncompressed

-- compression algorithms
\set array      1
\set dictionary 2

            

Reported by SQLint.

                  SELECT generate_series(16,20), d, d % 3, d / 3.0, NULL, d
    FROM generate_series(1, 5) d;

\ir include/compress_table_test.sql
TRUNCATE compressed;

-- test gorilla on ints
SELECT ARRAY[ord('time', :deltadelta, 0), seg('device', 0), com('data', :gorilla), com('floats', :gorilla), com('nulls', :array), com('texts', :dictionary)]::_timescaledb_catalog.hypertable_compression[] AS "COMPRESSION_INFO" \gset


            

Reported by SQLint.

              ALTER TABLE compressed DROP COLUMN data;
ALTER TABLE compressed DROP COLUMN nulls;

\set DECOMPRESS_FORWARD_CMD _timescaledb_internal.decompress_forward(time::_timescaledb_internal.compressed_data, NULL::INT) t, device, _timescaledb_internal.decompress_forward(floats::_timescaledb_internal.compressed_data, NULL::FLOAT(26)) f, _timescaledb_internal.decompress_forward(texts::_timescaledb_internal.compressed_data, NULL::TEXT) e

SELECT ARRAY[ord('time', :deltadelta, 0), seg('device', 0), com('floats', :gorilla), com('texts', :dictionary)]::_timescaledb_catalog.hypertable_compression[] AS "COMPRESSION_INFO" \gset

\ir include/compress_table_test.sql
TRUNCATE compressed;

            

Reported by SQLint.

              ALTER TABLE compressed ADD COLUMN dat2 _timescaledb_internal.compressed_data;
ALTER TABLE compressed ADD COLUMN ord _timescaledb_internal.compressed_data;

\set DECOMPRESS_FORWARD_CMD _timescaledb_internal.decompress_forward(time::_timescaledb_internal.compressed_data, NULL::INT) t, device, _timescaledb_internal.decompress_forward(floats::_timescaledb_internal.compressed_data, NULL::FLOAT(26)) f, _timescaledb_internal.decompress_forward(texts::_timescaledb_internal.compressed_data, NULL::TEXT) e, _timescaledb_internal.decompress_forward(dat2::_timescaledb_internal.compressed_data, NULL::INT) d2, _timescaledb_internal.decompress_forward(ord::_timescaledb_internal.compressed_data, NULL::INT) o

SELECT ARRAY[ord('time', :deltadelta, 0), seg('device', 0), com('floats', :gorilla), com('texts', :dictionary), ord('ord', :deltadelta, 1), com('dat2', :deltadelta)]::_timescaledb_catalog.hypertable_compression[] AS "COMPRESSION_INFO" \gset

\ir include/compress_table_test.sql
TRUNCATE compressed;

            

Reported by SQLint.

              
-- test skipping columns
CREATE TABLE missing_columns AS SELECT time, device, dat2 FROM uncompressed;
\set DATA_OUT missing_columns

\set DECOMPRESS_FORWARD_CMD _timescaledb_internal.decompress_forward(time::_timescaledb_internal.compressed_data, NULL::INT) t, device, _timescaledb_internal.decompress_forward(dat2::_timescaledb_internal.compressed_data, NULL::INT) d2

SELECT ARRAY[ord('time', :deltadelta, 0), seg('device', 0), com('dat2', :deltadelta)]::_timescaledb_catalog.hypertable_compression[] AS "COMPRESSION_INFO" \gset


            

Reported by SQLint.

              TRUNCATE compressed;


\set ON_ERROR_STOP 0

-- test compressing a non-existent column
SELECT ARRAY[ord('time', :deltadelta, 0), seg('device', 0), com('floats', :gorilla), com('texts', :dictionary), ord('ord', :deltadelta, 1), com('dat2', :deltadelta), com('fictional', :deltadelta)]::_timescaledb_catalog.hypertable_compression[] AS "COMPRESSION_INFO" \gset

SELECT ts_compress_table(:'DATA_IN'::REGCLASS, 'compressed'::REGCLASS,:'COMPRESSION_INFO'::_timescaledb_catalog.hypertable_compression[]);

            

Reported by SQLint.

              SELECT ts_compress_table(:'DATA_IN'::REGCLASS, 'compressed'::REGCLASS,:'COMPRESSION_INFO'::_timescaledb_catalog.hypertable_compression[]);
TRUNCATE compressed;

\set ON_ERROR_STOP 1
TRUNCATE uncompressed;


DROP TABLE compressed;
DROP TABLE uncompressed;

            

Reported by SQLint.

                  device _timescaledb_internal.compressed_data,
    time _timescaledb_internal.compressed_data);

\set DATA_IN uncompressed
\set DATA_OUT uncompressed

INSERT INTO uncompressed SELECT (i % 3)::BOOL, i, i / 3 FROM generate_series(1, 20) i;

SELECT ARRAY[ord('device', :deltadelta, 0), com('b', :deltadelta), com('time', :gorilla)]::_timescaledb_catalog.hypertable_compression[] AS "COMPRESSION_INFO" \gset

            

Reported by SQLint.

              -- LICENSE-TIMESCALE for a copy of the license.

-- Disable background workers since we are testing manual refresh
\c :TEST_DBNAME :ROLE_SUPERUSER
SELECT _timescaledb_internal.stop_background_workers();
SET ROLE :ROLE_DEFAULT_PERM_USER;
SET datestyle TO 'ISO, YMD';
SET timezone TO 'UTC';


            

Reported by SQLint.

              
-- Truncate one of the aggregates, but first test that we block
-- TRUNCATE ONLY
\set ON_ERROR_STOP 0
TRUNCATE ONLY cond_20;
\set ON_ERROR_STOP 1
TRUNCATE cond_20;

-- Should now be empty

            

Reported by SQLint.

              -- TRUNCATE ONLY
\set ON_ERROR_STOP 0
TRUNCATE ONLY cond_20;
\set ON_ERROR_STOP 1
TRUNCATE cond_20;

-- Should now be empty
SELECT * FROM cond_20
ORDER BY 1,2;

            

Reported by SQLint.

              
SELECT mat_hypertable_id AS cond_1_id
FROM _timescaledb_catalog.continuous_agg
WHERE user_view_name = 'cond_1' \gset

-- Test invalidations with bucket size 1
INSERT INTO conditions VALUES (0, 1, 1.0);

SELECT * FROM hyper_invals;

            

Reported by SQLint.

              
SELECT raw_hypertable_id AS thresh_hyper_id, mat_hypertable_id AS thresh_cagg_id
FROM _timescaledb_catalog.continuous_agg
WHERE user_view_name = 'thresh_2' \gset

-- There's no invalidation threshold initially
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold
WHERE hypertable_id = :thresh_hyper_id
ORDER BY 1,2;

            

Reported by SQLint.

              SELECT show_chunks AS chunk_to_drop
FROM show_chunks('threshold_test')
ORDER BY 1 DESC
LIMIT 1 \gset

DELETE FROM threshold_test
WHERE time > 6;

-- The last data in the hypertable is gone

            

Reported by SQLint.

              SELECT show_chunks AS chunk_to_drop
FROM show_chunks('conditions')
ORDER BY 1
LIMIT 1 \gset

-- Show the data before dropping one of the chunks
SELECT * FROM conditions
ORDER BY 1,2;


            

Reported by SQLint.

              
SELECT mat_hypertable_id AS cond_10_id
FROM _timescaledb_catalog.continuous_agg
WHERE user_view_name = 'cond_10' \gset

SELECT * FROM cagg_invals
WHERE cagg_id = :cond_10_id;

-- should trigger two individual refreshes

            

Reported by SQLint.

              CALL refresh_continuous_aggregate('cond_10', 0, 200);
RESET client_min_messages;

\set VERBOSITY default
-- Test acceptable values for materializations per refresh
SET timescaledb.materializations_per_refresh_window=' 5 ';
INSERT INTO conditions VALUES (140, 1, 1.0);
CALL refresh_continuous_aggregate('cond_10', 0, 200);
-- Large value will be treated as LONG_MAX

            

Reported by SQLint.

              SET timescaledb.materializations_per_refresh_window='-';
INSERT INTO conditions VALUES (140, 1, 1.0);
CALL refresh_continuous_aggregate('cond_10', 0, 200);
\set VERBOSITY terse

            

Reported by SQLint.

              -- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION test.condition() RETURNS VOID
    AS :MODULE_PATHNAME, 'ts_test_utils_condition' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION test.int64_eq() RETURNS VOID
    AS :MODULE_PATHNAME, 'ts_test_utils_int64_eq' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION test.ptr_eq() RETURNS VOID

            

Reported by SQLint.

              
-- We're testing that the test utils work and generate errors on
-- failing conditions
\set ON_ERROR_STOP 0
SELECT test.condition();
SELECT test.int64_eq();
SELECT test.ptr_eq();
SELECT test.double_eq();
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              SELECT test.int64_eq();
SELECT test.ptr_eq();
SELECT test.double_eq();
\set ON_ERROR_STOP 1

-- Test debug points
--
\set ECHO all


            

Reported by SQLint.

              
-- debug point already enabled
SELECT debug_point_enable('test_debug_point');
\set ON_ERROR_STOP 0
SELECT debug_point_enable('test_debug_point');
\set ON_ERROR_STOP 1
SELECT debug_point_release('test_debug_point');

-- debug point not enabled

            

Reported by SQLint.

              SELECT debug_point_enable('test_debug_point');
\set ON_ERROR_STOP 0
SELECT debug_point_enable('test_debug_point');
\set ON_ERROR_STOP 1
SELECT debug_point_release('test_debug_point');

-- debug point not enabled
\set ON_ERROR_STOP 0
SELECT debug_point_release('test_debug_point');

            

Reported by SQLint.

              SELECT debug_point_release('test_debug_point');

-- debug point not enabled
\set ON_ERROR_STOP 0
SELECT debug_point_release('test_debug_point');
\set ON_ERROR_STOP 1

-- error injections
--

            

Reported by SQLint.

              -- debug point not enabled
\set ON_ERROR_STOP 0
SELECT debug_point_release('test_debug_point');
\set ON_ERROR_STOP 1

-- error injections
--
CREATE OR REPLACE FUNCTION test_error_injection(TEXT)
RETURNS VOID

            

Reported by SQLint.

              SELECT test_error_injection('test_error');

SELECT debug_point_enable('test_error');
\set ON_ERROR_STOP 0
SELECT test_error_injection('test_error');
\set ON_ERROR_STOP 1

SELECT debug_point_release('test_error');
SELECT test_error_injection('test_error');

            

Reported by SQLint.

              SELECT debug_point_enable('test_error');
\set ON_ERROR_STOP 0
SELECT test_error_injection('test_error');
\set ON_ERROR_STOP 1

SELECT debug_point_release('test_error');
SELECT test_error_injection('test_error');

            

Reported by SQLint.

              	int num_iterations = PG_GETARG_INT32(0);
	int bytes, i, j;

	srand(time(0));

	for (j = 0; j < num_iterations; j++)
	{
		for (i = 0; i < num_test_strings(); i++)
		{

            

Reported by FlawFinder.

              {
	int bytes, i;

	srand(time(0));

	for (i = 0; i < num_test_strings(); i++)
	{
		HttpResponseState *state = ts_http_response_state_create();
		ssize_t bufsize = 0;

            

Reported by FlawFinder.

              			TestAssertTrue(bufsize >= bytes);

			/* Copy part of the message into the parsing state */
			memcpy(buf, TEST_RESPONSES[i], bytes);

			/* Now do the parse */
			success = ts_http_response_state_parse(state, bytes);

			TestAssertTrue(success);

            

Reported by FlawFinder.

              		TestAssertTrue(bufsize >= bytes);

		/* Copy all of the message into the parsing state */
		memcpy(buf, TEST_RESPONSES[i], bytes);

		/* Now do the parse */
		TestAssertTrue(ts_http_response_state_parse(state, bytes));

		TestAssertTrue(ts_http_response_state_is_done(state));

            

Reported by FlawFinder.

              
		TestAssertTrue(bufsize >= bytes);

		memcpy(buf, BAD_RESPONSES[i], bytes);

		TestAssertTrue(!ts_http_response_state_parse(state, bytes) ||
					   !ts_http_response_state_valid_status(state));

		ts_http_response_state_destroy(state);

            

Reported by FlawFinder.

              			ssize_t bufsize = 0;
			char *buf;

			bytes = rand() % (strlen(TEST_RESPONSES[i]) + 1);

			buf = ts_http_response_state_next_buffer(state, &bufsize);

			TestAssertTrue(bufsize >= bytes);


            

Reported by FlawFinder.

              
			success = ts_http_response_state_is_done(state);

			TestAssertTrue(bytes < strlen(TEST_RESPONSES[i]) ? !success : success);

			ts_http_response_state_destroy(state);
		}
	}
	PG_RETURN_NULL();

            

Reported by FlawFinder.

              
		buf = ts_http_response_state_next_buffer(state, &bufsize);

		bytes = strlen(TEST_RESPONSES[i]);

		TestAssertTrue(bufsize >= bytes);

		/* Copy all of the message into the parsing state */
		memcpy(buf, TEST_RESPONSES[i], bytes);

            

Reported by FlawFinder.

              
		buf = ts_http_response_state_next_buffer(state, &bufsize);

		bytes = strlen(BAD_RESPONSES[i]);

		TestAssertTrue(bufsize >= bytes);

		memcpy(buf, BAD_RESPONSES[i], bytes);


            

Reported by FlawFinder.

              -- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.

\ir include/insert_single.sql

SELECT * FROM test.show_columnsp('"one_Partition".%');
SELECT * FROM "one_Partition" ORDER BY "timeCustom", device_id, series_0, series_1, series_2;

--test that we can insert data into a 1-dimensional table (only time partitioning)

            

Reported by SQLint.

              SELECT * FROM "3dim" ORDER BY (time, device);

-- Test that large intervals and no interval fail for INTEGER
\set ON_ERROR_STOP 0
CREATE TABLE "inttime_err"(time INTEGER PRIMARY KEY, temp float);
SELECT create_hypertable('"inttime_err"', 'time', chunk_time_interval=>2147483648);
SELECT create_hypertable('"inttime_err"', 'time');
\set ON_ERROR_STOP 1
SELECT create_hypertable('"inttime_err"', 'time', chunk_time_interval=>2147483647);

            

Reported by SQLint.

              CREATE TABLE "inttime_err"(time INTEGER PRIMARY KEY, temp float);
SELECT create_hypertable('"inttime_err"', 'time', chunk_time_interval=>2147483648);
SELECT create_hypertable('"inttime_err"', 'time');
\set ON_ERROR_STOP 1
SELECT create_hypertable('"inttime_err"', 'time', chunk_time_interval=>2147483647);

-- Test that large intervals and no interval fail for SMALLINT
\set ON_ERROR_STOP 0
CREATE TABLE "smallinttime_err"(time SMALLINT PRIMARY KEY, temp float);

            

Reported by SQLint.

              SELECT create_hypertable('"inttime_err"', 'time', chunk_time_interval=>2147483647);

-- Test that large intervals and no interval fail for SMALLINT
\set ON_ERROR_STOP 0
CREATE TABLE "smallinttime_err"(time SMALLINT PRIMARY KEY, temp float);
SELECT create_hypertable('"smallinttime_err"', 'time', chunk_time_interval=>32768);
SELECT create_hypertable('"smallinttime_err"', 'time');
\set ON_ERROR_STOP 1
SELECT create_hypertable('"smallinttime_err"', 'time', chunk_time_interval=>32767);

            

Reported by SQLint.

              CREATE TABLE "smallinttime_err"(time SMALLINT PRIMARY KEY, temp float);
SELECT create_hypertable('"smallinttime_err"', 'time', chunk_time_interval=>32768);
SELECT create_hypertable('"smallinttime_err"', 'time');
\set ON_ERROR_STOP 1
SELECT create_hypertable('"smallinttime_err"', 'time', chunk_time_interval=>32767);

--make sure date inserts work even when the timezone changes the
CREATE TABLE hyper_date(time date, temp float);
SELECT create_hypertable('"hyper_date"', 'time');

            

Reported by SQLint.

              
--test rollback
BEGIN;
\set QUIET off
CREATE TABLE "data_records" ("time" bigint NOT NULL, "value" integer CHECK (VALUE >= 0));
SELECT create_hypertable('data_records', 'time', chunk_time_interval => 2592000000);

INSERT INTO "data_records" ("time", "value") VALUES (0, 1);
SAVEPOINT savepoint_1;

            

Reported by SQLint.

              INSERT INTO "data_records" ("time", "value") VALUES (2, 1);

SAVEPOINT savepoint_2;
\set ON_ERROR_STOP 0
INSERT INTO "data_records" ("time", "value") VALUES (3, -1);
\set ON_ERROR_STOP 1
ROLLBACK TO SAVEPOINT savepoint_2;
INSERT INTO "data_records" ("time", "value") VALUES (4, 1);


            

Reported by SQLint.

              SAVEPOINT savepoint_2;
\set ON_ERROR_STOP 0
INSERT INTO "data_records" ("time", "value") VALUES (3, -1);
\set ON_ERROR_STOP 1
ROLLBACK TO SAVEPOINT savepoint_2;
INSERT INTO "data_records" ("time", "value") VALUES (4, 1);

SAVEPOINT savepoint_3;
INSERT INTO "data_records" ("time", "value") VALUES (5, 0);

            

Reported by SQLint.

              
SELECT * FROM data_records;

\set QUIET on
ROLLBACK;

            

Reported by SQLint.

              CREATE INDEX ON :TABLE(time,dev);
CREATE INDEX ON :TABLE(time,dev,val);

\qecho basic DISTINCT queries on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_1' FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;

\qecho stable expression in targetlist on :TABLE

            

Reported by SQLint.

              :PREFIX SELECT DISTINCT dev_name, 'q1_2' FROM :TABLE ORDER BY dev_name;
:PREFIX SELECT DISTINCT dev, 'q1_3', NULL FROM :TABLE ORDER BY dev;

\qecho stable expression in targetlist on :TABLE
:PREFIX SELECT DISTINCT dev, 'q1_4', length(md5(now()::text)) FROM :TABLE ORDER BY dev;
:PREFIX SELECT DISTINCT dev_name, 'q1_5', length(md5(now()::text)) FROM :TABLE ORDER BY dev_name;

-- volatile expression in targetlist
:PREFIX SELECT DISTINCT dev, 'q1_6', length(md5(random()::text)) FROM :TABLE ORDER BY dev;

            

Reported by SQLint.

              :PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_stable(), 'q3_14' FROM :TABLE;
:PREFIX SELECT DISTINCT ON (dev_name) dev, int_func_volatile(), 'q3_15' FROM :TABLE;

\qecho DISTINCT with wholerow var
:PREFIX SELECT DISTINCT ON (dev) :TABLE FROM :TABLE;
-- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;

\qecho LIMIT queries on :TABLE

            

Reported by SQLint.

              -- should not use SkipScan since we only support SkipScan on single-column distinct
:PREFIX SELECT DISTINCT :TABLE FROM :TABLE;

\qecho LIMIT queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3;

\qecho range queries on :TABLE

            

Reported by SQLint.

              :PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev DESC, time DESC LIMIT 3;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE ORDER BY dev, time LIMIT 3;

\qecho range queries on :TABLE
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time BETWEEN 100 AND 300;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;

\qecho ordered append on :TABLE

            

Reported by SQLint.

              :PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time < 200;
:PREFIX SELECT DISTINCT ON (dev) dev FROM :TABLE WHERE time > 800;

\qecho ordered append on :TABLE
:PREFIX SELECT * FROM :TABLE ORDER BY time;
:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000;

\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;

            

Reported by SQLint.

              :PREFIX SELECT * FROM :TABLE ORDER BY time;
:PREFIX SELECT DISTINCT ON (time) time FROM :TABLE WHERE time BETWEEN 0 AND 5000;

\qecho SUBSELECTS on :TABLE
:PREFIX SELECT time, dev, val, 'q4_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a;
:PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;

\qecho ORDER BY

            

Reported by SQLint.

              :PREFIX SELECT NULL, dev, NULL, 'q4_3' FROM (SELECT DISTINCT ON (dev) dev FROM :TABLE) a;
:PREFIX SELECT time, dev, NULL, 'q4_4' FROM (SELECT DISTINCT ON (dev) dev, time FROM :TABLE) a;

\qecho ORDER BY
:PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;

\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;

            

Reported by SQLint.

              :PREFIX SELECT time, dev, val, 'q5_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev, time) a;
:PREFIX SELECT time, dev, val, 'q5_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE ORDER BY dev DESC, time DESC) a;

\qecho WHERE CLAUSES
:PREFIX SELECT time, dev, val, 'q6_1' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE dev > 5) a;
:PREFIX SELECT time, dev, val, 'q6_2' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE WHERE time > 5) a;
:PREFIX SELECT time, dev, val, 'q6_3' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE dev > 5;
:PREFIX SELECT time, dev, val, 'q6_4' FROM (SELECT DISTINCT ON (dev) * FROM :TABLE) a WHERE time > 5;
--\qecho immutable func in WHERE clause on :TABLE

            

Reported by SQLint.

              #define PRINT_MATINTERNAL_NAME(buf, prefix, hypertable_id)                                         \
	do                                                                                             \
	{                                                                                              \
		int ret = snprintf(buf, NAMEDATALEN, prefix, hypertable_id);                               \
		if (ret < 0 || ret > NAMEDATALEN)                                                          \
		{                                                                                          \
			ereport(ERROR,                                                                         \
					(errcode(ERRCODE_INTERNAL_ERROR),                                              \
					 errmsg(" bad materialization internal name")));                               \

            

Reported by FlawFinder.

              mattablecolumninfo_addentry(MatTableColumnInfo *out, Node *input, int original_query_resno)
{
	int matcolno = list_length(out->matcollist) + 1;
	char colbuf[NAMEDATALEN];
	char *colname;
	TargetEntry *part_te = NULL;
	ColumnDef *col;
	Var *var;
	Oid coltype, colcollation;

            

Reported by FlawFinder.

              			CAggTimebucketInfo *origquery_ht, WithClauseResult *with_clause_options)
{
	ObjectAddress mataddress;
	char relnamebuf[NAMEDATALEN];
	MatTableColumnInfo mattblinfo;
	FinalizeQueryInfo finalqinfo;
	CatalogSecurityContext sec_ctx;
	bool is_create_mattbl_index;


            

Reported by FlawFinder.

              	Oid nspid;
	RangeVar *part_rel = NULL, *mat_rel = NULL, *dum_rel = NULL;
	int32 materialize_hypertable_id;
	char trigarg[NAMEDATALEN];
	int ret;
	bool materialized_only =
		DatumGetBool(with_clause_options[ContinuousViewOptionMaterializedOnly].parsed);

	/* assign the column_name aliases in CREATE VIEW to the query. No other modifications to

            

Reported by FlawFinder.

              	do                                                                                             \
	{                                                                                              \
		if (schemaname &&                                                                          \
			strncmp(schemaname, INTERNAL_SCHEMA_NAME, strlen(INTERNAL_SCHEMA_NAME)) == 0)          \
			newuid = ts_catalog_database_info_get()->owner_uid;                                    \
		else                                                                                       \
			newuid = InvalidOid;                                                                   \
		if (newuid != InvalidOid)                                                                  \
		{                                                                                          \

            

Reported by FlawFinder.

              			const char *funcschema = NameStr(part_dimension->fd.integer_now_func_schema);
			const char *funcname = NameStr(part_dimension->fd.integer_now_func);

			if (strlen(funcschema) == 0 || strlen(funcname) == 0)
				ereport(ERROR,
						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
						 errmsg("custom time function required on hypertable \"%s\"",
								get_rel_name(ht->main_table_relid)),
						 errdetail("An integer-based hypertable requires a custom time"

            

Reported by FlawFinder.

              			const char *funcschema = NameStr(part_dimension->fd.integer_now_func_schema);
			const char *funcname = NameStr(part_dimension->fd.integer_now_func);

			if (strlen(funcschema) == 0 || strlen(funcname) == 0)
				ereport(ERROR,
						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
						 errmsg("custom time function required on hypertable \"%s\"",
								get_rel_name(ht->main_table_relid)),
						 errdetail("An integer-based hypertable requires a custom time"

            

Reported by FlawFinder.

              	{
		TargetEntry *te = (TargetEntry *) lfirst(lc);
		TargetEntry *modte = (TargetEntry *) lfirst(lc2);
		if (equal(node, te->expr))
		{
			return (Node *) modte->expr;
		}
	}
	/* didn't find a match in targetlist. If it is an aggregate, create a partialize column for

            

Reported by FlawFinder.

              --
-- Setup
--
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(timeout INT = -1) RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;

CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_run(timeout INT = -1, mock_start_time INT = 0) RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;

            

Reported by SQLint.

              CREATE OR REPLACE FUNCTION ts_bgw_params_reset_time(set_time BIGINT = 0, wait BOOLEAN = false) RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;

\set WAIT_ON_JOB 0
\set IMMEDIATELY_SET_UNTIL 1
\set WAIT_FOR_OTHER_TO_ADVANCE 2

-- Remove any default jobs, e.g., telemetry
DELETE FROM _timescaledb_config.bgw_job;

            

Reported by SQLint.

              DELETE FROM _timescaledb_config.bgw_job;
TRUNCATE _timescaledb_internal.bgw_job_stat;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

CREATE TABLE public.bgw_log(
    msg_no INT,
    mock_time BIGINT,
    application_name TEXT,

            

Reported by SQLint.

              SELECT * FROM _timescaledb_config.bgw_job;
SELECT * FROM timescaledb_information.job_stats;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

------------------------------
-- test reorder policy runs --
------------------------------


            

Reported by SQLint.

              SELECT COUNT(*) FROM _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as ht where c.hypertable_id = ht.id and ht.table_name='test_reorder_table';

SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_reorder_policies');
select add_reorder_policy('test_reorder_table', 'test_reorder_table_time_idx') as reorder_job_id \gset
SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_reorder_policies');

-- job was created
SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id;


            

Reported by SQLint.

              


\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE bgw_log;
TRUNCATE _timescaledb_internal.bgw_job_stat;
DELETE FROM _timescaledb_config.bgw_job;
SELECT ts_bgw_params_reset_time();
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

            

Reported by SQLint.

              TRUNCATE _timescaledb_internal.bgw_job_stat;
DELETE FROM _timescaledb_config.bgw_job;
SELECT ts_bgw_params_reset_time();
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-----------------------------------
-- test drop chunnks policy runs --
-----------------------------------


            

Reported by SQLint.

              SELECT COUNT(*) FROM _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as ht where c.hypertable_id = ht.id and ht.table_name='test_drop_chunks_table';

SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_retention_policies');
SELECT add_retention_policy('test_drop_chunks_table', INTERVAL '4 months') as drop_chunks_job_id \gset
SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_retention_policies');

SELECT alter_job(:drop_chunks_job_id, schedule_interval => INTERVAL '1 second');

SELECT * FROM timescaledb_information.jobs WHERE job_id=:drop_chunks_job_id;

            

Reported by SQLint.

              SELECT * FROM upsert_test;

-- Referencing constraints by name does not yet work on Hypertables. Check for proper error message.
\set ON_ERROR_STOP 0
INSERT INTO upsert_test VALUES ('2017-01-20T09:00:01', 12.3, 'yellow') ON CONFLICT ON CONSTRAINT upsert_test_pkey
DO UPDATE SET temp = 12.3 RETURNING time, temp, color;

-- Test that update generates error on conflicts
INSERT INTO upsert_test VALUES ('2017-01-21T09:00:01', 22.5, 'yellow') RETURNING *;

            

Reported by SQLint.

              -- Test that update generates error on conflicts
INSERT INTO upsert_test VALUES ('2017-01-21T09:00:01', 22.5, 'yellow') RETURNING *;
UPDATE upsert_test SET time = '2017-01-20T09:00:01';
\set ON_ERROR_STOP 1

-- Test with UNIQUE index on multiple columns instead of PRIMARY KEY constraint
CREATE TABLE upsert_test_unique(time timestamp, temp float, color text);
SELECT create_hypertable('upsert_test_unique', 'time');
CREATE UNIQUE INDEX time_color_idx ON upsert_test_unique (time, color);

            

Reported by SQLint.

              INSERT INTO upsert_test_multi_unique VALUES ('2017-01-21T09:00:01', 45.7, 'yellow') ON CONFLICT (time, color)
DO UPDATE SET temp = 45.7;
SELECT * FROM upsert_test_multi_unique ORDER BY time, color DESC;
\set ON_ERROR_STOP 0
INSERT INTO upsert_test_multi_unique VALUES ('2017-01-20T09:00:01', 23.5, 'purple') ON CONFLICT (time, color)
DO UPDATE set temp = 23.5;
\set ON_ERROR_STOP 1

CREATE TABLE upsert_test_space(time timestamp, device_id_1 char(20), to_drop int, temp float, color text);

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
INSERT INTO upsert_test_multi_unique VALUES ('2017-01-20T09:00:01', 23.5, 'purple') ON CONFLICT (time, color)
DO UPDATE set temp = 23.5;
\set ON_ERROR_STOP 1

CREATE TABLE upsert_test_space(time timestamp, device_id_1 char(20), to_drop int, temp float, color text);
--drop two columns; create one.
ALTER TABLE upsert_test_space DROP to_drop;
ALTER TABLE upsert_test_space DROP device_id_1, ADD device_id char(20);

            

Reported by SQLint.

              RETURNING *;

--errors out if used as an arbiter index
\set ON_ERROR_STOP 0
INSERT INTO upsert_test_multi_unique
VALUES ('2017-01-20T09:00:01', 25.9, 'purple')
ON CONFLICT (time, color) DO NOTHING
RETURNING *;
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              VALUES ('2017-01-20T09:00:01', 25.9, 'purple')
ON CONFLICT (time, color) DO NOTHING
RETURNING *;
\set ON_ERROR_STOP 1

--create table with one chunk that has a tup_conv_map and one that does not
--to ensure this, create a chunk before altering the table this chunk will not have a tup_conv_map
CREATE TABLE upsert_test_diffchunk(time timestamp, device_id char(20), to_drop int, temp float, color text);
SELECT create_hypertable('upsert_test_diffchunk', 'time', chunk_time_interval=> interval '1 month');

            

Reported by SQLint.

              --this insert will create an invalid tuple in source_data
--so that future calls to prep_insert_select will fail
INSERT INTO source_data VALUES('2000-01-02',-0.5);
\set ON_ERROR_STOP 0
EXECUTE prep_insert_select;
EXECUTE prep_insert_select;
\set ON_ERROR_STOP 1
DELETE FROM source_data WHERE value <= 0;
EXECUTE prep_insert_select;

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
EXECUTE prep_insert_select;
EXECUTE prep_insert_select;
\set ON_ERROR_STOP 1
DELETE FROM source_data WHERE value <= 0;
EXECUTE prep_insert_select;

PREPARE prep_insert AS INSERT INTO prepared_test VALUES('2000-01-01',0.5) ON CONFLICT (time) DO UPDATE SET value = EXCLUDED.value;


            

Reported by SQLint.

              -- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.

\ir include/rand_generator.sql
\c :TEST_DBNAME :ROLE_SUPERUSER
\ir include/compression_utils.sql
CREATE TYPE customtype;

CREATE OR REPLACE FUNCTION customtype_in(cstring) RETURNS customtype

            

Reported by SQLint.

                FOR TYPE customtype
  USING hash AS OPERATOR 1 =;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

CREATE TABLE test1 ("Time" timestamptz, i integer, b bigint, t text);
SELECT table_name from create_hypertable('test1', 'Time', chunk_time_interval=> INTERVAL '1 day');

INSERT INTO test1 SELECT t,  gen_rand_minstd(), gen_rand_minstd(), gen_rand_minstd()::text FROM generate_series('2018-03-02 1:00'::TIMESTAMPTZ, '2018-03-28 1:00', '1 hour') t;

            

Reported by SQLint.

              SELECT
  $$
  SELECT * FROM test1 ORDER BY "Time"
  $$ AS "QUERY" \gset

SELECT 'test1' AS "HYPERTABLE_NAME" \gset

\ir include/compression_test_hypertable.sql
\set TYPE timestamptz

            

Reported by SQLint.

              SELECT
  $$
  SELECT * FROM test2 ORDER BY c, "Time"
  $$ AS "QUERY" \gset

SELECT 'test2' AS "HYPERTABLE_NAME" \gset
\ir include/compression_test_hypertable.sql

\set TYPE int

            

Reported by SQLint.

              from test4
group by location ORDER BY location;

SELECT $$ SELECT * FROM test4 ORDER BY timec $$ AS "QUERY" \gset

SELECT 'test4' AS "HYPERTABLE_NAME" \gset

\ir include/compression_test_hypertable.sql
\set TYPE TIMESTAMPTZ

            

Reported by SQLint.

              select generate_series('2018-01-01 00:00'::timestamp, '2018-01-10 00:00'::timestamp, '2 hour'), NULL, gen_rand_minstd();


SELECT $$ SELECT * FROM test5 ORDER BY device_id, time $$ AS "QUERY" \gset

SELECT 'test5' AS "HYPERTABLE_NAME" \gset

\ir include/compression_test_hypertable.sql
\set TYPE TEXT

            

Reported by SQLint.

              INSERT INTO test6 SELECT t, NULL, customtype_in(t::TEXT::cstring)
  FROM generate_series(1, 200) t;

\set QUERY 'SELECT * FROM test6 ORDER BY device_id, time'

\set HYPERTABLE_NAME 'test6'

\ir include/compression_test_hypertable.sql
\set TYPE INT

            

Reported by SQLint.

                FROM generate_series(10, 20) t,
       generate_series('2019/03/01'::DATE, '2019/03/10', '1d') d;

\set QUERY 'SELECT * FROM test7 ORDER BY time, c1'

\set HYPERTABLE_NAME 'test7'

\ir include/compression_test_hypertable.sql
\set TYPE INT

            

Reported by SQLint.