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

Library import 241120-1113

1 files changed, 204 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/select.out b/yql/essentials/tests/postgresql/cases/select.out
new file mode 100644
index 0000000000..ddc7742c4d
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/select.out
@@ -0,0 +1,204 @@
+--
+-- SELECT
+--
+-- btree index
+-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
+--
+SELECT * FROM onek
+   WHERE onek.unique1 < 10
+   ORDER BY onek.unique1;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+       0 |     998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | KMBAAA   | OOOOxx
+       1 |     214 |   1 |    1 |   1 |      1 |       1 |        1 |           1 |         1 |        1 |   2 |    3 | BAAAAA   | GIAAAA   | OOOOxx
+       2 |     326 |   0 |    2 |   2 |      2 |       2 |        2 |           2 |         2 |        2 |   4 |    5 | CAAAAA   | OMAAAA   | OOOOxx
+       3 |     431 |   1 |    3 |   3 |      3 |       3 |        3 |           3 |         3 |        3 |   6 |    7 | DAAAAA   | PQAAAA   | VVVVxx
+       4 |     833 |   0 |    0 |   4 |      4 |       4 |        4 |           4 |         4 |        4 |   8 |    9 | EAAAAA   | BGBAAA   | HHHHxx
+       5 |     541 |   1 |    1 |   5 |      5 |       5 |        5 |           5 |         5 |        5 |  10 |   11 | FAAAAA   | VUAAAA   | HHHHxx
+       6 |     978 |   0 |    2 |   6 |      6 |       6 |        6 |           6 |         6 |        6 |  12 |   13 | GAAAAA   | QLBAAA   | OOOOxx
+       7 |     647 |   1 |    3 |   7 |      7 |       7 |        7 |           7 |         7 |        7 |  14 |   15 | HAAAAA   | XYAAAA   | VVVVxx
+       8 |     653 |   0 |    0 |   8 |      8 |       8 |        8 |           8 |         8 |        8 |  16 |   17 | IAAAAA   | DZAAAA   | HHHHxx
+       9 |      49 |   1 |    1 |   9 |      9 |       9 |        9 |           9 |         9 |        9 |  18 |   19 | JAAAAA   | XBAAAA   | HHHHxx
+(10 rows)
+
+--
+-- Test some cases involving whole-row Var referencing a subquery
+--
+select foo from (select 1 offset 0) as foo;
+ foo 
+-----
+ (1)
+(1 row)
+
+select foo from (select null offset 0) as foo;
+ foo 
+-----
+ ()
+(1 row)
+
+select foo from (select 'xyzzy',1,null offset 0) as foo;
+    foo     
+------------
+ (xyzzy,1,)
+(1 row)
+
+-- VALUES is also legal as a standalone query or a set-operation member
+VALUES (1,2), (3,4+4), (7,77.7);
+ column1 | column2 
+---------+---------
+       1 |       2
+       3 |       8
+       7 |    77.7
+(3 rows)
+
+VALUES (1,2), (3,4+4), (7,77.7)
+UNION ALL
+SELECT 2+2, 57
+UNION ALL
+TABLE int8_tbl;
+     column1      |      column2      
+------------------+-------------------
+                1 |                 2
+                3 |                 8
+                7 |              77.7
+                4 |                57
+              123 |               456
+              123 |  4567890123456789
+ 4567890123456789 |               123
+ 4567890123456789 |  4567890123456789
+ 4567890123456789 | -4567890123456789
+(9 rows)
+
+-- corner case: VALUES with no columns
+CREATE TEMP TABLE nocols();
+--
+-- Test ORDER BY options
+--
+CREATE TEMP TABLE foo (f1 int);
+INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);
+SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+ f1 
+----
+   
+   
+  1
+  3
+  7
+ 10
+ 42
+(7 rows)
+
+-- check if indexscans do the right things
+CREATE INDEX fooi ON foo (f1);
+SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+ f1 
+----
+   
+   
+  1
+  3
+  7
+ 10
+ 42
+(7 rows)
+
+CREATE INDEX fooi ON foo (f1 DESC);
+SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+ f1 
+----
+   
+   
+  1
+  3
+  7
+ 10
+ 42
+(7 rows)
+
+CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
+SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+ f1 
+----
+   
+   
+  1
+  3
+  7
+ 10
+ 42
+(7 rows)
+
+select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+     494 |      11 |   0 |    2 |   4 |     14 |       4 |       94 |          94 |       494 |      494 |   8 |    9 | ATAAAA   | LAAAAA   | VVVVxx
+(1 row)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+ unique2 
+---------
+      11
+(1 row)
+
+select * from onek2 where unique2 = 11 and stringu1 < 'B';
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+     494 |      11 |   0 |    2 |   4 |     14 |       4 |       94 |          94 |       494 |      494 |   8 |    9 | ATAAAA   | LAAAAA   | VVVVxx
+(1 row)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+ unique2 
+---------
+      11
+(1 row)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
+ unique2 
+---------
+      11
+(1 row)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
+ unique2 
+---------
+      11
+(1 row)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+ unique2 
+---------
+      11
+(1 row)
+
+select unique1, unique2 from onek2
+  where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
+ unique1 | unique2 
+---------+---------
+     494 |      11
+       0 |     998
+(2 rows)
+
+select unique1, unique2 from onek2
+  where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+ unique1 | unique2 
+---------+---------
+     494 |      11
+       0 |     998
+(2 rows)
+
+-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
+-- (see bug #5084)
+select * from (values (2),(null),(1)) v(k) where k = k order by k;
+ k 
+---
+ 1
+ 2
+(2 rows)
+
+select * from (values (2),(null),(1)) v(k) where k = k;
+ k 
+---
+ 2
+ 1
+(2 rows)
+