ydb/yql/essentials/tests/postgresql/cases/limit.out

--
-- LIMIT
-- Check the LIMIT/OFFSET feature of SELECT
--
SELECT ''::text AS two, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 50
		ORDER BY unique1 LIMIT 2;
 two | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |      51 |      76 | ZBAAAA
     |      52 |     985 | ACAAAA
(2 rows)

SELECT ''::text AS five, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 60
		ORDER BY unique1 LIMIT 5;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |      61 |     560 | JCAAAA
      |      62 |     633 | KCAAAA
      |      63 |     296 | LCAAAA
      |      64 |     479 | MCAAAA
      |      65 |      64 | NCAAAA
(5 rows)

SELECT ''::text AS two, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 60 AND unique1 < 63
		ORDER BY unique1 LIMIT 5;
 two | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |      61 |     560 | JCAAAA
     |      62 |     633 | KCAAAA
(2 rows)

SELECT ''::text AS three, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 100
		ORDER BY unique1 LIMIT 3 OFFSET 20;
 three | unique1 | unique2 | stringu1 
-------+---------+---------+----------
       |     121 |     700 | REAAAA
       |     122 |     519 | SEAAAA
       |     123 |     777 | TEAAAA
(3 rows)

SELECT ''::text AS zero, unique1, unique2, stringu1
		FROM onek WHERE unique1 < 50
		ORDER BY unique1 DESC LIMIT 8 OFFSET 99;
 zero | unique1 | unique2 | stringu1 
------+---------+---------+----------
(0 rows)

SELECT ''::text AS eleven, unique1, unique2, stringu1
		FROM onek WHERE unique1 < 50
		ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
 eleven | unique1 | unique2 | stringu1 
--------+---------+---------+----------
        |      10 |     520 | KAAAAA
        |       9 |      49 | JAAAAA
        |       8 |     653 | IAAAAA
        |       7 |     647 | HAAAAA
        |       6 |     978 | GAAAAA
        |       5 |     541 | FAAAAA
        |       4 |     833 | EAAAAA
        |       3 |     431 | DAAAAA
        |       2 |     326 | CAAAAA
        |       1 |     214 | BAAAAA
        |       0 |     998 | AAAAAA
(11 rows)

SELECT ''::text AS ten, unique1, unique2, stringu1
		FROM onek
		ORDER BY unique1 OFFSET 990;
 ten | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |     990 |     369 | CMAAAA
     |     991 |     426 | DMAAAA
     |     992 |     363 | EMAAAA
     |     993 |     661 | FMAAAA
     |     994 |     695 | GMAAAA
     |     995 |     144 | HMAAAA
     |     996 |     258 | IMAAAA
     |     997 |      21 | JMAAAA
     |     998 |     549 | KMAAAA
     |     999 |     152 | LMAAAA
(10 rows)

SELECT ''::text AS five, unique1, unique2, stringu1
		FROM onek
		ORDER BY unique1 OFFSET 990 LIMIT 5;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |     990 |     369 | CMAAAA
      |     991 |     426 | DMAAAA
      |     992 |     363 | EMAAAA
      |     993 |     661 | FMAAAA
      |     994 |     695 | GMAAAA
(5 rows)

SELECT ''::text AS five, unique1, unique2, stringu1
		FROM onek
		ORDER BY unique1 LIMIT 5 OFFSET 900;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |     900 |     913 | QIAAAA
      |     901 |     931 | RIAAAA
      |     902 |     702 | SIAAAA
      |     903 |     641 | TIAAAA
      |     904 |     793 | UIAAAA
(5 rows)

-- Test null limit and offset.  The planner would discard a simple null
-- constant, so to ensure executor is exercised, do this:
select * from int8_tbl limit (case when random() < 0.5 then null::bigint end);
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

select * from int8_tbl offset (case when random() < 0.5 then null::bigint end);
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

-- Test assorted cases involving backwards fetch from a LIMIT plan node
begin;
rollback;
select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
  from tenk1 group by thousand order by thousand limit 3;
  s1   |  s2   
-------+-------
 45000 | 45000
 45010 | 45010
 45020 | 45020
(3 rows)

SELECT  thousand
		FROM onek WHERE thousand < 5
		ORDER BY thousand FETCH FIRST 2 ROW ONLY;
 thousand 
----------
        0
        0
(2 rows)

-- SKIP LOCKED and WITH TIES are incompatible
SELECT  thousand
		FROM onek WHERE thousand < 5
		ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED;
ERROR:  SKIP LOCKED and WITH TIES options cannot be used together
-- should fail
SELECT ''::text AS two, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 50
		FETCH FIRST 2 ROW WITH TIES;
ERROR:  WITH TIES cannot be specified without ORDER BY clause
-- leave these views