Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

1 files changed, 164 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/limit.out b/yql/essentials/tests/postgresql/cases/limit.out
new file mode 100644
index 0000000000..90a448a1b8
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/limit.out
@@ -0,0 +1,164 @@
+--
+-- 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