diff options
Diffstat (limited to 'src/test/regress/expected/tablesample.out')
| -rw-r--r-- | src/test/regress/expected/tablesample.out | 331 |
1 files changed, 331 insertions, 0 deletions
diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out new file mode 100644 index 0000000..9ff4611 --- /dev/null +++ b/src/test/regress/expected/tablesample.out @@ -0,0 +1,331 @@ +CREATE TABLE test_tablesample (id int, name text) WITH (fillfactor=10); +-- use fillfactor so we don't have to load too much data to get multiple pages +INSERT INTO test_tablesample + SELECT i, repeat(i::text, 200) FROM generate_series(0, 9) s(i); +SELECT t.id FROM test_tablesample AS t TABLESAMPLE SYSTEM (50) REPEATABLE (0); + id +---- + 3 + 4 + 5 + 6 + 7 + 8 +(6 rows) + +SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (100.0/11) REPEATABLE (0); + id +---- +(0 rows) + +SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); + id +---- + 3 + 4 + 5 + 6 + 7 + 8 +(6 rows) + +SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (50) REPEATABLE (0); + id +---- + 4 + 5 + 6 + 7 + 8 +(5 rows) + +SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (5.5) REPEATABLE (0); + id +---- + 7 +(1 row) + +-- 100% should give repeatable count results (ie, all rows) in any case +SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100); + count +------- + 10 +(1 row) + +SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100) REPEATABLE (1+2); + count +------- + 10 +(1 row) + +SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100) REPEATABLE (0.4); + count +------- + 10 +(1 row) + +CREATE VIEW test_tablesample_v1 AS + SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (10*2) REPEATABLE (2); +CREATE VIEW test_tablesample_v2 AS + SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (99); +\d+ test_tablesample_v1 + View "public.test_tablesample_v1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+---------+------------- + id | integer | | | | plain | +View definition: + SELECT id + FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2); + +\d+ test_tablesample_v2 + View "public.test_tablesample_v2" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+---------+------------- + id | integer | | | | plain | +View definition: + SELECT id + FROM test_tablesample TABLESAMPLE system (99); + +-- check a sampled query doesn't affect cursor in progress +BEGIN; +DECLARE tablesample_cur SCROLL CURSOR FOR + SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); +FETCH FIRST FROM tablesample_cur; + id +---- + 3 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 4 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 5 +(1 row) + +SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); + id +---- + 3 + 4 + 5 + 6 + 7 + 8 +(6 rows) + +FETCH NEXT FROM tablesample_cur; + id +---- + 6 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 7 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 8 +(1 row) + +FETCH FIRST FROM tablesample_cur; + id +---- + 3 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 4 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 5 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 6 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 7 +(1 row) + +FETCH NEXT FROM tablesample_cur; + id +---- + 8 +(1 row) + +CLOSE tablesample_cur; +END; +EXPLAIN (COSTS OFF) + SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (2); + QUERY PLAN +-------------------------------------------------------------------- + Sample Scan on test_tablesample + Sampling: system ('50'::real) REPEATABLE ('2'::double precision) +(2 rows) + +EXPLAIN (COSTS OFF) + SELECT * FROM test_tablesample_v1; + QUERY PLAN +-------------------------------------------------------------------- + Sample Scan on test_tablesample + Sampling: system ('20'::real) REPEATABLE ('2'::double precision) +(2 rows) + +-- check inheritance behavior +explain (costs off) + select count(*) from person tablesample bernoulli (100); + QUERY PLAN +------------------------------------------------- + Aggregate + -> Append + -> Sample Scan on person person_1 + Sampling: bernoulli ('100'::real) + -> Sample Scan on emp person_2 + Sampling: bernoulli ('100'::real) + -> Sample Scan on student person_3 + Sampling: bernoulli ('100'::real) + -> Sample Scan on stud_emp person_4 + Sampling: bernoulli ('100'::real) +(10 rows) + +select count(*) from person tablesample bernoulli (100); + count +------- + 58 +(1 row) + +select count(*) from person; + count +------- + 58 +(1 row) + +-- check that collations get assigned within the tablesample arguments +SELECT count(*) FROM test_tablesample TABLESAMPLE bernoulli (('1'::text < '0'::text)::int); + count +------- + 0 +(1 row) + +-- check behavior during rescans, as well as correct handling of min/max pct +select * from + (values (0),(100)) v(pct), + lateral (select count(*) from tenk1 tablesample bernoulli (pct)) ss; + pct | count +-----+------- + 0 | 0 + 100 | 10000 +(2 rows) + +select * from + (values (0),(100)) v(pct), + lateral (select count(*) from tenk1 tablesample system (pct)) ss; + pct | count +-----+------- + 0 | 0 + 100 | 10000 +(2 rows) + +explain (costs off) +select pct, count(unique1) from + (values (0),(100)) v(pct), + lateral (select * from tenk1 tablesample bernoulli (pct)) ss + group by pct; + QUERY PLAN +-------------------------------------------------------- + HashAggregate + Group Key: "*VALUES*".column1 + -> Nested Loop + -> Values Scan on "*VALUES*" + -> Sample Scan on tenk1 + Sampling: bernoulli ("*VALUES*".column1) +(6 rows) + +select pct, count(unique1) from + (values (0),(100)) v(pct), + lateral (select * from tenk1 tablesample bernoulli (pct)) ss + group by pct; + pct | count +-----+------- + 100 | 10000 +(1 row) + +select pct, count(unique1) from + (values (0),(100)) v(pct), + lateral (select * from tenk1 tablesample system (pct)) ss + group by pct; + pct | count +-----+------- + 100 | 10000 +(1 row) + +-- errors +SELECT id FROM test_tablesample TABLESAMPLE FOOBAR (1); +ERROR: tablesample method foobar does not exist +LINE 1: SELECT id FROM test_tablesample TABLESAMPLE FOOBAR (1); + ^ +SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (NULL); +ERROR: TABLESAMPLE parameter cannot be null +SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (NULL); +ERROR: TABLESAMPLE REPEATABLE parameter cannot be null +SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (-1); +ERROR: sample percentage must be between 0 and 100 +SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (200); +ERROR: sample percentage must be between 0 and 100 +SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (-1); +ERROR: sample percentage must be between 0 and 100 +SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (200); +ERROR: sample percentage must be between 0 and 100 +SELECT id FROM test_tablesample_v1 TABLESAMPLE BERNOULLI (1); +ERROR: TABLESAMPLE clause can only be applied to tables and materialized views +LINE 1: SELECT id FROM test_tablesample_v1 TABLESAMPLE BERNOULLI (1)... + ^ +INSERT INTO test_tablesample_v1 VALUES(1); +ERROR: cannot insert into view "test_tablesample_v1" +DETAIL: Views containing TABLESAMPLE are not automatically updatable. +HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. +WITH query_select AS (SELECT * FROM test_tablesample) +SELECT * FROM query_select TABLESAMPLE BERNOULLI (5.5) REPEATABLE (1); +ERROR: TABLESAMPLE clause can only be applied to tables and materialized views +LINE 2: SELECT * FROM query_select TABLESAMPLE BERNOULLI (5.5) REPEA... + ^ +SELECT q.* FROM (SELECT * FROM test_tablesample) as q TABLESAMPLE BERNOULLI (5); +ERROR: syntax error at or near "TABLESAMPLE" +LINE 1: ...CT q.* FROM (SELECT * FROM test_tablesample) as q TABLESAMPL... + ^ +-- check partitioned tables support tablesample +create table parted_sample (a int) partition by list (a); +create table parted_sample_1 partition of parted_sample for values in (1); +create table parted_sample_2 partition of parted_sample for values in (2); +explain (costs off) + select * from parted_sample tablesample bernoulli (100); + QUERY PLAN +------------------------------------------- + Append + -> Sample Scan on parted_sample_1 + Sampling: bernoulli ('100'::real) + -> Sample Scan on parted_sample_2 + Sampling: bernoulli ('100'::real) +(5 rows) + +drop table parted_sample, parted_sample_1, parted_sample_2; |