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;