inputs/postgreSQL/limit.sql

0001 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0002 --
0003 -- LIMIT
0004 -- https://github.com/postgres/postgres/blob/REL_12_STABLE/src/test/regress/sql/limit.sql
0005
0006 SELECT '' AS two, unique1, unique2, stringu1
0007                 FROM onek WHERE unique1 > 50
0008                 ORDER BY unique1 LIMIT 2;
0009 SELECT '' AS five, unique1, unique2, stringu1
0010                 FROM onek WHERE unique1 > 60
0011                 ORDER BY unique1 LIMIT 5;
0012 SELECT '' AS two, unique1, unique2, stringu1
0013                 FROM onek WHERE unique1 > 60 AND unique1 < 63
0014                 ORDER BY unique1 LIMIT 5;
0015 -- [SPARK-28330] ANSI SQL: Top-level <result offset clause> in <query expression>
0016 -- SELECT '' AS three, unique1, unique2, stringu1
0017 --              FROM onek WHERE unique1 > 100
0018 --              ORDER BY unique1 LIMIT 3 OFFSET 20;
0019 -- SELECT '' AS zero, unique1, unique2, stringu1
0020 --              FROM onek WHERE unique1 < 50
0021 --              ORDER BY unique1 DESC LIMIT 8 OFFSET 99;
0022 -- SELECT '' AS eleven, unique1, unique2, stringu1
0023 --              FROM onek WHERE unique1 < 50
0024 --              ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
0025 -- SELECT '' AS ten, unique1, unique2, stringu1
0026 --              FROM onek
0027 --              ORDER BY unique1 OFFSET 990;
0028 -- SELECT '' AS five, unique1, unique2, stringu1
0029 --              FROM onek
0030 --              ORDER BY unique1 OFFSET 990 LIMIT 5;
0031 -- SELECT '' AS five, unique1, unique2, stringu1
0032 --              FROM onek
0033 --              ORDER BY unique1 LIMIT 5 OFFSET 900;
0034
0035 CREATE OR REPLACE TEMPORARY VIEW INT8_TBL AS SELECT * FROM
0036   (VALUES
0037     (123, 456),
0038     (123, 4567890123456789),
0039     (4567890123456789, 123),
0040     (4567890123456789, 4567890123456789),
0041     (4567890123456789, -4567890123456789))
0042   AS v(q1, q2);
0043
0044 -- Test null limit and offset.  The planner would discard a simple null
0045 -- constant, so to ensure executor is exercised, do this:
0046 -- [SPARK-29650] Discard a NULL constant in LIMIT
0047 select * from int8_tbl limit (case when random() < 0.5 then bigint(null) end);
0048 -- [SPARK-28330] ANSI SQL: Top-level <result offset clause> in <query expression>
0049 -- select * from int8_tbl offset (case when random() < 0.5 then bigint(null) end);
0050
0051 -- Test assorted cases involving backwards fetch from a LIMIT plan node
0052 -- [SPARK-20965] Support PREPARE/EXECUTE/DECLARE/FETCH statements
0053 -- begin;
0054 --
0055 -- declare c1 cursor for select * from int8_tbl limit 10;
0056 -- fetch all in c1;
0057 -- fetch 1 in c1;
0058 -- fetch backward 1 in c1;
0059 -- fetch backward all in c1;
0060 -- fetch backward 1 in c1;
0061 -- fetch all in c1;
0062 --
0063 -- declare c2 cursor for select * from int8_tbl limit 3;
0064 -- fetch all in c2;
0065 -- fetch 1 in c2;
0066 -- fetch backward 1 in c2;
0067 -- fetch backward all in c2;
0068 -- fetch backward 1 in c2;
0069 -- fetch all in c2;
0070 --
0071 -- declare c3 cursor for select * from int8_tbl offset 3;
0072 -- fetch all in c3;
0073 -- fetch 1 in c3;
0074 -- fetch backward 1 in c3;
0075 -- fetch backward all in c3;
0076 -- fetch backward 1 in c3;
0077 -- fetch all in c3;
0078 --
0079 -- declare c4 cursor for select * from int8_tbl offset 10;
0080 -- fetch all in c4;
0081 -- fetch 1 in c4;
0082 -- fetch backward 1 in c4;
0083 -- fetch backward all in c4;
0084 -- fetch backward 1 in c4;
0085 -- fetch all in c4;
0086 --
0087 -- rollback;
0088
0089 DROP VIEW INT8_TBL;
0090
0091 -- Stress test for variable LIMIT in conjunction with bounded-heap sorting
0092
0093 -- [SPARK-28330] ANSI SQL: Top-level <result offset clause> in <query expression>
0094 -- SELECT
0095 --   (SELECT n
0096 --      FROM (VALUES (1)) AS x,
0097 --           (SELECT n FROM generate_series(1,10) AS n
0098 --              ORDER BY n LIMIT 1 OFFSET s-1) AS y) AS z
0099 --   FROM generate_series(1,10) AS s;
0100
0101 --
0102 -- Test behavior of volatile and set-returning functions in conjunction
0103 -- with ORDER BY and LIMIT.
0104 --
0105
0106 -- [SPARK-29631] Support ANSI SQL CREATE SEQUENCE
0107 -- create temp sequence testseq;
0108
0109 -- explain (verbose, costs off)
0110 -- select unique1, unique2, nextval('testseq')
0111 --   from tenk1 order by unique2 limit 10;
0112
0113 -- select unique1, unique2, nextval('testseq')
0114 --   from tenk1 order by unique2 limit 10;
0115
0116 -- select currval('testseq');
0117
0118 -- explain (verbose, costs off)
0119 -- select unique1, unique2, nextval('testseq')
0120 --   from tenk1 order by tenthous limit 10;
0121
0122 -- select unique1, unique2, nextval('testseq')
0123 --   from tenk1 order by tenthous limit 10;
0124
0125 -- select currval('testseq');
0126
0127 -- explain (verbose, costs off)
0128 -- select unique1, unique2, generate_series(1,10)
0129 --   from tenk1 order by unique2 limit 7;
0130
0131 -- [SPARK-27767] Built-in function: generate_series
0132 -- select unique1, unique2, generate_series(1,10)
0133 --   from tenk1 order by unique2 limit 7;
0134
0135 -- explain (verbose, costs off)
0136 -- select unique1, unique2, generate_series(1,10)
0137 --   from tenk1 order by tenthous limit 7;
0138
0139 -- [SPARK-27767] Built-in function: generate_series
0140 -- select unique1, unique2, generate_series(1,10)
0141 --   from tenk1 order by tenthous limit 7;
0142
0143 -- use of random() is to keep planner from folding the expressions together
0144 -- explain (verbose, costs off)
0145 -- select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
0146
0147 -- [SPARK-27767] Built-in function: generate_series
0148 -- select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
0149
0150 -- explain (verbose, costs off)
0151 -- select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2
0152 -- order by s2 desc;
0153
0154 -- [SPARK-27767] Built-in function: generate_series
0155 -- select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2
0156 -- order by s2 desc;
0157
0158 -- test for failure to set all aggregates' aggtranstype
0159 -- explain (verbose, costs off)
0160 -- select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
0161 --   from tenk1 group by thousand order by thousand limit 3;
0162
0163 select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
0164   from tenk1 group by thousand order by thousand limit 3;